PostgreSQL: тюнинг для продакшена
PostgreSQL из коробки настроен консервативно. Дефолтные параметры рассчитаны на то, чтобы база запустилась на любом железе — хоть на Raspberry Pi с 1 ГБ RAM. Если вы деплоите PostgreSQL на продакшен-сервере с 16-64 ГБ памяти и SSD-дисками, а конфиг не трогали — вы используете от силы 10% возможностей.
За последние три года я оптимизировал PostgreSQL на десятке проектов: от небольших API-сервисов до баз с сотнями гигабайт данных. В этой статье собрал практические рекомендации по тюнингу, которые дают реальный результат.
postgresql.conf: параметры памяти
Начнём с самого важного — распределения памяти. Три ключевых параметра определяют, как PostgreSQL использует RAM.
shared_buffers
Это основной буферный кэш PostgreSQL — область разделяемой памяти, где хранятся страницы данных. Чтение из shared_buffers на порядки быстрее, чем с диска.
Правило: 25% от общего объёма RAM сервера. Больше 40% ставить не имеет смысла — PostgreSQL также полагается на кэш файловой системы (page cache ОС), и забирая всю память под shared_buffers, вы лишаете ОС возможности кэшировать файлы.
# Для сервера с 32 ГБ RAM
shared_buffers = 8GB
# Для сервера с 16 ГБ RAM
shared_buffers = 4GB
# Для VPS с 4 ГБ RAM
shared_buffers = 1GBПосле изменения shared_buffers требуется перезапуск PostgreSQL.
work_mem
Память, выделяемая на каждую операцию сортировки, хэширования или объединения в рамках одного запроса. Один сложный запрос с несколькими JOIN и ORDER BY может использовать work_mem многократно.
Формула для расчёта: (Total RAM - shared_buffers) / (max_connections * 2-3)
# Для сервера с 32 ГБ, 200 соединений:
# (32GB - 8GB) / (200 * 3) = ~40MB
work_mem = 40MB
# Для VPS с 4 ГБ, 50 соединений:
# (4GB - 1GB) / (50 * 3) = ~20MB
work_mem = 20MBОсторожно: если work_mem слишком большой, при всплеске нагрузки (много параллельных сложных запросов) сервер может уйти в OOM. Лучше начать с консервативного значения и увеличивать по необходимости.
Можно задавать work_mem на уровне отдельной сессии для тяжёлых аналитических запросов:
SET work_mem = '256MB';
-- Тяжёлый аналитический запрос
SELECT ... FROM large_table GROUP BY ... ORDER BY ...;
RESET work_mem;effective_cache_size
Это не выделение памяти — это подсказка планировщику запросов о том, сколько памяти доступно для кэширования (shared_buffers + page cache ОС). Планировщик использует это значение для выбора между index scan и sequential scan.
Правило: 50-75% от общего объёма RAM.
# Для сервера с 32 ГБ
effective_cache_size = 24GB
# Для VPS с 4 ГБ
effective_cache_size = 3GBНе требует перезапуска, применяется через SELECT pg_reload_conf(); или systemctl reload postgresql.
Остальные важные параметры
# Память для операций обслуживания (VACUUM, CREATE INDEX, ALTER TABLE)
# Можно ставить значительно больше work_mem — эти операции не параллелятся
maintenance_work_mem = 2GB
# WAL (Write-Ahead Log) — буферы журналирования
wal_buffers = 64MB
# Checkpoint: как часто PostgreSQL сбрасывает данные на диск
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
# Планировщик: стоимость случайного чтения с диска
# Для SSD значение должно быть ниже, чем дефолтное 4.0
random_page_cost = 1.1 # SSD
# random_page_cost = 4.0 # HDD (по умолчанию)
# Количество параллельных воркеров для запросов
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8EXPLAIN ANALYZE: понимаем, что делает запрос
Прежде чем оптимизировать запросы, нужно понять, как PostgreSQL их выполняет. EXPLAIN ANALYZE — ваш главный инструмент.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 20;Пример вывода:
Limit (cost=1543.21..1543.26 rows=20 width=44) (actual time=245.123..245.130 rows=20 loops=1)
Buffers: shared hit=892 read=2341
-> Sort (cost=1543.21..1567.32 rows=9644 width=44) (actual time=245.120..245.125 rows=20 loops=1)
Sort Key: (count(o.id)) DESC
Sort Method: top-N heapsort Memory: 27kB
-> HashAggregate (cost=1287.45..1383.89 rows=9644 width=44) (actual time=234.567..240.123 rows=9644 loops=1)
-> Hash Right Join (cost=412.33..1190.22 rows=19456 width=40) (actual time=12.345..189.012 rows=19456 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..621.56 rows=30156 width=8) (actual time=0.012..67.890 rows=30156 loops=1)
-> Hash (cost=287.44..287.44 rows=9991 width=36) (actual time=12.234..12.234 rows=9644 loops=1)
-> Seq Scan on users u (cost=0.00..287.44 rows=9991 width=36) (actual time=0.023..8.456 rows=9644 loops=1)
Filter: (created_at >= '2025-01-01')
Rows Removed by Filter: 15356
Planning Time: 0.234 ms
Execution Time: 245.456 msНа что обращать внимание:
- Seq Scan на большой таблице с фильтрацией — кандидат на индекс.
- shared read в Buffers — данные читались с диска, а не из кэша. Если значение большое, возможно, не хватает shared_buffers или кэша ОС.
- actual time значительно больше cost — планировщик ошибается в оценке. Обновите статистику:
ANALYZE table_name. - Rows Removed by Filter — если удаляется большая доля строк, нужен индекс.
Индексы: B-tree, GIN, GiST
Правильные индексы — это основа производительности. Но лишние индексы замедляют запись и занимают место. Каждый индекс должен быть обоснован.
B-tree (по умолчанию)
Подходит для операций сравнения: =, !=, больше, меньше, BETWEEN, IN, IS NULL. Это 90% случаев.
-- Индекс для фильтрации по дате
CREATE INDEX idx_orders_created_at ON orders (created_at);
-- Составной индекс: порядок колонок ВАЖЕН
-- Этот индекс работает для WHERE user_id = X AND status = Y
-- и для WHERE user_id = X (без status)
-- но НЕ работает для WHERE status = Y (без user_id)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Частичный индекс: только для активных заказов
-- Занимает меньше места, работает быстрее
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE status = 'active';
-- Covering index: включаем данные прямо в индекс
-- PostgreSQL может ответить на запрос, не обращаясь к таблице (Index Only Scan)
CREATE INDEX idx_orders_covering ON orders (user_id) INCLUDE (total_amount, status);GIN (Generalized Inverted Index)
Для полнотекстового поиска, массивов и JSONB:
-- Полнотекстовый поиск
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('russian', title || ' ' || body));
-- Поиск по JSONB
CREATE INDEX idx_events_data ON events USING GIN (data);
-- Поиск по jsonb с определёнными операциями
CREATE INDEX idx_events_data_path ON events USING GIN (data jsonb_path_ops);GIN-индексы медленнее обновляются, но быстрее ищут. Подходят для колонок, которые чаще читаются, чем пишутся.
GiST (Generalized Search Tree)
Для геоданных (PostGIS), диапазонов и нечёткого поиска:
-- Геопоиск (PostGIS)
CREATE INDEX idx_locations_geo ON locations USING GiST (coordinates);
-- Поиск по диапазонам дат
CREATE INDEX idx_bookings_period ON bookings USING GiST (daterange(check_in, check_out));
-- Поиск похожих строк (pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GiST (name gist_trgm_ops);Поиск неиспользуемых индексов
Индексы, которые не используются, нужно удалять — они замедляют INSERT/UPDATE/DELETE и занимают место:
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u') -- исключаем PK и UNIQUE constraints
)
ORDER BY pg_relation_size(indexrelid) DESC;
VACUUM и autovacuum
PostgreSQL использует MVCC (Multi-Version Concurrency Control): при обновлении строки старая версия не удаляется, а помечается как «мёртвая». VACUUM очищает эти мёртвые строки и возвращает пространство для переиспользования.
Почему VACUUM критически важен
Без VACUUM:
- Таблица и индексы растут бесконтрольно (table bloat).
- Запросы замедляются — приходится сканировать мёртвые строки.
- Transaction ID wraparound — при исчерпании 32-битного счётчика транзакций PostgreSQL принудительно остановится. Это не теоретическая проблема — я видел это в продакшене.
Настройка autovacuum
Autovacuum работает по умолчанию, но его параметры часто нужно тюнить:
# Как часто autovacuum проверяет таблицы (мс)
autovacuum_naptime = 30s # дефолт: 1min
# Порог запуска: мёртвых строк больше, чем
# autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * кол-во строк
autovacuum_vacuum_threshold = 50 # дефолт: 50
autovacuum_vacuum_scale_factor = 0.05 # дефолт: 0.2 (20%)
# Для таблицы в 1 млн строк:
# Дефолт: vacuum запустится после 200 050 мёртвых строк (20%)
# Тюнинг: vacuum запустится после 50 050 мёртвых строк (5%)
# Количество параллельных воркеров autovacuum
autovacuum_max_workers = 4 # дефолт: 3
# Ограничение нагрузки autovacuum на I/O
autovacuum_vacuum_cost_limit = 1000 # дефолт: -1 (используется vacuum_cost_limit = 200)Для «горячих» таблиц с интенсивной записью можно задать параметры индивидуально:
ALTER TABLE hot_events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 100,
autovacuum_analyze_scale_factor = 0.005
);Мониторинг VACUUM
-- Последний запуск VACUUM и ANALYZE по таблицам
SELECT
schemaname,
relname,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
n_dead_tup,
n_live_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END as dead_ratio_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;Если dead_ratio_pct для таблицы стабильно больше 10-20% — autovacuum не справляется. Увеличьте autovacuum_vacuum_cost_limit или уменьшите autovacuum_vacuum_scale_factor.
pg_stat_statements: мониторинг запросов
Расширение pg_stat_statements — обязательный инструмент для любого продакшена. Оно собирает статистику по всем выполненным запросам: время, количество вызовов, блокировки, I/O.
-- Включение расширения
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;В postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = allПосле перезапуска PostgreSQL:
-- Топ-20 самых медленных запросов (по суммарному времени)
SELECT
calls,
round(total_exec_time::numeric, 2) as total_time_ms,
round(mean_exec_time::numeric, 2) as avg_time_ms,
round(stddev_exec_time::numeric, 2) as stddev_ms,
rows,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- Запросы с наибольшим количеством чтений с диска
SELECT
calls,
shared_blks_hit,
shared_blks_read,
round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) as cache_hit_pct,
query
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;Целевой показатель: cache hit ratio больше 99% для OLTP-нагрузки. Если меньше — не хватает памяти или неоптимальные запросы сканируют слишком много данных.
-- Общий cache hit ratio по базе
SELECT
sum(blks_hit) as cache_hits,
sum(blks_read) as disk_reads,
round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) as cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();Периодически сбрасывайте статистику, чтобы видеть картину за определённый период:
SELECT pg_stat_statements_reset();Connection pooling с PgBouncer
PostgreSQL создаёт отдельный процесс (fork) на каждое соединение. При 500+ соединениях это становится проблемой: каждый процесс потребляет ~5-10 МБ RAM, context switching нагружает CPU.
PgBouncer решает эту проблему, работая как прокси между приложением и PostgreSQL:
; /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Режим пулинга
pool_mode = transaction
; Лимиты соединений
max_client_conn = 1000 ; максимум клиентских соединений к PgBouncer
default_pool_size = 25 ; соединений к PostgreSQL на базу/пользователя
min_pool_size = 5 ; минимум соединений в пуле
reserve_pool_size = 5 ; резерв для пиковой нагрузки
reserve_pool_timeout = 3 ; через сколько секунд использовать резерв
; Таймауты
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 30
; Логирование
log_connections = 0
log_disconnections = 0
stats_period = 60Файл аутентификации /etc/pgbouncer/userlist.txt:
"myapp_user" "md5hash_of_password"Хэш можно получить так:
SELECT 'md5' || md5('password' || 'myapp_user');Три режима пулинга:
- session — соединение привязано к клиенту до отключения. Безопасно, но экономит мало ресурсов.
- transaction — соединение возвращается в пул после завершения транзакции. Лучший баланс для большинства приложений.
- statement — соединение возвращается после каждого запроса. Максимальная эффективность, но не работает с multi-statement транзакциями и prepared statements.
Запуск и мониторинг:
# Запуск PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer
# Подключение к консоли администрирования
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
# В консоли PgBouncer:
SHOW STATS;
SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;После настройки PgBouncer приложение подключается к порту 6432 вместо 5432. PostgreSQL max_connections можно снизить до 50-100 (вместо дефолтных 100), а PgBouncer обработает тысячи клиентских соединений.
Бэкапы: pg_dump и pg_basebackup
Без бэкапов всё остальное не имеет смысла. Два основных инструмента:
pg_dump — логический бэкап
Создаёт SQL-дамп или архив базы. Подходит для баз до нескольких сотен гигабайт.
# Дамп в custom format (сжатый, поддерживает параллельный restore)
pg_dump -Fc -Z 6 -j 4 -f /backups/myapp_$(date +%Y%m%d_%H%M%S).dump myapp
# -Fc: custom format
# -Z 6: уровень сжатия (0-9)
# -j 4: параллельный дамп (4 потока)
# Восстановление
pg_restore -d myapp -j 4 --clean --if-exists /backups/myapp_20250604.dumpСкрипт для автоматических бэкапов через cron:
#!/bin/bash
# /opt/scripts/pg_backup.sh
BACKUP_DIR="/backups/postgresql"
RETENTION_DAYS=14
DB_NAME="myapp"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
# Создаём бэкап
pg_dump -Fc -Z 6 -j 4 -f "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" "$DB_NAME" 2>&1
if [ $? -eq 0 ]; then
echo "Backup completed: ${DB_NAME}_${TIMESTAMP}.dump"
# Удаляем старые бэкапы
find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
else
echo "ERROR: Backup failed!" >&2
exit 1
fi# Crontab: ежедневно в 3:00
0 3 * * * /opt/scripts/pg_backup.sh >> /var/log/pg_backup.log 2>&1pg_basebackup — физический бэкап
Копирует весь кластер PostgreSQL на уровне файлов. Быстрее для больших баз, необходим для настройки репликации и Point-in-Time Recovery (PITR).
# Полный бэкап кластера
pg_basebackup -D /backups/base_$(date +%Y%m%d)
-Ft -z -Xs
-P -v
-h localhost -U replicator
# -Ft: формат tar
# -z: gzip-сжатие
# -Xs: стриминг WAL во время бэкапа (консистентность без архивирования WAL)
# -P: прогресс-барДля PITR (восстановление на произвольный момент времени) нужно настроить архивирование WAL:
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /backups/wal_archive/%f'Восстановление на конкретный момент:
# В recovery.conf (PostgreSQL до 12) или postgresql.conf (12+)
restore_command = 'cp /backups/wal_archive/%f %p'
recovery_target_time = '2025-06-04 14:30:00'
recovery_target_action = 'promote'Чек-лист для продакшена
Сводка параметров для сервера с 32 ГБ RAM и SSD (адаптируйте под своё железо):
# Память
shared_buffers = 8GB
work_mem = 40MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB
wal_buffers = 64MB
# Диск и WAL
random_page_cost = 1.1
effective_io_concurrency = 200
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
# Параллелизм
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8
# Autovacuum
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025
autovacuum_vacuum_cost_limit = 1000
autovacuum_max_workers = 4
# Мониторинг
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
# Логирование медленных запросов
log_min_duration_statement = 500
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0Итог
Оптимизация PostgreSQL — это не одноразовая настройка, а непрерывный процесс. Начните с правильных параметров памяти и дисковой подсистемы, включите pg_stat_statements для мониторинга, настройте autovacuum под вашу нагрузку. Используйте EXPLAIN ANALYZE для каждого медленного запроса. Поставьте PgBouncer, если соединений больше ста. И обязательно — обязательно — настройте бэкапы до того, как они понадобятся. PostgreSQL отблагодарит вас стабильной работой и предсказуемой производительностью.