DBAtlas · Database operations atlas

Set statement_timeout / lock_timeout per role and database

PostgreSQLSessions

Command

-- 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;

What it does

Makes timeout parameters persistent at the role (and optionally database) level, so each workload runs with its own limits without touching the global setting.

When to use it

To keep runaway queries from exhausting resources, DDL/lock queues from freezing the application, and forgotten transactions from producing bloat.

Caution

Settings apply to NEW sessions only; recycle existing pooled connections. Users can override the value with SET in their session; this is not a security boundary. Do not set statement_timeout globally in postgresql.conf: maintenance jobs such as pg_dump, VACUUM and CREATE INDEX get cancelled too. Precedence: role+database > role > database > postgresql.conf.

Open in DBAtlas →

Related