Why Bound a Query
A single pathological query, perhaps a missing index or a bad plan, can run for minutes while holding locks and a connection. Without a bound it can starve the pool and pile up waiters. A statement timeout caps execution time so a runaway query is cancelled instead of dragging the whole system down.
Layers of Bounding
- A statement timeout limits any single statement's run time.
- An idle in transaction timeout kills transactions that hold locks while doing nothing, a common source of bloat and blocking.
- A client side cancellation can abort a query when the caller gives up, freeing server resources promptly.
Cancellation Is Cooperative
Cancelling a query asks the backend to stop at the next safe check point; it is not always instant. The transaction then rolls back, so partial work is undone. Design callers to treat a timeout as a normal, retriable outcome rather than a crash.
Aligning the Stack
Set the database timeout below the application and load balancer timeouts. Otherwise the client abandons the request while the database keeps working, wasting resources on a result nobody will read.
Key idea
Statement and idle timeouts cap how long a query or transaction may run, protecting shared resources, and timeouts should align across the stack so no layer waits on abandoned work.