Query Timeouts

A query without a timeout can run until it is killed by a human. During an incident that is exactly what you cannot afford: the queries you need to cancel are the ones consuming the resources you need to investigate.

Set deadlines at every layer

Timeouts must agree across layers, decreasing outward-in, or the outer layer gives up while the database keeps working on an abandoned query:

client / HTTP request timeout      5s
  application query timeout        3s
    database statement timeout     2s

If the database timeout is the longest, cancelled clients leave queries running. If it is the shortest, the database cancels work the application would still have used. The database limit should be the tightest one.

-- PostgreSQL: per role, so it applies to every session that connects as it.
ALTER ROLE app_service SET statement_timeout = '2s';
ALTER ROLE app_service SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_service SET lock_timeout = '1s';
 
-- Longer-running reporting role, deliberately separate.
ALTER ROLE reporting SET statement_timeout = '5min';

-- MySQL: milliseconds, SELECT only.
SET SESSION max_execution_time = 2000;

MongoDB takes the deadline per operation via maxTimeMS, which drivers usually expose as a query option.

lock_timeout deserves separate mention: it bounds how long a statement waits to acquire a lock, without limiting how long it runs once it has one. This is what makes migrations safe — a DDL statement that cannot get its lock quickly fails instead of queueing behind a long query and blocking every subsequent request on the table.

Resource Limits

Timeouts bound duration. Resource limits bound intensity, and both are needed — a query can do enormous damage well within two seconds.

• Memory per operation. PostgreSQL's work_mem is allocated per sort or hash node, so a complex plan can use several multiples of it in one query, multiplied again by parallel workers. ClickHouse has explicit max_memory_usage per query and per user; use them.
• Rows or bytes examined. ClickHouse's max_rows_to_read and max_bytes_to_read reject a runaway analytical query before it starts, which is far better than cancelling it midway.
• Concurrency. Limiting concurrent queries per role or queue keeps a burst of expensive queries from saturating CPU. This is what a pool per workload achieves in practice.
• Result size. An unbounded SELECT * that returns ten million rows consumes memory in the database, the network and the application. Enforce a maximum result size in the data access layer.