PostgreSQL timeouts: как настроить statement_timeout, lock_timeout и idle_in_transaction_session_timeout

Зачем вообще нужны таймауты в PostgreSQL

В продакшене проблемы с базой редко выглядят как «PostgreSQL упал». Чаще они маскируются под медленный сайт, рост очередей воркеров, внезапные 500-ки, зависшие деплои миграций или «плавающие» лаги реплик. И очень часто первопричина одна из трёх:

• запрос выполняется слишком долго и забирает ресурсы;
• запрос ждёт блокировку и в это время держит соединение;
• кто-то открыл транзакцию и ушёл «обедать», удерживая блокировки и раздувая MVCC-хвост.

Для этого у PostgreSQL есть группа таймаутов. Ниже разберём три главных: statement_timeout, lock_timeout, idle_in_transaction_session_timeout. И отдельно обсудим pgbouncer и типовую боль с cron-задачами.

Карта таймаутов: что именно ограничивает каждый параметр

Сначала договоримся о терминах: разные таймауты измеряют разное, и из-за этого легко «лечить не то».

• statement_timeout — максимальное время выполнения одного SQL-оператора (statement). Если превышено, сервер прерывает выполнение.
• lock_timeout — максимальное время ожидания получения блокировки для statement. Если блокировка не получена, statement падает ошибкой.
• idle_in_transaction_session_timeout — максимальное время, которое сессия может находиться в состоянии idle in transaction (транзакция открыта, но прямо сейчас ничего не выполняется). Если превышено, сервер разрывает соединение, транзакция откатывается.

Практический смысл: statement_timeout защищает CPU/IO от «вечных» запросов, lock_timeout — пул соединений от ожидания блокировок, а idle_in_transaction_session_timeout — от забытых транзакций, которые удерживают блокировки и мешают вакууму.

Если вы держите PostgreSQL на сервере под сайт или сервис, таймауты особенно важны: они дают предсказуемость и защищают от ситуаций, когда один неудачный запрос «съедает» весь пул. На практике это актуально и для виртуального хостинга, и для VDS с выделенными ресурсами.

Как задаются таймауты: уровень кластера, БД, роли, сессии

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

Быстрая проверка текущих значений

SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;

Понять, откуда берётся параметр и кто его переопределил, удобно через pg_settings:

SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings
WHERE name IN ('statement_timeout','lock_timeout','idle_in_transaction_session_timeout');

Глобально (postgresql.conf) и через ALTER SYSTEM

Глобальные значения задаются либо в postgresql.conf, либо через ALTER SYSTEM (пишет в postgresql.auto.conf). Для таймаутов обычно нужен reload, а не рестарт.

ALTER SYSTEM SET statement_timeout = '30s';
ALTER SYSTEM SET lock_timeout = '3s';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

Глобальные значения — это «страховочная сетка». Но чаще лучше настраивать точечно: по роли приложения, по базе или на уровне конкретных операций (миграции, админские задачи).

На уровне роли и базы: лучший баланс для приложений

Если у вас есть отдельная роль для приложения (что почти всегда правильно), задайте таймауты там. Тогда psql-админка и фоновые сервисы не пострадают.

ALTER ROLE app_user SET statement_timeout = '10s';
ALTER ROLE app_user SET lock_timeout = '2s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

Можно комбинировать с ограничением на конкретную БД:

ALTER ROLE app_user IN DATABASE app_db SET statement_timeout = '8s';

На уровне сессии/транзакции: для миграций и опасных участков

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

SET statement_timeout = '2min';
SET lock_timeout = '1s';
SET idle_in_transaction_session_timeout = '15s';

Если нужно, чтобы значение действовало только в рамках транзакции, используйте SET LOCAL:

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '30s';
/* ваш DDL или запрос */
COMMIT;

statement_timeout: как не «убить» продакшен и при этом защититься

statement_timeout прерывает запрос ошибкой. Это хорошо, когда запрос реально завис или вышел за пределы SLO. Но если поставить слишком агрессивно, можно получить лавину ошибок и повторов на уровне приложения.

Типовые симптомы, что statement_timeout нужен

• периодические пики CPU/IO из-за «случайно тяжёлых» запросов;
• ORM иногда генерирует запросы без нужных индексов;
• cron-джобы конкурируют с пользовательским трафиком;
• админы запускают «быстрый SELECT», который случайно делает seq scan по большой таблице.

Рекомендации по значениям

Универсальных цифр нет, но как старт часто работают такие диапазоны:

• для веб-приложения: statement_timeout 3–15s (с учётом ретраев и нагрузки);
• для фоновых воркеров: 30–120s (если это обработка задач);
• для админских сессий: либо без ограничения, либо большой лимит (например, 10–30min).

Нюанс: statement_timeout и сетевые таймауты

Если у вас со стороны приложения/прокси есть собственные таймауты, держите согласованность. Серверный statement_timeout обычно должен быть чуть меньше клиентского. Тогда отмена запроса будет «управляемой», а не обрывом соединения.

Диагностика: какие запросы упираются в statement_timeout

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

ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET log_lock_waits = 'on';
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

Если вы ведёте разбор производительности системно, подключайте pg_stat_statements и регулярный тюнинг индексов/автовакуума. По теме автовакауума у нас есть отдельный материал: тюнинг autovacuum и индексов в PostgreSQL.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

lock_timeout: лекарство от «ожидания вечных блокировок»

lock_timeout ограничивает ожидание блокировки. Это критично для DDL (миграции, ALTER TABLE, CREATE INDEX) и для любых операций, которые могут оказаться в очереди за долгой транзакцией.

Почему lock_timeout часто важнее statement_timeout

Ожидание блокировки может длиться очень долго. При этом запрос почти не потребляет CPU, но:

• держит соединение (а соединения — ограниченный ресурс);
• копит очереди в приложении;
• может блокировать цепочку других запросов из-за пула соединений.

Типовой сценарий: миграция схемы в рабочее время

Вы выкатываете миграцию, которая делает DDL и требует эксклюзивных блокировок. В это время в системе висит забытая транзакция (idle in transaction) или просто долго выполняющийся запрос. В результате миграция ждёт, вы ждёте, деплой стоит. С lock_timeout миграция быстро упадёт, вы увидите проблему и сможете безопасно повторить операцию позже.

Для миграций часто ставят короткий lock_timeout и адекватный statement_timeout:

BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5min';
/* DDL */
COMMIT;

Как понять, что вы упёрлись именно в lock

При превышении lock_timeout сервер вернёт ошибку, а в логах будет видно ожидание (если включён log_lock_waits). Для живой диагностики используйте представления активности.

SELECT pid, usename, datname, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY pid;

Кто кого блокирует (упрощённо):

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity blocked ON blocked.pid = bl.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.page IS NOT DISTINCT FROM bl.page
  AND kl.tuple IS NOT DISTINCT FROM bl.tuple
  AND kl.virtualxid IS NOT DISTINCT FROM bl.virtualxid
  AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND kl.classid IS NOT DISTINCT FROM bl.classid
  AND kl.objid IS NOT DISTINCT FROM bl.objid
  AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid
  AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted AND kl.granted;

Запрос выше не идеален и может быть тяжёлым на очень больших системах, но для большинства случаев он быстро показывает «кто блокирует».

idle_in_transaction_session_timeout: защита от «забытых» транзакций

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

Чем опасна idle in transaction

• удерживаются блокировки (и из-за этого страдают DDL и другие запросы);
• удерживаются версии строк (старые снимки), что мешает vacuum и увеличивает bloat;
• система выглядит «всё нормально», пока не приходит миграция или maintenance и не упирается в блок.

Как найти «праздные» транзакции

SELECT pid, usename, datname, xact_start, state, state_change, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Если таких сессий много или они живут минутами, почти всегда стоит включить idle_in_transaction_session_timeout хотя бы для роли приложения.

Осторожно с легаси-кодом

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

pgbouncer и таймауты: где настраивать, чтобы не было сюрпризов

Если вы используете pgbouncer, часть проблем выглядит как «PostgreSQL», но на самом деле это поведение пула. И наоборот: вы можете поставить таймауты в PostgreSQL, а приложение их не почувствует так, как ожидаете, из-за режима пула.

Три режима pool_mode и их влияние

• session — соединение с сервером закреплено за клиентом на всю сессию. Параметры сессии работают «как обычно».
• transaction — соединение закрепляется на время транзакции. Любые SET вне транзакции и «настройки сессии» могут не сохраниться для следующей транзакции.
• statement — соединение выделяется на один statement; многие вещи становятся непредсказуемыми, и этот режим используют редко.

Практический вывод: если приложение делает SET statement_timeout один раз при старте соединения, а pgbouncer работает в режиме transaction, то для следующих транзакций вы можете не получить ожидаемое поведение.

Как правильно задавать таймауты при transaction pooling

Самый надёжный подход — выставлять таймауты на стороне PostgreSQL на уровне роли/базы (ALTER ROLE ... SET и ALTER ROLE IN DATABASE ... SET). Тогда они применяются при установке server connection и будут действовать корректно независимо от поведения клиента.

Если нужно динамически менять таймауты для отдельных операций, делайте это внутри транзакции через SET LOCAL. Этот паттерн обычно дружит с transaction-пулом.

Ожидание в очереди pgbouncer и server-side таймауты

Если клиент ждёт свободный серверный коннект в очереди pgbouncer, statement_timeout PostgreSQL не поможет, потому что запрос ещё не дошёл до сервера. Для этого у pgbouncer есть собственные таймауты ожидания и лимиты.

Если вы хотите глубже разобраться в пуллинге, посмотрите нашу инструкцию: как устроен пул соединений PostgreSQL с pgbouncer.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

cron-скрипты: как таймауты спасают от «тихих» зависаний

cron-задачи любят ломать продакшен по двум причинам: они запускаются регулярно и часто написаны «по-быстрому». Типичный анти-паттерн: скрипт открывает транзакцию, делает выборку, потом пишет файл или делает внешний запрос, а транзакция всё это время висит открытой.

Что можно сделать без переписывания всего мира:

• для роли, под которой работает cron, поставить idle_in_transaction_session_timeout (например, 30–60s);
• для «опасных» DDL/обновлений — короткий lock_timeout, чтобы не зависать навечно;
• для тяжёлых отчётов — осмысленный statement_timeout и отдельное окно запуска, когда нагрузка ниже.

Если cron запускает psql, параметры можно задавать на старте сессии опциями клиента, но обычно надёжнее использовать настройки роли/базы, чтобы не зависеть от того, как именно написан скрипт и какие флаги в нём забыли.

Рекомендуемый стартовый «профиль» таймаутов

Ниже — не догма, а рабочая отправная точка для типичного веб-проекта. Тюнинг делайте по фактическим метрикам и логам.

1) Роль приложения (OLTP)

ALTER ROLE app_user SET statement_timeout = '10s';
ALTER ROLE app_user SET lock_timeout = '2s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

2) Роль миграций/деплоя

Часто миграциям нужен большой statement_timeout, но маленький lock_timeout.

ALTER ROLE deploy_user SET statement_timeout = '10min';
ALTER ROLE deploy_user SET lock_timeout = '1s';
ALTER ROLE deploy_user SET idle_in_transaction_session_timeout = '60s';

3) Админская роль

Админке иногда нужно отключать ограничения, но при этом idle in transaction всё равно лучше ограничить.

ALTER ROLE dba SET statement_timeout = '0';
ALTER ROLE dba SET lock_timeout = '0';
ALTER ROLE dba SET idle_in_transaction_session_timeout = '5min';

Что делать, если после включения таймаутов посыпались ошибки

Это нормальный этап взросления системы: таймауты вытаскивают скрытые проблемы наружу. Дальше действуем по плану, чтобы не скатиться в «выключили обратно».

1) Классифицируйте ошибки

• ошибка по statement_timeout — запрос реально долго выполняется;
• ошибка по lock_timeout — ожидание блокировки;
• разрыв по idle_in_transaction_session_timeout — код держит транзакцию открытой без работы.

2) Быстро найдите виновника

В момент инцидента самое полезное — pg_stat_activity (кто ждёт и кто блокирует) и логи ожиданий (log_lock_waits). Если проблема повторяется, подключайте систематическую аналитику: pg_stat_statements (если включён) и разбор «топа» запросов по времени и количеству вызовов.

3) Исправьте причину, а не симптом

• долгий запрос: индекс, переписывание запроса, ограничение выборки, правильные JOIN-условия;
• lock wait: сокращайте длительность транзакций, убирайте «пользовательские» транзакции, переносите DDL, используйте варианты операций без длительных эксклюзивных блокировок там, где это возможно;
• idle in transaction: чините код (коммит/роллбек вовремя), не держите транзакции во время внешних вызовов.

Чеклист внедрения таймаутов без боли

1. Соберите инвентарь ролей: приложение, миграции, аналитика, cron, админка.

2. Включите минимально полезное логирование: log_lock_waits, разумный deadlock_timeout, порог log_min_duration_statement.

3. Сначала выставьте таймауты на уровне роли приложения (не глобально) и наблюдайте.

4. Если используете pgbouncer в режиме transaction, избегайте «SET при коннекте» в приложении: опирайтесь на ALTER ROLE ... SET и точечные SET LOCAL.

5. Добавьте отдельные профили для миграций и cron, чтобы не конфликтовать с онлайном.

6. Через неделю пересмотрите значения: где слишком жёстко, где слишком мягко, какие запросы требуют оптимизации.

Итог

statement_timeout, lock_timeout и idle_in_transaction_session_timeout — это базовая гигиена PostgreSQL. Они помогают удерживать предсказуемость, защищают пул соединений и ускоряют обнаружение проблем: долгих запросов, блокировок и неправильной работы с транзакциями. Начинайте с настроек на уровне роли приложения, учитывайте особенности pgbouncer и подкрепляйте изменения логированием и регулярной диагностикой.