Промты для PostgreSQL: 12 сценариев от оптимизации медленных запросов до аналитики на миллиардах строк
PostgreSQL — одна из самых мощных реляционных СУБД, но её производительность напрямую зависит от качества запросов и схемы. По данным Stack Overflow Developer Survey 2025, PostgreSQL остаётся самой популярной базой данных среди разработчиков. При этом многие сталкиваются с медленными запросами, неоптимальными индексами и сложными миграциями. AI-ассистенты (ChatGPT, Claude, Gemini) могут стать незаменимым инструментом, если правильно формулировать промты. В этой подборке — 12 проверенных сценариев с примерами и пояснениями.
1. Разбор EXPLAIN ANALYZE: находим узкие места
Задача: Понять, почему запрос выполняется 2 секунды вместо 20 мс.
Промт:
Ты — эксперт по PostgreSQL. Проанализируй вывод EXPLAIN (ANALYZE, BUFFERS) для запроса:
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01'
GROUP BY u.name;
Вот план выполнения:
[вставьте вывод EXPLAIN]
Объясни, какие узлы самые дорогие, почему выбран именно этот план, и предложи 3 конкретных шага оптимизации (индексы, переписывание, настройки).
Пример результата: AI укажет на Seq Scan по orders, предложит индекс на orders(created_at, user_id) и покажет, как изменится план.
2. Переписывание медленного JOIN
Задача: Ускорить запрос с множественными JOIN без изменения логики.
Промт:
Перепиши запрос так, чтобы уменьшить количество сканирований:
SELECT c.name, p.title, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE c.region = 'EU' AND o.status = 'shipped';
Предложи вариант с CTE или LATERAL JOIN, объясни разницу в производительности.
Пример: AI предложит заменить JOIN на EXISTS или использовать материализованное CTE, если это уместно.
3. Проектирование индексов под конкретный запрос
Задача: Создать оптимальный индекс для часто выполняемого запроса.
Промт:
У меня есть таблица events (id, user_id, event_type, created_at, payload JSONB).
Запрос: SELECT * FROM events WHERE user_id = 123 AND event_type = 'click' ORDER BY created_at DESC LIMIT 10;
Какой индекс создать? Рассмотри варианты: B-tree, составной, частичный. Дай DDL и объясни выбор.
Пример: AI порекомендует CREATE INDEX idx_events_user_type_created ON events (user_id, event_type, created_at DESC); и пояснит, что он покрывает фильтрацию и сортировку.
4. Безопасные миграции без простоя
Задача: Добавить колонку или изменить тип в большой таблице без блокировки.
Промт:
Как безопасно добавить NOT NULL колонку в таблицу users (10 млн строк) в PostgreSQL 16? Напиши пошаговый план с использованием ALTER TABLE ... ADD COLUMN ... DEFAULT, затем UPDATE батчами и созданием констрейнта через NOT VALID.
Пример: AI напомнит, что в PostgreSQL 11+ добавление колонки с DEFAULT не переписывает таблицу, и предложит использовать ALTER TABLE ... ADD CONSTRAINT ... NOT VALID для минимизации блокировок.
5. Оконные функции для аналитики
Задача: Рассчитать накопительный итог и ранжирование без подзапросов.
Промт:
Напиши SQL-запрос с оконными функциями: для каждого пользователя выведи сумму покупок за всё время и ранг по сумме внутри региона. Таблица sales (user_id, region, amount, sale_date).
Пример:
SELECT user_id, region, SUM(amount) OVER (PARTITION BY user_id) AS total_user,
RANK() OVER (PARTITION BY region ORDER BY SUM(amount) OVER (PARTITION BY user_id) DESC) AS rnk
FROM sales;
6. JSONB-запросы: извлекаем данные эффективно
Задача: Быстро фильтровать по вложенным полям JSONB.
Промт:
Есть таблица products (id, data JSONB). Поле data содержит {"category": "electronics", "specs": {"ram": 16}}.
Напиши запрос для поиска всех товаров с ram >= 8. Какой индекс нужен? Используй операторы @>, ->, ->>.
Пример: AI предложит CREATE INDEX idx_products_specs_ram ON products ((data->'specs'->>'ram')) WHERE data->'specs'->>'ram' IS NOT NULL; и запрос с data @> '{"specs": {"ram": 16}}'.
7. Партиционирование больших таблиц
Задача: Разбить таблицу на партиции по диапазону дат.
Промт:
Как партиционировать таблицу logs (id, created_at, message) по месяцам в PostgreSQL 16? Напиши DDL для партиционированной таблицы и создай партиции на 2026 год. Объясни, как работает автоматическое создание партиций через pg_partman.
Пример: AI покажет CREATE TABLE logs (...) PARTITION BY RANGE (created_at); и создание партиций logs_2026_01 и т.д.
8. Генерация тестовых данных
Задача: Заполнить таблицу миллионом строк для нагрузочного тестирования.
Промт:
Сгенерируй 1 млн строк в таблицу users (id serial, name text, email text, created_at timestamp) с случайными данными. Используй generate_series и random(). Напиши один INSERT.
Пример:
INSERT INTO users (name, email, created_at)
SELECT 'User'
|| i, 'user' || i || '@example.com', NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 1000000) AS i;
9. Оптимизация запросов с LIKE и полнотекстовым поиском
Задача: Ускорить поиск по подстроке.
Промт:
Запрос: SELECT * FROM articles WHERE title LIKE '%postgres%';
Как ускорить? Рассмотри pg_trgm, GIN-индекс и полнотекстовый поиск. Дай примеры.
Пример: AI порекомендует CREATE EXTENSION pg_trgm; CREATE INDEX idx_title_trgm ON articles USING GIN (title gin_trgm_ops);.
10. Анализ блокировок и deadlocks
Задача: Найти причину взаимной блокировки.
Промт:
В логах PostgreSQL появились deadlock detected. Как найти запросы, которые блокируют друг друга? Напиши запрос к pg_locks и pg_stat_activity. Предложи стратегию предотвращения.
Пример: AI даст запрос, соединяющий pg_locks с pg_stat_activity, и совет использовать SELECT ... FOR UPDATE SKIP LOCKED.
11. Настройка autovacuum для высоконагруженных таблиц
Задача: Уменьшить bloat и избежать замедления.
Промт:
Таблица orders часто обновляется. Autovacuum не успевает. Как настроить параметры autovacuum для этой таблицы? Напиши ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000). Объясни влияние.
Пример: AI пояснит, что уменьшение scale_factor заставляет autovacuum запускаться чаще.
12. Мониторинг производительности через pg_stat_statements
Задача: Найти топ-10 самых ресурсоёмких запросов.
Промт:
Как включить pg_stat_statements и найти запросы с наибольшим total_exec_time? Напиши запрос для выборки топ-10 и объясни, как сбросить статистику.
Пример:
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Как адаптировать промты под свою схему
Всегда указывайте версию PostgreSQL (например, 16), размер таблиц, существующие индексы и ожидаемое время выполнения. Чем больше контекста, тем точнее ответ. Используйте EXPLAIN (ANALYZE, BUFFERS) для диагностики. Помните: AI — это помощник, а не замена DBA. Проверяйте рекомендации на тестовой среде.
Эти 12 сценариев покрывают большинство ежедневных задач разработчика и аналитика. Начните с EXPLAIN ANALYZE и индексов — это даёт наибольший эффект. Экспериментируйте, комбинируйте промты и делитесь своими находками.
Полезные ссылки:
- Официальная документация PostgreSQL: https://www.postgresql.org/docs/current/
- Using EXPLAIN: https://www.postgresql.org/docs/current/using-explain.html
- pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html
- Stack Overflow Developer Survey 2025: https://survey.stackoverflow.co/2025/
Комментарии