Оптимизация PostgreSQL: индексы, партиционирование, EXPLAIN ANALYZE

Введение: почему оптимизация PostgreSQL — это навык, а не разовая задача

PostgreSQL — одна из самых мощных и зрелых реляционных СУБД, но её производительность в production напрямую зависит от того, насколько хорошо команда понимает внутренние механизмы работы базы данных. Оптимизация PostgreSQL — это не «добавил индекс и забыл», а постоянный итеративный процесс, встроенный в культуру разработки.

По мере роста данных запросы, которые работали мгновенно при 10 000 строк, начинают «висеть» при 10 миллионах. Меняется распределение данных, появляются новые паттерны запросов, изменяется нагрузка. Именно поэтому оптимизация PostgreSQL производительности — это живой навык, который нужно регулярно применять.

В этой статье мы пройдём весь путь от диагностики медленных запросов до тонкой настройки конфигурации — с реальными SQL-примерами и разбором планов запросов.

Инструменты диагностики: pg_stat_statements, auto_explain и EXPLAIN ANALYZE

pg_stat_statements: находим самые дорогие запросы

Первый шаг оптимизации — найти, что именно тормозит. Расширение pg_stat_statements собирает статистику по всем выполненным запросам и является стандартом де-факто для диагностики производительности PostgreSQL.

Подключаем расширение:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- В базе данных
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Запрос для поиска самых медленных запросов по суммарному времени выполнения:

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Обращайте внимание на запросы с высоким mean_exec_time и большим количеством calls — они дают максимальный прирост при оптимизации.

auto_explain: автоматическая запись медленных планов

Расширение auto_explain автоматически логирует план выполнения для запросов, превышающих порог времени:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1000  -- запросы дольше 1 секунды
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = text

Это особенно полезно в production, когда воспроизвести медленный запрос вручную сложно.

EXPLAIN ANALYZE: читаем план запроса правильно

EXPLAIN ANALYZE — основной инструмент анализа запросов в PostgreSQL. Рассмотрим реальный пример:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.email
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 100;

Пример вывода:

Limit  (cost=15234.56..15234.81 rows=100 width=52) (actual time=892.341..892.387 rows=100 loops=1)
  ->  Sort  (cost=15234.56..15259.56 rows=10000 width=52) (actual time=892.338..892.352 rows=100 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 33kB
        ->  HashAggregate  (cost=14734.56..14884.56 rows=10000 width=52) (actual time=878.234..889.123 rows=8934 loops=1)
              Group Key: u.id, u.email
              ->  Hash Join  (cost=3456.78..13234.56 rows=200000 width=24) (actual time=45.234..756.123 rows=198432 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    Buffers: shared hit=1234 read=8932
                    ->  Seq Scan on orders o  (cost=0.00..8234.56 rows=200000 width=8) (actual time=0.012..312.456 rows=200000 loops=1)
                    ->  Hash  (cost=2956.78..2956.78 rows=40000 width=24) (actual time=44.123..44.123 rows=39876 loops=1)
                          ->  Seq Scan on users u  (cost=0.00..2956.78 rows=40000 width=24) (actual time=0.015..38.234 rows=39876 loops=1)
                                Filter: (created_at > '2024-01-01'::timestamp)
                                Rows Removed by Filter: 12456
Planning Time: 2.345 ms
Execution Time: 892.567 ms

Что здесь важно заметить:

Seq Scan on orders — полный перебор таблицы заказов. 200 000 строк без индекса — плохой знак.
Buffers: shared read=8932 — большинство данных читается с диска, не из кэша.
Rows Removed by Filter: 12456 — фильтр по created_at на users работает, но без индекса.

Ключевое правило: смотрите на расхождение между estimated rows и actual rows. Большое расхождение означает устаревшую статистику — запускайте ANALYZE.

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

B-tree: универсальный выбор

B-tree индекс используется по умолчанию и подходит для большинства сценариев: равенство, диапазоны, сортировка, LIKE с фиксированным префиксом.

-- Стандартный индекс
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Составной индекс (порядок столбцов важен!)
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

-- Индекс для сортировки и диапазона
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at DESC);

Важно: всегда создавайте индексы с CONCURRENTLY в production — это не блокирует таблицу.

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

GIN (Generalized Inverted Index) оптимален для типов данных, содержащих множество значений.

-- Индекс для JSONB
CREATE INDEX CONCURRENTLY idx_products_attributes ON products USING GIN(attributes);

-- Запрос, использующий GIN
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "XL"}';

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

SELECT * FROM articles
WHERE to_tsvector('russian', title || ' ' || body) @@ to_tsquery('russian', 'оптимизация & PostgreSQL');

GiST: геоданные и нечёткий поиск

GiST индексы используются с типами геоданных (PostGIS), диапазонами (tsrange, int4range) и расширениями pg_trgm для нечёткого поиска.

-- Индекс для нечёткого поиска (требует pg_trgm)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_users_email_trgm ON users USING GiST(email gist_trgm_ops);

-- LIKE без фиксированного префикса теперь использует индекс
SELECT * FROM users WHERE email LIKE '%gmail%';

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

BRIN (Block Range INdex) — компактный индекс для таблиц с физически упорядоченными данными (временные ряды, логи, события).

-- Для таблицы событий с монотонно растущим timestamp
CREATE INDEX CONCURRENTLY idx_events_occurred_at
ON events USING BRIN(occurred_at) WITH (pages_per_range = 128);

-- BRIN занимает в тысячи раз меньше места, чем B-tree
-- Подходит для запросов по диапазонам дат на очень больших таблицах

Частичные и функциональные индексы

Частичные индексы: индексируем только нужные строки

Частичный индекс содержит только строки, удовлетворяющие условию WHERE. Это значительно уменьшает размер индекса и ускоряет запросы.

-- Индексируем только активные заказы (не завершённые)
CREATE INDEX CONCURRENTLY idx_orders_active
ON orders(created_at)
WHERE status NOT IN ('completed', 'cancelled');

-- Индекс для нечитанных уведомлений
CREATE INDEX CONCURRENTLY idx_notifications_unread
ON notifications(user_id, created_at)
WHERE read_at IS NULL;

-- Запрос автоматически использует частичный индекс
SELECT * FROM notifications
WHERE user_id = 42 AND read_at IS NULL
ORDER BY created_at DESC;

Функциональные индексы: индексируем результат выражения

-- Case-insensitive поиск по email
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users(LOWER(email));

-- Запрос теперь использует индекс
SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');

-- Индекс по части даты
CREATE INDEX CONCURRENTLY idx_orders_date
ON orders(DATE(created_at));

SELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2024-12-01';

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

Партиционирование позволяет разбить большую таблицу на логические части (партиции), что ускоряет запросы за счёт partition pruning — PostgreSQL пропускает партиции, которые не могут содержать нужные данные.

Range партиционирование: для временных данных

-- Создаём партиционированную таблицу
CREATE TABLE events (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  payload JSONB,
  occurred_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (occurred_at);

-- Создаём партиции по месяцам
CREATE TABLE events_2024_11 PARTITION OF events
  FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

CREATE TABLE events_2024_12 PARTITION OF events
  FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Индексы создаются на каждой партиции
CREATE INDEX ON events_2024_12(user_id);
CREATE INDEX ON events_2025_01(user_id);

-- Партиционирование + автоматическое создание партиций через pg_partman

Проверяем, что partition pruning работает:

EXPLAIN SELECT * FROM events
WHERE occurred_at BETWEEN '2025-01-01' AND '2025-01-31';

-- Вывод покажет Append с одной партицией events_2025_01
-- Остальные партиции полностью пропускаются

List партиционирование: для категорий и регионов

CREATE TABLE orders (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  region VARCHAR(20) NOT NULL,
  total NUMERIC(12,2),
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_eu PARTITION OF orders
  FOR VALUES IN ('DE', 'FR', 'NL', 'IT', 'ES');

CREATE TABLE orders_us PARTITION OF orders
  FOR VALUES IN ('NY', 'CA', 'TX', 'FL');

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Hash партиционирование: равномерное распределение

CREATE TABLE user_events (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  data JSONB,
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

-- 4 партиции для равномерного распределения
CREATE TABLE user_events_0 PARTITION OF user_events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_events_1 PARTITION OF user_events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_events_2 PARTITION OF user_events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_events_3 PARTITION OF user_events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Настройка конфигурации PostgreSQL

Правильная конфигурация PostgreSQL даёт прирост производительности без изменения схемы или запросов.

Ключевые параметры

-- Размер shared buffers: 25% от RAM для выделенного сервера
shared_buffers = 8GB

-- Оценка доступной памяти для кэша ОС (влияет на планировщик)
effective_cache_size = 24GB

-- Память для сортировки и хэш-джойнов (на каждый запрос/соединение!)
work_mem = 64MB

-- Память для операций обслуживания (VACUUM, CREATE INDEX)
maintenance_work_mem = 2GB

-- Параллельные рабочие процессы
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 16

-- Стоимость случайного I/O (для SSD уменьшаем)
random_page_cost = 1.1  -- SSD
# random_page_cost = 4.0  -- HDD (по умолчанию)

-- Включаем JIT для аналитических запросов
jit = on
jit_above_cost = 100000

Важно: work_mem умножается на количество соединений и операций сортировки внутри одного запроса. При 100 соединениях и work_mem = 256MB теоретически можно использовать 25GB RAM только на сортировку.

Оптимизация JOIN-запросов и подзапросов

Используйте CTE с умом

В PostgreSQL до версии 12 CTE были «заборами оптимизации» — планировщик не мог «заглянуть» внутрь. Начиная с PostgreSQL 12 это поведение изменилось, но иногда нужно управлять им явно:

-- Materializing CTE (форсируем материализацию)
WITH expensive_cte AS MATERIALIZED (
  SELECT user_id, SUM(amount) as total
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.email, c.total
FROM users u
JOIN expensive_cte c ON c.user_id = u.id
WHERE c.total > 1000;

-- NOT MATERIALIZED — позволяем планировщику оптимизировать
WITH recent_users AS NOT MATERIALIZED (
  SELECT id FROM users WHERE created_at > '2024-01-01'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM recent_users);

EXISTS vs IN vs JOIN

-- Медленно для больших подзапросов
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- Быстрее: EXISTS с коррелированным подзапросом
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 1000
);

-- Или JOIN с DISTINCT
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 1000;

Управление стратегией JOIN

-- Если планировщик выбирает неправильный тип JOIN
SET enable_hashjoin = off;  -- Отключаем hash join
SET enable_nestloop = off;  -- Отключаем nested loop

-- Используйте pg_hint_plan для более точного управления в production
-- SELECT /*+ HashJoin(u o) */ u.*, o.total FROM users u JOIN orders o ON ...

-- Не забудьте сбросить настройки после
RESET enable_hashjoin;
RESET enable_nestloop;

Вакуум и bloat: следим за состоянием таблиц

PostgreSQL использует MVCC — при UPDATE и DELETE старые версии строк не удаляются сразу. Накопление «мёртвых» строк называется bloat и приводит к деградации производительности.

Мониторинг bloat и состояния вакуума

-- Статистика по вакууму таблиц
SELECT
  schemaname,
  relname AS table_name,
  n_dead_tup AS dead_tuples,
  n_live_tup AS live_tuples,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY dead_ratio_pct DESC NULLS LAST
LIMIT 20;

-- Размер bloat по индексам
SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Настройка autovacuum для высоконагруженных таблиц

-- Агрессивный autovacuum для конкретной таблицы
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 1% мёртвых строк запускает вакуум
  autovacuum_analyze_scale_factor = 0.005, -- 0.5% для ANALYZE
  autovacuum_vacuum_cost_delay = 2,        -- ms задержки между страницами
  autovacuum_vacuum_threshold = 100
);

-- Ручной VACUUM ANALYZE при необходимости
VACUUM (ANALYZE, VERBOSE) orders;

-- Для устранения сильного bloat — VACUUM FULL (блокирует таблицу!)
-- Лучше использовать pg_repack в production
-- pg_repack --table orders mydb

Обнаружение неиспользуемых индексов

-- Индексы, которые никогда не сканировались
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

Неиспользуемые индексы — это не просто зря занятое место. Они замедляют INSERT, UPDATE и DELETE, так как PostgreSQL должен обновлять каждый индекс при изменении данных.

Заключение: регулярный аудит как часть культуры разработки

Оптимизация производительности PostgreSQL — это дисциплина, а не разовая акция. Рекомендуемый минимальный цикл для production-систем:

Ежедневно: мониторинг медленных запросов через pg_stat_statements, алерты на деградацию времени ответа.
Еженедельно: проверка состояния autovacuum, анализ новых индексов и неиспользуемых.
Ежемесячно: полный аудит планов запросов для топ-20 самых нагруженных запросов, проверка bloat.
При изменениях схемы: запускать EXPLAIN ANALYZE на все запросы, затрагивающие изменённые таблицы.

Инвестиция в понимание внутреннего устройства PostgreSQL окупается многократно. Правильно выбранный индекс может превратить запрос на 30 секунд в запрос на 5 миллисекунд. Партиционирование позволяет работать с таблицами на сотни гигабайт так же быстро, как с небольшими таблицами. А регулярный мониторинг предотвращает ночные инциденты.

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

Оптимизация производительности PostgreSQL: индексы, партиционирование и EXPLAIN ANALYZE на практике