Представьте: вы тратите полдня на отладку сложного JOIN, чтобы через месяц понять, что этот запрос можно было написать за 10 минут с помощью правильно сформулированного промта. Я сам прошел через это: работая с PostgreSQL в production, я часто ловил себя на мысли, что 80% времени уходит не на написание кода, а на поиск информации в документации и на Stack Overflow. Но за последний год я выработал систему промтов, которая сократила мое время на рутинные задачи с базами данных на 70%. Ниже — 15 проверенных шаблонов, разделенных по уровням сложности: от генерации базовых запросов до тонкой настройки PostgreSQL. Каждый промт сопровождается примером использования и реальным результатом, чтобы вы могли сразу применить его в своей работе.
Базовые промты: от идеи к SQL-запросу
1. Генерация сложных SQL-запросов на естественном языке
Задача: Быстро получить рабочий SQL-запрос, не вспоминая синтаксис.
Промт:
Ты — эксперт по SQL. Составь запрос для PostgreSQL, который выведет имена клиентов и сумму их заказов за последний месяц, отсортированных по убыванию суммы. Таблицы: customers (id, name, created_at), orders (id, customer_id, amount, created_at). Учти, что у клиента может не быть заказов — тогда сумма должна быть 0.
Пример результата:
SELECT c.name,
COALESCE(SUM(o.amount), 0) AS total_sum
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
OR o.created_at IS NULL
GROUP BY c.id, c.name
ORDER BY total_sum DESC;
Промт экономит время на синтаксисе, но всегда проверяйте логику: здесь я бы уточнил, нужно ли учитывать заказы за «последний месяц» или за календарный. AI хорошо справляется с рутиной, но бизнес-правила лучше задавать явно.
2. Написание запросов для MongoDB
Задача: Сгенерировать агрегацию для MongoDB, не открывая документацию.
Промт:
Создай агрегационный конвейер MongoDB для коллекции orders, чтобы получить общую выручку по каждому товару за последний год. Документ: {product_id, quantity, price, created_at}. Включи только товары с количеством > 1.
Пример результата:
db.orders.aggregate([
{ $match: { created_at: { $gte: new Date(new Date().setFullYear(new Date().getFullYear() - 1)) } } },
{ $match: { quantity: { $gt: 1 } } },
{ $group: { _id: "$product_id", totalRevenue: { $sum: { $multiply: ["$quantity", "$price"] } } } },
{ $sort: { totalRevenue: -1 } }
])
Промт полезен, когда нужно быстро получить структуру запроса, но всегда адаптируйте даты под свой формат хранения.
3. Проектирование схемы базы данных
Задача: Спроектировать нормализованную схему для нового проекта.
Промт:
Спроектируй схему PostgreSQL для интернет-магазина. Сущности: товары, категории, заказы, клиенты. Учти, что товар может быть в нескольких категориях, а заказ содержит несколько позиций. Укажи типы данных, первичные и внешние ключи, индексы.
Пример результата:
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
CREATE TABLE product_categories (
product_id INT REFERENCES products(id),
category_id INT REFERENCES categories(id),
PRIMARY KEY (product_id, category_id)
);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
name TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);
Этот промт помогает на старте проекта. Важно проговаривать все ограничения, которые нужны бизнесу: например, что email клиента уникален.
4. Объяснение сложного SQL-запроса
Задача: Понять, что делает чужой или давно написанный запрос.
Промт:
Объясни, что делает этот SQL-запрос в PostgreSQL, построчно, простыми словами:
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM events
)
SELECT * FROM ranked WHERE rn = 1;
Пример результата:
Этот запрос использует оконную функцию ROW_NUMBER() для нумерации строк в порядке убывания created_at в рамках каждого user_id. Внешний запрос выбирает строки с rn = 1, то есть самую последнюю запись для каждого пользователя. Это классический способ получить «последнее событие по каждому пользователю» без подзапросов и self-join.
Такие промты — отличный способ быстро разобраться в legacy-коде или подготовиться к собеседованию.
5. Оптимизация простых запросов для начинающих
Задача: Улучшить производительность простых запросов.
Промт:
Есть таблица users (id, email, name, created_at) с 1 млн записей. Запрос SELECT * FROM users WHERE email = 'test@example.com' выполняется 2 секунды. Что можно сделать?
Пример ответа:
Самое очевидное — создать индекс на поле email: CREATE INDEX idx_users_email ON users (email);. Но перед этим проверьте, есть ли уже индекс, с помощью \d users в psql. Если индекс есть, но запрос все равно медленный, возможно, PostgreSQL выбирает полное сканирование из-за устаревшей статистики. Запустите ANALYZE;. Также убедитесь, что в WHERE не используется функция над столбцом (например, LOWER(email)), так как это делает индекс бесполезным.
Этот промт учит системному подходу к диагностике, а не просто выполнению команд.
Продвинутые промты: диагностика и профилирование
6. Анализ плана выполнения запроса
Задача: Понять, почему запрос тормозит, с помощью EXPLAIN ANALYZE.
Промт:
Проанализируй план выполнения PostgreSQL для запроса:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
Таблица orders содержит 10 млн строк, индекс на customer_id уже есть. Что означают цифры в выводе?
Пример результата:
Вывод покажет фактические строки и время. Если вы видите Seq Scan на большой таблице, значит, индекс не используется. Причины: либо customer_id не подходит для индекса (например, поле имеет тип без сравнения), либо запрос выбирает слишком много строк. Обратите внимание на строку «Execution Time» — если она высокая, но планировщик выбрал Index Scan, то проблема может быть в самом индексе (фрагментация) или в том, что нужно увеличить work_mem для более эффективной сортировки.
Важно: используйте EXPLAIN (ANALYZE, BUFFERS) для более детальной информации о кэшировании.
7. Поиск и устранение медленных запросов в PostgreSQL
Задача: Найти запросы, которые выполняются дольше всего.
Промт:
Как найти в PostgreSQL 14 самые медленные запросы за последние 24 часа, используя pg_stat_statements? Напиши SQL-запрос, который покажет топ-10 по среднему времени выполнения.
Пример результата:
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.conf (shared_preload_libraries = 'pg_stat_statements'). Это стандартный инструмент, который собирает статистику по всем запросам. Выполнив запрос, вы получите список с количеством вызовов и временем. Среднее время выполнения — ключевой показатель для поиска узких мест.
8. Диагностика блокировок и взаимоблокировок
Задача: Определить, есть ли блокировки в базе.
Промт:
В PostgreSQL происходят взаимоблокировки. Как получить информацию о текущих блокировках из pg_locks? Напиши запрос, который покажет pid, тип блокировки и связанный запрос.
Пример результата:
SELECT l.pid, l.locktype, l.mode,
a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype = 'transactionid';
Этот запрос покажет все транзакции, ожидающие другие. Для поиска блокировок по конкретным таблицам добавьте условие l.relation = 'users'::regclass. Если вы видите, что один процесс держит блокировку слишком долго, можно его завершить командой SELECT pg_terminate_backend(pid);, но сначала разберитесь в причинах.
9. Настройка параметров PostgreSQL для конкретной нагрузки
Задача: Подобрать оптимизацию под высоконагруженную OLTP-систему.
Промт:
У нас PostgreSQL 15 на сервере с 16 ГБ ОЗУ и SSD. Какие значения параметров в postgresql.conf стоит изменить для OLTP-нагрузки (много коротких транзакций)? Объясни влияние каждого параметра.
Пример результата:
shared_buffers = 4GB # примерно 25% ОЗУ
work_mem = 64MB # для сортировок и хешей
maintenance_work_mem = 1GB # для VACUUM и CREATE INDEX
wal_buffers = 16MB
max_connections = 100 # лучше меньше, но с запасом по другим ресурсам
Эти значения — базовые рекомендации из официальной документации PostgreSQL. Однако не копируйте их слепо: параметры зависят от характера данных и нагрузки. Используйте pgtune (https://pgtune.leopard.in.ua/) для начальной оценки, но окончательные настройки делайте на основе мониторинга.
Помните: изменение shared_buffers требует перезагрузки сервера, а work_mem можно менять на лету через SET для конкретной сессии.
10. Оптимизация запросов с помощью подсказок (hints)
Задача: Заставить планировщик использовать конкретный план.
Промт:
Есть запрос, который выполняется 10 секунд, но если принудительно использовать индекс, то 1 секунду. Как в PostgreSQL заставить планировщик выбрать этот индекс? Используй расширение pg_hint_plan.
Пример результата:
/*+
IndexScan(orders idx_orders_customer_id)
*/
SELECT * FROM orders WHERE customer_id = 42;
Расширение pg_hint_plan позволяет управлять планировщиком. Установите его через CREATE EXTENSION pg_hint_plan; и добавьте в shared_preload_libraries. Но используйте осторожно: при изменении данных или статистики оптимальный план может измениться, и хинт станет вредным. Лучше понять, почему планировщик ошибается, и исправить статистику или индексы.
Это продвинутый инструмент, который применяют, когда нет времени на глубокий анализ.
Экспертные промты: тонкая настройка и нестандартные задачи
11. Автоматическое создание индексов по профилю нагрузки
Задача: Определить, какие индексы нужны на основе журнала запросов.
Промт:
У нас включен auto_explain в PostgreSQL. Проанализируй логи и предложи индексы для таблицы orders, если самые частые запросы фильтруют по customer_id и created_at, а также сортируют по created_at DESC. Напиши CREATE INDEX для каждой рекомендации.
Пример результата:
-- Составной индекс для фильтрации по клиенту и дате
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);
-- Индекс для сортировки по дате, если запросы выбирают все заказы за период
CREATE INDEX idx_orders_created ON orders (created_at DESC);
Индекс на (customer_id, created_at) покрывает оба условия: фильтрацию по клиенту и сортировку по дате. Обратите внимание на порядок колонок: сначала равенство, затем диапазон или сортировка.
Этот промт хорош, когда нужно быстро понять, какие индексы действительно нужны, на основе реальной нагрузки, а не предположений.
12. Генерация случайных данных для тестирования производительности
Задача: Наполнить таблицу тестовыми данными.
Промт:
Сгенерируй SQL-скрипт для PostgreSQL, который заполнит таблицу employees (id, name, salary, department_id) 10000 случайными записями. Используй generate_series и random().
Пример результата:
INSERT INTO employees (name, salary, department_id)
SELECT
'Employee_' || i,
round((random() * 100000 + 30000)::numeric, 2) AS salary,
floor(random() * 10 + 1)::int AS department_id
FROM generate_series(1, 10000) AS i;
Такой скрипт полезен для нагрузочного тестирования. Однако для более реалистичных данных используйте библиотеки типа faker или расширение pg_generate_series с заданными распределениями. Помните, что random() дает равномерное распределение, что редко встречается в реальности.
13. Использование JSONB в PostgreSQL для гибкой схемы
Задача: Спроектировать таблицу с JSONB-полем и оптимизировать запросы к нему.
Промт:
Как хранить данные с изменяемой структурой в PostgreSQL, используя JSONB? Приведи пример создания таблицы, вставки и индекса для поиска по полю внутри JSONB.
Пример результата:
CREATE TABLE events (
id SERIAL PRIMARY KEY,
payload JSONB NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- Вставка
INSERT INTO events (payload) VALUES ('{"user_id": 123, "action": "click", "page": "home"}');
-- GIN-индекс для @> оператора
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- Поиск
SELECT * FROM events WHERE payload @> '{"user_id": 123}';
JSONB позволяет быть гибким, но за это приходится платить производительностью и сложностью поддержки. Используйте его только там, где действительно нужна изменяемая структура. Для регулярных запросов лучше выделить ключевые поля в отдельные столбцы.
14. Миграция данных между MongoDB и PostgreSQL
Задача: Перенести данные из MongoDB в PostgreSQL с преобразованием структуры.
Промт:
У нас есть коллекция MongoDB с документами {_id: ObjectId, name: string, tags: [string], created_at: Date}. Нужно перенести в PostgreSQL таблицу items (id uuid, name text, tags text[], created_at timestamptz). Как это сделать?
Пример результата:
*Можно использовать утилиту mongoexport для экспорта в JSON, затем написать скрипт на Python или Go для трансформации и вставить через COPY. Пример на Python:
import psycopg2
from pymongo import MongoClient
client = MongoClient('mongodb://localhost:27017')
db = client['mydb']
conn = psycopg2.connect('dbname=mydb')
cur = conn.cursor()
for doc in db.items.find():
cur.execute(
'INSERT INTO items (id, name, tags, created_at) VALUES (%s, %s, %s, %s)',
(str(doc['_id']), doc['name'], doc['tags'], doc['created_at'])
)
conn.commit()
Этот подход работает для небольших объемов. Для больших данных используйте потоковую передачу и батчи. Убедитесь, что типы данных корректно преобразуются (ObjectId -> uuid).
15. Прогнозирование размера таблиц и индексов
Задача: Оценить, сколько места займет таблица с учетом индексов.
Промт:
Оцени размер таблицы и индексов в PostgreSQL для таблицы events (event_id bigint, user_id int, payload jsonb, created_at timestamptz) с 50 млн строк. Напиши SQL, который вычислит текущий размер, и объясни, как влияют типы данных.
Пример результата:
SELECT pg_size_pretty(pg_total_relation_size('events')) AS total_size;
Для оценки на будущее можно использовать pg_table_size и pg_indexes_size. JSONB добавляет накладные расходы на хранение ключей, поэтому для больших объемов рассмотрите вариант с обычными колонками. Точный прогноз сделать сложно, но можно посчитать средний размер строки, умножив на количество записей. Например, если средняя строка ~200 байт, то 50 млн строк займут около 10 ГБ данных плюс индексы (~10-30% от данных).
Заключение
Эти 15 промтов — не панацея, но они превращают AI в полезного ассистента, который экономит часы рутинной работы. Начните с базовых промтов для генерации запросов, постепенно переходя к диагностике и оптимизации. Помните: любой сгенерированный код нужно проверять и тестировать, а промты — адаптировать под свой контекст. Если вы хотите систематизировать эти навыки и научиться применять AI не только в базах данных, но и в других задачах разработки, обратите внимание на курсы Asibiont, где мы подробно разбираем подобные техники в рамках обучения работе с AI-агентами. А пока — экспериментируйте, и пусть ваши базы данных работают быстрее.
Комментарии