Продвинутая работа с PostgreSQL: индексы, EXPLAIN, транзакции и VACUUM

Продвинутая работа с PostgreSQL — это не новые команды SQL, а умение объяснить базе, как выполнять запросы быстро и корректно под нагрузкой. Основы (CREATE, INSERT, SELECT, JOIN) — отдельная тема, здесь я считаю их пройденными. В этой части разбираю четыре опоры производительности: типы индексов и выбор между ними, чтение плана через EXPLAIN, транзакции с уровнями изоляции поверх MVCC и уборку мусора через VACUUM. Каждый блок — с рабочим SQL и разбором того, что показывает вывод.

Для примеров использую одну таблицу заказов на несколько миллионов строк. Ее и создадим.

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    user_id     bigint      NOT NULL,
    status      text        NOT NULL,
    amount      numeric(12,2) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    payload     jsonb
);
-- Наполнение для теста: 5 млн строк со случайными данными
INSERT INTO orders (user_id, status, amount, created_at, payload)
SELECT (random()*100000)::bigint,
       (ARRAY['new','paid','shipped','cancelled'])[1 + (random()*3)::int],
       (random()*10000)::numeric(12,2),
       now() - (random()*365) * interval '1 day',
       jsonb_build_object('promo', random() < 0.1)
FROM generate_series(1, 5000000);

После массовой вставки полезно один раз выполнить ANALYZE orders; — планировщик обновит статистику, иначе оценки строк будут грубыми.

Индексы: какой тип под какую задачу

Индекс — это отдельная структура на диске, которая позволяет найти строки без полного перебора таблицы. За ускорение чтения платишь местом на диске и замедлением записи: каждый INSERT, UPDATE и DELETE должен обновить и индексы. Поэтому индекс ставят под конкретный шаблон запросов, а не «на всякий случай» на каждый столбец.

PostgreSQL умеет несколько типов индексов, и они не взаимозаменяемы. Ниже — какой под что.

Начинать почти всегда стоит с B-tree — он покрывает большинство фильтров и сортировок. Специальные типы берут, когда B-tree структурно не подходит.

Создадим B-tree под частый фильтр по пользователю.

CREATE INDEX idx_orders_user ON orders (user_id);

Отдельно стоит знать про частичный индекс — он покрывает только строки, подходящие под условие WHERE. Это экономит место и ускоряет обновление, когда запросы всегда идут по узкому срезу.

-- Индекс только по новым заказам: их обычно ищут и обрабатывают чаще
CREATE INDEX idx_orders_new ON orders (created_at)
WHERE status = 'new';

Признак выбора простой: если в запросах есть устойчивое условие (status = 'new', deleted_at IS NULL), частичный индекс почти всегда компактнее и быстрее полного.

Для поиска внутри jsonb обычный B-tree не сработает — нужен GIN.

CREATE INDEX idx_orders_payload ON orders USING gin (payload);
-- Найдет заказы с промо, используя индекс, а не перебор
SELECT id FROM orders WHERE payload @> '{"promo": true}';

BRIN — особый случай: он хранит не ссылки на строки, а границы значений по диапазонам блоков. Он крошечный и полезен на больших таблицах, где данные физически растут по порядку (например, по времени вставки). На перемешанных данных он бесполезен.

-- Десятки килобайт вместо сотен мегабайт B-tree на том же столбце
CREATE INDEX idx_orders_created_brin ON orders USING brin (created_at);

EXPLAIN и EXPLAIN ANALYZE: как прочитать план

Различать эти две команды нужно сразу. EXPLAIN показывает план и оценки планировщика, но запрос не выполняет. EXPLAIN ANALYZE реально выполняет запрос и добавляет фактическое время и число строк. Для SELECT это безопасно, а вот для UPDATE, DELETE и INSERT ANALYZE применит изменения — их оборачивают в транзакцию с откатом.

EXPLAIN ANALYZE
SELECT id, status FROM orders WHERE user_id = 42;

Без индекса план выглядит примерно так (числа зависят от данных и железа):

Seq Scan on orders  (cost=0.00..96000.00 rows=48 width=12)
                    (actual time=0.4..612.7 rows=51 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 4999949
Planning Time: 0.2 ms
Execution Time: 612.9 ms

Seq Scan — последовательный проход по всей таблице, Rows Removed by Filter почти 5 млн: база прочитала все строки ради полусотни нужных. После CREATE INDEX idx_orders_user тот же запрос дает другой план.

Index Scan using idx_orders_user on orders  (cost=0.43..12.9 rows=48 width=12)
                                            (actual time=0.05..0.11 rows=51 loops=1)
  Index Cond: (user_id = 42)
Execution Time: 0.14 ms

Здесь Index Scan и условие ушло в Index Cond — база сразу пришла к нужным строкам. Читать план стоит по такому алгоритму: смотрю самый вложенный узел (он выполняется первым), сравниваю оценку rows из планировщика с фактическим actual rows из ANALYZE, ищу большие расхождения и узлы с наибольшим временем. Сильное расхождение оценки и факта — обычно сигнал устаревшей статистики, лечится ANALYZE. Опция BUFFERS показывает, сколько данных читалось с диска, а сколько из кеша.

EXPLAIN (ANALYZE, BUFFERS)
SELECT status, count(*) FROM orders GROUP BY status;

Важная оговорка: Seq Scan — не всегда плохо. Если запрос возвращает заметную долю таблицы, планировщик осознанно выбирает полный проход, потому что случайные обращения по индексу вышли бы дороже. Индекс лечит выборку малой доли строк, а не любой медленный запрос.

Транзакции, уровни изоляции и MVCC

Транзакция — группа операций, которая применяется целиком или не применяется вовсе (свойства ACID). Внутри одной сессии это BEGIN ... COMMIT либо откат через ROLLBACK.

Под капотом согласованность обеспечивает MVCC (multiversion concurrency control). UPDATE не переписывает строку на месте: PostgreSQL создает новую версию строки, а старую помечает как устаревшую служебными полями видимости. Каждая транзакция работает со снимком данных, актуальным на свой момент. Отсюда ключевое следствие: читающие не блокируют пишущих, а пишущие не блокируют читающих. Плата за это — старые версии строк накапливаются, и их потом убирает VACUUM (следующий раздел).

Уровень изоляции задает, какие аномалии между параллельными транзакциями допустимы. В PostgreSQL их четыре, но фактически работают три — Read Uncommitted ведет себя как Read Committed, грязного чтения тут не бывает.

Read Committed видит каждый новый оператор в свежем снимке — между двумя SELECT в одной транзакции данные могут измениться. Repeatable Read фиксирует снимок на все время транзакции.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(amount) FROM orders;      -- допустим, 25 000 000.00
-- в этот момент другая сессия вставила и закоммитила новые заказы
SELECT sum(amount) FROM orders;      -- то же 25 000 000.00: снимок зафиксирован
COMMIT;

Serializable — самый строгий: PostgreSQL отслеживает опасные пересечения и при риске нарушения откатывает одну из транзакций с ошибкой сериализации. Это не «медленно», это условие корректности для сценариев вроде переводов между счетами. Расплата — приложение обязано уметь повторить транзакцию, получившую ошибку сериализации (код SQLSTATE 40001). Упрощение: считать снимок «замороженной копией базы» — удобная модель, но точная видимость зависит от уровня изоляции и уже открытых параллельных транзакций.

VACUUM, autovacuum и раздувание таблиц

Из MVCC следует проблема: устаревшие версии строк (dead tuples) занимают место, пока их кто-то может увидеть. Когда таких версий много, таблица и ее индексы физически распухают — это bloat (раздувание). Симптом — таблица на диске растет, а число живых строк стоит на месте, запросы и Seq Scan замедляются.

Оценить масштаб можно по системной статистике.

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

Убирает мертвые версии VACUUM: он не возвращает место операционной системе, но освобождает его для повторного использования внутри таблицы и обновляет карту видимости. Обычно эту работу делает autovacuum — фоновый процесс, который запускается сам, когда доля изменений превышает порог. Его не отключают: без него bloat растет неограниченно. Тонкая настройка идет через параметры вроде autovacuum_vacuum_scale_factor глобально или на уровне таблицы.

-- Разово и вручную, с обновлением статистики и отчетом
VACUUM (VERBOSE, ANALYZE) orders;

Отдельно стоит VACUUM FULL — он физически переписывает таблицу и возвращает место в ОС, но берет блокировку ACCESS EXCLUSIVE: на время работы таблица недоступна ни для чтения, ни для записи. На бою это опасная команда.

-- ОПАСНО на проде: полная блокировка таблицы на время работы.
-- Безопасный порядок: окно обслуживания, оценка размера и времени,
-- при возможности - онлайн-альтернатива pg_repack вместо VACUUM FULL.
VACUUM FULL orders;   -- выполнять только осознанно, вне пиковой нагрузки

Если раздулись именно индексы, их пересобирают без долгой блокировки через REINDEX INDEX CONCURRENTLY idx_orders_user;.

Оптимизация запросов: порядок действий

Оптимизацию удобно вести как чек-лист, а не наугад:

1. Снять план проблемного запроса через EXPLAIN (ANALYZE, BUFFERS).
2. Найти узел с наибольшим временем и Seq Scan там, где выбирается малая доля строк.
3. Проверить, свежая ли статистика: расхождение оценки и факта — повод сделать ANALYZE.
4. Подобрать индекс под условие фильтра и сортировки; для узкого среза — частичный.
5. Пересмотреть сам запрос: лишние сортировки, функции над индексируемым столбцом (WHERE lower(email) = ... мешает обычному индексу), выборка ненужных столбцов.
6. Проверить, не мешает ли bloat: большой разрыв живых и мертвых строк лечит VACUUM.

Порядок важен: сначала измеряю планом, потом меняю схему. Добавлять индексы вслепую — значит замедлить запись, не ускорив чтение.

Выводы

• Индекс подбирают под шаблон запроса: B-tree — база под равенство и диапазоны, GIN — под jsonb и массивы, BRIN — под большие упорядоченные таблицы, частичный — под устойчивый узкий срез.
• EXPLAIN показывает план без выполнения, EXPLAIN ANALYZE выполняет запрос и дает факт; расхождение оценки и факта — сигнал обновить статистику через ANALYZE.
• MVCC дает параллелизм без взаимных блокировок чтения и записи, но плодит мертвые версии строк; уровень изоляции выбирают под требования к корректности.
• Serializable закрывает аномалии сериализации ценой возможных откатов — приложение должно уметь повторять транзакцию.
• VACUUM и autovacuum убирают мертвые кортежи и сдерживают раздувание; VACUUM FULL возвращает место в ОС, но блокирует таблицу целиком.

Где применяется / связь с практикой

Индексы, чтение планов, изоляция транзакций и настройка autovacuum — ежедневный инструментарий администратора баз данных и backend-разработчика, который отвечает за производительность. Это ровно те навыки, по которым запрос «медленно работает база» превращается в конкретную правку схемы или запроса, а не в увеличение железа.

FAQ

Нужно ли создавать индекс под первичный ключ и внешний ключ?
Под PRIMARY KEY и UNIQUE индекс PostgreSQL создает автоматически. А под FOREIGN KEY — нет: индекс на столбце-ссылке добавляют вручную, иначе проверки и каскадные операции идут перебором.

Почему запрос игнорирует созданный индекс?
Частые причины: устаревшая статистика (помогает ANALYZE), запрос возвращает большую долю строк (планировщик осознанно берет Seq Scan) или функция над столбцом (lower(col)) — тогда нужен индекс по выражению.

Чем VACUUM отличается от VACUUM FULL?
Обычный VACUUM освобождает место для повторного использования внутри таблицы и не блокирует ее. VACUUM FULL физически переписывает таблицу, возвращает место в ОС, но берет полную блокировку — его запускают только в окно обслуживания.