PostgreSQL без боли: 13 промтов, которые ускоряют запросы и спасают от даунтайма

PostgreSQL без боли: 13 промтов, которые ускоряют запросы и спасают от даунтайма

Медленный SQL-запрос может обрушить продакшен, а миграция без блокировок — превратиться в ночной кошмар. По данным DB-Engines, PostgreSQL стабильно входит в топ-5 самых популярных СУБД, и с каждым годом нагрузка на инженеров данных растёт. Но что, если рутину — анализ планов, поиск узких мест, генерацию индексов — можно делегировать AI-ассистенту? В этой подборке — 13 проверенных промтов, которые превращают хаос в структурированный процесс оптимизации. Каждый промт сопровождается примером и пояснением, для каких задач он подходит.

1. Анализ EXPLAIN ANALYZE: находим самое дорогое место

Задача: быстро интерпретировать вывод EXPLAIN (ANALYZE, BUFFERS) и понять, какой узел плана съедает больше всего времени.

Промт:

Ты — эксперт по PostgreSQL. Проанализируй план выполнения ниже. Найди узлы с наибольшим actual time и rows. Объясни, почему оптимизатор выбрал Seq Scan вместо Index Scan. Предложи 2-3 конкретных шага: какие индексы создать или переписать запрос.

План:
<вставь вывод EXPLAIN ANALYZE>

Пример: вы запускаете EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'; и видите Seq Scan с 1.2 млн строк. Промт укажет на отсутствие составного индекса и предложит CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);.

Пояснение: всегда используйте BUFFERS, чтобы видеть I/O. AI не заменит DBA, но ускорит первичную диагностику.

2. Поиск узких мест в JOIN'ах

Задача: выявить неэффективные соединения, особенно когда одна таблица значительно больше другой.

Промт:

У меня есть запрос с JOIN трёх таблиц. Проанализируй план и скажи, где происходит Nested Loop с большим количеством итераций. Предложи, как изменить порядок JOIN'ов или добавить индексы. Вот DDL таблиц:
<вставь CREATE TABLE>
Вот запрос:
<вставь SQL>
Вот план:
<вставь EXPLAIN ANALYZE>

Пример: запрос объединяет users (10 тыс. строк), orders (5 млн) и products (100 тыс.). AI подскажет, что Nested Loop по orders без индекса на user_id даёт 5 млн итераций — нужно заменить на Hash Join или добавить индекс.

Пояснение: в PostgreSQL оптимизатор сам выбирает метод соединения, но статистика может устареть. Промт поможет понять, когда нужно ANALYZE.

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

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

Промт:

Сгенерируй DDL для индекса, который ускорит следующий запрос. Учти селективность колонок, порядок в составном индексе и возможность использования INCLUDE. Объясни, почему выбран именно такой порядок.

Запрос:
<вставь SQL>
Схема таблицы:
<вставь CREATE TABLE>

Пример: для запроса SELECT id, total FROM orders WHERE user_id = 1 AND created_at > '2026-01-01' ORDER BY created_at DESC LIMIT 10; AI предложит CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);.

Пояснение: CONCURRENTLY избегает блокировки таблицы. INCLUDE добавляет колонки для Index-Only Scan.

4. Переписывание N+1 запроса в один JOIN

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

Промт:

Преобразуй этот набор запросов (N+1) в один SQL с JOIN или подзапросом. Покажи исходный код на Python/Django и оптимизированный вариант. Оцени, во сколько раз сократится количество запросов.

Исходный код:
<вставь код>

Пример: в Django for user in users: print(user.orders.count()) даёт N+1. AI предложит User.objects.annotate(orders_count=Count('orders')) или сырой SQL с LEFT JOIN LATERAL.

Пояснение: N+1 — самая частая причина медленных API. Промт помогает быстро переписать на select_related/prefetch_related.

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

Задача: разбить таблицу на партиции по диапазону дат или хешу, чтобы ускорить запросы и упростить архивацию.

Промт:

Предложи стратегию партиционирования для таблицы <имя> с колонкой <дата>. Опиши шаги: создание партиционированной таблицы, миграция данных, создание индексов на партициях. Учти, что таблица уже содержит <N> строк и активно пишется.

DDL:
<вставь CREATE TABLE>

Пример: для events с 500 млн строк AI предложит PARTITION BY RANGE (created_at) с месячными партициями и автоматизацию через pg_partman.

Пояснение: в PostgreSQL 13+ есть встроенное партиционирование. Промт поможет избежать блокировок при миграции.

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

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

Промт:

Мне нужно добавить колонку <имя> типа <тип> в таблицу <имя> и создать индекс. Напиши последовательность SQL-команд для выполнения без блокировки чтения/записи. Используй CONCURRENTLY и избегай ACCESS EXCLUSIVE LOCK.

Текущий DDL:
<вставь>

Пример: AI предложит: ALTER TABLE users ADD COLUMN age INT; (без DEFAULT), затем CREATE INDEX CONCURRENTLY idx_users_age ON users(age);. Для NOT NULL — сначала добавить колонку, заполнить батчами, потом установить constraint.

Пояснение: в PostgreSQL 11+ добавление колонки с DEFAULT не переписывает таблицу, но блокировка всё равно возможна. Промт учитывает версию.

7. Аудит прав доступа

Задача: проверить, у кого есть лишние привилегии, и минимизировать риски.

Промт:

Проанализируй список ролей и их привилегий в PostgreSQL. Найди роли с правами SUPERUSER или CREATEDB, которые не должны их иметь. Предложи SQL для отзыва лишних прав и создания безопасной роли для приложения.

Список ролей:
<вставь вывод \du+>

Пример: AI обнаружит, что роль app_user имеет CREATEROLE, и предложит REVOKE CREATEROLE FROM app_user;.

Пояснение: аудит прав — обязательная часть compliance. Промт помогает быстро сгенерировать команды.

8. Мониторинг блокировок и deadlock'ов

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

Промт:

На основе вывода pg_locks и pg_stat_activity найди блокирующие транзакции. Объясни, какой запрос держит блокировку и какие ждут. Предложи, как переписать запросы, чтобы избежать deadlock.

Данные:
<вставь вывод запросов>

Пример: AI покажет, что UPDATE accounts SET balance = balance - 100 WHERE id = 1; ждёт блокировки от другой транзакции, и предложит упорядочить доступ к строкам.

Пояснение: используйте pg_blocking_pids() для диагностики. Промт автоматизирует анализ.

9. Оптимизация запроса с CTE и оконными функциями

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

Промт:

Перепиши этот запрос, заменив CTE на подзапросы или временные таблицы, если это ускорит выполнение. Проанализируй, не мешает ли MATERIALIZED CTE. Предложи альтернативы с оконными функциями.

Запрос:
<вставь SQL>
План:
<вставь EXPLAIN>

Пример: AI определит, что WITH RECURSIVE вызывает многократное сканирование, и предложит использовать LATERAL JOIN.

Пояснение: в PostgreSQL 12+ CTE могут инлайниться, но не всегда. Промт помогает выбрать стратегию.

10. Настройка autovacuum для горячих таблиц

Задача: уменьшить bloat и избежать замедления из-за мёртвых строк.

Промт:

Предложи параметры autovacuum для таблицы <имя> с учётом интенсивности записи. Объясни, как изменить autovacuum_vacuum_scale_factor и autovacuum_vacuum_threshold. Дай SQL для установки.

Статистика:
<вставь pg_stat_user_tables>

Пример: для таблицы с 10 млн строк и 100 тыс. обновлений в день AI предложит ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01);.

Пояснение: слишком частый autovacuum создаёт нагрузку, редкий — bloat. Промт помогает найти баланс.

11. Генерация миграции для Django/SQLAlchemy

Задача: автоматически создать файл миграции на основе изменений в моделях.

Промт:

Сгенерируй код миграции для Django (или Alembic) на основе следующих изменений: добавлена колонка <имя> в модель <имя>, удалён индекс <имя>. Учти, что миграция должна быть безопасной для продакшена.

Изменения:
<опиши>

Пример: AI напишет migrations.AddField с null=True и отдельную миграцию для AddIndex с CONCURRENTLY.

Пояснение: ORM не всегда генерирует оптимальный SQL. Промт добавляет ручные правки.

12. Анализ медленных запросов из pg_stat_statements

Задача: найти топ-10 запросов по total_time и предложить оптимизации.

Промт:

Проанализируй вывод pg_stat_statements. Отсортируй по total_time, найди запросы с высоким mean_time. Для каждого предложи индекс или переписывание. Учти количество вызовов.

Данные:
<вставь SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;>

Пример: AI выявит запрос с 1 млн вызовов и средним временем 50 мс — его оптимизация даст наибольший эффект.

Пояснение: расширение pg_stat_statements обязательно для мониторинга. Промт автоматизирует рутину.

13. Визуализация плана через PEV2

Задача: превратить текстовый EXPLAIN в интерактивную диаграмму для презентации команде.

Промт:

Сгенерируй JSON для PEV2 (Postgres Explain Visualizer) на основе этого плана. Убедись, что формат совместим с https://explain.dalibo.com/. Если нужно, добавь расчёт成本.

План:
<вставь EXPLAIN (FORMAT JSON)>

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

Пояснение: PEV2 — официальный инструмент Dalibo. Промт помогает подготовить данные.

Эти 13 промтов покрывают ключевые задачи от диагностики до безопасных изменений. Начните с EXPLAIN ANALYZE и pg_stat_statements — они дают наибольший эффект. Помните: AI — ассистент, а не замена DBA. Всегда проверяйте сгенерированный SQL на тестовой среде. Экспериментируйте, адаптируйте промты под свою схему — и ваши отчёты станут быстрее, а ночные дежурства спокойнее. Удачи в оптимизации!