Enforce Database Query Timeouts

Set statement_timeout, lock_timeout, and idle_in_transaction_session_timeout per role and transaction so runaway queries cannot exhaust the pool.

~25 minpostgresqltimeoutsresilience

Task

Bound how long any single query or lock wait can run, and surface timeouts as controlled 503 responses instead of silent queueing.

When to use this

A reporting query occasionally blocks a connection for minutes, the pool fills, and every endpoint times out. The database looks healthy while the API is down, because nothing limits how long a single statement may hold a connection.

Prerequisites

PostgreSQL 13 or newer
A superuser role for setting database and role defaults
Application code that can map SQLSTATE errors to HTTP responses

Steps

1. Set conservative timeouts as role defaults so every new session inherits them.

ALTER ROLE app_rw SET statement_timeout = '5s';
ALTER ROLE app_rw SET lock_timeout = '2s';
ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '30s';

Expected output: New sessions for app_rw cancel statements after 5 seconds and lock waits after 2 seconds.

2. Allow a longer budget only inside the reporting transaction instead of weakening the role default.

BEGIN;
SET LOCAL statement_timeout = '120s';
SELECT region, sum(total) FROM orders GROUP BY region;
COMMIT;

Expected output: The report may run for two minutes; all other statements on that connection keep the 5 second default.

3. Map the cancellation SQLSTATE to a retryable gateway response instead of a generic 500.

try {
  await db.query(sql);
} catch (err) {
  if (err.code === '57014') {
    return res.status(503).json({ error: 'query_timeout', retryable: true });
  }
  if (err.code === '55P03') {
    return res.status(409).json({ error: 'lock_timeout', retryable: true });
  }
  throw err;
}

Expected output: Timeouts return 503 or 409 with a retryable flag rather than an opaque 500.

4. Prove the limit works with a deliberately slow statement.

psql -U app_rw -d appdb -c 'SELECT pg_sleep(10);'

Expected output: ERROR: canceling statement due to statement timeout after about 5 seconds

Troubleshooting

Problem

A legitimate nightly batch starts failing with statement timeout

Solution

Give batch jobs a dedicated role with a larger statement_timeout and lock_timeout instead of raising the default for the API.

Problem

The transaction is aborted after a timeout and later queries fail

Solution

Catch SQLSTATE 57014 or 55P03, roll back the transaction, and retry the whole unit of work; a timed-out transaction cannot continue.

Limitations

statement_timeout does not stop work already inside COMMIT or synchronous replication waits.
It bounds individual statements, not the total time of a request that issues many queries.

Why query timeouts matter

A connection pool has a fixed number of slots. If one query can run forever, a handful of slow statements can occupy every slot and turn a local slowdown into a full outage. PostgreSQL offers three complementary limits: statement_timeout bounds execution, lock_timeout bounds waiting for a lock, and idle_in_transaction_session_timeout closes sessions that hold snapshots while doing nothing.

Set defaults at the role level

Role defaults apply to every new session without application changes. Five seconds is a reasonable API starting point; measure your p99 first. Use SET LOCAL inside a specific transaction when a report or migration genuinely needs longer. SET LOCAL resets at commit or rollback and cannot leak to other requests that reuse the connection.

ALTER ROLE app_rw SET statement_timeout = '5s';
ALTER ROLE app_rw SET lock_timeout = '2s';
ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '30s';

Handle the errors

Use different SQLSTATE codes to distinguish cases: 57014 means the statement was canceled, 55P03 means a lock wait timed out. Return a retryable 503 for the first and a conflict-style response for the second. Always roll back and retry the entire transaction; PostgreSQL aborts the transaction on cancellation, so continuing with the same connection is not safe.

Verify and observe

Run SELECT pg_sleep(10) as the application role and confirm it fails after roughly five seconds. Export timeout counters per query name, and alert when the rate climbs: a burst usually means a missing index, lock contention, or a plan regression, not a need for a larger timeout.