⏱️Query timeouts

Bound worst-case work

Timeouts protect user experience and shared pools. Set them at the driver (statement.setQueryTimeout), the pool or proxy, or with SET statement_timeout in PostgreSQL. Align client-side, driver-side, and server-side limits so you do not get half-applied stories: cancellation interrupts execution, but transactional effects depend on what completed before abort.

Set statement timeouts per workload class, OLTP short, batch longer, with explicit overrides in jobs. Long-running sorts may not stop instantly; pooling clients may need to discard bad connections after timeout.

SQL

PostgreSQL session timeout

SET statement_timeout = '2s';

SELECT pg_sleep(5);

✓Illustrative result set

result
ERROR: canceling statement due to statement timeout

SQL

Identify long running queries

SELECT pid, now() - query_start AS running_for, state, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30 seconds';

✓Illustrative result set

pid	running_for	state
4821	00:01:12	active

Timeout traps

Only client timeouts while the server keeps burning CPU.
Retrying non-idempotent writes blindly after timeout.
Interactive default infinite in BI tools against OLTP primaries.

Hardening patterns

Align JDBC, socket, and statement timeouts coherently.
Log timeout fingerprints (query hash) for triage.
Make batch jobs resumable with checkpoints.
Route heavy BI to replicas with higher limits.

📡Layers

Every hop can enforce or ignore a limit.

Document which layer owns the timeout for each API: app, pooler, gateway, and database should not fight each other with contradictory defaults.

Pooling

After a statement timeout, some drivers leave the connection in an ambiguous state, know whether your pool validates or evicts connections on errors.

TL;DR

Statement timeouts abort long-running queries to protect shared resources (CPU, locks, pool slots). They surface as client errors, not graceful partial results, design idempotent retries for safe reads, not blind retries for writes. Set timeouts at the client, pooler, and server where each layer makes sense; a missing timeout is often the root cause of cascading outages under slow queries.