Lorixen.
EN

statement_timeout: stop runaway queries taking down your app

Short answer: PostgreSQL will happily run a query for hours, or let a forgotten transaction hold locks all day, unless you tell it not to. Set statement_timeout so runaway queries are cancelled, lock_timeout so statements don't queue forever behind a lock, and idle_in_transaction_session_timeout so abandoned transactions are closed. Set them per database role or per connection, not globally, with a shorter limit for the web app than for reports and migrations, and log what gets cancelled.

How one query freezes an app

Most "the whole app froze" incidents on a small PostgreSQL-backed app follow a few patterns:

  • A runaway query. Someone runs a report without a date filter, or a new feature ships with a missing index, and one query scans millions of rows. Database CPU and disk are saturated and every other request slows down.
  • A lock queue. A long transaction holds a lock on a table. A migration then waits for a stronger lock, and every normal query that arrives after it queues behind the migration. Within seconds, the app's connection pool is full of waiting requests.
  • An abandoned transaction. Code opens a transaction, then hits an error or waits on an external API, and never commits. The session sits "idle in transaction", holding locks and blocking vacuum from cleaning up.

PostgreSQL has a setting aimed at each of these.

The settings

PostgreSQL's client connection defaults documentation describes them:

  • statement_timeout aborts any statement that takes longer than the specified time. The documentation notes that setting it in postgresql.conf isn't recommended because it would affect all sessions.
  • lock_timeout aborts any statement that waits longer than the specified time while trying to acquire a lock on a table, index, row or other object. The documentation points out that if statement_timeout is set, a lock_timeout equal to or longer than it is pointless, because the statement timeout would always fire first.
  • idle_in_transaction_session_timeout terminates any session that has been idle within an open transaction for longer than the specified time.
  • transaction_timeout, available in recent versions, terminates any session that spends longer than the specified time in a transaction, whether explicit or implicit. The documentation also advises against setting it globally in postgresql.conf.

All default to zero, meaning no limit, which is why many apps have none.

Set them where they belong

Because a sensible limit for a web request is far too short for a monthly report or a data migration, apply timeouts to the right sessions rather than to the whole server:

Who connects Example approach
Web app role Short statement timeout, short lock timeout, idle-in-transaction timeout
Background jobs role Longer statement timeout
Reporting or BI role Longer statement timeout, ideally on a read replica
Migrations Short lock timeout, statement timeout set per migration

You can set defaults per role:

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

These numbers are illustrative. Choose them from your own slowest legitimate queries, measured from logs or monitoring, plus a margin. Settings can also be applied per session with SET, or per transaction with SET LOCAL, when one operation needs a different limit.

Handle the cancellation in the app

When a timeout fires, PostgreSQL returns an error and the statement, and usually the transaction, is rolled back. Your app should:

  • Catch the specific error and show a helpful message, such as "This report is taking too long; try a shorter date range", rather than a generic failure.
  • Log the query and its duration so the slow path can be fixed.
  • Not automatically retry heavy queries in a loop, which just repeats the load.

For background jobs, a timeout is a signal to break the work into smaller batches.

Protect migrations with lock_timeout

Schema changes are a common trigger for lock queues. A short lock_timeout on a migration means that if the table is busy, the migration fails quickly instead of waiting and blocking everything behind it. You can then retry at a quieter moment.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN gift_message text;

A failed migration you can rerun is far better than a migration that silently freezes the app for minutes.

Find the queries that need fixing

Timeouts are a safety net, not a fix. Use the errors they produce to find work worth improving:

  • Log slow statements with log_min_duration_statement, set to a value that catches problems without flooding logs.
  • Use an extension such as pg_stat_statements, where available, to see which queries consume the most time overall.
  • Check pg_stat_activity during incidents to see long-running and idle-in-transaction sessions.

Then add indexes, limit result sets, paginate, or move heavy work to a replica or a background job.

Managed databases

Hosted PostgreSQL services generally let you set these parameters per role or database with the same SQL, and some expose them in their dashboards. A few restrict which settings ordinary roles can change. Check your provider's documentation before relying on a particular setting.

A worked example

Take a hypothetical Portland coffee-roaster whose online shop froze for several minutes one afternoon. A staff member had run an export from the admin panel with no date filter, and at the same time a deploy ran a migration that waited for a lock behind it. Checkout requests queued behind both. The developer set a five-second statement timeout and short lock timeout for the web role, a longer limit for a separate reporting role, a 30-second idle-in-transaction timeout, and a three-second lock timeout in migrations. The export now runs as a background job with a date range, and the next busy-table migration failed fast and succeeded on retry.

FAQ

Won't timeouts break legitimate long queries?

Only if the limit is too short for that role. Give long jobs their own role or set a longer limit for that transaction.

Is statement_timeout per statement or per request?

Per statement. A request running many statements could take longer overall; transaction_timeout, where available, limits the whole transaction.

Do timeouts cancel queries cleanly?

Yes. The statement is cancelled and its changes rolled back, so data stays consistent.

Should my connection pooler set these?

Some poolers can, but role-level settings in PostgreSQL apply regardless of how connections arrive.

If one slow query or migration has frozen your app before, ask us to set sensible database limits.

Sources

WhatsAppBook a call