PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект / Хабр

PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект

Пролог: Когда база данных говорит «нет»

Это был обычный понедельник. Я пил кофе, проверял почту, и вдруг — волна уведомлений в Slack. «Сайт не грузится!», «Отчеты зависли!», «Что происходит?».

Наш проект, который успешно работал с несколькими сотнями тысяч записей, перешагнул психологически важный рубеж — 10 миллионов строк в таблице заказов. И PostgreSQL, который раньше летал, внезапно начал ползти как улитка.

Диагноз: почему 10 миллионов — это магия?

Многие думают: «10 миллионов записей — это же немного!». На практике это точка, где:

Индексы перестают помещаться в оперативную память
Планировщик запросов начинает выбирать неоптимальные пути
Простые JOIN'ы превращаются в многоминутные операции

Наш главный проблемный запрос выглядел так:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2023-01-01'
    AND o.status = 'completed'
    AND u.is_active = true
GROUP BY u.id, u.name
HAVING SUM(o.amount) > 10000
ORDER BY total_amount DESC
LIMIT 50;

Время выполнения: 28 секунд. Для веб-интерфейса — смерть.

Шаг 1: Анализ — снимаем показания с пациента

Первое правило оптимизации: измеряй всё!

-- Включаем детальный мониторинг
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SELECT pg_reload_conf();

-- Смотрим самые тяжелые запросы
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Результаты показали, что наш запрос:

Выполнялся 127 раз в день
Суммарно тратил 56 минут процессорного времени
Читал 15 миллионов строк для возврата 50 результатов

Шаг 2: Индексы — правильная расстановка приоритетов

Оказалось, наши индексы были созданы без понимания того, как их будет использовать планировщик.

Было:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Стало:

-- Составной индекс, покрывающий фильтрацию и джойны
CREATE INDEX idx_orders_covering ON orders(user_id, created_at, status)
WHERE status = 'completed';

-- Частичный индекс для активных пользователей
CREATE INDEX idx_users_active ON users(id, name) 
WHERE is_active = true;

Магия частичных индексов: они в 3 раза меньше и в 5 раз быстрее.

Шаг 3: Переписывание запроса — искусство компромиссов

Иногда нужно не добавлять индексы, а пересмотреть саму логику:

WITH potential_users AS (
    SELECT u.id, u.name
    FROM users u
    WHERE u.is_active = true
),
user_totals AS (
    SELECT 
        pu.id,
        pu.name,
        COUNT(o.id) as order_count,
        SUM(o.amount) as total_amount
    FROM potential_users pu
    JOIN orders o ON pu.id = o.user_id
    WHERE o.created_at >= '2023-01-01'
        AND o.status = 'completed'
    GROUP BY pu.id, pu.name
    HAVING SUM(o.amount) > 10000
)
SELECT *
FROM user_totals
ORDER BY total_amount DESC
LIMIT 50;

Разбивка на CTE (Common Table Expressions) помогла планировщику лучше оптимизировать выполнение.

Шаг 4: Расширенные техники — когда стандартных методов недостаточно

Анализ статистики

-- Обновляем статистику для планировщика
ANALYZE orders;
ANALYZE users;

-- Проверяем селективность индексов
SELECT schemaname, tablename, attname, n_distinct
FROM pg_stats
WHERE tablename IN ('orders', 'users')
ORDER BY tablename, attname;

Настройка параметров БД

-- Увеличиваем память для работы с большими данными
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET shared_buffers = '4GB';
SELECT pg_reload_conf();

Шаг 5: Результаты — магия в цифрах

После всех оптимизаций:

Метрика	До оптимизации	После оптимизации
Время выполнения	28 секунд	120 миллисекунд
Чтение строк	15 миллионов	8 тысяч
Размер индексов	4.2 ГБ	1.1 ГБ
Нагрузка на CPU	95%	12%

Ускорение в 233 раза — и это без апгрейда железа!

Выводы: уроки, которые мы усвоили

Индексы ≠ волшебная палочка. Нужно понимать, как их будет использовать планировщик
10 миллионов — это психологический барьер. После него меняется поведение СУБД
Анализируй до оптимизации. Без EXPLAIN ANALYZE и pg_stat_statements ты работаешь вслепую
Иногда проще переписать запрос, чем добавить еще один индекс
Статистика — твой друг. Регулярный ANALYZE помогает планировщику принимать правильные решения

Эпилог: Жизнь после оптимизации

Сейчас наша база перевалила за 50 миллионов записей, и те запросы, которые мы оптимизировали, до сих пор работают быстро. Мы внедрили регулярный мониторинг медленных запросов и ревизию индексов.

Самое главное — мы перестали бояться больших объемов данных. PostgreSQL справляется с ними великолепно, если знать, как с ним работать.

Совет напоследок: не ждите, пока проблемы с производительностью ударят по пользователям. Регулярно проверяйте pg_stat_statements и находите медленные запросы до того, как они найдут вас.

А с какими проблемами производительности PostgreSQL сталкивались вы? Делитесь опытом в комментариях!

PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект