12 промтов для PostgreSQL: от разбора EXPLAIN ANALYZE до аналитики на миллиардах строк

Почему промт к ChatGPT — это ещё один инструмент рядом с pgAdmin

Каждый, кто хотя бы раз открывал EXPLAIN ANALYZE на запросе с шестью JOIN-ами и миллиардом строк, знает это чувство: вывод на 300 строк, Seq Scan там, где его быть не должно, и вопрос «с чего вообще начать». Официальная документация PostgreSQL (https://www.postgresql.org/docs/current/using-explain.html) подробно описывает каждое поле плана, но не объясняет, что именно ломается в вашем запросе.

Здесь и помогает LLM. Не как «магия, которая сама всё ускорит», а как терпеливый коллега-архитектор, который прочитает план, укажет на узкое место и предложит три варианта переписывания. В этой подборке — 12 промтов, которые я реально использую в работе с PostgreSQL 16/17: от чтения планов до партиционирования таблиц на сотни гигабайт. Каждый промт написан так, чтобы вы могли подставить свою схему и получить конкретный, проверяемый результат.

Важно: ни один промт не отменяет проверку на реальных данных. ChatGPT не видит вашу статистику pg_statistic, поэтому любую рекомендацию по индексам и переписыванию запроса нужно подтверждать через EXPLAIN (ANALYZE, BUFFERS) на репрезентативной выборке.

1. Разбор плана EXPLAIN ANALYZE: «объясни, где я теряю время»

Когда использовать: запрос работает секунды или минуты, а вы не понимаете, какой узел плана доминирует.

Промт:

Ты — эксперт по PostgreSQL 16. Ниже полный вывод EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT).
Схема таблиц: <вставь \d+ таблиц или CREATE TABLE>.
Объём: users ~ 50M строк, orders ~ 2B строк, order_items ~ 6B строк.

1. Найди топ-3 узла плана по вкладу в actual time (не по стоимости).
2. Для каждого объясни, ПОЧЕМУ оптимизатор выбрал этот путь (оценки rows vs actual rows, selectivity).
3. Укажи, где estimate сильно расходится с actual и что это значит для статистики.
4. Предложи 3 гипотезы ускорения с ожидаемым эффектом и способом проверки.

<вставь вывод EXPLAIN ANALYZE>

Пример фрагмента плана:

Nested Loop  (cost=0.86..142390.55 rows=1 width=64) (actual time=0.041..8421.331 rows=248 rows=248 loops=1)
  Buffers: shared hit=18234 read=92011
  ->  Index Scan using orders_customer_id_idx on orders o (actual time=0.021..0.4 rows=12 loops=1)
  ->  Index Scan using order_items_order_id_idx on order_items oi (actual time=0.02..680.1 rows=21 loops=1)
        Filter: (oi.quantity > 0)
        Rows Removed by Filter: 18432

Что искать в ответе: расхождение rows=21 (оценка) и rows Removed by Filter: 18432 — типичный признак устаревшей статистики или коррелированных колонок. В таких случаях CREATE STATISTICS ... (dependencies, ndistinct) часто работает лучше, чем новый индекс.

Как адаптировать: замените объёмы таблиц и вставьте реальный вывод EXPLAIN (ANALYZE, BUFFERS). Просите ответ в формате таблицы «узел → проблема → действие» — так проще действовать по шагам.

2. Переписывание медленного JOIN: подсказки против материализации

Когда использовать: Nested Loop на миллионах строк или Hash Join, который упирается в память.

Промт:

Этот запрос выполняется 47 секунд на PostgreSQL 16, work_mem = 64MB, shared_buffers = 8GB.
Перепиши его минимум двумя способами: (a) через EXISTS/LATERAL, (b) через оконную функцию или агрегацию заранее.
Для каждого варианта: сохранит ли он семантику? какие индексы нужны? какой ожидаемый план?
Не предлагай изменения схемы, если можно обойтись запросом.

Запрос:
SELECT o.id, o.created_at, c.email, sum(oi.price*oi.qty) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid' AND o.created_at >= now() - interval '30 days'
GROUP BY o.id, o.created_at, c.email
ORDER BY total DESC LIMIT 100;

Типичный ответ, который работает: заменить JOIN на customers подзапросом или LEFT JOIN LATERAL, а агрегацию order_items вынести в CTE с MATERIALIZED. Часто помогает и «двухфазный» подход: сначала выбрать топ-100 orders, потом джойнить детали.

Проверка: сравните EXPLAIN (ANALYZE, BUFFERS) до и после. Смотрите не на cost, а на actual time и Buffers: read.

3. Проектирование индексов: частичные, covering и составные

Когда использовать: после анализа плана, когда ясно, что нужен индекс, но непонятно какой.

Промт:

Схема: orders(id bigint, customer_id bigint, status text, created_at timestamptz, total numeric, payload jsonb)
Запросы (топ-5 по частоте):
1) WHERE status='paid' AND created_at > $1 ORDER BY created_at DESC LIMIT 50
2) WHERE customer_id = $1 AND status IN ('paid','shipped')
3) SELECT id, total FROM orders WHERE created_at::date = $1

Предложи минимальный набор индексов. Для каждого: тип (btree/gin/brin), колонки, порядок, INCLUDE, WHERE-условие.
Объясни, почему не стоит добавлять остальные. Учти стоимость записи и размер.

Реальные варианты из ответа:

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
  ON orders (created_at DESC)
  INCLUDE (id, total)
  WHERE status = 'paid';

CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status IN ('paid','shipped');

Важно: запрос №3 с created_at::date = $1 не использует индекс по created_at, потому что функция слева убивает sargability. Правильный вариант — created_at >= $1 AND created_at < $1 + interval '1 day'. Попросите модель явно указать такие «индекс-убийцы» — она находит их быстро.

Как адаптировать: дайте реальные топ-запросы из pg_stat_statements (SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20).

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

Когда использовать: ALTER TABLE на таблице, которая не может лежать в блокировке.

Промт:

Мне нужно на таблице orders (2B строк, 400GB, hot path) сделать:
- добавить колонку currency text NOT NULL DEFAULT 'USD'
- добавить индекс по (customer_id, created_at)
- переименовать колонку total в amount

PostgreSQL 16. Составь пошаговый план миграции без длительных блокировок.
Для каждого шага: SQL, тип блокировки, ожидаемое время, что делать при откате.
Учти, что ADD COLUMN с DEFAULT в PG 11+ не перезаписывает таблицу — подтверди это.

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

• В PostgreSQL 11+ ADD COLUMN ... DEFAULT с константным значением — операция только с метаданными (подтверждено в release notes: https://www.postgresql.org/docs/11/release-11.html).
• CREATE INDEX CONCURRENTLY не блокирует запись, но не работает внутри транзакции и может оставить invalid index при сбое.
• ALTER TABLE ... RENAME COLUMN берёт ACCESS EXCLUSIVE lock, но выполняется мгновенно.

Шаблон безопасного порядка:

-- 1. Добавить колонку (metadata-only)
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'USD';
-- 2. Индекс параллельно
CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at);
-- 3. Переименование в окне низкой нагрузки, с lock_timeout
SET lock_timeout = '3s';
ALTER TABLE orders RENAME COLUMN total TO amount;

Как адаптировать: всегда указывайте версию PostgreSQL — поведение ADD COLUMN и SET NOT NULL менялось между версиями.

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

Когда использовать: retention, cohort-анализ, running totals, дедупликация.

Промт:

PostgreSQL 16. Таблица events(user_id bigint, event_name text, ts timestamptz) — 8B строк, партиционирована по месяцам.
Нужно: для каждого пользователя — дата первого события, дата последнего, число активных дней, и флаг «вернулся в течение 7 дней после регистрации».
Напиши один запрос с оконными функциями. Объясни, почему не стоит делать self-join.
Покажи план: какие оконные функции требуют сортировки, как уменьшить work_mem pressure.

Пример запроса:

WITH first_seen AS (
  SELECT user_id, min(ts) AS first_ts
  FROM events GROUP BY user_id
)
SELECT e.user_id,
       f.first_ts,
       max(e.ts) AS last_ts,
       count(DISTINCT e.ts::date) AS active_days,
       bool_or(e.ts <= f.first_ts + interval '7 days' AND e.ts > f.first_ts) AS returned_7d
FROM events e
JOIN first_seen f USING (user_id)
GROUP BY e.user_id, f.first_ts;

Тонкость: count(DISTINCT ...) в оконных контекстах дорогой; на больших объёмах лучше COUNT(DISTINCT ...) заменить на подзапрос с GROUP BY user_id, ts::date и count(*). Попросите модель сравнить оба варианта по плану.

6. JSONB-запросы: GIN, jsonpath и когда переходить в колонки

Когда использовать: есть payload jsonb или attributes jsonb и запросы по вложенным ключам.

Промт:

PostgreSQL 16, таблица products(id, name, attributes jsonb) — 30M строк.
Запросы:
1) attributes @> '{"color":"red"}'
2) attributes->>'brand' = 'Acme'
3) attributes @? '$.specs.weight ? (@ > 10)'

Предложи индексы (GIN с jsonb_path_ops vs jsonb_ops), объясни разницу.
Для каждого запроса покажи, какой индекс сработает, а какой нет.
Если ключ brand используется в 80% запросов — стоит ли вынести его в отдельную колонку с btree-индексом?

Ответ, который подтверждается документацией (https://www.postgresql.org/docs/current/datatype-json.html):

• jsonb_ops поддерживает ?, ?|, ?&, @> и извлечение ключей.
• jsonb_path_ops компактнее и быстрее для @>, но не поддерживает ?-операторы.

-- для @> и @?
CREATE INDEX CONCURRENTLY idx_products_attrs_path ON products USING gin (attributes jsonb_path_ops);
-- если нужны ?-операторы
CREATE INDEX CONCURRENTLY idx_products_attrs ON products USING gin (attributes);

Практический вывод: если ключ brand встречается в большинстве запросов, вынос в brand text + btree-индекс почти всегда быстрее GIN. GIN выигрывает, когда ключи разнородны.

7. Партиционирование больших таблиц: range, list и hash

Когда использовать: таблица перевалила за несколько сотен гигабайт, VACUUM не успевает, старые данные нужно быстро удалять.

Промт:

PostgreSQL 16. Таблица logs(id bigserial, ts timestamptz, level text, message text, service text) — 1.2B строк, растёт на 50M/месяц.
Нужно: партиционировать по ts (range, месяцы), хранить 12 месяцев, старые партиции удалять.
1. Составь пошаговый план миграции существующей таблицы в партиционированную без простоя.
2. Как удалять старые партиции (DROP vs DETACH)?
3. Как избежать проблемы с глобальными уникальными индексами?
4. Стоит ли делать sub-partitioning по service?

Что важно помнить: в PostgreSQL до 11 включительно партиционирование работало через наследование (INHERITS), а декларативное PARTITION BY появилось в PG 10 и серьёзно улучшилось в PG 11+ (partition pruning, поддержка PK/FK). В PG 17 добавили улучшения для partition-wise join. Проверяйте версию.

Безопасный паттерн миграции:

-- создаём новую партиционированную таблицу
CREATE TABLE logs_new (LIKE logs INCLUDING ALL) PARTITION BY RANGE (ts);
-- партиции по месяцам
CREATE TABLE logs_2026_09 PARTITION OF logs_new FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
-- переливаем батчами, затем переименовываем в окне

Как адаптировать: укажите частоту вставок и допустимое окно простоя — от этого зависит, делать ли триггерную репликацию или батч-перелив.

8. Генерация реалистичных тестовых данных

Когда использовать: нужно нагрузить staging данными, похожими на прод, без выгрузки PII.

Промт:

PostgreSQL 16. Сгенерируй SQL для наполнения таблиц:
customers(id, email, created_at, country), orders(id, customer_id, created_at, status, total)
Нужно: 100k клиентов, 5M заказов, распределение статусов как в реальности (paid 70%, shipped 20%, cancelled 10%),
created_at — за последние 2 года с ростом объёма к текущей дате (не uniform).
Используй generate_series, random(), нормальное распределение через Box-Muller.
Не используй расширения, только стандартный PG.

Рабочий приём:

INSERT INTO customers (email, created_at, country)
SELECT 'user'

|| g || '@example.com',
       now() - (random() * interval '730 days'),
       (ARRAY['US','DE','BR','IN','JP'])[1 + floor(random()*5)::int]
FROM generate_series(1, 100000) g;

Для «роста к текущей дате» используйте взвешивание: now() - (random()^2 * interval '730 days') — плотность смещается к настоящему.

9. Оптимизация VACUUM и autovacuum для write-heavy таблиц

Когда использовать: таблица с высокой частотой UPDATE/DELETE, bloat растёт, autovacuum не успевает.

Промт:

PostgreSQL 16. Таблица sessions(id, user_id, data jsonb, updated_at) — 200M строк, 90% UPDATE, autovacuum default.
Проблема: n_dead_tup растёт, bloat > 40%, autovacuum запускается редко.
1. Какие параметры autovacuum менять для этой таблицы (per-table storage params)?
2. Как оценить bloat без pgstattuple?
3. Когда нужен pg_repack?
Дай конкретные значения и обоснование.

Что часто советует модель (и это подтверждается документацией https://www.postgresql.org/docs/current/runtime-config-autovacuum.html):

ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

pg_repack нужен, когда bloat уже занял место и autovacuum не возвращает его ОС. Для оценки bloat без pgstattuple используйте запросы на основе pg_class.relpages vs pgstatindex или известные скрипты (например, от ioguix, https://github.com/ioguix/pgsql-bloat-estimation).

10. Чтение pg_stat_statements: найти реальные узкие места

Когда использовать: регулярный аудит производительности, а не разовый разбор.

Промт:

Вот топ-20 запросов из pg_stat_statements по total_exec_time (PostgreSQL 16):
<вставь вывод>

1. Сгруппируй по паттерну (одинаковые запросы с разными параметрами).
2. Для каждого паттерна: сколько вызовов, среднее время, вклад в общее время.
3. Какие 3 запроса дадут максимальный выигрыш при оптимизации?
4. Для каждого предложи: индекс, переписывание или кэширование на уровне приложения.

Важный нюанс: pg_stat_statements нормализует запросы, заменяя константы на $1, но IN (...) с разным числом элементов считается разными запросами. Попросите модель учитывать это при группировке.

11. Разбор блокировок и deadlock-ов

Когда использовать: приложение периодически ловит deadlock detected или запросы висят.

Промт:

PostgreSQL 16. Вот лог: 'deadlock detected' с деталями процессов.
Вот DDL таблиц и порядок операций в двух транзакциях.
1. Восстанови последовательность блокировок, которая привела к deadlock.
2. Укажи минимальную правку в порядке операций (или в индексах), чтобы deadlock исчез.
3. Как настроить deadlock_timeout и log_lock_waits для диагностики?

Типичный вывод: две транзакции берут row-locks в разном порядке. Решение — детерминированный порядок (например, всегда обновлять строки по возрастанию id) или снижение уровня изоляции до READ COMMITTED с SELECT ... FOR UPDATE в начале.

Полезно помнить: PostgreSQL обнаруживает deadlock через deadlock_timeout (по умолчанию 1s), после чего один из процессов откатывается. Это документировано в https://www.postgresql.org/docs/current/runtime-config-locks.html.

12. Миграция схемы: сгенерировать и проверить diff

Когда использовать: перед релизом, когда нужно сверить «что в проде» и «что в миграциях».

Промт:

PostgreSQL 16. Вот текущая схема из pg_dump --schema-only (прод) и набор миграций из репозитория.
1. Найди расхождения: колонки, индексы, constraints, дефолты, типы.
2. Для каждого расхождения: это drift или корректное состояние?
3. Сгенерируй идемпотентный SQL для приведения прода к состоянию миграций.
4. Отметь опасные операции (DROP COLUMN, ALTER TYPE) и предложи безопасный порядок.

Практическая ценность: такой промт ловит классические проблемы — индекс, созданный вручную на проде и забытый в репозитории, или NOT NULL, добавленный без DEFAULT и ломающий вставки в старом коде.

Сводная таблица: какой промт под какую задачу

Как не навредить себе этими промтами

Три правила, которые сэкономят вам инциденты. Первое: любая рекомендация по индексу проверяется через EXPLAIN (ANALYZE, BUFFERS) на данных, близких к проду. LLM не видит вашу статистику и оценивает селективность «на глаз». Второе: CREATE INDEX CONCURRENTLY и ALTER TABLE всегда прогоняйте сначала на копии — модель может предложить синтаксически верный, но операционно опасный порядок. Третье: уточняйте версию PostgreSQL в каждом промте — поведение ADD COLUMN DEFAULT, партиционирования и NULLS NOT DISTINCT в уникальных индексах (появилось в PG 15) отличается между версиями.

Эти 12 промтов — не замена DBA, а способ сократить путь от «запрос тормозит» до «вот гипотеза, вот как её проверить». Начните с промта №1 на самом медленном запросе из pg_stat_statements — обычно именно он приносит первый ощутимый выигрыш в производительности. Официальная документация PostgreSQL (https://www.postgresql.org/docs/current/) остаётся главным источником истины, а промты — лишь ускорителем её чтения и применения.