Один запрос без LIMIT положил реплику, а вместе с ней все отчёты

MAATRIX

В пятницу вечером отвалились все отчёты разом: дашборд для финансов, ежедневная выгрузка для маркетинга, внутренняя админка с метриками по заказам. Никто ничего не деплоил, конфиги не трогали — просто в какой-то момент реплика PostgreSQL, с которой всё это читало, перестала отвечать. История о том, как один аналитический запрос без LIMIT уронил читающую реплику и почему для этого не потребовалось ни сбоя железа, ни ошибки в коде.

Что сломалось

Первый сигнал пришёл не от базы, а от пользователей: отчёты в BI-панели зависли на этапе загрузки, а через несколько минут стали падать по таймауту. Почти сразу за этим сработал алерт мониторинга — реплика, с которой качало отчёты и аналитика, ушла в высокую нагрузку на диск.

Схема была стандартная: один основной сервер PostgreSQL принимает запись, вторая нода — потоковая реплика (streaming replication), которая обслуживает только чтение. На неё специально вынесли всю аналитику и отчётность, чтобы не грузить продакшн-базу тяжёлыми выборками. Это правильный паттерн, о нём подробно писали в материале про то, как работает репликация и почему реплика отстаёт — но у него есть обратная сторона: реплика становится единой точкой отказа для всей отчётности, если её никак не защитить от тяжёлых запросов.

Через семь-восемь минут после первых жалоб реплика перестала принимать новые подключения вообще. В логах пошли ошибки could not extend file... No space left on device. Диск оказался забит под ноль — не данными, а временными файлами PostgreSQL.

Что видели в логах и метриках

Первым делом посмотрели на активные запросы через pg_stat_activity:

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 20;

Один запрос выделялся сразу — он выполнялся уже почти двадцать минут в состоянии active, и его текст был обрезан, но начинался с чего-то вроде:

SELECT o.*, u.email, u.name, p.title
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.created_at > '2024-01-01'
ORDER BY o.created_at DESC;

Никакого LIMIT. Таблица orders на тот момент — за несколько лет работы сервиса — разрослась до многих десятков миллионов строк, и фильтр created_at > '2024-01-01' почти ничего не отсекал: под него подпадала львиная доля таблицы.

Дальше посмотрели на диск:

df -h /var/lib/postgresql
du -sh /var/lib/postgresql/15/main/base/pgsql_tmp/*

Каталог pgsql_tmp — это как раз то место, куда PostgreSQL сбрасывает промежуточные данные, когда операции сортировки или хеширования не помещаются в work_mem. И там лежали файлы суммарным весом в десятки гигабайт — это были остатки внешней сортировки того самого запроса без LIMIT вместе с ORDER BY по неиндексированной по сути выборке.

Параллельно проверили лаг репликации:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Лаг рос на глазах — с секунд до минут. Реплика физически не успевала применять WAL, потому что диск был занят записью временных файлов сортировки, а не воспроизведением потока изменений с мастера.

Какие гипотезы отбросили

Первые двадцать минут разбора ушли не в ту степь, и это нормально для инцидента, где симптом (реплика недоступна) сильно отличается по виду от причины (один SQL-запрос). Проверили и отбросили:

• Проблему сети между мастером и репликой. ping и трассировка показывали чистый канал, pg_stat_replication на мастере не жаловался на разрыв соединения — просто реплика применяла WAL медленнее, чем он приходил.
• Деградацию диска. SMART-статус диска был в порядке, до инцидента iostat не показывал аномалий. Это исключало версию с физическим износом накопителя — тема разобрана отдельно в материале про то, как SSD умирает медленно из-за wear leveling, но здесь диск был жив, просто забит под завязку.
• Автовакуум, который якобы "съел" ресурсы. pg_stat_progress_vacuum показывал штатную фоновую работу, не связанную по времени со всплеском.
• Утечку соединений из приложения. Число активных бэкендов было в пределах нормы, кроме одного зависшего запроса — не десятки клиентов, ломящихся одновременно, а один.
• Обновление ПО или деплой. Проверили журнал деплоев — в этот день никто ничего не выкатывал ни на бэкенд, ни на инфраструктуру.

Отбросив всё лишнее, оставалась одна причина, которая идеально стыковалась с временной шкалой: запуск отчёта в BI-инструменте пятнадцатью минутами ранее.

В чём была реальная причина

Аналитик обновил дашборд с фильтром "последние заказы", но при редактировании отчёта случайно убрал ограничение "показывать последние 500 записей" — в конструкторе отчёта это визуально было галочкой, которая слетела при копировании виджета. BI-система сформировала запрос буквально "выгрузить все заказы с начала прошлого года со связанными пользователями и товарами", без LIMIT и без пагинации.

Дальше сработала цепочка вполне логичных, но разрушительных последствий:

1. Планировщик PostgreSQL честно оценил объём выборки — десятки миллионов строк — и выбрал план с сортировкой по created_at DESC для ORDER BY.
2. Сортировка такого объёма не помещалась в work_mem (он был настроен на разумные для обычных запросов значения), поэтому PostgreSQL перешёл на внешнюю сортировку с использованием временных файлов на диске.
3. Реплика в этот момент обслуживала ещё несколько параллельных отчётов поменьше — каждый из них тоже писал во временные файлы, суммарно нагрузка на диск подскочила резко.
4. Диск реплики заполнился временными файлами до отказа. Это не такая уж редкая ошибка конфигурации — подробнее разбирали в статье о том, как работает индекс и когда он ускоряет, а когда замедляет запрос: в этом случае индекс по created_at формально существовал, но диапазонный фильтр > '2024-01-01' отсекал слишком мало строк, чтобы планировщик выбрал Index Scan вместо полного скана с сортировкой.
5. Когда места не осталось, процесс применения WAL на реплике тоже начал получать ошибки записи — реплика не могла ни выполнять запросы, ни продолжать репликацию.

Ключевая деталь, которая всё объясняет: реплика физически одна, и любой процесс на ней — хоть аналитический запрос, хоть применение WAL — конкурирует за один и тот же диск. Изоляции между "тяжёлым отчётом" и "критичной инфраструктурной задачей" не было никакой.

Как остановили и восстановили работу

Порядок действий был примерно такой:

-- нашли и остановили зависший запрос
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE pid = <pid_проблемного_запроса>;

-- если cancel не сработал за разумное время — принудительно
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid = <pid_проблемного_запроса>;

pg_cancel_backend останавливает запрос мягко, pg_terminate_backend — обрывает соединение целиком, если процесс завис настолько, что не реагирует на отмену. После остановки запроса временные файлы этого бэкенда PostgreSQL удаляет автоматически.

Но диск уже был заполнен, а реплика частично зависла — пришлось руками почистить pgsql_tmp от осиротевших файлов и перезапустить сервис PostgreSQL на реплике:

systemctl restart postgresql@15-main

После перезапуска реплика подключилась к мастеру и начала догонять лаг репликации. На это ушло заметное время — WAL накопился за минуты простоя, и применение "оптом" тоже требует ресурсов диска и CPU. Отчёты вернулись только после того, как лаг сократился до нуля, а не сразу после рестарта процесса.

Отдельно проверили, что сам мастер не пострадал: pg_stat_replication показывал, что WAL-сегменты копились, но не терялись, а место на мастере не заканчивалось — там временных файлов от чужого запроса не было, потому что запрос выполнялся именно на реплике.

Что изменили после инцидента

Разбор инцидента ценен, только если после него что-то реально меняется, а не просто "давайте будем внимательнее". Изменения разделили на три уровня.

Ограничения на уровне PostgreSQL для роли отчётности:

ALTER ROLE reporting_readonly SET statement_timeout = '60s';
ALTER ROLE reporting_readonly SET work_mem = '32MB';

statement_timeout гарантирует, что ни один запрос от BI-системы физически не сможет висеть двадцать минут — он будет отменён автоматически. Отдельный work_mem для роли отчётности не решает проблему полностью (при большом объёме сортировки временные файлы всё равно появятся), но делает поведение предсказуемым и одинаковым для всех запросов этой роли.

Изоляция временных файлов от основного диска. Вынесли pgsql_tmp на отдельный table space на отдельном разделе:

CREATE TABLESPACE temp_ts LOCATION '/mnt/pg_temp';
ALTER SYSTEM SET temp_tablespaces = 'temp_ts';

Теперь даже если один запрос снова сгенерирует гигантскую сортировку, он забьёт отдельный раздел, а не тот же диск, где лежат данные и WAL. Реплика в худшем случае откажет в конкретном запросе, но не потеряет возможность применять WAL.

Мониторинг и алерты вместо ручных проверок постфактум. До инцидента никто не следил за размером pgsql_tmp и за темпом роста лага репликации в реальном времени — смотрели на лаг только когда уже сыпались жалобы пользователей. Добавили:

• алерт на лаг репликации больше 30 секунд (подробно про то, как это настроить на Grafana, есть в материале про мониторинг баз данных через Grafana);
• алерт на занятое место в разделе с временными файлами;
• сбор pg_stat_statements, чтобы регулярно видеть топ самых тяжёлых запросов по времени выполнения и по объёму записанных временных файлов (temp_blks_written), а не узнавать о них постфактум.

Ниже — сравнение конфигурации до и после для роли отчётности:

Отдельно поговорили с командой аналитики про то, что сама возможность собрать запрос без ограничения строк в конструкторе отчётов — это не мелочь UX, а прямой риск для инфраструктуры. Договорились, что новые шаблоны отчётов с полными выгрузками без пагинации проходят короткое ревью инженера перед публикацией.

Так же обсуждали смежный сценарий — что будет, если подобное случится не с отчётным запросом, а с обычным долгим SELECT от продакшн-сервиса. Разница небольшая: любой тяжёлый запрос без ограничений на реплике для чтения потенциально способен повторить эту историю, если work_mem, statement_timeout и расположение временных файлов не настроены заранее, а не по факту первого инцидента.

Частые вопросы