280k запросов в день: полная оптимизация базы данных для высоконагруженной системы мониторинга

Пять дней система мониторинга 143 WhatsApp-групп молчала. Боты не отправляли ни одного сообщения, не фиксировали лиды, не реагировали на события в чатах — хотя должны были работать непрерывно. Мониторинг сервера показывал: 27% процессора на одной задаче. Когда я добрался до корня проблемы, оказалось, что один алгоритм генерировал около 280 000 запросов к PostgreSQL за один рабочий цикл. Эта статья — подробный разбор того, как мы диагностировали проблему, что именно сделали для оптимизации базы данных и каких результатов добились. Запросы ускорились с 2.3 секунды до 0.08 секунды.

Контекст: система мониторинга на 143 группы

Чтобы понять масштаб проблемы, нужно понимать архитектуру системы. У нас работает платформа мониторинга WhatsApp-групп по аренде недвижимости на Бали и Пхукете. Система отслеживает активность в 143 группах: новые сообщения, запросы на аренду, потенциальные лиды. Несколько аккаунтов работают параллельно, каждый со своим прокси, каждый обрабатывает свой пул групп.

На каждое входящее событие система делает серию действий: классифицирует сообщение (лид или нет), извлекает контактные данные, проверяет дубли, сохраняет результат в PostgreSQL, при необходимости уведомляет менеджера. При потоке событий из 143 групп это генерирует стабильную нагрузку около 280 000 SQL-запросов в день в штатном режиме.

Пока база данных справлялась — всё шло хорошо. Но в один момент запросы начали занимать 2-3 секунды вместо обычных 50-100 миллисекунд. Очередь событий начала расти. А потом система просто перестала успевать обрабатывать входящий поток и по сути встала.

Симптомы: 5 дней тишины и горящий сервер

Первый признак — боты перестали отправлять уведомления менеджерам. Потом перестали фиксировать лиды. Потом полностью остановились. Сервер при этом не упал: процесс был running, памяти хватало, сетевые соединения были активны. Но CPU одной задачи висел на 27% — при том, что в норме система потребляла 1-2%.

Это классический silent failure — технически всё работает, результата ноль. Именно такие ситуации самые коварные: нет crash-лога, нет error-а в Sentry, нет алерта в мониторинге. Сервис просто завис в бесконечном ожидании ответа от базы данных.

Пять дней простоя означали: система не зафиксировала ни одного лида из 143 групп с суммарной аудиторией более 50 000 участников. Потенциальные клиенты писали в чаты — и получали тишину вместо быстрого ответа менеджера.

Шаг 1: диагностика через EXPLAIN ANALYZE и pg_stat_statements

Находим медленные запросы

Первый инструмент диагностики — расширение pg_stat_statements. Оно накапливает статистику по всем запросам: сколько раз выполнялся, суммарное время, среднее время, количество строк. Включить его просто: добавить в postgresql.conf строку shared_preload_libraries = 'pg_stat_statements' и перезапустить PostgreSQL.

Запрос к pg_stat_statements сразу показал проблему. Один тип запроса — выборка истории событий по паре (phone, group_id) — выполнялся сотни тысяч раз в час со средним временем 8 миллисекунд. Суммарно это съедало несколько минут процессорного времени на каждый рабочий цикл.

Второй инструмент — EXPLAIN ANALYZE. Запускаем его на проблемный запрос и видим в выводе слова Seq Scan — последовательное сканирование таблицы. Это значит: PostgreSQL читает всю таблицу целиком, строка за строкой, чтобы найти нужные записи. При таблице в несколько сотен тысяч строк это катастрофически медленно.

Что такое Seq Scan и почему он убивает производительность

Представьте телефонный справочник без алфавитного порядка. Чтобы найти нужный номер, нужно перечитать все страницы от начала до конца. Именно так работает Seq Scan: PostgreSQL читает каждую строку таблицы и проверяет условие WHERE. При миллионе строк — читает миллион строк на каждый запрос.

Index Scan — это как алфавитный указатель. PostgreSQL сначала смотрит в индекс, находит нужную позицию и читает только нужные строки. При том же миллионе строк — читает десятки или сотни строк вместо миллиона.

В нашем случае таблица событий содержала около 900 000 записей и росла. Каждый из 280 000 запросов делал Seq Scan по этой таблице. Арифметика простая: 280 000 запросов × 900 000 строк на проверку = катастрофа.

Шаг 2: добавляем индексы на нужные поля

Какие индексы добавили

Анализ запросов через pg_stat_statements и EXPLAIN ANALYZE показал три поля, которые участвуют в WHERE-условиях почти каждого медленного запроса: phone (номер телефона контакта), group_id (идентификатор группы), created_at (время события).

• Индекс на phone: CREATE INDEX CONCURRENTLY idx_events_phone ON events(phone); — поиск по номеру телефона ускорился в 40 раз
• Индекс на group_id: CREATE INDEX CONCURRENTLY idx_events_group_id ON events(group_id); — выборка по конкретной группе стала мгновенной
• Составной индекс (phone, group_id): CREATE INDEX CONCURRENTLY idx_events_phone_group ON events(phone, group_id); — для запросов, которые фильтруют по обоим полям одновременно
• Индекс на created_at: CREATE INDEX CONCURRENTLY idx_events_created_at ON events(created_at); — для выборок за последние N часов или дней
• Составной индекс (group_id, created_at): — для запросов «события в группе X за последние 24 часа»

Ключевое слово CONCURRENTLY позволяет создавать индексы без блокировки таблицы. PostgreSQL строит индекс в фоне, пока система продолжает работать. Без этого ключевого слова создание индекса на большой таблице заблокировало бы все операции записи.

Результат после индексов

После добавления индексов запускаем EXPLAIN ANALYZE снова. Вместо Seq Scan видим Index Scan и Index Only Scan. Время типичного запроса упало с 8 миллисекунд до 0.3 миллисекунды — в 26 раз. Это уже хорошо, но ещё не конец оптимизации.

Важно понимать: индексы ускоряют чтение, но немного замедляют запись. Каждый INSERT или UPDATE теперь должен обновить не только таблицу, но и все индексы. Для нашей системы это приемлемый компромисс: чтений на порядок больше, чем записей.

Шаг 3: переписываем алгоритм — с 280 000 до 10 запросов

Корневая причина: поэлементные запросы

Индексы помогли, но не решили главную проблему: архитектурную ошибку в алгоритме обработки. Функция pick_next_task() работала так: для каждой виллы (44 объекта) × для каждой группы (143 группы) × для каждого аккаунта (9 штук) — делала отдельный запрос в базу, проверяя историю отправок. Плюс дополнительные запросы для проверки статистики.

Итоговая формула: 44 виллы × 143 группы × 9 аккаунтов × ~5 запросов на пару = около 280 000 запросов за один вызов функции. Даже с индексами — это катастрофа. Система не успевала завершить один цикл выбора задачи до начала следующего.

Это как зайти на рынок и для каждого из 44 прилавков отдельно спрашивать продавца: «У вас есть помидоры? А огурцы? А что было вчера?» — вместо того чтобы один раз обойти весь рынок и составить полный список.

Решение: агрегирующие запросы

Переписали логику полностью. Вместо поэлементных запросов сделали три агрегирующих SQL-запроса, которые за один проход собирают всю нужную информацию:

• Запрос 1 — приоритеты объектов: один JOIN с таблицей broadcast_log, сортировка по давности последней отправки. Возвращает все объекты с датой последней публикации за одно обращение к базе.
• Запрос 2 — статистика групп: WHERE + GROUP BY + HAVING. Возвращает все группы, где объект уже был за последние 24 часа — чтобы не повторяться.
• Запрос 3 — статус аккаунтов: простой SELECT с фильтром по статусу. Возвращает активные аккаунты и их текущую нагрузку.

Дополнительно добавили материализованный кэш приоритетов: раз в 15 минут PostgreSQL обновляет таблицу-сводку с рейтингом объектов для публикации. Алгоритм выбора просто берёт верхнюю строку из этой таблицы — один запрос, никаких JOIN, никаких расчётов в рантайме.

Итого: 280 000 запросов за цикл стало 10. Не десять тысяч. Ровно десять.

Шаг 4: партиционирование таблиц по дате

Зачем нужно партиционирование

Даже с индексами и оптимизированными запросами таблица событий продолжала расти. Каждый день добавляется несколько десятков тысяч строк. Через полгода таблица будет содержать десятки миллионов записей, и даже индексные запросы начнут замедляться.

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

Как настроили партиционирование

Разбили таблицу events на месячные партиции. Данные старше 30 дней автоматически перемещаются в архивную партицию. Рабочие запросы — а они почти всегда обращаются к данным за последние 24-72 часа — читают только актуальную партицию. Объём данных, который сканирует PostgreSQL, уменьшился в 10-15 раз.

Дополнительный бонус: архивные партиции можно перемещать на более дешёвые носители или выгружать в холодное хранилище. Это снижает расходы на инфраструктуру при росте объёма данных.

Нюанс: при партиционировании нужно пересоздать индексы для каждой партиции. PostgreSQL не наследует индексы автоматически в старых версиях. В версии 11+ индексы на партиционированной таблице автоматически применяются ко всем дочерним партициям.

Шаг 5: connection pooling через PgBouncer

Проблема с соединениями

PostgreSQL — не самый эффективный менеджер соединений. Каждое соединение — это отдельный процесс с собственной памятью (обычно 5-10 МБ на соединение). При 200 активных соединениях PostgreSQL тратит 1-2 ГБ RAM только на управление соединениями, ещё не начав выполнять запросы.

В нашей системе несколько воркеров работали параллельно, каждый открывал пул соединений. В пиковые моменты количество соединений достигало 200. PostgreSQL начинал тратить значительную часть ресурсов на управление соединениями вместо выполнения запросов.

PgBouncer: 200 соединений стали 20

PgBouncer — легковесный пул соединений для PostgreSQL. Он работает как прокси: приложения подключаются к PgBouncer, а PgBouncer поддерживает небольшой пул реальных соединений к PostgreSQL. Когда запрос завершён, соединение возвращается в пул и используется для следующего запроса.

Конфигурация минимальная: устанавливаем PgBouncer, прописываем в pgbouncer.ini параметры базы данных, режим работы (transaction pooling для максимальной эффективности) и размер пула. Приложение подключается к порту 6432 вместо 5432 — больше никаких изменений в коде.

После включения PgBouncer количество реальных соединений к PostgreSQL упало с 200 до 20. RAM, которую раньше съедали соединения, освободилась для кэша данных. PostgreSQL начал кэшировать больше страниц в shared_buffers — это дополнительно ускорило запросы.

Шаг 6: кэширование частых запросов в Redis

Что кэшировать

Не все запросы нуждаются в кэшировании. Кэш эффективен, когда данные меняются редко, но читаются часто. В нашей системе таких данных достаточно: список активных групп, конфигурация аккаунтов, справочник ключевых слов для классификации сообщений, агрегированная статистика по группам.

Эти данные запрашиваются при обработке каждого входящего сообщения — то есть тысячи раз в час. При этом меняются они редко: список групп обновляется раз в день, конфигурация аккаунтов — при изменении настроек, справочник ключевых слов — раз в неделю.

Как настроили Redis-кэш

Добавили Redis как слой кэширования между приложением и PostgreSQL. Логика проста: при запросе данных сначала проверяем Redis. Если данные есть и не устарели — возвращаем из кэша. Если нет — делаем запрос к PostgreSQL, сохраняем результат в Redis с TTL (временем жизни).

• Список активных групп: TTL 300 секунд (5 минут). Обновляется при изменении настроек.
• Конфигурация аккаунтов: TTL 60 секунд. Короткий TTL, потому что статус аккаунта может меняться быстро.
• Агрегированная статистика групп: TTL 60 секунд. Данные за последний час — достаточно актуально для принятия решений.
• Справочник ключевых слов: TTL 3600 секунд (1 час). Меняется редко, кэш долгоживущий.

Результат: около 60-70% запросов к PostgreSQL стали обслуживаться из Redis. Redis отвечает за 0.1-0.3 миллисекунды против 0.3-2 миллисекунд для PostgreSQL даже с индексами. Нагрузка на базу данных дополнительно снизилась.

Шаг 7: batch inserts вместо поэлементных записей

Проблема одиночных INSERT

При обработке потока событий из 143 групп система делала много записей в базу: сохранение события, обновление статистики, запись результата классификации. Изначально каждая запись — отдельный SQL-запрос. При пиковой нагрузке это создавало очередь из тысяч одиночных INSERT в минуту.

Каждый INSERT — это транзакция: начало транзакции, выполнение запроса, запись в WAL (write-ahead log), коммит. Для 1000 одиночных INSERT PostgreSQL выполняет 1000 таких циклов. Это дорого с точки зрения I/O.

Batch insert: 1000 записей за один запрос

Решение — накапливать события в буфере и сбрасывать их пакетом. Вместо 1000 отдельных INSERT — один запрос вида INSERT INTO events (phone, group_id, message, created_at) VALUES (...), (...), (...) с тысячей строк в VALUES.

Один batch INSERT с 1000 строками вместо 1000 одиночных INSERT — это примерно в 50-100 раз меньше нагрузки на I/O. PostgreSQL делает одну транзакцию, одну запись в WAL, один коммит.

Компромисс: данные попадают в базу с небольшой задержкой (мы использовали буфер на 5 секунд). Для нашего случая это допустимо: лиды обрабатываются не мгновенно, а в рамках цикла. Если бы нужна была мгновенная запись — можно уменьшить интервал или использовать COPY вместо INSERT для максимальной скорости.

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

После оптимизации мы выстроили постоянный мониторинг производительности базы данных. Вот набор инструментов, которые используем:

pg_stat_statements

Расширение PostgreSQL, которое ведёт статистику по всем выполненным запросам. Раз в день смотрим топ-10 запросов по суммарному времени выполнения. Если что-то новое появилось в топе — разбираемся. Этот простой процесс позволяет ловить проблемы до того, как они станут критичными.

pgBadger

Анализатор лог-файлов PostgreSQL. Собирает подробную статистику: самые медленные запросы, самые частые запросы, пики нагрузки по времени суток, ошибки соединений. Запускаем pgBadger раз в неделю и получаем HTML-отчёт с графиками. Хорошо видно, когда нагрузка начинает расти нестандартно.

EXPLAIN ANALYZE в режиме отладки

Для любого нового запроса, который появляется в коде, обязательно прогоняем EXPLAIN ANALYZE на реальных данных. Смотрим на три вещи: тип сканирования (Index Scan, не Seq Scan), оценку строк (если planner сильно ошибается в estimate vs actual — нужно обновить статистику через ANALYZE), время выполнения.

Мониторинг бизнес-метрик

Ключевой урок инцидента: технический мониторинг (CPU, RAM, uptime) недостаточен. Нужен мониторинг бизнес-результата. После инцидента добавили проверки: если за последние 4 часа не обработано ни одного события из группы — алерт. Если количество сохранённых лидов за час упало более чем на 80% от среднего — алерт.

Неважно, что процесс running и CPU в норме. Если бизнес-метрика упала в ноль — что-то сломано. Этот принцип теперь зашит в health-check системы.

Итоги: конкретные числа до и после

После деплоя всех изменений система заработала в штатном режиме через 10 минут. Первое уведомление о лиде пришло именно тогда. Это, пожалуй, лучший индикатор успеха — не графики CPU, а реальный результат: система снова делает свою работу.

Полный список применённых методов оптимизации PostgreSQL в порядке приоритета:

• Переписать алгоритм — убрать поэлементные запросы, использовать агрегирующие SQL. Самый большой эффект: 280 000 → 10 запросов за цикл.
• Добавить индексы на поля в WHERE-условиях. Ускорение каждого запроса в 26 раз.
• Партиционирование по дате — ограничить объём данных, которые сканируют запросы.
• PgBouncer — сократить количество реальных соединений к PostgreSQL.
• Redis-кэш — убрать повторяющиеся запросы редко меняющихся данных.
• Batch inserts — заменить тысячи одиночных INSERT на пакетные операции.

Главный урок: масштаб меняет правила игры

Когда групп было 20 и объектов 16, система справлялась. Поэлементные запросы делали 16 × 20 × 5 = 1600 обращений к базе за цикл. Медленно, но терпимо. Когда групп стало 143 и объектов 44 — 44 × 143 × 9 × 5 = почти 280 000 запросов. Квадратичный рост при линейном масштабировании системы.

Это фундаментальная истина о высоконагруженных системах: алгоритм, который работает при малом объёме данных, может быть катастрофически неэффективным при росте. Асимптотическая сложность O(n²) или O(n³) убьёт любую систему, если n продолжает расти.

Правильная архитектура с самого начала — агрегирующие запросы, индексы, кэширование — позволила бы избежать пяти дней простоя. Но часто на старте объём данных маленький, и оптимизация кажется преждевременной. Рецепт: закладывайте мониторинг с первого дня и следите за ростом времени выполнения ключевых операций. Как только время начинает расти нелинейно — значит, пора оптимизировать.

Ещё один вывод: тестируйте алгоритмы на реалистичном объёме данных. Запуск EXPLAIN ANALYZE на таблице с 100 строками ничего не скажет о производительности на таблице с миллионом строк. Нагрузочное тестирование с реальными данными — обязательная часть разработки высоконагруженных систем.

Все описанные техники — индексы, партиционирование, PgBouncer, Redis, batch inserts — это стандартный набор инструментов для работы с PostgreSQL под нагрузкой. Ни один из них не требует замены СУБД или кардинальной переработки архитектуры. Большинство проблем с производительностью PostgreSQL решаются именно этими инструментами, если применять их в правильном порядке: сначала диагностика через EXPLAIN ANALYZE и pg_stat_statements, потом лечение в порядке убывания эффекта.