PostgreSQL оптимизация для highload: индексы, query planner и партиционирование

PostgreSQL в production: реальные проблемы

PostgreSQL — мощнейшая реляционная СУБД, но без правильной настройки она может стать узким местом. Разберём практические техники оптимизации для систем с нагрузкой от 1000 RPS.

EXPLAIN ANALYZE — ваш главный инструмент

Прежде чем оптимизировать, нужно понять, что происходит:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.*, s.tier 
FROM users u
JOIN subscriptions s ON s.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
ORDER BY u.created_at DESC
LIMIT 100;

Что искать в плане:

• Seq Scan на большой таблице → нужен индекс
• Hash Join vs Nested Loop → зависит от размера данных
• Высокий actual time → узкое место
• Rows Removed by Filter >> возвращаемых строк → плохая селективность

Индексы: выбрать правильный тип

B-tree (по умолчанию)

-- Для точного поиска и диапазонов
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);

-- Составной индекс — порядок важен!
CREATE INDEX idx_usage_user_date ON usage_logs(user_id, created_at DESC);

Partial Index — индекс только по части данных

-- Только активные подписки (не индексируем отменённые)
CREATE INDEX idx_subs_active ON subscriptions(user_id)
WHERE status = 'active';

-- Запрос автоматически использует этот индекс:
SELECT * FROM subscriptions WHERE user_id = $1 AND status = 'active';

GIN — для массивов и JSONB

-- Для поиска по тегам
CREATE INDEX idx_articles_tags ON blog_articles USING GIN(tags);

-- Запрос:
SELECT * FROM blog_articles WHERE tags @> ARRAY['AI', 'Python'];

BRIN — для временных серий

-- Для огромных таблиц с монотонно растущими данными (логи)
CREATE INDEX idx_logs_created_brin ON usage_logs USING BRIN(created_at);
-- Занимает в 100x меньше места чем B-tree

Партиционирование таблиц

При таблицах > 100M строк партиционирование критично:

-- Партиционирование usage_logs по месяцам
CREATE TABLE usage_logs (
  id UUID DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  cost_usd DECIMAL(10,6)
) PARTITION BY RANGE (created_at);

-- Создаём партиции
CREATE TABLE usage_logs_2026_04 PARTITION OF usage_logs
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE usage_logs_2026_05 PARTITION OF usage_logs
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

PostgreSQL автоматически маршрутизирует запросы в нужную партицию.

Connection Pooling с PgBouncer

Каждое соединение с PostgreSQL — это процесс (~5MB RAM). При 1000 пользователях одновременно прямые соединения убьют сервер.

# /etc/pgbouncer/pgbouncer.ini
[databases]
aichat_db = host=localhost dbname=aichat_db

[pgbouncer]
pool_mode = transaction      # Лучший режим для веб-приложений
max_client_conn = 1000       # Клиентские соединения
default_pool_size = 20       # Реальные соединения с PostgreSQL
reserve_pool_size = 5

В приложении подключаетесь к PgBouncer (порт 6432), не напрямую к PostgreSQL.

Настройка postgresql.conf

# Память (для 8GB RAM сервера)
shared_buffers = 2GB              # 25% RAM
effective_cache_size = 6GB        # 75% RAM
work_mem = 64MB                   # Для сортировок и хешей
maintenance_work_mem = 512MB      # Для VACUUM, CREATE INDEX

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9

# Параллелизм
max_parallel_workers_per_gather = 4
max_worker_processes = 8

Мониторинг

-- Медленные запросы (нужен pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

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

-- Bloat таблиц (нужен VACUUM)
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Заключение

PostgreSQL справляется с огромными нагрузками при правильной настройке. Начните с EXPLAIN ANALYZE, добавляйте индексы точечно, настройте PgBouncer — и большинство проблем производительности решатся без смены БД.