PostgreSQL performance tuning 2026

Практическое руководство по ускорению PostgreSQL в 2025–2026 годах с конкретными командами, настройками и примерными числами для серверов от 16 до 512 ГБ оперативной памяти. Приводятся готовые конфигурации shared_buffers, work_mem, autovacuum, примеры партиционирования и пошаговые действия при replication lag.

• Включите расширение и соберите топ запросов по времени и вызовам (Postgres 14–16, 2025):

  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  -- затем в psql
  SELECT query, calls, total_time, mean_time
  FROM pg_stat_statements
  ORDER BY total_time DESC
  LIMIT 20;

• Запустите EXPLAIN ANALYZE для одной медленной инструкции (пример):

  EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
  SELECT u.id, u.name, o.amount
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE u.created_at > '2025-01-01'
  ORDER BY o.amount DESC
  LIMIT 100;

• Проверьте блокировки и ожидания:

  SELECT pid, usename, wait_event_type, wait_event, query_start, state, query
  FROM pg_stat_activity
  WHERE state != 'idle'
  ORDER BY query_start DESC
  LIMIT 50;

• Соберите I/O-снимок (пример для Linux):

  iostat -x 5 12
  vmstat 5 12

• Сделайте нагрузочное тестирование локально для воспроизводимости: pgbench 14+ (пример 2025):

  pgbench -c 100 -j 8 -T 300 -S postgres
  # -c clients, -j jobs, -T seconds, -S select-only

Шаг 1: настройка shared_buffers

shared_buffers — ключевой параметр для работы PostgreSQL с буферами. Неправильная установка приводит к непродуктивным I/O и потерям производительности на NVMe/SSD.

1. Правило руки 2025–2026: выделяйте 15–40% оперативной памяти на выделенный сервер базы данных, но не менее 512 МБ и не более 25–32 ГБ на старых версиях для оптимальной работы. Для PostgreSQL 14–16 и ОС Linux с современными ядрами задавайте:

   # Пример для сервера с 64GB RAM
   shared_buffers = '16GB'  # 25% от 64GB
   effective_cache_size = '48GB'  # ~75% от RAM, учитывая OS page cache

2. Если сервер имеет 256–512 ГБ RAM и ожидание больших снэпшотов, увеличьте shared_buffers до 32–64 ГБ, но тестируйте: большие shared_buffers могут усложнить checkpoint и recovery. На NVMe иногда лучше меньше, чтобы оставить место ОС на кэш — 20–30% RAM.
3. Проверка после изменения: перезапустите Postgres и посмотрите на показатели буферов и checkpoints

   SELECT name, setting FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size');
   SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint FROM pg_stat_bgwriter;

   Если количество buffers_checkpoint растёт сверх 1000 в час для нагрузки OLTP, уменьшите checkpoint_timeout или перенастройте wal_size.

Шаг 2: work_mem и connection pool

work_mem влияет на память для сортировок и хеш-таблиц в одном процессе. Неправильная оценка work_mem приводит к OOM или к слишком частому использованию диска для временных файлов.

1. Рассчитываем безопасное значение:
   • Определите физическую RAM и вычтите shared_buffers и OS: free_ram = total_ram - shared_buffers - reserved_os (например 2–4 ГБ).
   • Оцените одновременно выполняющиеся операции, создающие сортировки/хеши. Если вы используете connection pooler типа PgBouncer в режиме session/transaction, то число одновременных backend-подключений меньше реального числа клиентов.
   Пример расчёта для 64GB RAM, shared_buffers=16GB, reserve OS=4GB, ожидаемых 200 одновременных бэкендов: free_ram = 64-16-4 = 44GB; допустимый work_mem\_aggregate = 44GB/200 = 0.22GB ≈ 220MB. Установите conservative work_mem = 64–128MB для OLTP и 512MB–2GB для аналитики.
2. Рекомендуемые практики 2026 по pooler-ам:
   • PgBouncer в режиме transaction: max_client_conn = 500, default_pool_size = 100, reserve_pool_size = 20 для 64 ГБ сервера с 100 backend slots.
   • pgpool-II используйте для репликации/фейловеров, но не для простого пула соединений; он тяжелее по ресурсам.
   Пример конфигурации PgBouncer (fragment):

   [databases]
   postgres = host=127.0.0.1 port=5432 dbname=postgres
   
   [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
   default_pool_size = 100
   reserve_pool_size = 20
   reserve_pool_timeout = 5

3. Мониторьте temp files и temp bytes: если после увеличения work_mem вы видите рост temp_files, значит запросы стали использовать диск для сортировок. Команда для проверки:

   SELECT datname, sum(temp_files) AS temp_files, sum(temp_bytes) AS temp_bytes
   FROM pg_stat_database
   GROUP BY datname;

Шаг 3: vacuum и autovacuum

Разумная настройка VACUUM и autovacuum критична для предотвращения bloat и удержания индексов эффективными. По состоянию на 2025–2026 активнее появляются большие OLTP-базы с быстрым обновлением — autovacuum требует корректировки.

1. Базовые настройки в postgresql.conf (пример для 2026):

   autovacuum = on
   autovacuum_max_workers = 10   # стандарт 3 -> увеличиваем для больших кластеров
   autovacuum_naptime = 10       # проверять каждые 10 секунд
   autovacuum_vacuum_threshold = 50
   autovacuum_vacuum_scale_factor = 0.02  # 2% для горячих таблиц
   autovacuum_analyze_scale_factor = 0.01
   autovacuum_freeze_max_age = 200000000

2. Увеличьте maintenance_work_mem для больших VACUUM и CREATE INDEX: например 1–4 ГБ в зависимости от RAM. На сервере с 64 ГБ можно поставить 2GB.

   maintenance_work_mem = '2GB'

3. Определите bloat и при необходимости используйте pg_repack или VACUUM FULL. Пример запроса для оценки bloat (простая версия):

   SELECT schemaname, tablename, reltuples::bigint AS rows, pg_total_relation_size(relid) AS total_bytes
   FROM pg_catalog.pg_stat_user_tables
   ORDER BY total_bytes DESC
   LIMIT 20;

4. Запланируйте регулярные ручные VACUUM для больших таблиц ночью; для онлайн-операций используйте pg_repack (поддерживает 2021–2026). Стоимость: pg_repack на таблице 200 ГБ обычно выполняется за 30–180 минут в зависимости от I/O и CPU.
5. Мониторинг autovacuum: смотрите pg_stat_user_tables: n_dead_tup, n_live_tup и last_autovacuum. Пример запроса:

   SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
   FROM pg_stat_user_tables
   ORDER BY n_dead_tup DESC
   LIMIT 50;

   Если n_dead_tup растёт быстро (>1M в час), уменьшите autovacuum_vacuum_scale_factor для этой таблицы или создайте cron job для pg_repack.

Шаг 4: partitioning

Партиционирование уменьшает объем сканируемых данных, ускоряет очистку старых данных и улучшает параллелизм для больших таблиц. С 2024–2026 партиционирование стало стандартной практикой для логов, метрик и таблиц с временными данными.

1. Выбор типа партиционирования: range по timestamp — для временных рядов; hash — для равномерного распределения по key; list — для ограниченных множеств.
2. Пример: месяц/день партиционирования для таблицы orders, с автоматическим созданием партиций за 24 месяца (Postgres 11+):

   CREATE TABLE orders (
     id bigserial PRIMARY KEY,
     user_id bigint NOT NULL,
     amount numeric(12,2),
     created_at timestamptz NOT NULL
   ) PARTITION BY RANGE (created_at);
   
   -- создаём партиции по месяцам
   CREATE TABLE orders_2025_01 PARTITION OF orders
   FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
   -- можно автоматизировать создание партиций скриптом на cron

3. Индексы: создавайте локальные индексы на каждой партиции. Для глобальных уникальных ограничений используйте уникальные индексные стратегии (Postgres 11+ поддерживает уникальные индексы при наличии колонок партиционирования).
4. Преимущества на практике: на таблице 1 TB с историей 5 лет партиционирование ежемесячно снизило среднее время запросов выборки по диапазону на 7–15x и упростило архивирование (DROP PARTITION освобождало место за 1–2 секунды вместо VACUUM FULL на 1 TB).
5. Автоматизация: пишите job-скрипт, который ежемесячно добавляет новые партиции и архивирует старые в объектное хранилище или отдельный сервер. Пример cron-скрипта, запускаемого 1-го числа в 00:05:

   # /usr/local/bin/create_monthly_partition.sh
   psql -d mydb -c "DO $$ BEGIN
     EXECUTE format('CREATE TABLE IF NOT EXISTS orders_%s PARTITION OF orders FOR VALUES FROM (''%s-01'') TO (''%s-01'')', to_char(now(),'YYYY_MM'), to_char(now(),'YYYY-MM'), to_char(now()+interval '1 month','YYYY-MM'));
   END $$;"

Шаг 5: оптимизация индексов

Индексы ускоряют выборки, но замедляют вставки/обновления и увеличивают расход дискового пространства. Оптимизация индексного набора часто даёт больший эффект, чем изменение параметров конфигурации.

1. Проверьте неиспользуемые индексы: используйте pg_stat_user_indexes и pg_stat_user_tables. Пример запроса:

   SELECT schemaname, relname, indexrelname, idx_scan
   FROM pg_stat_user_indexes
   WHERE idx_scan = 0
   ORDER BY pg_total_relation_size(indexrelid) DESC
   LIMIT 50;

   Если idx_scan = 0 в течение нескольких месяцев, возможно индекс можно удалить.
2. Переиндексыруйте большие таблицы во внепиковое время: REINDEX CONCURRENTLY для Postgres 12+. Пример: REINDEX INDEX CONCURRENTLY idx_orders_user_id;
3. Составные индексы: создавайте их по порядку, соответствующему WHERE/ORDER BY. Если часто выполняется ORDER BY created_at DESC LIMIT N, индекс по (created_at DESC) поможет.
4. Используйте covering indexes (include columns) для предотвращения доступа к heap: CREATE INDEX idx ON table (col1) INCLUDE (col2, col3). Это экономит I/O.
5. Мониторинг размера индекса: pg_relation_size(index) и сравнение с таблицей. Благоразумный порог: индекс > 30% размера таблицы требует проверки целесообразности.

Какие метрики мониторить?

Наблюдение должно быть многослойным: база, ОС, сеть, диски. Собирайте метрики в Prometheus/Grafana или аналог. Привожу список ключевых метрик с порогами и причинами тревоги.

• cache_hit_ratio (pg_stat_database blks_hit/(blks_hit+blks_read)) — стремитесь к >95% для OLTP; если <90% — увеличить shared_buffers/effective_cache_size или снизить random_page_cost.
• checkpoint duration и frequency (pg_stat_bgwriter.checkpoints_timed, checkpoints_req, checkpoints_timed) — длительные чекпойнты >5–10 секунд указывают на неправильно выставленный checkpoint_timeout или слишком маленький wal_size. Цель: держать duration <5s и избегать too-frequent checkpoints.
• wal_files/archiving backlog — если много накопленных WAL-архивов, увеличьте max_wal_size или проверьте скорость архивации. Порог: >10GB накопленных WAL считается тревожным для низколатентных приложений.
• replication lag — см. следующий раздел; порог для критичных систем >5s считается проблемой.
• disk I/O: await > 20ms для SSD или > 50ms для HDD — повод для оптимизации запросов/рутин или рассмотрения NVMe.
• CPU load: процессорная загрузка >70% на длительное время требует анализа запросов и индексов; check CPU steal в виртуальных средах.
• number of backends и max connections — если достигается limit, используйте pooler. Нормальное значение active_backends/max_connections в пределах 50–70% от capacity.
• autovacuum stats: autovacuum workers busy ratio, n_dead_tup. Если autovacuum не справляется — увеличьте autovacuum_max_workers или уменьшайте thresholds для горячих таблиц.

Что делать при replication lag?

Replication lag — распространённая проблема при репликации на географические узлы или при чрезмерной нагрузке на primary. В 2025–2026 важной практикой стало разграничение WAL retention и настройка apply_workers для ускорения реплик.

1. Проверьте текущие LSN и lag:

   -- на primary
   SELECT pg_current_wal_lsn();
   -- на replica
   SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;
   -- на primary: статус репликации
   SELECT pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state, write_lag, flush_lag, replay_lag
   FROM pg_stat_replication;

2. Если replay_delay > 5s для низколатентных приложений, выполните последовательность действий:
   • Проверьте сеть: ping/iperf между primary и replica, latency <10ms желательно для sync-replication.
   • Увеличьте wal_sender timeout и wal_receiver timeout для стабильности, но это не решает медленное применение WAL.
   • Настройте replica_apply_workers (Postgres 13+ поддерживает parallel apply для logical replication) или increase apply_workers в pglogical/pg_rewind: пример для logical replication set on replica:

     ALTER SYSTEM SET max_apply = 4;  -- пример, реальное имя параметра зависит от версии/расширения

   • Увеличьте wal_compression = on на primary, если сеть узкая; компрессия уменьшит передаваемый трафик и замедление при большом количестве обновлений. Пример: wal_compression = on
   • Если реплика существенно отстаёт, и вы неоднократно видите большие лаги, рассмотрите resync: остановите replica, сделайте pg_basebackup с primary на свежую копию. Пример команды:

     pg_basebackup -h primary.host -D /var/lib/postgresql/14/main -U replication -P -v --wal-method=stream

     Рекомендуется использовать rsync/ssh/pg_basebackup и проверять checksums.
   • В случае логической репликации проверьте количество apply_workers и механизмы conflict handling; увеличьте parallel_workers если у вас много partitioned tables и независимых данных.
3. Практический чек-лист при обнаружении lag:
   1. Снять метрики сети и I/O на реплике (iostat, sar).
   2. Проверить нагрузку на реплике: CPU, disk I/O, temp files. Иногда replica выполняет тяжелые аналитические запросы и отстаёт; временно ограничьте нагрузку.
   3. Увеличить max_wal_senders на primary при возникновении ошибок подключения; проверьте wal_keep_size или используя replication slots — следите за growth в случае отсутствующей реплики.

Практика 2026: на кластере с primary в EU и двух read-replica в APAC, включение wal_compression и pg_basebackup раз в 24 часа снизили средний replication lag c 18 секунд до 2–3 секунд при пиковых нагрузках.

Для продолжения оптимизации перед внедрением на проде подготовьте план регрессионного тестирования: снимите нагрузку, снимайте метрики до/после и применяйте изменения сначала в staging.

Дополнительные материалы и чеклисты по мониторингу и настройке см. в следующих разделах сайта: Оптимизация производительности и PostgreSQL: советы и конфигурации.

Частые вопросы

Как уменьшить время checkpoint?

Сокращение времени checkpoint достигается балансом между wal_size, checkpoint_timeout и числом одновременных записей. Если чекпойнты происходят слишком часто, увеличьте max_wal_size (например, до 1–4GB на OLTP) и checkpoint_timeout (например, до 15–30 минут), чтобы уменьшить частоту. Для сокращения длительности самого чекпойнта увеличьте bgwriter_lru_maxpages и настройте wal_writer_delay; также уменьшите shared_buffers при чрезмерном использовании, если заметны большие bursts I/O. Контролируйте показатели через pg_stat_bgwriter: buffers_checkpoint, checkpoints_timed и duration; цель — редкие, но быстрые чекпойнты, длительность <5 секунд.

Что такое effective_cache_size и сколько ставить?

effective_cache_size — это подсказка планировщику запросов о размере доступного файлового кэша ОС; она не резервирует память, а помогает выбирать планы. Рекомендация: устанавливайте в районе 50–75% от общей RAM на выделенном сервере баз данных. Например, на 64GB RAM при shared_buffers=16GB разумно поставить effective_cache_size≈48GB (75% от RAM). Для виртуальных сред или при высокой конкуренции за память снижайте до 40–50% и тестируйте. После изменения пересмотрите планы с помощью EXPLAIN, так как планировщик может начать выбирать индексные сканы вместо последовательных при высоком effective_cache_size.

Почему увеличивается bloat?

Bloat (раздувание таблиц и индексов) возникает из-за большого числа UPDATE/DELETE и недостаточной работы VACUUM/ANALYZE. Если autovacuum не справляется (малое число workers, высокие thresholds) или если транзакции долгие и удерживают старые xmin, то мёртвые кортежи не удаляются, и размер растёт. Решения: увеличить autovacuum_max_workers, уменьшить autovacuum_vacuum_scale_factor для горячих таблиц, запускать pg_repack для онлайн-очистки, пересмотреть логику приложения (уменьшить частые UPDATE) и сократить длительность транзакций. Мониторьте pg_stat_user_tables.n_dead_tup и периодически считающийся bloat-скрипт для определения проблемных объектов.

Как настроить PgBouncer для 1000 клиентов?

При обслуживании до 1000 клиентов используйте PgBouncer в режиме transaction. Примерная конфигурация для сервера с 64GB RAM и 64 CPU: max_client_conn=2000, default_pool_size=200, reserve_pool_size=50, reserve_pool_timeout=5. На стороне Postgres выставьте max_connections равным default_pool_size * number_of_db_users (обычно 200–500) и следите за peak_active_connections. Важно: не забывайте о лимите рабочих процессов и памяти на сервере: рассчитывайте work_mem с учётом реального количества backend-подключений. Тестируйте стрессом (pgbench) и мониторьте приходящие очереди в PgBouncer (SHOW POOLS; SHOW STATS) и латентность соединений, чтобы избежать очередей в poolе.