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

Вступление

Каждый день я вижу, как разработчики тратят часы на переписывание SQL-запросов, разбор EXPLAIN ANALYZE и борьбу с медленными индексами. Сам PostgreSQL — мощная штука, но без правильного подхода даже простой JOIN может превратиться в кошмар. В этой подборке — 11 промтов, которые я лично применяю в работе с PostgreSQL. Они не просто генерят код, а помогают думать, как оптимизатор, и избегать классических граблей.

Все промты проверены на реальных проектах: от аналитических отчётов до высоконагруженных OLTP-систем. Примеры кода рабочие, синтаксис соответствует PostgreSQL 16. Если вы аналитик, бэкендер или DBA — здесь найдёте что-то полезное.

1. Генерация сложного запроса с CTE и агрегациями

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

Промт:

Ты — эксперт по PostgreSQL. Напиши запрос, который вернёт за каждый месяц 2026 года: категорию, общую сумму продаж, сумму возвратов и чистую выручку. Используй CTE для агрегации продаж и возвратов, затем JOIN. Избегай коррелированных подзапросов. Таблицы: sales (id, category_id, amount, created_at), returns (id, sale_id, amount, created_at), categories (id, name).

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

WITH monthly_sales AS (
  SELECT date_trunc('month', created_at) AS month,
         category_id,
         SUM(amount) AS total_sales
  FROM sales
  WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
  GROUP BY 1, 2
),
monthly_returns AS (
  SELECT date_trunc('month', r.created_at) AS month,
         s.category_id,
         SUM(r.amount) AS total_returns
  FROM returns r
  JOIN sales s ON s.id = r.sale_id
  WHERE r.created_at >= '2026-01-01' AND r.created_at < '2027-01-01'
  GROUP BY 1, 2
)
SELECT c.name AS category,
       ms.month,
       ms.total_sales,
       COALESCE(mr.total_returns, 0) AS total_returns,
       ms.total_sales - COALESCE(mr.total_returns, 0) AS net_revenue
FROM monthly_sales ms
JOIN categories c ON c.id = ms.category_id
LEFT JOIN monthly_returns mr ON mr.month = ms.month AND mr.category_id = ms.category_id
ORDER BY ms.month, c.name;

Пояснение: CTE делают запрос читаемым и позволяют планировщику оптимизировать каждую часть отдельно. LEFT JOIN гарантирует, что категории без возвратов не потеряются.

2. Чтение EXPLAIN ANALYZE: находим узкие места

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

Промт:

Вот вывод EXPLAIN (ANALYZE, BUFFERS) для запроса. Объясни, какие операции самые дорогие, есть ли seq scan на больших таблицах, и что можно улучшить. Предложи конкретные шаги: индексы, переписывание, настройки.

Пример вывода:

Hash Join  (cost=1000.00..5000.00 rows=10000 width=50) (actual time=500.123..1500.456 rows=50000 loops=1)
  Buffers: shared hit=10000 read=5000
  ->  Seq Scan on orders  (cost=0.00..2000.00 rows=100000 width=20) (actual time=0.010..200.000 rows=100000 loops=1)
        Filter: (status = 'completed'::text)
        Rows Removed by Filter: 50000
  ->  Hash  (cost=500.00..500.00 rows=10000 width=30) (actual time=100.000..100.000 rows=10000 loops=1)
        ->  Index Scan using idx_customers_id on customers  (cost=0.00..500.00 rows=10000 width=30) (actual time=0.020..50.000 rows=10000 loops=1)

Анализ: Seq Scan на orders удаляет 50% строк — нужен индекс на status. Hash Join занимает много времени из-за большого объёма. Рекомендация: CREATE INDEX idx_orders_status ON orders(status); и проверить, не лучше ли JOIN с фильтром по индексированному полю.

3. Поиск отсутствующих индексов

Задача: база тормозит, но непонятно, каких индексов не хватает.

Промт:

Проанализируй статистику pg_stat_user_tables и pg_stat_user_indexes. Найди таблицы с большим количеством seq_scan и высоким avg_time, но без подходящих индексов. Предложи индексы для самых частых запросов. Учти, что индексы замедляют запись.

Пример запроса для сбора статистики:

SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

Результат: таблица orders с seq_scan=5000 и n_live_tup=1M — кандидат на индекс по полю created_at, если часто фильтруете по дате.

4. Безопасные миграции без блокировок

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

Промт:

Напиши миграцию для PostgreSQL, которая добавляет NOT NULL колонку с дефолтом в таблицу на 100 млн строк. Используй подход с отдельными шагами: добавление колонки без NOT NULL, заполнение батчами, затем установка ограничения. Учти блокировки.

Пример:

-- Шаг 1: добавить колонку без NOT NULL
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

-- Шаг 2: заполнить батчами (в цикле)
UPDATE users SET last_login = now() WHERE last_login IS NULL AND id BETWEEN 1 AND 10000;
-- повторять для следующих диапазонов

-- Шаг 3: установить NOT NULL
ALTER TABLE users ALTER COLUMN last_login SET NOT NULL;

Пояснение: такой подход минимизирует время блокировки. Для очень больших таблиц используйте pg_repack или CREATE INDEX CONCURRENTLY.

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

Задача: посчитать running total и rank по продажам.

Промт:

Напиши запрос с оконными функциями: для каждого менеджера выведи сумму продаж за месяц, накопительный итог с начала года и место в рейтинге по месяцу. Используй OVER с PARTITION BY и ORDER BY.

Пример:

SELECT manager_id,
       date_trunc('month', sale_date) AS month,
       SUM(amount) AS monthly_sales,
       SUM(SUM(amount)) OVER (PARTITION BY manager_id ORDER BY date_trunc('month', sale_date)) AS running_total,
       RANK() OVER (PARTITION BY date_trunc('month', sale_date) ORDER BY SUM(amount) DESC) AS rank_in_month
FROM sales
GROUP BY manager_id, date_trunc('month', sale_date)
ORDER BY manager_id, month;

Пояснение: оконные функции позволяют избежать self-join и подзапросов, часто работают быстрее.

6. Борьба с N+1 в ORM

Задача: в Django/SQLAlchemy каждый объект тянет связанные данные отдельным запросом.

Промт:

У меня есть ORM-запрос, который вызывает N+1. Предложи, как переписать его на чистом SQL с JOIN или использовать select_related/prefetch_related. Покажи итоговый код и объясни, почему это быстрее.

Пример: вместо for user in users: print(user.profile.bio) используйте:

SELECT u.id, u.name, p.bio
FROM users u
JOIN profiles p ON p.user_id = u.id;

Пояснение: один запрос вместо N+1 снижает нагрузку на сеть и БД в разы.

7. Аудит схемы и нормализация

Задача: в таблице дублируются данные, нужно привести к 3NF.

Промт:

Проанализируй схему таблицы orders (id, customer_name, customer_email, product_name, product_price). Предложи нормализацию до 3NF: какие таблицы создать, какие связи, как мигрировать данные без потерь. Напиши SQL для создания новых таблиц и переноса.

Пример:

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT, email TEXT UNIQUE);
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), product_id INT REFERENCES products(id));

INSERT INTO customers (name, email) SELECT DISTINCT customer_name, customer_email FROM old_orders;
INSERT INTO products (name, price) SELECT DISTINCT product_name, product_price FROM old_orders;
INSERT INTO orders (customer_id, product_id) SELECT c.id, p.id FROM old_orders o JOIN customers c ON c.email = o.customer_email JOIN products p ON p.name = o.product_name;

Пояснение: нормализация убирает аномалии вставки/обновления и экономит место.

8. Оптимизация запросов с помощью индексов на выражения

Задача: часто ищем по lower(email), но индекс не используется.

Промт:

Создай индекс, который ускорит запрос SELECT * FROM users WHERE lower(email) = 'test@example.com';. Объясни, почему обычный индекс не работает.

Пример:

CREATE INDEX idx_users_lower_email ON users (lower(email));

Пояснение: индекс на выражении позволяет планировщику использовать его для запросов с функцией.

9. Партиционирование больших таблиц

Задача: таблица логов растёт на 10 млн строк в месяц.

Промт:

Напиши DDL для партиционирования таблицы logs по диапазону дат (месяц). Учти, что PostgreSQL 16 поддерживает декларативное партиционирование. Покажи, как создать секции на 2026 год.

Пример:

CREATE TABLE logs (
  id BIGSERIAL,
  message TEXT,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2026_01 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- и так далее

Пояснение: партиционирование ускоряет запросы по дате и упрощает удаление старых данных.

10. Мониторинг и поиск долгих запросов

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

Промт:

Настрой pg_stat_statements и напиши запрос, который покажет топ-10 запросов по среднему времени выполнения. Учти, что расширение нужно установить.

Пример:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Пояснение: pg_stat_statements — официальное расширение PostgreSQL для трекинга запросов.

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

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

Промт:

Напиши SQL-запрос, который сгенерирует 1 млн строк в таблицу users с случайными именами и email. Используй generate_series и random().

Пример:

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

|| i || '@example.com'
FROM generate_series(1, 1000000) AS i;

Пояснение: generate_series — быстрый способ создать много строк без внешних скриптов.

Заключение

Эти 11 промтов покрывают основные боли при работе с PostgreSQL: от написания сложных запросов до безопасных миграций. Используйте их как шаблоны, адаптируя под свою схему. Главное — всегда проверяйте план выполнения и тестируйте на копии продакшна. Если хотите углубиться, изучите официальную документацию PostgreSQL по EXPLAIN, индексам и партиционированию. А если у вас есть свои любимые промты — делитесь в комментариях.

Помните: AI — это помощник, а не замена пониманию того, как работает база. Чем лучше вы знаете PostgreSQL, тем точнее будут ваши промты и тем быстрее запросы.