Set a statement timeout so one query cannot take the site down

The claim

A single slow database query, left to run without a time limit, can bring down an entire application — not because the query itself is important, but because while it holds a connection and locks, every other request piles up behind it until the connection pool is exhausted and the whole system stops responding. A statement timeout, set at the database, is a one-line configuration that converts this cascading failure into a single failed query, and its absence is one of the most common reasons a small performance problem becomes a full outage.

How one query takes everything down

The mechanism is a pile-up. A query that should take 10 milliseconds instead takes 90 seconds — a missing index after a data-size change, an accidental cross join, a lock it is waiting on. While it runs, it holds a database connection from your limited pool. More requests arrive, each needing a connection; they wait. The slow query is still running, so the connections it and others are holding are not returned. Within seconds the pool is fully checked out, and now every request — including the fast, healthy ones that have nothing to do with the slow query — cannot get a connection and fails. One bad query has become total unavailability, and the site is down not because it is overloaded but because it is waiting.

The one-line defence

A statement timeout tells the database to cancel any query that runs longer than a set limit, freeing its connection and its locks. Set it globally as a backstop:

-- database-wide default: no single query may run longer than 15s
ALTER DATABASE app SET statement_timeout = '15s';

With this in place, the 90-second query is cancelled at 15 seconds. The request that issued it gets an error — which is bad, but bounded — and its connection returns to the pool immediately. The pile-up never forms, because no query can hold a connection long enough to start one. A single query fails; the site stays up. That is the entire trade, and it is almost always the right one, because one failed request is incomparably better than every request failing.

Set different limits for different work

A single global timeout is a blunt backstop, and the better pattern is to match the limit to the kind of work. Interactive web requests should have a tight timeout, because a user is waiting and a query that takes even a few seconds has already failed from their perspective. Background jobs and reports legitimately take longer and should be allowed to, on their own connections with their own, more generous limits:

-- tight limit for the interactive web role
ALTER ROLE web_app SET statement_timeout = '5s';

-- generous limit for the background/reporting role
ALTER ROLE batch_worker SET statement_timeout = '5min';

This is the important refinement: the timeout that protects your interactive traffic should be measured in single-digit seconds, because anything slower is already a failed user experience, while the reporting query that genuinely needs two minutes runs as a different role on a different connection and cannot, by construction, exhaust the pool that serves your users. Separating these two workloads is what lets you set an aggressive timeout where it matters without breaking the legitimate slow work.

Add a timeout on the acquisition, too

The statement timeout bounds how long a query runs; a separate limit should bound how long a request waits to get a connection in the first place. If the pool is momentarily busy, a request should wait briefly and then fail fast rather than hanging indefinitely, because a request hanging on connection acquisition is just as stuck as one hanging on a slow query, and fails the user just as completely:

# application pool config
acquire_timeout: 3s     # fail fast if no connection is free

Together, the statement timeout and the acquisition timeout put an upper bound on how long anything can take: a request either gets a connection quickly and runs a query that is guaranteed to finish or be cancelled within the limit, or it fails quickly and cleanly. Nothing hangs, and because nothing hangs, nothing piles up.

Make the timeout visible when it fires

A cancelled query is a signal, not just an error to swallow. When a statement times out, log it with the query and the duration, because a timeout firing means something is genuinely wrong — a missing index, a lock contention problem, a query that needs optimising — and the timeout has bought you the chance to fix it calmly instead of during an outage. Treat a rising rate of timeouts as an early warning that a query is degrading, and investigate it before it degrades to the point where even the timeout's protection is a steady stream of user-facing errors.

What to set today

Add a global statement_timeout as a backstop if you have none — this alone removes the worst version of the cascade. Then separate your interactive and background database work into different roles and give the interactive role a timeout measured in seconds. Add an acquisition timeout in your connection pool. These three changes together mean that no single slow query, and no momentary pool pressure, can turn into the whole site hanging — the failure stays contained to the one request that caused it, which is exactly where a failure should stay.