PostgreSQL 16/17/18: администрирование и тюнинг производительности в продакшене

Введение: PostgreSQL в продакшене — другой зверь

Поднять PostgreSQL локально — просто. Запустить его в продакшене под реальной нагрузкой так, чтобы он не падал, не тормозил и не раздувался до потери диска — это уже инженерия.

PostgreSQL 16, 17 и 18 принесли серьёзные улучшения производительности: логическая репликация стала намного мощнее, параллельные запросы умнее, планировщик научился большему. Но дефолтная конфигурация по-прежнему рассчитана на «запустить на ноутбуке с 256 МБ RAM», а не на production-сервер с 128 ГБ памяти.

Эта статья — системный разбор всего, что нужно сделать, чтобы PostgreSQL работал быстро, надёжно и предсказуемо. Никакой воды: только параметры, SQL, реальные кейсы.

Глава 1. Конфигурация: postgresql.conf с нуля

Память: самые важные параметры

# postgresql.conf

# shared_buffers — основной кэш PostgreSQL в памяти.
# Правило: 25-40% от RAM сервера.
# На 64 ГБ RAM: 16 ГБ
shared_buffers = 16GB

# effective_cache_size — подсказка планировщику, сколько памяти
# доступно для кэширования (shared_buffers + OS page cache).
# Правило: 50-75% от RAM.
# На 64 ГБ RAM: 48 ГБ
effective_cache_size = 48GB

# work_mem — память для одной операции сортировки/хэширования
# ВНИМАНИЕ: умножается на число параллельных запросов × число операций в плане!
# На сервере с 500 соединениями и work_mem=256MB → потенциально 128 ГБ!
# Разумно: 4-64 МБ для OLTP, 256 МБ-1 ГБ для аналитики
work_mem = 32MB

# maintenance_work_mem — для VACUUM, CREATE INDEX, ALTER TABLE
# Больше = быстрее индексы и вакуум. Безопасно давать больше, чем work_mem.
maintenance_work_mem = 2GB

# huge_pages — используем hugepages Linux для shared_buffers
# Обязательно для shared_buffers > 8 ГБ
huge_pages = on

WAL и checkpoint: баланс между скоростью и надёжностью

# wal_level — минимальный уровень для репликации
# replica — для физической репликации
# logical — для логической репликации (больше overhead)
wal_level = replica

# Размер WAL буфера (с PostgreSQL 16 wal_buffers=auto работает хорошо)
wal_buffers = 64MB

# checkpoint_completion_target — размазываем запись checkpoint во времени
# 0.9 означает: записать грязные страницы за 90% интервала между checkpoint
checkpoint_completion_target = 0.9

# max_wal_size — максимальный объём WAL между checkpoint
# При большой нагрузке на запись увеличьте до 4-16 ГБ
# Это НЕ размер хранилища WAL, а порог для инициации checkpoint
max_wal_size = 4GB

# min_wal_size — минимальный резерв WAL файлов
min_wal_size = 1GB

# wal_compression — сжатие WAL (PostgreSQL 15+: поддержка lz4, zstd)
# Снижает I/O, небольшой CPU overhead
wal_compression = lz4

Параллелизм (PostgreSQL 16+)

# max_worker_processes — общий пул фоновых процессов
max_worker_processes = 16

# max_parallel_workers_per_gather — параллельные воркеры на один запрос
# Правило: не более числа физических ядер / 2
max_parallel_workers_per_gather = 4

# max_parallel_workers — суммарно параллельных воркеров
max_parallel_workers = 8

# max_parallel_maintenance_workers — для CREATE INDEX CONCURRENTLY, VACUUM
max_parallel_maintenance_workers = 4

# parallel_tuple_cost, parallel_setup_cost — влияют на решение планировщика
# использовать параллельность. Снизить если параллельные планы не строятся.
parallel_tuple_cost = 0.1
parallel_setup_cost = 100

Соединения

# max_connections — ОСТОРОЖНО! Каждое соединение ≈ 5-10 МБ памяти.
# При PgBouncer: достаточно 100-200 серверных соединений.
# Без пула: реальное число ≤ 200-300
max_connections = 200

# superuser_reserved_connections — резерв для DBA
superuser_reserved_connections = 5

Планировщик: тонкая настройка

# random_page_cost — стоимость случайного чтения страницы.
# Для SSD: 1.1-1.5 (против дефолта 4.0 для HDD).
# Занижение → планировщик чаще выбирает Index Scan.
random_page_cost = 1.1

# seq_page_cost — стоимость последовательного чтения (база = 1.0)
seq_page_cost = 1.0

# effective_io_concurrency — параллельных I/O для Bitmap Heap Scan
# SSD: 200-300, HDD: 2-4, NVMe: 500+
effective_io_concurrency = 200

# default_statistics_target — точность статистики для планировщика
# Дефолт 100. Для колонок с высокой кардинальностью — до 500.
default_statistics_target = 200

# enable_partitionwise_join — важно для партиционированных таблиц
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

Глава 2. Индексная стратегия

Правильные индексы — половина успеха. Неправильные — гарантированный bloat и тормоза на INSERT/UPDATE.

Типы индексов: когда что использовать

B-Tree — дефолт, для равенства и диапазонов:

-- Стандартный случай
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Частичный индекс — только активные записи
-- Занимает меньше места, быстрее обновляется
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status = 'active';

-- Покрывающий индекс (INCLUDE) — избегаем обращения к таблице
-- PostgreSQL 11+, активно улучшен в 16/17
CREATE INDEX idx_orders_cover ON orders(user_id)
INCLUDE (total_amount, status, created_at);

-- Составной: порядок имеет значение!
-- Ставьте впереди колонки с высокой кардинальностью
-- и те, по которым фильтрация точнее
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at);

GIN — для массивов, JSONB, полнотекстового поиска:

-- JSONB поиск
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- Полнотекстовый поиск
CREATE INDEX idx_articles_fts ON articles
USING GIN(to_tsvector('russian', title || ' ' || body));

-- Поиск в массивах
CREATE INDEX idx_tags ON posts USING GIN(tags);

BRIN — для очень больших таблиц с естественной сортировкой:

-- Для таблиц логов, временных рядов — экономия места 99%+
-- BRIN не хранит каждое значение, только мин/макс по блокам
CREATE INDEX idx_events_time_brin ON events
USING BRIN(created_at) WITH (pages_per_range = 128);

-- PostgreSQL 14+: bloom filter в BRIN
CREATE INDEX idx_events_bloom ON events
USING BRIN(device_id, created_at)
WITH (pages_per_range = 64);

Hash — только для точного равенства, быстрее B-Tree:

CREATE INDEX idx_sessions_token ON sessions
USING HASH(session_token);

Найти неиспользуемые и дублирующие индексы

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Дублирующие индексы
SELECT
    indrelid::regclass AS table_name,
    array_agg(indexrelid::regclass) AS indexes,
    array_agg(indkey) AS index_keys
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

-- Индексы vs размер таблицы: раздутые индексы
SELECT
    t.tablename,
    pg_size_pretty(pg_total_relation_size(t.tablename::regclass)) AS total,
    pg_size_pretty(pg_relation_size(t.tablename::regclass)) AS table_size,
    pg_size_pretty(
        pg_total_relation_size(t.tablename::regclass) -
        pg_relation_size(t.tablename::regclass)
    ) AS indexes_size,
    round(
        (pg_total_relation_size(t.tablename::regclass) -
         pg_relation_size(t.tablename::regclass))::numeric /
        nullif(pg_total_relation_size(t.tablename::regclass), 0) * 100, 1
    ) AS index_ratio_pct
FROM pg_tables t
WHERE t.schemaname = 'public'
ORDER BY pg_total_relation_size(t.tablename::regclass) DESC
LIMIT 30;

Глава 3. EXPLAIN ANALYZE: читаем план запроса как профессионал

EXPLAIN ANALYZE — главный инструмент оптимизации. Без него — гадание на кофейной гуще.

-- Всегда используйте все опции
EXPLAIN (
    ANALYZE,           -- Реально выполнить и показать время
    BUFFERS,           -- Показать попадания/промахи кэша
    FORMAT TEXT,       -- или JSON для авто-анализа
    TIMING ON,         -- Время каждого узла
    SETTINGS ON,       -- Показать изменённые параметры
    WAL ON             -- PostgreSQL 13+: WAL активность
)
SELECT ...;

Анатомия плана: на что смотреть

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '7 days'
  AND o.status = 'completed';

-- Типичный вывод:
--                                               QUERY PLAN
-- Hash Join  (cost=1250.00..8934.21 rows=1523 width=48) (actual time=45.231..189.443 rows=1287 loops=1)
--   Buffers: shared hit=4521 read=2341   ← read > 0 = данных нет в кэше
--   Hash Cond: (o.user_id = u.id)
--   ->  Bitmap Heap Scan on orders o  (cost=87.3..7512.4 rows=1523 width=32)
--         (actual time=2.341..145.231 rows=1287 loops=1)
--         Recheck Cond: (created_at > (now() - '7 days'::interval))
--         Filter: (status = 'completed')
--         Rows Removed by Filter: 4521   ← КРАСНЫЙ ФЛАГ: фильтруем 4521 строк!
--         Heap Blocks: exact=1823
--         Buffers: shared hit=123 read=1823
--         ->  Bitmap Index Scan on idx_orders_created_at
--               Index Cond: (created_at > (now() - '7 days'::interval))
--   ->  Hash  (cost=890.00..890.00 rows=21000 width=24) (actual time=42.3..42.3 rows=21000 loops=1)
--         Buckets: 32768  Batches: 1  Memory Usage: 1856kB
--         Buffers: shared hit=4398 read=518
--         ->  Seq Scan on users u  (cost=0.00..890.00 rows=21000 width=24)
-- Planning Time: 1.234 ms
-- Execution Time: 190.123 ms   ← Реальное время!

Красные флаги в плане:

Автоматический поиск медленных запросов

-- pg_stat_statements: топ-20 самых дорогих запросов
-- Требует: shared_preload_libraries = 'pg_stat_statements'
-- postgresql.conf: pg_stat_statements.track = all

SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    round((total_exec_time / sum(total_exec_time) OVER () * 100)::numeric, 2) AS pct_total,
    round(rows::numeric / calls, 1) AS avg_rows,
    -- Соотношение кэш-попаданий
    round(
        100.0 * shared_blks_hit /
        nullif(shared_blks_hit + shared_blks_read, 0), 2
    ) AS cache_hit_pct,
    -- Нормализованный текст запроса (без значений параметров)
    left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

-- Запросы с самым высоким среднем временем (не суммарным!)
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 5
  AND mean_exec_time > 100  -- Больше 100 мс в среднем
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Запросы с плохим cache hit ratio (много disk reads)
SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    shared_blks_read,
    shared_blks_hit,
    round(100.0 * shared_blks_hit /
          nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
  AND (shared_blks_hit + shared_blks_read) > 0
  AND shared_blks_read > shared_blks_hit  -- Больше промахов чем попаданий
ORDER BY shared_blks_read DESC
LIMIT 20;

Глава 4. Autovacuum: настройка, а не молитва

Autovacuum — не враг, а друг. Но дефолтные настройки рассчитаны на небольшие таблицы. На больших таблицах он либо не успевает, либо тормозит рабочую нагрузку.

Понять текущее состояние vacuum

-- Таблицы с наибольшим dead tuple bloat
SELECT
    schemaname,
    relname AS tablename,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    autovacuum_count,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Таблицы, которым скоро нужен vacuum (по счётчику транзакций)
-- age() показывает сколько транзакций прошло с последнего freeze
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS size,
    age(relfrozenxid) AS xid_age,
    round(age(relfrozenxid)::numeric / 2000000000 * 100, 2) AS freeze_pct,
    -- Когда автовакуум сделает freeze (по умолчанию при 150M транзакций)
    (200000000 - age(relfrozenxid)) AS txids_until_freeze
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE relkind = 'r'
  AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

-- Текущие процессы autovacuum
SELECT
    pid,
    now() - xact_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

Оптимальная настройка autovacuum

# postgresql.conf — глобальные настройки autovacuum

# Число процессов autovacuum
autovacuum_max_workers = 6   # Дефолт 3; на активном сервере — 4-8

# Стоимостной лимит для autovacuum (throttling)
# Дефолт 200 — очень агрессивное ограничение скорости.
# На SSD можно поднять до 800-2000.
autovacuum_vacuum_cost_limit = 800

# Задержка между "порциями" vacuum (cooldown)
# При cost_limit=800 и delay=2ms → ~400 МБ/с максимальная скорость vacuum
autovacuum_vacuum_cost_delay = 2ms

# Порог запуска VACUUM: n_dead_tup > autovacuum_vacuum_threshold + n_live_tup * scale_factor
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.02   # 2% от таблицы (дефолт 20%)

# Порог запуска ANALYZE
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.01  # 1% (дефолт 20%)

# Для больших таблиц scale_factor делает vacuum очень редким:
# Таблица 100M строк × 0.02 = 2M dead tuples до запуска vacuum — МНОГО

Настройка per-table (лучше глобальных для горячих таблиц):

-- Для высокоактивных таблиц: vacuum чаще, агрессивнее
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.005,   -- Запуск при 0.5% dead tuples
    autovacuum_analyze_scale_factor = 0.002,  -- Analyze при 0.2%
    autovacuum_vacuum_cost_limit = 1600,      -- Более высокий лимит I/O
    autovacuum_vacuum_cost_delay = 1          -- Меньше пауз
);

-- Для append-only таблиц (логи, временные ряды):
-- Vacuum не нужен часто, но freeze — важен
ALTER TABLE event_log SET (
    autovacuum_vacuum_scale_factor = 0.2,        -- Редкий vacuum (мало UPDATE/DELETE)
    autovacuum_freeze_max_age = 500000000,        -- Freeze через 500M транзакций
    autovacuum_vacuum_cost_limit = 2000           -- Быстрый когда запустился
);

-- Проверить что настройки применились:
SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('orders', 'event_log');

Обнаружение table bloat (раздутых таблиц)

-- Скрипт оценки bloat (не требует сторонних расширений)
WITH constants AS (
    SELECT current_setting('block_size')::numeric AS bs,
           23 AS hdr, 8 AS ma
),
columns_per_table AS (
    SELECT att.attrelid,
           count(*) AS cols,
           -- Байт nullmap на строку
           (count(*) + 7) / 8 AS nullhdr
    FROM pg_attribute att
    WHERE att.attnum > 0 AND NOT att.attisdropped
    GROUP BY 1
),
rows_estimate AS (
    SELECT c.oid,
           CASE WHEN c.reltuples < 0 THEN 0 ELSE c.reltuples END AS est_rows,
           c.relpages,
           c.relname,
           n.nspname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT
    re.nspname || '.' || re.relname AS table_name,
    re.est_rows,
    re.relpages AS current_pages,
    pg_size_pretty(re.relpages * 8192) AS current_size,
    -- Оценочный реальный размер
    pg_size_pretty(
        ceil(re.est_rows * 30 / 8192.0)::bigint * 8192
    ) AS estimated_real_size,
    round(
        100.0 * (re.relpages - ceil(re.est_rows * 30 / 8192.0)) /
        nullif(re.relpages, 0), 1
    ) AS bloat_pct
FROM rows_estimate re
WHERE re.relpages > 100
ORDER BY (re.relpages - ceil(re.est_rows * 30 / 8192.0)) DESC
LIMIT 20;

-- Для точного bloat используйте расширение pgstattuple:
-- CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');
-- Поля: table_len, live_tuple_count, dead_tuple_count, dead_tuple_percent, free_space

Глава 5. Connection Pooling с PgBouncer

Каждое соединение с PostgreSQL — это отдельный процесс (~5 МБ памяти + overhead планировщика). 1000 соединений = 5 ГБ памяти только на процессы. PgBouncer решает эту проблему.

Режимы PgBouncer

Конфигурация PgBouncer

# /etc/pgbouncer/pgbouncer.ini

[databases]
# Синтаксис: alias = host=... dbname=... port=... user=...
myapp = host=127.0.0.1 port=5432 dbname=myapp_db

# Для чтения — отдельный пул на реплику
myapp_ro = host=replica.internal port=5432 dbname=myapp_db

[pgbouncer]
# Режим пула
pool_mode = transaction

# Адрес и порт PgBouncer
listen_addr = 0.0.0.0
listen_port = 5432

# Максимум клиентских соединений (к PgBouncer)
max_client_conn = 2000

# Размер серверного пула на базу (к PostgreSQL)
# PostgreSQL: max_connections = 200
# PgBouncer: default_pool_size = 80 (на каждую базу)
default_pool_size = 80

# Минимальный пул (держим готовые соединения)
min_pool_size = 10

# Резерв для суперпользователя (аналог reserved_connections)
reserve_pool_size = 5
reserve_pool_timeout = 3

# Таймауты
server_idle_timeout = 600      # Закрыть серверное соединение через 10 мин idle
client_idle_timeout = 0        # Не закрывать клиентские (0 = infinite)
server_connect_timeout = 5     # Таймаут подключения к PostgreSQL
query_timeout = 0              # 0 = нет лимита на запрос (лучше ставить в app)
query_wait_timeout = 120       # Ждать свободного соединения до 120 с

# Проверка соединений
server_check_query = select 1
server_check_delay = 30

# Аутентификация (scram-sha-256 — стандарт PG 14+)
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Логирование (не слишком подробное — влияет на производительность)
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

# Admin интерфейс
admin_users = pgbouncer_admin
stats_users = monitoring_user

# Производительность
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5

Мониторинг PgBouncer

-- Подключиться к admin БД PgBouncer:
-- psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer

-- Состояние пулов
SHOW POOLS;
-- cl_active  — клиентов с активным серверным соединением
-- cl_waiting — клиентов в очереди (ждут свободного соединения!)
-- sv_active  — серверных соединений в работе
-- sv_idle    — серверных соединений в ожидании (пул)
-- sv_used    — только что освобождённые (не проверены ещё)
-- maxwait    — максимальное время ожидания клиента (критический параметр!)

-- Статистика
SHOW STATS;
-- total_query_time — суммарное время выполнения запросов
-- avg_query_time   — среднее время запроса
-- total_wait_time  — суммарное время ожидания в очереди

-- Список клиентов
SHOW CLIENTS;

-- Перезагрузить конфиг без перезапуска
RELOAD;

-- Сбросить статистику
RESET STATS;

Интеграция с PostgreSQL 17: встроенный connection shard

PostgreSQL 17 улучшил max_connections по производительности и добавил механизм connection_obeys_lc_messages — мелочь, но полезная. Работа над встроенным пулингом (connection pooling) ведётся активно, следите за PostgreSQL 18.

Глава 6. Партиционирование: когда таблица растёт до сотен ГБ

Партиционирование делит одну логическую таблицу на несколько физических. PostgreSQL 16/17 значительно улучшили работу с партициями: умный pruning, параллельные операции, partition-wise joins.

RANGE партиционирование (самое частое — по дате)

-- Создание партиционированной таблицы
CREATE TABLE events (
    id          BIGSERIAL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    device_id   INT NOT NULL,
    event_type  TEXT NOT NULL,
    payload     JSONB,
    PRIMARY KEY (id, created_at)  -- created_at обязательна в PK для партиций!
) PARTITION BY RANGE (created_at);

-- Создание партиций (вручную или автоматически)
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- DEFAULT партиция для данных вне диапазона
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Индексы создаются на каждой партиции отдельно
-- (или глобально через CREATE INDEX на родительской — PG 11+)
CREATE INDEX idx_events_device ON events(device_id, created_at);
-- Автоматически создаст индекс на каждой партиции!

Автоматическое создание партиций (pg_partman)

Ручное создание партиций — путь к ошибкам. Используйте pg_partman:

-- Установка pg_partman
-- Добавить в postgresql.conf: shared_preload_libraries = 'pg_partman_bgw'

-- Настройка автоматического управления партициями
SELECT partman.create_parent(
    p_parent_table   => 'public.events',
    p_control        => 'created_at',
    p_interval       => 'monthly',       -- или 'weekly', 'daily', 'yearly'
    p_premake        => 3,               -- Создавать 3 будущих партиции заранее
    p_start_partition => '2024-01-01'
);

-- Настройка retention (удаление старых партиций)
UPDATE partman.part_config
SET retention = '12 months',           -- Хранить 12 месяцев
    retention_keep_table = false,       -- Удалять партицию физически
    retention_keep_index = false        -- Удалять и индексы
WHERE parent_table = 'public.events';

-- Запуск обслуживания вручную (обычно pg_partman_bgw делает это сам)
CALL partman.run_maintenance_proc();

Partition Pruning: проверяем что планировщик умный

-- Планировщик должен сканировать только нужные партиции
EXPLAIN
SELECT count(*)
FROM events
WHERE created_at BETWEEN '2024-03-01' AND '2024-03-31';

-- Ищем в плане: "Partitions: events_2024_03"
-- НЕ должно быть: "Append (всех партиций)"
-- Если pruning не работает — проверьте что условие на колонку партиционирования
-- и её тип совпадают (нет неявных каст)

-- Partition pruning во время выполнения (runtime pruning, PG 11+)
-- Работает даже для параметров ($1, $2) если enable_partition_pruning = on
SET enable_partition_pruning = on;  -- Дефолт on в PG 16+

LIST партиционирование (по типу/региону)

CREATE TABLE orders (
    id         BIGSERIAL,
    region     TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    total      DECIMAL(10,2)
) PARTITION BY LIST (region);

CREATE TABLE orders_eu   PARTITION OF orders FOR VALUES IN ('DE', 'FR', 'NL', 'PL');
CREATE TABLE orders_us   PARTITION OF orders FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_asia PARTITION OF orders FOR VALUES IN ('CN', 'JP', 'KR', 'IN');
CREATE TABLE orders_rest PARTITION OF orders DEFAULT;

HASH партиционирование (равномерное распределение)

-- Для таблиц без естественного ключа партиционирования
-- Гарантирует примерно равный размер партиций
CREATE TABLE sessions (
    id         UUID DEFAULT gen_random_uuid(),
    user_id    INT NOT NULL,
    data       JSONB
) PARTITION BY HASH (user_id);

-- Создаём N партиций (степень двойки — хорошая практика)
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... и т.д. до sessions_7

Глава 7. Репликация: PostgreSQL 16/17/18

Физическая репликация (Streaming Replication)

# На Primary: postgresql.conf
wal_level = replica
max_wal_senders = 10          # Максимум одновременных реплик
wal_keep_size = 1GB           # Буфер WAL для реплик (PG 13+, заменил wal_keep_segments)
hot_standby = on              # Разрешить запросы на реплике
hot_standby_feedback = on     # Реплика сообщает Primary о своих транзакциях

# pg_hba.conf на Primary — разрешаем репликацию с адреса реплики:
# host replication replicator 10.0.0.2/32 scram-sha-256

# На Standby: создание базовой копии
pg_basebackup \
    -h primary.host \
    -U replicator \
    -D /var/lib/postgresql/17/main \
    -P \
    --wal-method=stream \
    --checkpoint=fast \
    --write-recovery-conf    # Создаёт standby.signal и postgresql.auto.conf

# postgresql.auto.conf на Standby (создаётся pg_basebackup):
# primary_conninfo = 'host=primary.host port=5432 user=replicator password=...'

-- Мониторинг репликации на Primary:
SELECT
    client_addr,
    usename,
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    -- Лаг в байтах
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    -- Лаг во времени (PG 10+)
    write_lag,
    flush_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication
ORDER BY replay_lag DESC;

-- На Standby — проверка своего лага:
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_lag,
    pg_is_in_recovery() AS is_replica,
    pg_last_wal_receive_lsn() AS received_lsn,
    pg_last_wal_replay_lsn() AS replayed_lsn;

Логическая репликация (PostgreSQL 16/17: серьёзно улучшена)

Логическая репликация в PostgreSQL 16 получила:

• Двунаправленная (bidirectional) репликация — обе стороны могут принимать запись

• Streaming больших транзакций в реальном времени (без ожидания COMMIT)

• Параллельное применение изменений на подписчике

-- На Publisher (источник):
-- postgresql.conf: wal_level = logical

-- Создание публикации
CREATE PUBLICATION my_pub
    FOR TABLE orders, users, products
    WITH (publish = 'insert, update, delete', publish_via_partition_root = true);

-- Для всех таблиц:
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- На Subscriber (назначение):
-- Создание подписки
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=primary.host port=5432 dbname=mydb user=replicator password=secret'
    PUBLICATION my_pub
    WITH (
        connect = true,
        slot_name = 'my_sub_slot',
        synchronous_commit = 'off',  -- Более быстрая репликация
        streaming = on               -- PG 14+: stream больших транзакций
    );

-- Мониторинг логической репликации на Publisher:
SELECT
    slot_name,
    plugin,
    slot_type,
    database,
    active,
    active_pid,
    -- КРИТИЧНО: wal_status = 'lost' означает что слот отстал и WAL удалён
    wal_status,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
        AS subscriber_lag
FROM pg_replication_slots;

-- ОПАСНОСТЬ: неактивный logical slot держит WAL! Диск кончится.
-- Если слот не используется > 24ч — проверить и при необходимости удалить:
-- SELECT pg_drop_replication_slot('my_sub_slot');

Глава 8. Мониторинг: что смотреть в продакшене

Системные вьюшки — источник правды

-- ===== АКТИВНЫЕ ЗАПРОСЫ И БЛОКИРОВКИ =====

-- Запросы дольше 30 секунд — потенциальные проблемы
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event,
    client_addr,
    usename,
    application_name
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > INTERVAL '30 seconds'
  AND state != 'idle'
ORDER BY duration DESC;

-- Граф блокировок: кто кого блокирует
WITH RECURSIVE lock_graph AS (
    -- Базовый случай: запросы, ожидающие блокировку
    SELECT
        blocked.pid AS blocked_pid,
        blocked.query AS blocked_query,
        blocked.query_start AS blocked_start,
        blocker.pid AS blocker_pid,
        blocker.query AS blocker_query,
        0 AS depth
    FROM pg_stat_activity blocked
    JOIN pg_stat_activity blocker
        ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
    WHERE blocked.wait_event_type = 'Lock'
    
    UNION ALL
    
    -- Рекурсивный случай: цепочки блокировок
    SELECT
        lg.blocked_pid,
        lg.blocked_query,
        lg.blocked_start,
        blocker.pid,
        blocker.query,
        lg.depth + 1
    FROM lock_graph lg
    JOIN pg_stat_activity blocker
        ON blocker.pid = ANY(pg_blocking_pids(lg.blocker_pid))
    WHERE lg.depth < 10
)
SELECT
    blocked_pid,
    left(blocked_query, 80) AS blocked_query,
    now() - blocked_start AS wait_duration,
    blocker_pid,
    left(blocker_query, 80) AS blocker_query,
    depth
FROM lock_graph
ORDER BY wait_duration DESC;

-- Если нужно убить зависший запрос (мягко):
-- SELECT pg_cancel_backend(pid);  -- Отмена запроса, транзакция откатывается

-- Если не реагирует (жёстко):
-- SELECT pg_terminate_backend(pid);  -- Завершение процесса


-- ===== КЭШ И I/O =====

-- Cache hit ratio (цель: > 99%)
SELECT
    sum(heap_blks_hit) AS heap_hit,
    sum(heap_blks_read) AS heap_read,
    round(
        sum(heap_blks_hit)::numeric /
        nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 3
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- По каждой таблице:
SELECT
    relname AS table_name,
    heap_blks_hit,
    heap_blks_read,
    round(heap_blks_hit::numeric / nullif(heap_blks_hit + heap_blks_read, 0) * 100, 2)
        AS cache_hit_pct,
    idx_blks_hit,
    idx_blks_read,
    round(idx_blks_hit::numeric / nullif(idx_blks_hit + idx_blks_read, 0) * 100, 2)
        AS idx_cache_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read + heap_blks_hit > 0
ORDER BY heap_blks_read DESC
LIMIT 20;


-- ===== CHECKPOINT СТАТИСТИКА =====
SELECT
    checkpoints_timed,
    checkpoints_req,            -- Если часто req >> timed: увеличить max_wal_size
    checkpoint_write_time / 1000 AS write_sec,
    checkpoint_sync_time / 1000 AS sync_sec,
    buffers_checkpoint,
    buffers_clean,
    buffers_backend,            -- Если много: bgwriter не успевает → тюнинг bgwriter
    buffers_backend_fsync,      -- НЕ ноль = ПРОБЛЕМА: backend делает fsync сам
    buffers_alloc
FROM pg_stat_bgwriter;

-- Если buffers_backend > 0 — увеличить bgwriter_lru_maxpages:
-- bgwriter_lru_maxpages = 200     (дефолт 100)
-- bgwriter_lru_multiplier = 4.0   (дефолт 2.0)
-- bgwriter_delay = 50ms           (дефолт 200ms)

Скрипт ежедневного health-check

-- Сохранить как daily_healthcheck.sql и запускать через cron

\echo '=== PostgreSQL Daily Health Check ==='
\echo ''

\echo '--- Database Sizes ---'
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN ('postgres', 'template0', 'template1')
ORDER BY pg_database_size(datname) DESC;

\echo ''
\echo '--- Top 10 Largest Tables ---'
SELECT schemaname || '.' || tablename AS table,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

\echo ''
\echo '--- Tables with High Dead Tuple Ratio (> 10%) ---'
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) > 0.1
  AND n_live_tup > 1000
ORDER BY dead_pct DESC;

\echo ''
\echo '--- Replication Lag ---'
SELECT application_name, replay_lag, sync_state
FROM pg_stat_replication;

\echo ''
\echo '--- Long-Running Transactions (> 1 hour) ---'
SELECT pid,
       usename,
       now() - xact_start AS duration,
       left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > INTERVAL '1 hour'
  AND pid != pg_backend_pid()
ORDER BY duration DESC;

\echo ''
\echo '--- Unused Indexes (0 scans) ---'
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- > 10 МБ
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

Глава 9. Новинки PostgreSQL 16/17/18

PostgreSQL 16 (2023)

Логическая репликация от standby — теперь можно публиковать изменения не только с primary, разгружая мастер.

Параллельный COPY — загрузка данных через COPY стала параллельной.

Улучшения планировщика для GROUP BY с параллелизмом.

pg_stat_io — новая системная вьюшка для детальной статистики I/O:

-- PostgreSQL 16+: детальная I/O статистика
SELECT backend_type, object, context, reads, writes, extends,
       op_bytes,
       hits,
       evictions,
       reuses,
       fsyncs,
       read_time, write_time
FROM pg_stat_io
ORDER BY reads + writes DESC;
-- Особенно полезно: сравнить hits vs reads для разных backend_type

COPY FROM ... WHERE — фильтрация при загрузке данных:

-- Загружаем только нужные строки
COPY orders FROM '/tmp/orders.csv' CSV HEADER
WHERE status = 'completed' AND total > 100;

PostgreSQL 17 (2024)

MERGE стал намного мощнее — поддержка RETURNING, DO NOTHING:

-- PostgreSQL 17: MERGE с RETURNING
MERGE INTO inventory AS target
USING incoming_stock AS source
    ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, quantity) VALUES (source.product_id, source.quantity)
RETURNING target.product_id, target.quantity, merge_action();
-- merge_action() → 'INSERT' или 'UPDATE'

Incremental sorting улучшен — быстрее для DISTINCT и ORDER BY.

pg_stat_statements получил toplevel — разделение top-level vs вложенных запросов.

Vacuum improvements — улучшена скорость заморозки (freeze), меньше I/O.

VACUUM (SKIP_DATABASE_STATS) — ускорение вакуума многих мелких таблиц.

Размер WAL записей уменьшен — меньше I/O при интенсивной записи.

PostgreSQL 18 (2025, в разработке / ранние беты)

Встроенный асинхронный I/O (io_method = io_uring) — огромный прирост для NVMe SSD, особенно при высоком параллелизме:

# postgresql.conf (PostgreSQL 18 при использовании Linux io_uring)
io_method = io_uring     # Дефолт: sync; альтернатива: worker

Планировщик с ML-hints — работа над улучшением кардинальности оценок.

GRANT/REVOKE для роли по умолчанию — улучшена система безопасности.

Глава 10. Практические кейсы: реальные проблемы и их решения

Кейс 1: «Запросы стали медленнее после VACUUM»

-- Симптом: autovacuum отработал, но запросы стали медленнее.
-- Причина: устаревшая статистика. VACUUM не обновляет статистику!

-- Решение 1: Принудительный ANALYZE
ANALYZE VERBOSE orders;

-- Или для всей БД:
-- vacuumdb --analyze-only --all

-- Решение 2: Увеличить точность статистики для проблемных колонок
ALTER TABLE orders
    ALTER COLUMN status SET STATISTICS 500,
    ALTER COLUMN region SET STATISTICS 500;

ANALYZE orders;

-- Проверить статистику после:
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('status', 'region', 'created_at');

Кейс 2: «Диск заполнился WAL файлами»

-- Причина 1: Зависший логический слот репликации
SELECT slot_name, active, wal_status,
       pg_size_pretty(
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE wal_status != 'reserved';

-- Если слот неактивен и держит WAL — удалить после согласования с командой:
SELECT pg_drop_replication_slot('stale_slot_name');

-- Причина 2: archive_command не успевает
-- Проверить:
SELECT last_archived_wal, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

-- Временная мера: уменьшить max_wal_size
-- Постоянная: починить archive_command или увеличить место

-- Причина 3: Слишком агрессивные checkpoint
-- Уменьшить wal_keep_size если репликация живая

Кейс 3: «Connection pool переполнен, приложение не может подключиться»

-- Диагноз: смотрим pg_stat_activity
SELECT state, count(*), left(query, 50) AS sample_query
FROM pg_stat_activity
WHERE datname = 'myapp_db'
GROUP BY state, left(query, 50)
ORDER BY count(*) DESC;

-- Частая причина: idle in transaction (транзакция открыта и забыта)
SELECT pid, now() - xact_start AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > INTERVAL '5 minutes'
ORDER BY idle_duration DESC;

-- Быстрое решение: убить зависшие idle in transaction
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > INTERVAL '10 minutes';

-- Постоянное решение: idle_in_transaction_session_timeout
-- postgresql.conf:
-- idle_in_transaction_session_timeout = 5min
-- idle_session_timeout = 30min  (PG 14+)

Кейс 4: «Таблица растёт несмотря на DELETE»

-- Table bloat: место от удалённых строк не возвращается OS.
-- PostgreSQL помечает строки как "мёртвые", VACUUM освобождает их
-- для ПОВТОРНОГО ИСПОЛЬЗОВАНИЯ, но не возвращает OS (кроме pg_toast).

-- Проверить bloat:
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    n_dead_tup,
    n_live_tup
FROM pg_stat_user_tables
JOIN pg_class USING (relid)
WHERE relname = 'your_table';

-- Решение 1: VACUUM FULL (блокирует таблицу! Используйте в окно обслуживания)
VACUUM FULL ANALYZE your_table;

-- Решение 2: pg_repack (без блокировки!)
-- Устанавливается отдельно: https://github.com/reorg/pg_repack
-- pg_repack -d mydb -t your_table

-- Решение 3: для партиционированных таблиц — просто удалить старую партицию
-- ALTER TABLE events DETACH PARTITION events_2022_01;
-- DROP TABLE events_2022_01;  -- Мгновенное освобождение места!

Заключение: чеклист production PostgreSQL

КОНФИГУРАЦИЯ
□ shared_buffers = 25-40% RAM
□ effective_cache_size = 50-75% RAM
□ work_mem настроен с учётом max_connections × parallel_workers
□ random_page_cost = 1.1-1.5 для SSD/NVMe
□ huge_pages = on (при shared_buffers > 8 ГБ, настроен в Linux)
□ max_wal_size = 2-8 ГБ (зависит от нагрузки)
□ wal_compression = lz4 (PG 15+)
□ idle_in_transaction_session_timeout = 5min
□ statement_timeout = установлен разумный лимит

МОНИТОРИНГ
□ pg_stat_statements включён и регулярно анализируется
□ Алерт на cache hit ratio < 95%
□ Алерт на replication lag > 60s
□ Алерт на bloat > 30% для критичных таблиц
□ Алерт на неактивные replication slots
□ Ежедневный health check запрос

AUTOVACUUM
□ autovacuum_max_workers = 4-6
□ autovacuum_vacuum_cost_delay = 2ms (SSD)
□ autovacuum_vacuum_cost_limit = 800-2000
□ Scale factor снижен для горячих таблиц
□ Мониторинг n_dead_tup и xid_age

СОЕДИНЕНИЯ
□ PgBouncer в transaction mode
□ max_connections ≤ 300 (больше — через пул)
□ Настроен pool_size в PgBouncer
□ Мониторинг cl_waiting в PgBouncer

ИНДЕКСЫ
□ Аудит неиспользуемых индексов (pg_stat_user_indexes)
□ Составные индексы с правильным порядком колонок
□ INCLUDE для покрывающих индексов
□ BRIN для append-only больших таблиц

РЕПЛИКАЦИЯ
□ Мониторинг replay_lag
□ Мониторинг pg_replication_slots на утечку WAL
□ Проверка wal_status всех слотов
□ hot_standby_feedback = on на репликах

БЕЗОПАСНОСТЬ
□ scram-sha-256 в pg_hba.conf
□ Минимальные привилегии для каждого пользователя
□ ssl = on + проверка сертификатов
□ log_connections/log_disconnections для аудита

PostgreSQL — невероятно мощная система, которая «из коробки» даёт примерно 20% своего потенциала. Правильная конфигурация, индексная стратегия и мониторинг превращают её в продукт, который выдерживает тысячи транзакций в секунду на десятках терабайт данных — без дорогостоящих «облачных» альтернатив.