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 сталкивались вы? Делитесь опытом в комментариях!
Комментарии (35)
redballtoy
03.10.2025 15:34Пробовали в первую часть cte добавить еще фильтр по дате и статусу, тогда бы агрегация далее происходила бы по еще меньшему набору данных. Пробовали? Какой был бы результат. Хотелось бы подробнее про то как вы пришли именно к такому решению по оптимизации запроса которое вы здесь описали. С разбором планов запроса например.
yanchick
03.10.2025 15:34Увидел даты. Первый вопрос возник, а таблица партицирована? Даже если не на этапе дизайна, а в процессе эксплуатации.
Gromilo
03.10.2025 15:34CREATEINDEX idx_orders_coveringONorders(user_id, created_at, status)WHEREstatus = 'completed';Имеет ли смысл в индекс добавлять статус, если он всегда completed? Я бы сказал, что нет, но может какая хитрость есть.
polotenze
03.10.2025 15:34Почему нет? Индекс хранит ссылки на записи со статусом completed. Если в запросе есть where status = completed, то планировщик использует индекс. Если бы был какой нибудь where status = created, то индекс не использовался бы. Единственный момент - сколько в БД % записей с таким статусом. Если он довольно высок (заказ в конце как правило или completed, или какой нибудь другой конечный статус), то тогда индекс будет просто неэффективен.
Ну и частичный индекс занимает меньше места чем полный, и соответственно чуть быстрее ищет.
Gromilo
03.10.2025 15:34Не уверен, что понял ответ. Поэтому поясню вопрос :)
Я про то в составном индексе есть поле статус и в ограничении есть статус. Т.е. в поле индекса статус всегда будет один и тот же. Поэтом возник вопрос, а зачем?
Кажется такой индекс будет работать так же и меньше места занимать.CREATEINDEX idx_orders_coveringONorders(user_id, created_at)WHEREstatus = 'completed';
sledov
03.10.2025 15:34Может, эта мысль покажется крамольной, но если этот запрос так важен для вас, что "Сайт не грузится!" и "Отчёты зависли!", почему бы не сделать отдельную физическую таблицу для тоталзов, и обновлять её по мере добавления заказов, вместо того, чтобы постоянно агрегировать по 50 миллионам записей?
erogov
03.10.2025 15:34Всегда было интересно, почему на SQL так часто пишут
is_active = true, хотя логично же простоis_active, как в любом нормальном языке программирования.
Isiirk
03.10.2025 15:34Вам явно требуется архитектор баз данных... У вас прям все минусы, которые мог заложить программист в решение. И нет видимо вообще понимания как работают запросы
alan008
03.10.2025 15:34Вероятно потому, что в sql булева логика троична, в том смысле что в ней ещё замешаны значения NULL, а не только true и false. Например, обе проверки field=true и field=false будут ложны при значении поля равном NULL
erogov
03.10.2025 15:34Так ведь
fieldиfield = trueэквивалентны и в троичной логике.alan008
03.10.2025 15:34Тогда другое объяснение. В MS SQL, насколько знаю, нет типа boolean для самих полей , а есть тип bit, и там пишут where bit_field=1, подразумевая именно сравнение с числом 1, а не с boolean.
erogov
03.10.2025 15:34Вот кстати да, возможно это привычка с других систем. В Оракле boolean тоже никогда не было в базе, его добавили совсем недавно.
silentz
03.10.2025 15:34Отдельная CTE для WITH potential_users мне кажется не обязателен - постгря бы сама исходя из фильтра подставила бы данные из нужного индекса
Pusk1
03.10.2025 15:34Попадал на такие истории не раз. Вы на первом шагу. Дальше разбиениеина на партиции по датам транзакций, затем таблица с уже расчитанными агрегатами, потом перенос таких операций в аналитическую базу данных. Где-то в середине можно добавить материализированные представления.
Ещё может помочь обрезание транщакционных данных. Например, не работали с клиентом 3 года - выбрасывает все транзакции по нему. Но это не всегда применимо.
Kilor
03.10.2025 15:3410M - это еще маловато для секционирования, пожалуй. Да и агрегаты тут явно избыточны, если итоговый запрос пока уложился в 120ms.
Groh
03.10.2025 15:34Когда есть партиции по дате, просто в полночь удаляете все партии старше определённого возраста
Kilor
03.10.2025 15:34Тут, конечно, без плана исходного запроса добрая половина оптимизаций выглядит сделанной непонятно зачем, но попробуем зайти на задачу с точки зрения алгоритмов и прикладной логики.
Сформулируем условие: "Вывести TOP-50 активных (
is_active = true) пользователей, у кого наберется хотя бы по 10K оборота, лидирующих по сумме (amount) отгрузок (status = 'completed') заказов, созданных (created_at) с 01.01.2023."Из базовой прикладной логики можно предположить, что большинство пользователей, отгружавших на диапазоне последних 3 лет, будут активными и по сей день (и чем этот интервал меньше, тем больше вероятность, что все отгружавшие - активны).
По этой причине эффективнее сразу "схлопнуть" все заказы интервала по пользователю.
Потом подтягиваем активных пользователей к полученной выборке и не забываем посортировать снова после JOIN.
Как-то примерно так:
SELECT u.id , u.name , o.order_count , o.total_amount FROM ( SELECT user_id , count(id) order_count , sum(amount) total_amount FROM orders WHERE status = 'completed' AND created_at >= '2023-01-01' GROUP BY user_id HAVING total_amount > 10000 ORDER BY total_amount DESC LIMIT 50 ) o JOIN users u ON (u.id, u.is_active) = (o.user_id, TRUE) ORDER BY total_amount DESC;И только после этого имеет смысл прикидывать, какие из индексов нам реально пригодятся тут:
CREATE INDEX ON orders(created_at) WHERE status = 'completed'; CREATE INDEX ON users(id) WHERE is_active = true;Понятно, что индекс
orders(user_id, created_at)тоже может быть полезен, но или не для этой задачи, или если разных пользователей у нас всего десяток-полтора.
Akina
03.10.2025 15:34На практике это точка, где:
Индексы перестают помещаться в оперативную память
Планировщик запросов начинает выбирать неоптимальные пути
Не могли бы вы подтвердить фактическими данными, что именно на 10кк записей произошли указанные "неприятности". Особенно в части плана выполнения запроса при 9.9кк и 10.1кк записей.
И да, при количестве записей в таблицах в 10кк - вот не верю я, что какие-то два вшивых индекса по одному полю каждый (по user_id и created_at) зажрали 4 с хвостом гига на диске. НЕ ВЕРЮ!!!
Заодно поясните - что, все 127 раз в день запрос выполнялся с одними и теми же значениями всех параметров-литералов? Но если так - а нафига вы вообще его выполняете, если все значения параметров известны и фиксированы? Тем более что запрос на чистые TOP50, на пагинацию даже не намекает, а в таком случае 99% за то, что даже ошибочное включение в этот список 51-го и пропуск 50-го ну вообще ни на что не влияет - то есть достаточно выполнить однократный предрасчёт, а всем 127 запросившим предоставлять готовые результаты, пусть и на полчаса устаревшие, но зато менее чем за миллисекунду.
PS. Я вообще вот не верю подобным стопервым рассказкам. Почти убеждён, что инцидент придуман, просто высосан из пальца для написания статьи, и не имел места на практике. Уж больно он невероятный.
Anarchist
03.10.2025 15:34Сделать поле orders_sum у users и триггер на изменения в orders, обновляющие orders_sum?
tbl
03.10.2025 15:34Удивительно, olap-запрос после какого-то порога начинает убивать oltp-базу. Никогда такого не было, и вот опять...
Эти местные прикладывания подорожника через какое-то время снова перестанут работать.
И да, 256 MB на сессию для бд, которая обслуживает внешний сайт - ну такое
gsl23
03.10.2025 15:34Статья про оптимизацию плана без единого плана)
Улыбнуло :-- Включаем детальный мониторингALTERSYSTEMSETshared_preload_libraries = 'pg_stat_statements';SELECTpg_reload_conf();AI и/или лень хоть как то проверить было ?
alan008
Исходный запрос простейший и если его PG выполняет 28 секунд при наличии двух исходных индексов, это как минимум странно.
Хотелось бы посмотреть план выполнения исходного запроса и точное кол-во записей в таблицах Users и Orders.
Немного смутил GROUP BY по u.name, это некрасиво, надеюсь PG такое оптимизирует на автомате, понимая что группировки по u.id достаточно. Можно было не тащить u.name в запрос, а потом уже подставить по справочнику (внешней логикой).
То, что status хранится строкой а не ID на справочник этих статусов - тоже попахивает )
Gromilo
Должен оптимизировать, т.к. u.name можно даже не писать в GROUP BY , ид достаточно
alan008
>>т.к. u.name можно даже не писать в GROUP BY , ид достаточно
MS SQL Server требует писать в group by всё что есть в select'e без агрегатных функций (даже если это 100500 полей одной таблицы). Если postgres позволяет не писать, это удобно конечно
Blumfontein
Постгрес тоже требует
aleksandy
Вообще-то этого требует SQL-стандарт. Остальное - лишь следствие следования этому стандарту различными СУБД.
Gromilo
А у меня работает такой запрос:
Kilor
https://www.postgresql.org/docs/release/9.1.0/
Еще с 9.1 требует не всегда. В какой-то из более поздних версий определение доступных к возврату столбцов было заменено на что-то типа "однозначно функционально вычислимые".