PostgreSQL под нагрузкой: промты, которые превращают EXPLAIN в план действий

PostgreSQL под нагрузкой: промты, которые превращают EXPLAIN в план действий

Медленный запрос в проде — это не повод паниковать, а задача на 15 минут, если правильно разговаривать с ИИ. За годы работы с PostgreSQL я собрал набор промтов, которые стабильно экономят часы: от разбора планов через EXPLAIN (ANALYZE, BUFFERS) до миграций без даунтайма по правилам из документации PostgreSQL.

Главный принцип: модель не должна угадывать схему. Всегда передавайте DDL таблиц, вывод EXPLAIN целиком и версию сервера (SELECT version();). Так ответы перестают быть общими и превращаются в конкретные рекомендации.

Ниже — 10 промтов с реальными примерами. Синтаксис и фичи проверены по официальной документации: PostgreSQL EXPLAIN, CREATE INDEX, ALTER TABLE, Window Functions, JSONB.

1. Разбор EXPLAIN-плана на человеческом языке

Задача: понять, где именно теряется время.
Промт: «Ты DBA PostgreSQL. Вот вывод EXPLAIN (ANALYZE, BUFFERS) и DDL таблиц. Объясни каждый узел плана, найди узкие места (Seq Scan, Nested Loop с большим числом итераций, Sort на диск), оцени соответствие оценок планировщика реальности. Дай 3 гипотезы и способ проверки каждой».

Передавайте не только cost, но и actual time, rows, loops, а также Buffers. Если rows в плане отличается от actual rows в разы — планировщик ошибается в селективности, и это первый кандидат на разбор.

2. Переписывание медленного JOIN

Задача: ускорить запрос, который «повесил» реплику.
Промт: «Перепиши этот запрос так, чтобы планировщик мог использовать Hash Join вместо Nested Loop. Сохрани семантику, покажи до/после и объясни, почему новый план лучше».

Пример: вместо коррелированного подзапроса в SELECT часто выигрывает JOIN с агрегатом в CTE. Но не увлекайтесь: PostgreSQL 12+ умеет инлайнить CTE (MATERIALIZED / NOT MATERIALIZED — см. WITH Queries). Просите модель явно указать, где нужна материализация.

3. Генерация индекса под конкретный запрос

Задача: добавить индекс, не сломав запись.
Промт: «Предложи индекс для этого WHERE/ORDER BY. Укажи тип (B-tree, GIN, BRIN), порядок колонок, частичность, INCLUDE. Напиши CREATE INDEX CONCURRENTLY и предупреди о блокировках».

Правила, которые я всегда держу под рукой:

CREATE INDEX CONCURRENTLY не берёт тяжёлую блокировку, но не работает внутри транзакции и может оставить невалидный индекс — проверяйте pg_index.indisvalid.

4. Безопасные миграции без даунтайма

Задача: добавить колонку и бэкфилл на таблице в 500 млн строк.
Промт: «Составь пошаговый план миграции без блокировок: добавление nullable-колонки, батчевый бэкфилл, добавление constraint через NOT VALID + VALIDATE CONSTRAINT, переключение приложения. Укажи, что откатывается».

Ключевые факты из документации: ALTER TABLE ... ADD COLUMN без DEFAULT в PostgreSQL 11+ не переписывает таблицу; ADD CONSTRAINT ... NOT VALID затем VALIDATE CONSTRAINT берёт SHARE UPDATE EXCLUSIVE, а не ACCESS EXCLUSIVE. Это и есть основа zero-downtime миграций.

5. Оконные функции вместо самодельных агрегатов

Задача: ранжирование и накопительные итоги.
Промт: «Перепиши этот запрос с GROUP BY/подзапросами на оконные функции. Нужны: топ-3 по выручке в каждой категории, накопительная сумма по дням, разница с предыдущей строкой».

Каркас, который модель обычно выдаёт корректно:

SELECT category, product, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn,
       SUM(revenue) OVER (PARTITION BY category ORDER BY day) AS running_total,
       revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY day) AS delta
FROM sales;

Оконные функции выполняются после WHERE и GROUP BY, поэтому фильтровать по rn нужно во внешнем запросе.

6. Работа с JSONB без боли

Задача: вытащить данные из jsonb-колонки.
Промт: «Покажи запросы с операторами ->, ->>, @>, ?| для этой структуры. Предложи GIN-индекс и проверь, что он используется через EXPLAIN».

Пример: SELECT data->>'email' FROM users WHERE data @> '{"plan":"pro"}'; — оператор @> (containment) индексируется GIN-ом, в отличие от data->>'plan' = 'pro', который требует expression-индекса.

7. Диагностика блокировок и deadlock-ов

Задача: прод встал, запросы ждут.
Промт: «Вот вывод pg_locks, pg_stat_activity и pg_blocking_pids(). Найди цепочку блокировок, назови транзакцию-источник, предложи безопасный способ её завершить».

Всегда уточняйте pg_blocking_pids(pid) — это быстрее, чем вручную сопоставлять pg_locks. И помните: pg_terminate_backend() — крайняя мера, сначала проверьте wait_event_type = 'Lock'.

8. Партиционирование по времени

Задача: таблица логов растёт на миллионы строк в день.
Промт: «Спроектируй декларативное партиционирование по RANGE (created_at) с месячными партициями. Дай DDL родителя, пример создания партиции и автосоздание через pg_partman или cron».

PostgreSQL 10+ поддерживает декларативное партиционирование; начиная с 11-й версии появился routing вставок и поддержка уникальных ключей, включающих ключ партиционирования. Планировщик отсекает партиции по WHERE created_at >= ... — это главный выигрыш.

9. Батчевый UPDATE/DELETE вместо одного большого

Задача: удалить старые записи, не раздувая WAL и autovacuum.
Промт: «Перепиши DELETE FROM logs WHERE created_at < now() - interval '90 days' на батчевый с ctid и LIMIT, добавь COMMIT между батчами и метрику прогресса».

DELETE FROM logs
WHERE ctid IN (
  SELECT ctid FROM logs WHERE created_at < now() - interval '90 days' LIMIT 10000
);

Повторяйте в цикле, пока DELETE 0. Такой подход снижает риск долгих блокировок и переполнения WAL.

10. Ревью запроса перед мержем

Задача: не пропустить опасный SQL в код-ревью.
Промт: «Проверь этот SQL как ревьюер: есть ли Seq Scan на больших таблицах, неявные касты, SELECT *, отсутствие лимитов, N+1 на стороне ORM. Дай чек-лист и конкретные правки».

Этот промт хорошо работает вместе с EXPLAIN на staging с реалистичным объёмом данных — на пустой таблице планировщик почти всегда выберет Seq Scan и введёт в заблуждение.

Главный вывод за годы практики: ИИ ускоряет работу с PostgreSQL ровно настолько, насколько полный контекст вы ему даёте. DDL, версия, план, объём данных — и промт превращается в работу senior-DBA за минуты. Начните с промта №1: возьмите самый медленный запрос из pg_stat_statements и разберите его план сегодня.

Если хотите глубже — держите под рукой официальную документацию и pg_stat_statements; а системный подход к таким задачам можно прокачать в текстовых курсах ASI Biont.