EXPLAIN ANALYZE без слёз: 11 промтов, которые вытянут PostgreSQL и MongoDB, когда DBA в отпуске

PostgreSQL без DBA: 11 промтов, которые переписывают медленные запросы, индексы и миграции за вас

Вы — разработчик, аналитик или тимлид, который отвечает за базу данных, но рядом нет выделенного DBA. Запросы внезапно начинают тормозить, индексы не используются, миграции ломают продакшен, а ORM генерирует N+1. Знакомо? По данным опроса Stack Overflow Developer Survey 2025, PostgreSQL остаётся самой популярной СУБД среди разработчиков (её используют около 49% респондентов), но при этом лишь в крупных компаниях есть штатные администраторы БД. Остальным приходится справляться своими силами — и здесь на помощь приходят большие языковые модели.

Эта подборка — не просто список промтов. Это практический инструмент, который поможет вам диагностировать и оптимизировать PostgreSQL-базы без глубоких знаний в администрировании. Каждый промт содержит задачу, готовый текст для копирования, пример результата и пояснение, когда его применять. Мы разберём разбор EXPLAIN ANALYZE, переписывание JOIN-монстров, генерацию безопасных миграций, партиционирование, устранение N+1 в ORM и многое другое. Все примеры проверены на PostgreSQL 16+ и соответствуют официальной документации.

1. Диагностика медленного запроса через EXPLAIN ANALYZE

Задача: У вас есть запрос, который выполняется секундами, и нужно понять, почему.

Промт:

Ты — эксперт по PostgreSQL. Проанализируй вывод EXPLAIN (ANALYZE, BUFFERS) для следующего запроса. Найди узкие места: последовательные сканирования, неэффективные соединения, неверные оценки кардинальности, избыточные буферные чтения. Предложи конкретные шаги: какие индексы создать, как переписать запрос, нужно ли обновить статистику. Приведи альтернативный вариант запроса с комментариями.

Запрос: [вставьте SQL]
План: [вставьте вывод EXPLAIN ANALYZE]

Пример результата: Модель укажет на Seq Scan по таблице orders с фильтром по user_id, предложит создать индекс CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);, а также обратит внимание на неверную оценку rows (планировщик ожидал 100 строк, а получил 10 000) и порекомендует выполнить ANALYZE orders;.

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

2. Переписывание JOIN-монстра

Задача: Запрос с 5+ JOIN и подзапросами работает медленно и не поддаётся оптимизации индексами.

Промт:

Перепиши следующий SQL-запрос, чтобы уменьшить количество JOIN и подзапросов. Используй CTE (WITH), оконные функции или временные таблицы, если это улучшит план. Сохрани семантику. Объясни, почему новая версия будет быстрее. Приведи план выполнения до и после (можно гипотетически).

Исходный запрос: [вставьте SQL]

Пример результата: Модель может предложить заменить коррелированный подзапрос на LEFT JOIN LATERAL, а также разбить запрос на два CTE с материализацией. Например, вместо SELECT * FROM a WHERE id IN (SELECT a_id FROM b WHERE ...) предложит JOIN b ON b.a_id = a.id с предварительной фильтрацией.

Когда применять: Когда запрос содержит множество вложенных SELECT или JOIN, а индексы уже созданы, но не помогают.

3. Генерация безопасной миграции

Задача: Нужно изменить схему (добавить колонку, переименовать таблицу) без блокировки таблицы и потери данных.

Промт:

Сгенерируй безопасную миграцию для PostgreSQL, которая добавляет новую колонку email в таблицу users с ограничением NOT NULL и уникальным индексом. Учти, что таблица большая (миллион строк), и нельзя блокировать запись. Используй подход с отдельными шагами: добавление колонки с DEFAULT, заполнение, создание индекса CONCURRENTLY, добавление ограничения. Напиши SQL для каждого шага с пояснениями.

Пример результата:

-- Шаг 1: Добавить колонку без NOT NULL
ALTER TABLE users ADD COLUMN email TEXT;
-- Шаг 2: Заполнить значения (батчами)
UPDATE users SET email = 'user'

|| id || '@example.com' WHERE email IS NULL;
-- Шаг 3: Создать уникальный индекс CONCURRENTLY
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Шаг 4: Добавить ограничение NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Когда применять: При любых изменениях схемы в продакшене, особенно на больших таблицах.

4. Поиск недостающих индексов

Задача: Запросы часто выполняют Seq Scan, хотя могли бы использовать индекс.

Промт:

Проанализируй следующие запросы и определи, какие индексы отсутствуют. Учти селективность, порядок колонок в WHERE и JOIN. Предложи конкретные CREATE INDEX с указанием типа (B-tree, GIN, GiST) и обоснованием. Если индекс уже есть, но не используется, объясни почему.

Запросы:
1. [SQL]
2. [SQL]

Пример результата: Модель порекомендует составной индекс (status, created_at) для запроса с фильтром по статусу и сортировкой по дате, а также частичный индекс WHERE status = 'active' для ускорения выборки активных записей.

Когда применять: После сбора статистики pg_stat_user_tables и pg_stat_user_indexes, когда видите много seq_scan.

5. Партиционирование большой таблицы

Задача: Таблица разрослась до сотен гигабайт, запросы замедлились, очистка старых данных занимает часы.

Промт:

Предложи стратегию партиционирования для таблицы events (event_time timestamp, user_id int, data jsonb). Таблица растёт на 10 млн строк в месяц, храним 12 месяцев. Нужно ускорить запросы по диапазону дат и упростить удаление старых данных. Напиши SQL для создания партиционированной таблицы, секций на месяц, а также пример запроса с учётом партиционирования. Учти ограничения PostgreSQL 16.

Пример результата:

CREATE TABLE events (
  event_time TIMESTAMP NOT NULL,
  user_id INT,
  data JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_10 PARTITION OF events
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Модель также посоветует использовать DETACH PARTITION для удаления старых секций вместо DELETE.

Когда применять: Когда таблица превышает несколько десятков гигабайт и растёт линейно.

6. Устранение N+1 в ORM

Задача: Приложение на Django/SQLAlchemy генерирует сотни запросов при обходе связей.

Промт:

У меня есть ORM-код (укажите фреймворк), который вызывает N+1 запросов. Проанализируй код, найди проблемные места и предложи оптимизацию с помощью жадной загрузки (select_related/prefetch_related в Django, joinedload в SQLAlchemy). Приведи исправленный код и объясни, как это уменьшит количество запросов.

Код: [вставьте код]

Пример результата: Для Django модель предложит заменить for book in Book.objects.all(): print(book.author.name) на Book.objects.select_related('author').all(), что сократит количество запросов с N+1 до 1.

Когда применять: При обнаружении множества однотипных запросов в логах БД или при использовании Django Debug Toolbar.

7. Оптимизация запросов с JSONB

Задача: Запросы к JSONB-полям работают медленно, хотя данные проиндексированы GIN.

Промт:

У меня есть таблица с колонкой data JSONB, и запросы вида `SELECT * FROM t WHERE data @> '{"key": "value"}'` выполняются долго. Предложи, как улучшить производительность: какие индексы использовать (GIN, GIST, B-tree), как переписать запрос, чтобы задействовать индекс. Приведи примеры EXPLAIN ANALYZE до и после.

Схема таблицы: [описание]
Пример запроса: [SQL]

Пример результата: Модель порекомендует создать GIN-индекс с jsonb_path_ops для ускорения оператора @>, а также использовать функциональный индекс по конкретному ключу, если запросы всегда фильтруют по одному полю.

Когда применять: Если вы активно используете JSONB и заметили, что запросы не используют существующие индексы.

8. Настройка autovacuum для больших таблиц

Задача: Таблица часто обновляется, но autovacuum не успевает, что приводит к раздуванию и замедлению.

Промт:

У меня есть таблица с частыми UPDATE/DELETE (более 1000 операций в минуту). Autovacuum не справляется, размер таблицы растёт. Предложи настройки autovacuum для этой таблицы (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold и др.). Объясни, как эти параметры влияют на сборку мусора. Напиши SQL для изменения настроек на уровне таблицы.

Пример результата:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 100);

Модель пояснит, что уменьшение scale_factor заставит autovacuum запускаться чаще, но это увеличит нагрузку на I/O.

Когда применять: При частых обновлениях и удалениях в больших таблицах, когда стандартные настройки не справляются.

9. Анализ и оптимизация блокировок

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

Промт:

Проанализируй вывод pg_locks и pg_stat_activity. Найди блокирующие и ожидающие транзакции. Предложи, как разрешить конфликт (завершить процесс, изменить порядок операций, использовать NOWAIT). Напиши SQL для диагностики и решения. Объясни, как предотвратить подобные ситуации в будущем.

Вывод pg_locks: [вставьте]
pg_stat_activity: [вставьте]

Пример результата: Модель определит, что транзакция с pg_backend_pid=1234 держит блокировку на таблицу users, и предложит выполнить SELECT pg_terminate_backend(1234); (если это безопасно), а также изменить код, чтобы уменьшить время удержания блокировки.

Когда применять: При периодических зависаниях запросов или ошибках «deadlock detected».

10. Генерация тестовых данных для нагрузки

Задача: Нужно протестировать производительность запросов на реалистичных данных, но нет продакшен-дампа.

Промт:

Сгенерируй SQL для создания тестовой таблицы users с 1 миллионом строк. Колонки: id serial, name text, email text, created_at timestamp, status text. Данные должны быть реалистичными: имена, email, случайные даты за последний год, статусы из набора ('active', 'inactive', 'pending'). Используй generate_series и случайные функции. Напиши запрос для вставки.

Пример результата:

INSERT INTO users (name, email, created_at, status)
SELECT
  'User' || i,
'user'

|| i || '@example.com',
  NOW() - (random() * INTERVAL '365 days'),
  (ARRAY['active','inactive','pending'])[floor(random()*3+1)]
FROM generate_series(1, 1000000) AS i;

Когда применять: Для нагрузочного тестирования и проверки планов запросов на больших объёмах.

11. Перевод запроса с MongoDB на PostgreSQL

Задача: Вы мигрируете с MongoDB на PostgreSQL и нужно перевести агрегационный пайплайн в SQL.

Промт:

Переведи следующий MongoDB aggregation pipeline в SQL для PostgreSQL. Используй JSONB для хранения документов, если это необходимо. Сохрани логику группировок, сортировок и вычислений. Приведи эквивалентный SQL с комментариями.

Pipeline: [вставьте JSON]

Пример результата: Модель преобразует $group в GROUP BY, $match в WHERE, $project в SELECT с алиасами. Для вложенных документов предложит использовать операторы ->> и #>>.

Когда применять: При миграции с NoSQL на реляционную БД или при гибридном использовании.

Все эти промты — не волшебная таблетка, а инструмент, который экономит время и помогает принимать обоснованные решения. Экспериментируйте, адаптируйте под свои задачи и всегда проверяйте рекомендации на тестовой среде. Помните: даже самая умная модель не заменит понимания основ работы СУБД, но может стать отличным помощником в повседневной работе.

Если вы хотите систематизировать знания по работе с PostgreSQL и MongoDB без DBA, обратите внимание на практические курсы, где разбираются реальные кейсы оптимизации. А пока — сохраняйте подборку в закладки и применяйте промты в своих проектах. Удачной оптимизации!