Запрос редко становится проблемой только потому, что «долго выполняется». В production он конкурирует за соединения, память, страницы в кеше и блокировки. Один и тот же SQL может укладываться в несколько миллисекунд на тестовой базе и ставить сервис в очередь при другом распределении данных или параллелизме. Больно в итоге не базе, а операции пользователя: обработчик ждёт соединение, транзакция удерживает блокировку, повторные попытки усиливают нагрузку.
Поэтому я не начинаю с совета «добавить индекс». Сначала связываю симптом в сервисе с конкретным типом запроса, планом и состоянием базы. Индекс — одно из возможных изменений, у которого есть цена на запись и эксплуатацию.
Контекст и ограничения production
Фраза «PostgreSQL медленный» почти ничего не задаёт. Нужны как минимум форма нагрузки, распределение параметров и место задержки. Запрос может быть дешёвым сам по себе, но ждать освобождения соединения. Может быстро читать одну запись и долго ждать блокировку. Может иметь хороший медианный результат и проваливаться на редком крупном клиенте. Усреднение скрывает все три случая.
До изменения схемы я фиксирую ограничения:
- какая операция сервиса деградирует и каков её бюджет времени;
- задержка возникает до получения соединения, в базе или после чтения строк;
- запрос выполняется отдельно или внутри транзакции с другими действиями;
- какие параметры дают медленный вариант и насколько они типичны;
- каково соотношение чтения и записи, допустима ли дополнительная цена индекса;
- можно ли безопасно строить индекс на рабочей таблице и как откатить изменение.
Данные на стенде должны воспроизводить не только объём, но и перекосы. Равномерно сгенерированные статусы не покажут проблему таблицы, где почти все строки уже завершены, а рабочая выборка мала. Тёплый кеш не покажет стоимость чтения после перезапуска или смены набора активно используемых данных. Один последовательный запуск не покажет конкуренцию.
Рабочая модель: от операции к страницам данных
Я рассматриваю путь целиком, а не только верхнюю строку EXPLAIN.
flowchart LR
A[Операция API] --> B[Пул соединений Go]
B --> C[Планировщик и исполнитель PostgreSQL]
C --> D[Shared buffers]
D --> E[Хранилище]
C --> F[Блокировки и WAL]
A -. метрики операции .-> G[Наблюдаемость]
B -. ожидание соединения .-> G
C -. pg_stat_statements и wait events .-> GЭта модель не даёт свалить любую задержку на планировщик. Если большая часть времени ушла на получение соединения, новый индекс может сократить время удержания ресурса, но не исправит неверный размер пула или длинные транзакции. Если процесс ждёт блокировку, план запроса вообще не объясняет основную задержку.
Снять исходное состояние
pg_stat_statements показывает частоту, суммарное время и разброс по нормализованным запросам. Я сопоставляю их с метрикой операции и отметками выпусков: дорогой редкий отчёт и дешёвый частый запрос требуют разных решений.
План читаю снизу вверх. Сверяю оценку числа строк с фактом, смотрю loops, фактически прочитанные буферы, временные файлы и сортировки. Особенно важна ошибка оценки: если планировщик ожидал несколько строк, а получил большой набор, выбранный Nested Loop или способ доступа мог быть разумным только для ошибочной картины данных.
Здесь и ниже orders — условная форма запроса, а не схема конкретного проекта.
-- Проверяем план критичного чтения на репрезентативных параметрах.
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT id, available_at
FROM orders
WHERE merchant_id = $1
AND status = 'ready'
AND available_at <= $2
ORDER BY available_at, id
LIMIT $3;
-- Поддерживаем равенство, диапазон и порядок только для рабочей выборки.
CREATE INDEX CONCURRENTLY idx_orders_ready_merchant_available
ON orders (merchant_id, available_at, id)
WHERE status = 'ready';ANALYZE действительно выполняет запрос. Для изменяющих запросов я использую безопасную копию данных или транзакцию с осознанным откатом; для тяжёлого чтения не запускаю его на основной базе только ради красивого плана. План со стенда полезен лишь при сопоставимых статистике, объёме и распределении значений.
Проектировать индекс под путь доступа
Индекс должен выражать конкретный путь: от равенства по владельцу выборки к диапазону и нужному порядку. Показанный частичный индекс меньше полного, если рабочий статус занимает небольшую долю таблицы, и поддерживает порядок без отдельной сортировки. Но это не бесплатное ускорение. Предикат запроса должен позволять планировщику доказать применимость индекса. Универсальный запрос с параметризованным статусом может его не использовать. Изменение набора рабочих статусов потребует миграции, а каждый индекс увеличивает объём WAL, работу vacuum, потребление кеша и стоимость вставок и обновлений.
Порядок полей тоже не сводится к правилу «самое селективное первым». Я ставлю рядом условия равенства, затем учитываю диапазон, сортировку и реальные варианты запроса. Если тот же индекс пытаются использовать для пяти несовместимых путей, обычно получается крупная структура, которая ни один из них не обслуживает хорошо.
CREATE INDEX CONCURRENTLY снижает время блокировки записей, но дольше строится, создаёт дополнительную нагрузку и может оставить невалидный индекс после сбоя. Для такой миграции нужны наблюдение, проверка результата и отдельный путь удаления. Слово CONCURRENTLY — не план развёртывания.
Уменьшить работу до изменения схемы
Иногда лучший индекс — тот, который не пришлось добавлять. Сначала проверяю, действительно ли сервису нужны все выбранные строки и поля. Большой результат остаётся большим: его надо сформировать в базе, передать по сети и разобрать в Go.
Для последовательного просмотра устойчивее курсорная пагинация, если продукт допускает её семантику:
SELECT id, created_at, status
FROM orders
WHERE merchant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT $4;В паре с индексом (merchant_id, created_at DESC, id DESC) стоимость страницы не растёт вместе с номером, как у большого OFFSET. Цена решения — более сложный контракт API: нельзя честно обещать произвольный переход на страницу, а вставки между запросами требуют заранее определённой семантики снимка.
Другие рабочие изменения обычно прозаичны: убрать N+1, объединить точечные чтения в ограниченную пачку, вынести необязательную работу из транзакции, перестать обновлять строку без изменения значений. Денормализация или заранее рассчитанное представление оправданы, когда путь чтения стабилен и команда готова владеть обновлением, запаздыванием и восстановлением производных данных.
Типичные поломки и как их ловят
Быстро в плане, медленно в сервисе
Одиночный EXPLAIN ANALYZE не создаёт конкуренции. В приложении отдельно измеряю ожидание соединения и выполнение запроса. В PostgreSQL смотрю wait_event, блокирующую транзакцию и возраст транзакций. Частая причина — сетевой вызов или тяжёлое вычисление внутри открытой транзакции. Ускорять следующий SELECT в такой ситуации вторично.
План подходит большинству параметров, но не крайним
Один продавец, период или статус может содержать несопоставимо больше строк, чем остальные. Средняя оценка становится опасной. Это видно по сравнению оценённых и фактических строк на проблемных параметрах. Помогают актуальная статистика, повышенная детализация статистики для значимого столбца или extended statistics для зависимых признаков. Цена — дополнительный анализ таблицы и усложнение эксплуатации; настройки нужно подтверждать планом, а не применять ко всей базе.
Отдельно проверяю prepared statements: generic plan экономит планирование, но может быть плох для параметров с разной селективностью. Принудительный custom plan тоже не универсален — он добавляет стоимость планирования. Решение принимается для конкретного запроса и распределения вызовов.
Индекс есть, а чтение остаётся дорогим
Причины различаются: запрос возвращает значительную часть таблицы, типы или выражения не совпадают, статистика устарела, данные физически читаются случайно, индекс раздут, либо выбранные строки требуют множества обращений к heap. Факт наличия Index Scan ещё не означает хороший план. Я проверяю число строк, буферы и циклы, а не название узла.
База захлёбывается после «масштабирования» сервиса
Каждый экземпляр приложения приносит свой пул. Увеличение числа экземпляров может создать больше активных запросов, чем база способна выполнять параллельно, и ухудшить задержку для всех. Ограниченный пул здесь служит обратным давлением. Его размер связывают с допустимым параллелизмом базы и числом экземпляров, а не с желанием убрать ожидание любой ценой.
Производительность деградирует постепенно
Рост мёртвых версий строк, отставание autovacuum, долгие транзакции и накопление таблицы часто выглядят как «вчера индекс работал». Проверяю динамику размера таблиц и индексов, ход vacuum, возраст транзакций и изменение планов. REINDEX или ручной VACUUM может убрать симптом, но без причины — параметров autovacuum, горячих обновлений или удерживаемого снимка — проблема вернётся.
Что измерять
Метрики должны соединять пользовательскую операцию и ресурс базы. Для критичных путей я держу четыре слоя:
- в сервисе: частота, медиана и хвост задержки по операции, ошибки, тайм-ауты, число возвращённых строк;
- в пуле: занятые и свободные соединения, очередь и время ожидания соединения;
- по запросам: вызовы, суммарное и среднее время, строки, чтения и попадания буферов, временные блоки, WAL;
- в базе: активные и ожидающие сессии, типы ожиданий, блокировки, взаимоблокировки, ход autovacuum, контрольные точки, задержка хранилища и реплик.
Один cache hit ratio не доказывает здоровье базы: высокий процент легко соседствует с небольшим числом очень дорогих физических чтений. Среднее время тоже не показывает редкий тяжёлый параметр. Полезнее сравнивать распределения до и после выпуска на одинаковых временных окнах и форме трафика, сохраняя идентификатор версии приложения.
Для медленных запросов применяю ограниченное по длительности и объёму журналирование. auto_explain помогает поймать планы внутри приложения, но сам создаёт накладные расходы, а параметры и текст запросов могут содержать чувствительные данные. Включать его без выборки, срока выключения и правил доступа — плохая диагностика.
У оптимизации должен быть проверяемый результат: уменьшилось ли время операции, число прочитанных страниц, время удержания соединения или объём временных данных. Если изменился только вид плана, задача не решена.
Когда так делать не стоит
Не каждый последовательный просмотр требует курсора, и не каждый фильтр — отдельного индекса. На небольшой стабильной таблице последовательное чтение может быть дешевле, а сложная схема индексов только ухудшит запись. Частичный индекс вреден, если предикат постоянно меняется или запросы не могут использовать его явно. Покрывающий индекс вреден для широких и часто обновляемых полей.
Партиционирование не является лекарством от большой таблицы. Оно полезно, когда граница совпадает с удалением данных, обслуживанием или устойчивым отсечением разделов. При случайном ключе доступа оно добавляет таблицы, индексы, миграции и риск плохого pruning, не устраняя исходную работу.
Кеш не заменяет исправление неограниченного запроса: он меняет профиль нагрузки и добавляет протокол инвалидирования. Реплика для чтения не подходит, когда операция требует немедленно увидеть собственную запись. Увеличение тайм-аута не лечит очередь, а разрешает ей расти дольше.
Иногда правильное решение — оставить запрос как есть. Если он редкий, ограничен по параллелизму и не влияет на критический путь, стоимость новой структуры и её сопровождения может быть выше экономии. Production-оптимизация начинается с бюджета системы, а не с эстетики плана.
Что унести в ревью
У изменения производительности должны быть три ответа: какую пользовательскую операцию оно исправляет, каким измерением подтверждена причина и какую новую стоимость оно добавляет. Для индекса это запись, WAL, место и обслуживание. Для денормализации — согласованность и восстановление. Для большего пула — конкуренция внутри базы.
Хорошее ревью не заканчивается на EXPLAIN. Оно проверяет проблемные параметры, параллельную нагрузку, миграцию и откат, затем сравнивает метрики операции после выпуска. Надёжность PostgreSQL складывается из этого контура: наблюдать путь целиком, уменьшать реальную работу и оплачивать только те ускорения, которыми команда готова владеть.