PostgreSQL statement_timeout per Request: Bounding Slow Queries

Why an HTTP request timeout does not stop the query

A request timeout at the proxy or app server ends the HTTP exchange, not the database work. When the load balancer gives up after its deadline, the user gets a 504, but the PostgreSQL backend serving that request has no idea. It keeps scanning, sorting, or waiting on a lock until the statement finishes. A backend busy executing a query generally does not notice that the client has gone away until it next tries to talk to it. Meanwhile, the app server often still holds the pooled connection, along with any row or table locks the transaction has taken and the CPU and I/O the plan is consuming.

Retries make this worse. The user clicks again, or a client library retries automatically. Each attempt starts a fresh copy of the same slow query, while the abandoned copies are still running.

Illustrative estimate (assumed numbers, not a benchmark)

Busy connections ≈ request rate × attempts per request × query duration.

Assume an endpoint receives 2 requests/s, each request makes 1 retry (2 attempts total), and its query runs for 60 s. The proxy times out at 10 s, but that does not shorten the query.

2 × 2 × 60 = 240 connections stay occupied. A pool of 50 is exhausted, and unrelated endpoints start queuing behind it.

This failure mode hits backend and platform engineers running monoliths on a shared database behind a connection pool, where one slow endpoint can starve every other endpoint. The remedy is to bound the database work itself and cancel it. Shortening the HTTP timeout does not stop the query.

Copy +---------+  attempt 1   +-----------+        +-------------------+
 | Client  |------------->| Proxy/LB  |------->| App server        |
 |         |<---- 504 ----| timeout:  |        | pool: 50 conns    |
 |         |   at 10 s    | 10 s      |        |                   |
 |         |  attempt 2   |           |        | conn A: busy      |
 |         |------------->|           |------->| conn B: busy      |
 +---------+   (retry)    +-----------+        | still checked out |
                                               | after the 504     |
                                               +---------+---------+
                                                         |
                                                         v
 +--------------------------------------------------------------------+
 | PostgreSQL (shared database)                                       |
 |  backend A: query runs 60 s, unaware of the 504                    |
 |  backend B: same query again, started by the retry                 |
 |  each holds a pooled connection, locks, CPU and I/O                |
 +--------------------------------------------------------------------+
                                   |
                                   v
 +--------------------------------------------------------------------+
 | Other endpoints queue for a connection -> pool exhausted           |
 +--------------------------------------------------------------------+

The proxy returns a 504 at 10 s, but each attempt's query keeps running in PostgreSQL and holds its pooled connection, locks and CPU, so retries pile up until other endpoints are starved of connections.

Who should use per-request statement timeouts

Per-request timeouts pay off when three conditions hold together: requests have a known deadline, many endpoints share one database, and connections come from a bounded pool. In practice that means:

• Backend teams running user-facing OLTP services, such as web apps and APIs, where every request already has an HTTP or gateway deadline and a query that outlives it is pure waste.
• Platform and SRE engineers responsible for a shared PostgreSQL instance, who need one team's slow endpoint to fail on its own instead of exhausting connections for everyone else.
• Multi-tenant applications, where one tenant's unusually large data set can turn an ordinarily fast query into a slow one.
• Teams that run online schema migrations, who need lock_timeout so DDL does not stall the lock queue for live traffic.

It is less useful, or needs a different shape, in some cases:

• Pure batch or analytics workloads with no request deadline. A role-level default and job-specific limits fit them better.
• Endpoints whose latency legitimately varies by orders of magnitude, where any single budget is either too tight or meaningless.
• Low-concurrency internal tools with a dedicated database, where pool exhaustion cannot spread to other services. A global or role default is usually enough there.

If you cannot change application code, start with a role or database default. Per-request scoping needs a wrapper in the data-access layer.

How per-request timeouts work end to end

The mechanism has five steps, each covered in detail in the sections that follow:

1. The request gets an absolute deadline on arrival. Middleware records "this request must finish by time T" in the request context.
2. Each transaction gets a scoped limit. The data-access wrapper opens a transaction and runs SET LOCAL statement_timeout (and a smaller lock_timeout), computed from the time remaining until T minus a margin for writing the response.
3. PostgreSQL enforces the limit server-side. If a statement runs past its timeout, or a lock wait exceeds lock_timeout, the backend cancels the statement itself and returns SQLSTATE 57014 or 55P03. This works whether or not the client is still listening, which is what the HTTP timeout alone cannot do.
4. The application handles the error. It rolls back the aborted transaction, maps the failure to a 503/504, and retries only if budget remains. Where the application notices the deadline first, the driver sends a cancel request for the running query.
5. The setting disappears with the transaction. COMMIT or ROLLBACK restores the previous value, so the pooled connection returns clean and the next request starts from the role-level baseline.

The result is that database work is bounded by the same deadline as the HTTP response, and a slow query releases its connection, locks and CPU when the request gives up rather than when the query happens to finish.

How statement_timeout, lock_timeout and idle_in_transaction_session_timeout work

statement_timeout bounds elapsed time for a single statement, measured from when the server receives it. That includes time spent waiting on locks. lock_timeout bounds only the wait to acquire any one lock on a table, row, or other object. It exists so a statement queued behind a long-held lock fails quickly, before it consumes its whole statement budget. idle_in_transaction_session_timeout targets a different failure: a client that opened a transaction and then stopped sending commands, often because application code is blocked on an HTTP call while holding row locks. In all three, a value of zero disables the timeout.

Note that lock_timeout reports 55P03, not 57014, so match both codes when handling errors.

After either cancellation, the transaction is still open but aborted. Every later command fails with SQLSTATE 25P02 until the client issues ROLLBACK. A pooled connection returned without that rollback poisons the next request that uses it. The idle timeout instead kills the connection, so the pool must detect the dead connection and discard it.

statement_timeout applies per statement. It does not limit a request or a transaction.

Illustrative estimate (assumed numbers, not a benchmark): worst-case DB time = statements per request × statement_timeout = 10 × 2 s = 20 s. Each statement stays within its 2 s limit, yet the request runs far past an assumed 5 s HTTP deadline.

Scoping timeouts per request with SET LOCAL

The safe way to apply a request-specific limit is SET LOCAL inside an explicit transaction. A SET LOCAL value lasts only until the transaction ends. COMMIT or ROLLBACK restores the previous value, so when the connection returns to the pool, the next request inherits nothing. Outside a transaction block, SET LOCAL has no effect and PostgreSQL emits only a warning. Make sure your code actually opens a transaction first, or the timeout you think you set is never applied.

A plain session-level SET statement_timeout persists until RESET or disconnect. On a pooled connection it carries over to whichever request borrows that connection next. That request then runs with a limit it never chose, which is hard to debug. ALTER ROLE ... SET and ALTER DATABASE ... SET are a different tool. They set defaults applied when a session starts, which makes them a good place for a conservative baseline that SET LOCAL tightens per request.

Transaction-mode poolers raise the stakes. Consecutive statements from one client may run on different server backends, so session-level SET is unreliable there. A single autocommit SELECT now needs BEGIN, SET LOCAL and COMMIT around it. That adds at least one extra network round trip per transaction unless your driver pipelines those statements. It also holds a transaction open for statements that never needed one.

Copy-- Baseline: a conservative default applied when app_user starts a new session.
-- Existing sessions keep their old value until they reconnect.
ALTER ROLE app_user IN DATABASE appdb SET statement_timeout = '30s';

-- Per-request limit: SET LOCAL lasts only for this transaction.
BEGIN;
SET LOCAL statement_timeout = '2s';
SHOW statement_timeout;           -- 2s
SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;                         -- cancelled if it runs longer than 2s
COMMIT;                           -- ROLLBACK resets the value too

SHOW statement_timeout;           -- back to 30s; the next borrower inherits nothing

-- Pitfall: with no open transaction, SET LOCAL only raises a warning
-- and the limit is never applied.
SET LOCAL statement_timeout = '2s';
-- WARNING:  SET LOCAL can only be used in transaction blocks
SHOW statement_timeout;           -- still 30s

-- Anti-pattern: a session-level SET persists after this request finishes.
-- On a pooled connection the next request runs with 2s too.
SET statement_timeout = '2s';
-- ... request ends, connection goes back to the pool ...
SHOW statement_timeout;           -- 2s, leaked to the next borrower
RESET statement_timeout;          -- restores the role/database default (30s)

A role-level baseline set with ALTER ROLE ... SET, a per-request limit set with SET LOCAL that resets at COMMIT, the warning-only no-op when SET LOCAL runs outside a transaction, and a session-level SET leaking past the request that set it.

Deriving the statement budget from the request deadline

Store an absolute deadline in the request context when the request arrives, not a duration. A duration loses track of time already spent in middleware, authentication and earlier queries. Before each statement, or before each transaction if you set the timeout once per transaction, compute:

statement_timeout = deadline − now − response_margin

The margin covers serializing the result and writing the response. If the result falls below a small floor, fail the request immediately rather than issue a query that cannot finish in time. Recomputing on every call matters because a fixed per-statement value multiplies: three statements, each allowed 4 s, can hold a 5 s request for 12 s.

Illustrative estimate (assumed numbers, not a benchmark): deadline 5,000 ms, response margin 300 ms, 200 ms spent before the first query. First budget = 5,000 − 200 − 300 = 4,500 ms. If that query takes 1,800 ms and application code another 100 ms, the second budget = 5,000 − 2,100 − 300 = 2,600 ms.

Set lock_timeout well below the statement budget, for example a few hundred milliseconds. Time spent waiting for a lock counts toward statement_timeout, so without a separate limit a blocked query looks the same as a slow one. With lock_timeout set, lock waits fail with SQLSTATE 55P03 (lock_not_available), and statement timeouts and other cancellations raise 57014 (query_canceled). Your logs can then tell contention apart from bad plans.

This matters most for migrations. DDL such as ALTER TABLE needs an ACCESS EXCLUSIVE lock. While it waits behind a long-running transaction, it sits in the lock queue, and every later query on that table queues behind it, plain SELECTs included. Give DDL sessions a short lock_timeout so they give up quickly. Retry them with exponential backoff and jitter until the lock is granted.

Copyimport time
import psycopg
from psycopg import errors

RESPONSE_MARGIN_MS = 300   # reserved for serialization and writing the response
MIN_BUDGET_MS = 50         # below this, fail fast instead of querying
LOCK_TIMEOUT_MS = 300      # well below the statement budget

class DeadlineExceeded(Exception):
    pass

def budget_ms(deadline: float) -> int:
    # Recompute from the absolute deadline so earlier work is accounted for
    remaining = (deadline - time.monotonic()) * 1000 - RESPONSE_MARGIN_MS
    if remaining < MIN_BUDGET_MS:
        raise DeadlineExceeded(f"only {remaining:.0f} ms left")
    return int(remaining)

def query(conn: psycopg.Connection, deadline: float, sql: str, params=()):
    ms = budget_ms(deadline)
    lock_ms = min(LOCK_TIMEOUT_MS, ms // 2)
    with conn.transaction(), conn.cursor() as cur:
        # is_local = true: both settings revert when this transaction ends,
        # so a pooled connection does not carry them to the next request
        cur.execute(
            "SELECT set_config('statement_timeout', %s, true),"
            "       set_config('lock_timeout', %s, true)",
            (f"{ms}ms", f"{lock_ms}ms"),
        )
        cur.execute(sql, params)
        return cur.fetchall()

def handle(conn: psycopg.Connection, user_id: int):
    deadline = time.monotonic() + 5.0  # absolute deadline, set on arrival
    try:
        user = query(conn, deadline, "SELECT * FROM users WHERE id = %s", (user_id,))
        orders = query(conn, deadline, "SELECT * FROM orders WHERE user_id = %s", (user_id,))
        return user, orders
    except errors.LockNotAvailable:  # SQLSTATE 55P03: lock contention
        raise
    except errors.QueryCanceled:     # SQLSTATE 57014: slow execution / timeout
        raise

Each query recomputes statement_timeout from the request's absolute deadline minus a response margin, sets a smaller lock_timeout with SET LOCAL semantics via set_config, and catches lock waits (55P03) separately from statement timeouts (57014).

Cancelling queries and handling the timeout error in application code

Server-side timeouts cover the case where the database notices the problem first. The other half is the application noticing first: the client disconnects, or the request deadline expires while a query is still running. Context-aware drivers react by opening a separate connection and sending a cancel request that carries the backend's process ID and secret key. This has the same effect as pg_cancel_backend. The cancel is best-effort. It can arrive after the query has already finished, and the backend acts on it only at its next interrupt check. If the cancel races with COMMIT, the application cannot tell whether the transaction committed, so writes that might be replayed need an idempotency key.

Whether the server timed out or the client cancelled, the error is SQLSTATE 57014 (query_canceled). Handle it the same way in both cases:

1. Roll back explicitly. The transaction is now aborted. A connection returned to the pool in that state makes the next borrower's statements fail. Most pools only discard or reset it if you tell them to.
2. Map the status deliberately. A statement_timeout firing under your own deadline is a server-side failure, so use 503 or 504, not 500. If the client disconnected, there is no one to respond to. Record the request as client-closed so it does not inflate your error rate.
3. Retry only within the remaining budget. A query that just exhausted its statement budget will usually exhaust it again. Blind retries multiply load at the moment the database is struggling.

Cancelling a large write is not free. PostgreSQL marks the aborted transaction quickly, but every row it touched is left behind as a dead tuple that vacuum must clean up. The I/O already spent is also wasted.

Copyimport time
from psycopg import errors

HEADROOM_MS = 50  # leave time to roll back and write the response

def run_report(pool, deadline: float, account_id: int):
    """pool is a psycopg_pool.ConnectionPool; deadline is a time.monotonic() value."""
    remaining_ms = int((deadline - time.monotonic()) * 1000) - HEADROOM_MS
    if remaining_ms <= 0:
        # No budget left: do not start (or retry) the query at all.
        return 504, {"error": "deadline exceeded"}

    with pool.connection() as conn:
        try:
            with conn.cursor() as cur:
                # is_local=true scopes the timeout to this transaction only,
                # so it never leaks to the next borrower of this connection.
                cur.execute("SELECT set_config('statement_timeout', %s, true)",
                            (f"{remaining_ms}ms",))
                cur.execute("SELECT id, total FROM orders WHERE account_id = %s",
                            (account_id,))
                rows = cur.fetchall()
            conn.commit()
            return 200, rows
        except errors.QueryCanceled as e:  # SQLSTATE 57014
            # The transaction is aborted. Roll back before the connection
            # goes back to the pool so the next borrower gets a clean session.
            conn.rollback()
            # Server timeout or client-side cancel inside our own deadline:
            # report it as a gateway timeout, not a generic 500. No retry here;
            # the budget that justified a retry has just been spent.
            return 504, {"error": "query timed out", "sqlstate": e.sqlstate}
        except Exception:
            conn.rollback()
            raise

A request handler that derives a transaction-scoped statement_timeout from the remaining request budget, catches SQLSTATE 57014, rolls back before returning the connection to the pool, maps the error to 504, and does not retry once the budget is spent.

Observability and exempting legitimate long-running work

A timeout error on its own tells you little. When you catch SQLSTATE 57014, log the timeout that was actually in effect for that transaction, the elapsed time, the route or job name, and the remaining request budget. Without the effective value, you cannot tell a query that genuinely ran long from one that inherited a budget already mostly spent upstream. If budgets shrink as requests wait in queues, the elapsed-versus-budget ratio is often the first sign.

Errors only show you queries that have already failed. To see queries that are still running, sample pg_stat_activity for active backends whose now() - query_start exceeds a threshold, and include wait_event_type so lock waits are distinguishable from CPU or I/O work. To catch queries before they fail, set log_min_duration_statement well below your per-request timeout, around half of it. A query that creeps from 400 ms to 900 ms against a 1-second budget then appears in the logs weeks before it starts returning errors.

Exempting long-running work

Reports, exports, background jobs, migrations and admin sessions legitimately exceed request-scale budgets. The tempting fix is to raise the global default, but that also loosens the limit for every web request. Instead, give this work its own role and connection pool, with role-level defaults set through ALTER ROLE ... SET, and its own code path that applies a larger, explicit timeout. Keep lock_timeout tight even there. A slow export is acceptable, but an export blocking a migration's lock queue is not.

Alternatives, trade-offs and impact on engineering cost

A role-level default is a good baseline because it fails safe. Per-request SET LOCAL adds precision on top of it. The role default stays in place for code that bypasses the wrapper, so a missed path falls back to a coarse limit rather than none. To find those paths, add a test that runs each repository entry point and asserts that SHOW statement_timeout returns the per-request value.

Tight limits have a cost. A plan change or a load peak that would have made pages slower will instead produce a spike in errors. Set budgets from observed latency with headroom, alert on timeout counts separately from other errors, and avoid per-request budgets on endpoints whose latency varies by orders of magnitude.

The development work is a transaction wrapper, error mapping, and tests for each path, written once and kept up to date. In return, an incident usually shows up as a named timeout error on a specific endpoint. Without it, engineers have to reconstruct the event from an exhausted pool, a blocked lock queue, and pg_stat_activity snapshots taken after the fact.

How per-request timeouts save engineering time

The time savings come from turning diffuse, cross-service incidents into local, attributable errors:

• Shorter diagnosis. A logged 57014 or 55P03 with the route, effective timeout and elapsed time points straight at the offending query and tells you whether it was slow execution or lock contention. Without it, the visible symptom is pool exhaustion across unrelated endpoints, and someone has to work backwards to find the cause.
• Less manual intervention. The database cancels runaway statements itself, so on-call engineers spend less time finding backends in pg_stat_activity and running pg_cancel_backend or pg_terminate_backend by hand during an incident.
• Smaller blast radius. One slow endpoint fails on its own instead of degrading every service on the shared database. Fewer teams get paged, and fewer people join the incident.
• Safer migrations. A short lock_timeout on DDL makes a blocked migration fail and retry rather than stall live traffic. That removes a common reason for scheduling schema changes out of hours and watching them by hand.
• Earlier warning. Combined with log_min_duration_statement, regressions show up as slow-query log entries well before they become timeouts, so they can be fixed in normal working hours.
• No connection-state bugs. Because SET LOCAL resets at transaction end, the hard-to-reproduce class of bugs caused by session settings leaking between pooled requests does not arise.

Illustrative estimate (assumed numbers, not a measurement): assume a team has one pool-exhaustion incident a month, each taking 3 engineers 2 hours to trace from symptoms to the slow query, or 6 engineer-hours. If a named timeout error on the endpoint cuts the trace to 1 engineer for 30 minutes, the saving is about 5.5 engineer-hours a month. Against that, assume 2 to 3 engineer-days to build the wrapper, error mapping and per-path tests. On those assumed inputs the work pays for itself within a few months. Substitute your own incident frequency and team size. If pool exhaustion has never happened to you, the main benefit is insurance rather than recovered time.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql statement_timeout per request, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.