롤 및 데이터베이스별 statement_timeout / lock_timeout 설정

명령어

-- Application role: short queries, short lock waits, a limit on forgotten transactions
ALTER ROLE web_app SET statement_timeout = '30s';
ALTER ROLE web_app SET lock_timeout = '5s';
ALTER ROLE web_app SET idle_in_transaction_session_timeout = '10min';

-- Let the reporting role run longer, only in this database
ALTER ROLE report_app IN DATABASE mydb SET statement_timeout = '15min';

-- Show configured role/database settings
SELECT coalesce(r.rolname, '(all)') AS role,
       coalesce(d.datname, '(all)') AS database,
       s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase
ORDER BY 1, 2;

-- Revert
ALTER ROLE web_app RESET lock_timeout;

하는 일

timeout 파라미터를 롤(필요하면 데이터베이스) 수준에서 영구 설정하여 전역 설정을 건드리지 않고 각 워크로드가 자체 한도로 실행되게 한다.

사용 시점

폭주 쿼리가 리소스를 고갈시키고, DDL/락 대기열이 애플리케이션을 멈추며, 잊힌 트랜잭션이 bloat을 만드는 것을 막기 위해.

주의

설정은 새 세션에만 적용되므로 기존 pool 연결을 재순환한다. 사용자는 세션에서 SET으로 값을 재정의할 수 있으므로 보안 경계가 아니다. postgresql.conf에서 statement_timeout을 전역으로 설정하지 않는다. pg_dump, VACUUM, CREATE INDEX 같은 유지보수 작업도 취소된다. 우선순위: role+database > role > database > postgresql.conf.