Как оптимизировать поиск данных в PostgreSQL

# Оптимизация поиска данных в PostgreSQL

1. Индексы - основной инструмент оптимизации

B-Tree индекс (по умолчанию)

Используется для простого поиска и сортировки.

-- Одиночный индекс
CREATE INDEX idx_user_email ON users(email);

-- Композитный индекс (для WHERE с несколькими условиями)
CREATE INDEX idx_user_status_created ON users(status, created_at DESC);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_user_email_unique ON users(email);

-- Частичный индекс (только активные пользователи)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Проверить использование индекса
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

GIN индекс (для полнотекстового поиска)

Для поиска по JSON, массивам, full-text search.

ALTER TABLE users ADD COLUMN tags TEXT[];

-- GIN индекс для массивов
CREATE INDEX idx_user_tags ON users USING gin(tags);

-- Поиск
SELECT * FROM users WHERE tags @> ARRAY['premium'];

-- Full-text search индекс
CREATE INDEX idx_user_search ON users 
USING gin(to_tsvector('russian', name || ' ' || email));

-- Поиск
SELECT * FROM users 
WHERE to_tsvector('russian', name || ' ' || email) 
      @@ plainto_tsquery('russian', 'иван');

BRIN индекс (для больших таблиц)

Менее затратный чем B-Tree, хорош для больших таблиц с временным порядком.

-- Для очень больших таблиц (миллионы строк)
CREATE INDEX idx_orders_created_brin 
ON orders USING brin(created_at);

-- Занимает намного меньше места
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

2. EXPLAIN ANALYZE - диагностика запросов

-- Базовый EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- С подробной статистикой
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM users WHERE email = 'test@example.com';

-- Анализируем запрос с JOIN'om
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id
ORDER BY post_count DESC
LIMIT 10;

-- Результаты анализируют на:
-- - Seq Scan vs Index Scan
-- - Rows: estimated vs actual
-- - Filter: условия, применяемые после сканирования
-- - Buffers: попадания в cache

3. Оптимизация WHERE условий

-- Плохо: функция на колонке - индекс не использует
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Хорошо: индекс на нижний регистр
CREATE INDEX idx_user_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Плохо: OR условие
SELECT * FROM users WHERE email = 'test1@example.com' OR email = 'test2@example.com';

-- Хорошо: IN
SELECT * FROM users WHERE email IN ('test1@example.com', 'test2@example.com');

-- Плохо: NOT IN может быть медленнее с большим набором
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

-- Хорошо: NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM banned_users WHERE user_id = u.id);

-- Плохо: LIKE с wildcard в начале
SELECT * FROM users WHERE name LIKE '%ivan%';

-- Хорошо: Full-text search
SELECT * FROM users 
WHERE to_tsvector(name) @@ plainto_tsquery('ivan');

4. Оптимизация JOIN'ов

-- Убедись что есть индексы на внешние ключи
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- Хороший запрос (используются индексы)
SELECT u.id, u.name, COUNT(p.id) as post_count
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id;

-- Плохо: функция на внешнем ключе
SELECT * FROM posts 
WHERE user_id::text = '123';

-- Хорошо
SELECT * FROM posts WHERE user_id = 123;

5. Пагинация - оптимизация LIMIT/OFFSET

-- Плохо: большие OFFSET медленнее
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 1000000;

-- Хорошо: keyset pagination
SELECT * FROM posts 
WHERE created_at < (SELECT created_at FROM posts ORDER BY id LIMIT 1 OFFSET 1000000)
ORDER BY created_at DESC LIMIT 10;

-- Или с cursor
SELECT * FROM posts 
WHERE id < :last_post_id
ORDER BY id DESC LIMIT 10;

6. Connection Pooling из Java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class PostgreSQLConfig {
    public static HikariDataSource createDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("postgres");
        config.setPassword("password");
        
        // Оптимизация для поиска
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(10000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        
        // Для больших результатов
        config.addDataSourceProperty("defaultFetchSize", "1000");
        config.addDataSourceProperty("preparedStatementCacheSize", "250");
        config.addDataSourceProperty("preparedStatementCacheSqlLimit", "2048");
        
        return new HikariDataSource(config);
    }
}

7. Батчинг для bulk операций

@Repository
public class UserRepository {
    @PersistenceContext
    private EntityManager em;
    
    public void batchInsertUsers(List<User> users) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        
        try (PreparedStatement pstmt = 
             em.unwrap(Connection.class).prepareStatement(sql)) {
            
            int count = 0;
            for (User user : users) {
                pstmt.setString(1, user.getName());
                pstmt.setString(2, user.getEmail());
                pstmt.addBatch();
                
                if (++count % 1000 == 0) {
                    pstmt.executeBatch();
                    em.flush();
                }
            }
            pstmt.executeBatch();
        }
    }
}

// Spring Data JPA
@Configuration
public class JpaConfig {
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource ds) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(ds);
        
        Properties props = new Properties();
        props.setProperty("hibernate.jdbc.batch_size", "20");
        props.setProperty("hibernate.order_inserts", "true");
        props.setProperty("hibernate.order_updates", "true");
        em.setJpaProperties(props);
        
        return em;
    }
}

8. Query кэширование

@Service
@CacheConfig(cacheNames = "users")
public class UserService {
    @Cacheable(key = "#email")
    public User getUserByEmail(String email) {
        // Долгий запрос
        return userRepository.findByEmail(email).orElse(null);
    }
    
    @CacheEvict(key = "#user.email")
    public void updateUser(User user) {
        userRepository.save(user);
    }
}

// Redis кэширование
@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public CacheManager cacheManager(RedisConnectionFactory factory) {
        return new RedisCacheManager.create(factory);
    }
}

9. Статистика таблиц

-- Информация о размере таблиц
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    n_live_tup as rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Индексы и их размеры
SELECT 
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan as scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Неиспользуемые индексы
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

10. Настройки PostgreSQL для оптимизации

-- postgresql.conf
# Для большей памяти
shared_buffers = 4GB              # 25% RAM для servers с 32GB+
effective_cache_size = 12GB       # 75% RAM
work_mem = 10MB                   # RAM/max_connections

# Для быстрых запросов
random_page_cost = 1.1            # для SSD
effective_io_concurrency = 200    # для SSD

# Для масштабирования
max_connections = 200
max_wal_size = 4GB
checkpoint_completion_target = 0.9

# Запуск автовакуума
auto_vacuum = on
auto_vacuum_naptime = '10s'

Best Practices

1. Индексируй все колонки в WHERE и JOIN'е - это 80% оптимизации
2. Используй EXPLAIN ANALYZE регулярно - мониторь производительность
3. Выбирай правильный тип индекса - B-Tree для общего случая, GIN для FTS
4. Избегай функций на индексируемых колонках - или создай индекс на функцию
5. Батчируй bulk операции - 1000 строк за раз
6. Кэшируй результаты - Redis для часто используемых данных
7. Пагинируй с keyset курсором - не используй большие OFFSET
8. Профилируй slow queries - логируй queries медленнее 100ms
9. Анализируй статистику таблиц - знай размеры и количество строк
10. Регулярно VACUUM и ANALYZE - поддерживай статистику БД