Postgres statement_timeout per Request: Bounding Monolith Queries

Why doesn't a request timeout in a monolith stop the query in Postgres?

When a load balancer, reverse proxy or app server times out, it closes a socket and returns a 504 or drops the connection. None of those layers can tell Postgres to stop. The query runs in a separate backend process on the database server. That process only notices the client has gone when it tries to send results back. A long sort, a sequential scan or a wait on a lock sends nothing, so the work continues until it finishes.

Often the client hasn't gone either. If the proxy gives up but the app worker is still blocked in the driver call, the worker keeps its pooled connection checked out. The transaction stays open, which has three effects:

• Row locks taken by UPDATE or SELECT ... FOR UPDATE stay held, so later requests queue behind work nobody will read.
• The pooled connection is unavailable. Enough abandoned requests can empty the pool, and then healthy endpoints start timing out too.
• The transaction's snapshot holds back the xmin horizon, so vacuum cannot remove dead rows that newer transactions have produced.

You can see this in pg_stat_activity:

• state = 'active' with an old query_start is a statement still running.
• wait_event_type = 'Lock' means it is waiting on another session.
• An old xact_start shows how long the snapshot and locks have been held.

This matters most to teams running OLTP monoliths, where one connection pool and one set of hot tables are shared by every endpoint. The fix is to give the database its own deadline, set per request, so Postgres cancels the work itself.

Copy-- Find statements that outlived any HTTP timeout and are still doing work.
-- Run as a role allowed to see other sessions' queries (e.g. pg_read_all_stats).
SELECT
  pid,
  usename,
  application_name,
  state,
  wait_event_type,
  wait_event,
  now() - query_start AS query_age,  -- how long this statement has run
  now() - xact_start  AS xact_age,   -- how long locks and snapshot have been held
  backend_xmin,                      -- non-null: this session holds back vacuum
  left(query, 80)     AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND (
        (state = 'active' AND query_start < now() - interval '30 seconds')
     OR (state = 'idle in transaction' AND xact_start < now() - interval '30 seconds')
  )
ORDER BY xact_start NULLS LAST;

-- Who is each lock waiter blocked by?
SELECT
  pid,
  pg_blocking_pids(pid) AS blocked_by,
  now() - query_start   AS waiting_for,
  left(query, 80)       AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Manual relief only: cancels the current statement, the connection stays open.
-- SELECT pg_cancel_backend(12345);

A pg_stat_activity query that finds statements still running or transactions still open after the HTTP layer has timed out, shows which sessions are blocking them, and notes manual cancellation as a stopgap.

Who should use per-request statement_timeout?

Per-request timeouts pay off when many request types share one database, one connection pool and one set of hot tables. In that setup, one slow endpoint can hurt every other endpoint. The technique fits you if most of the following are true:

• You run a monolith or a small number of large services against one Postgres primary. Every endpoint draws from the same pool, so one runaway query can starve unrelated requests.
• Requests have a real deadline. A load balancer, proxy or client gives up after a known time, and work that finishes after that point is wasted.
• Endpoints have very different latency profiles. An autocomplete lookup and a checkout flow should not share one global timeout. A single value is either too loose for the fast path or too tight for the slow one.
• You use a transaction-mode pooler, or you plan to. Session-level SET is unsafe there, while transaction-scoped settings are safe.
• You have had incidents involving lock pile-ups, pool exhaustion or long-running queries that someone had to cancel by hand.

It is a weaker fit in these cases:

• Batch-only or analytics systems. No user is waiting, and long scans are normal. A role-level ceiling per workload is usually enough.
• Single-tenant tools with low concurrency. One slow query cannot starve anyone else.
• Codebases where you cannot hook transaction start. An example is an ORM that runs everything in autocommit, with no extension point. In that case, start with role or database defaults and add per-request budgets once you control where transactions begin.

Within a team, the people who own the request path and the data access layer do the work: backend engineers and whoever maintains the ORM or database helpers. The people who benefit most are on-call engineers and DBAs, because incidents become shorter and easier to attribute.

What is the difference between statement_timeout, lock_timeout and idle_in_transaction_session_timeout?

Postgres has three separate timers for this, and each one measures a different kind of waiting. Choosing the right one depends on what you want to stop.

When either of the first two timers cancels a statement inside a transaction block, the whole transaction becomes aborted. The application must issue ROLLBACK before it can reuse the connection.

The third timer works differently because it does not cancel a statement. It kills the backend process. This is the only way to release locks that are held by a handler that started a transaction and then stalled in application code, for example while calling a slow HTTP API. Your connection pool will see the closed connection on its next use and must discard it.

For interactive requests, set lock_timeout well below statement_timeout, for two reasons:

• Overlap. statement_timeout already counts lock wait. If both values are equal, lock_timeout adds nothing.
• Queueing. Lock requests are granted in the order they arrive. A statement waiting for a conflicting lock also blocks requests that queue behind it. If ALTER TABLE is waiting for an exclusive lock, every plain SELECT on that table waits behind it.

A short lock wait therefore keeps your request from extending a pile-up that other requests are stuck in. It also returns a distinct error code that you can retry. A statement that is slowly scanning rows is a different problem, and you diagnose it separately.

How does a per-request statement_timeout work?

The mechanism has three parts: a configuration value scoped to one transaction, a timer inside the backend process, and a cancellation that the backend applies to itself.

1. The value is scoped to the transaction. statement_timeout and lock_timeout are ordinary configuration parameters. The effective value comes from layers. A database or role default (ALTER DATABASE ... SET, ALTER ROLE ... SET) applies when the session starts. A session-level SET overrides it. SET LOCAL, or set_config(..., true), overrides both, but only until the current transaction ends. Postgres then restores the previous value. This is why the same pooled connection can serve a 200 ms request and then a 5 s request with no leftover state.

2. The backend arms a timer when a statement starts. When a statement begins executing, the backend reads the current statement_timeout and, if the value is non-zero, starts a timer. lock_timeout works the same way, but its timer runs only while the backend waits to acquire a lock. Because the setting is read when the statement starts, a SET LOCAL affects the statements that follow it in the same transaction, not one that is already running.

3. On expiry, the backend cancels its own statement. When the timer fires, the backend sets an interrupt flag. Executor loops, sorts, scans and lock waits check that flag regularly, so the statement stops at the next check. It does not need to wait until it tries to send data to the client. The statement fails with 57014 or 55P03, the transaction is marked aborted, and any locks it took are released when the application rolls back. The connection itself stays open and can be reused after ROLLBACK.

The part you build in the application connects these pieces to the HTTP request:

• The request records its deadline when it arrives.
• Before each query, the data access layer computes the time remaining and writes it into the transaction with SET LOCAL or set_config.
• Separately, the driver can send a protocol cancel request if the handler gives up first.
• The error handler maps the resulting SQLSTATE to an HTTP status and decides whether a retry is safe.

The following sections cover each of these steps.

How do you set Postgres statement_timeout per request with SET LOCAL?

Set the limits inside the request's own transaction so they apply only to that request. The handler opens an explicit transaction, issues SET LOCAL statement_timeout and SET LOCAL lock_timeout as its first statements, and then does its work. If you need to pass the value as a bind parameter, set_config('statement_timeout', $1, true) does the same thing. Postgres reverts both settings at COMMIT or ROLLBACK. The pooled connection goes back to the pool clean, and the next request does not inherit a 200 ms limit meant for an autocomplete endpoint.

Outside a transaction block, SET LOCAL only raises a warning and changes nothing. Autocommit statements therefore need a wrapping transaction, or they fall back to whatever role or database default exists.

A transaction-mode pooler hands server connections to different clients between transactions, so a session SET ends up applying to strangers. Use the role or database default as a ceiling that catches anything that skips the per-request path, and use SET LOCAL to tighten the limit per request.

This has costs. Each request needs an extra round trip unless you send the SET LOCAL in the same batch as the first query. ORMs that manage their own transactions or run in autocommit can also open the transaction somewhere you are not hooked in. Find the place where your framework begins its transaction and issue the settings there.

Copy-- One-time setup: role-level ceiling for anything that skips the per-request path.
-- Applies to new sessions only; existing pooled connections keep their old value.
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET lock_timeout = '5s';

-- Per request: the handler opens its own transaction and tightens the limits first.
BEGIN;
SET LOCAL statement_timeout = '200ms';  -- e.g. autocomplete endpoint
SET LOCAL lock_timeout = '100ms';       -- fail fast instead of queueing behind a lock

-- Equivalent form when the value comes from the app as a bind parameter:
--   SELECT set_config('statement_timeout', $1, true);  -- true = transaction-local

SELECT id, name
FROM products
WHERE name ILIKE 'wid%'
LIMIT 10;
COMMIT;  -- both settings revert here (and on ROLLBACK)

-- Back on the same connection: the 200ms value is gone.
SHOW statement_timeout;  -- 30s, the role default

-- Outside a transaction block, SET LOCAL only warns and changes nothing:
SET LOCAL statement_timeout = '200ms';
-- WARNING:  SET LOCAL can only be used in transaction blocks
SHOW statement_timeout;  -- still 30s

-- Avoid this on a transaction-mode pooler: it sticks to the server connection
-- and leaks to whichever client is handed that connection next.
-- SET statement_timeout = '200ms';

A role-level default acts as the ceiling, and a per-request transaction tightens statement_timeout and lock_timeout with SET LOCAL. Both values revert at COMMIT, so the pooled connection goes back clean.

How do you derive the database timeout budget from the HTTP deadline?

Once that hook exists, the value it writes should not be a constant. A fixed statement_timeout of a few seconds ignores how much of the request has already been spent. Compute the budget at each query instead:

budget = deadline − elapsed − margin, then clamp to a floor.

The margin reserves time for work that happens after the query returns: mapping rows, serializing the response, and writing it to the client. Without it, a query can finish just inside the deadline and the response still misses it.

Illustrative estimate (assumed numbers, not a benchmark)

• Assumed HTTP deadline: 10s
• Assumed time already spent (auth, an upstream call): 1.5s
• Assumed margin: 1s
• Budget: 10 − 1.5 − 1 = 7.5s, so the handler issues SET LOCAL statement_timeout = '7500ms'.
• If a later query in the same request starts at 6s elapsed: 10 − 6 − 1 = 3s, so '3000ms'.
• lock_timeout can be set to a fraction of that value, so lock waits fail early and leave time for a clear error.

The floor matters because the formula can return zero or a negative number. In Postgres, statement_timeout = 0 disables the timeout entirely, so a request that is almost out of time would get an unbounded query. Below the floor, skip the query and fail the request immediately.

The cost is plumbing. The deadline has to reach every repository method, ORM session, and raw query helper. Coverage then depends on every path reading it. A single helper that opens its own connection, or a background lookup that bypasses the hook, runs with the server default. No error is raised, so the gap stays hidden until that query is the slow one.

Copyimport time

MARGIN_S = 1.0   # assumed: reserve for row mapping, serialization, response write
FLOOR_MS = 100   # assumed floor: below this, don't start the query at all

class DeadlineExceeded(Exception):
    pass

def remaining_budget_ms(deadline: float) -> int:
    # deadline is an absolute time.monotonic() value set when the request arrived
    budget_ms = int((deadline - time.monotonic() - MARGIN_S) * 1000)
    # Never pass 0 or less: statement_timeout = 0 disables the timeout entirely
    if budget_ms < FLOOR_MS:
        raise DeadlineExceeded(f"{budget_ms}ms left, floor is {FLOOR_MS}ms")
    return budget_ms

def apply_budget(cur, deadline: float) -> None:
    # Call inside an open transaction, right before each query
    budget_ms = remaining_budget_ms(deadline)
    lock_ms = max(budget_ms // 4, 1)  # lock waits fail early, leaving time for an error
    # set_config(name, value, true) is transaction-local like SET LOCAL,
    # but accepts bind parameters
    cur.execute("SELECT set_config('statement_timeout', %s, true)", (f"{budget_ms}ms",))
    cur.execute("SELECT set_config('lock_timeout', %s, true)", (f"{lock_ms}ms",))

def fetch_orders(conn, deadline: float, customer_id: int):
    # Every repository method must take the deadline and call apply_budget;
    # a helper that skips it silently runs with the server default timeout
    with conn.transaction(), conn.cursor() as cur:  # psycopg 3
        apply_budget(cur, deadline)
        cur.execute("SELECT id, total FROM orders WHERE customer_id = %s", (customer_id,))
        return cur.fetchall()

A per-query helper that computes deadline minus elapsed minus a margin, refuses to run below a floor so the value can never become 0, and applies transaction-local statement_timeout and lock_timeout before each query.

How do you cancel in-flight queries and handle the errors safely?

Setting the budget only bounds how long Postgres works. When the handler itself gives up, because the client disconnected or an upstream deadline passed, the query should stop too. Pass a deadline or cancellation token through every layer down to the driver call, so the code that abandons the request is also the code that cancels the work.

Most drivers turn that cancellation into a protocol cancel request. They open a separate connection carrying the backend's process ID and secret key, and the server interrupts the running statement, much as pg_cancel_backend does from SQL. This is best-effort. The request can arrive after the statement has already finished, it can be lost with the network, or it can land on the next statement. Keep the server-side statement_timeout as the guarantee.

A cancelled statement aborts the entire transaction, and every later command fails until you roll back. Roll back before you release the connection. If the rollback fails or the connection state is unclear, close the connection rather than returning it to the pool.

Do not blindly retry non-idempotent writes. A timeout can fire after the statement has run, or during the commit, so the client cannot always tell whether the write happened. Cap retries with a per-request retry budget and jittered exponential backoff. Without those limits, retries multiply load on a database that is already slow, which is often why the timeouts started.

Copyimport asyncio
from psycopg import errors
from psycopg_pool import AsyncConnectionPool

pool = AsyncConnectionPool("postgresql://app@db.internal:5432/app", open=False)

async def get_order(order_id: int, remaining_s: float):
    """Idempotent read. remaining_s is what is left of the caller's deadline."""
    try:
        # On exit the pool rolls back any open transaction and discards
        # connections left in a bad state, so an aborted transaction is
        # never handed to the next request.
        async with pool.connection() as conn:
            async with conn.transaction():
                # Server-side backstop. It still fires if the client cancel
                # request is lost or arrives late.
                await conn.execute("SET LOCAL statement_timeout = '2s'")
                # Active cancellation. When the deadline passes, wait_for
                # cancels the task, and psycopg sends a protocol cancel
                # request for the running statement.
                cur = await asyncio.wait_for(
                    conn.execute(
                        "SELECT id, status FROM orders WHERE id = %s",
                        (order_id,)),
                    timeout=remaining_s)
                return 200, await cur.fetchone(), {}
    except (asyncio.TimeoutError, errors.QueryCanceled):
        # 57014: the caller's deadline is gone. Because this is a read, an
        # upstream retry is safe if it fits in the caller's retry budget.
        return 504, None, {}
    except errors.LockNotAvailable:
        # 55P03: lock_timeout fired. The caller can retry after a pause.
        # For writes, retry only when the request has an idempotency key.
        return 503, None, {"Retry-After": "1"}

An async psycopg 3 handler that sets a server-side statement_timeout, cancels the in-flight query when the caller's deadline expires, relies on the pool to roll back or discard the aborted connection, and maps query_canceled to 504 and lock_not_available to 503 with Retry-After.

Which timeout policy should interactive requests, jobs, reports and migrations use?

Per-request budgets work when one user is waiting and the work can be safely discarded. Other workloads need their own policies. Set each policy as a role-level default (ALTER ROLE ... SET statement_timeout) so each workload starts from its own default, and use SET LOCAL to tighten it further.

Migrations need the most care. ALTER TABLE requests an ACCESS EXCLUSIVE lock. If a long transaction already holds a conflicting lock, the DDL waits in the lock queue. Every later query on that table then queues behind the DDL, even plain reads. A migration can block traffic to the table without ever running.

The fix is to fail fast and try again:

• Set lock_timeout to a few seconds.
• Attempt the DDL.
• On a lock timeout error, sleep with jittered backoff and retry, up to a fixed attempt limit.
• If the limit is reached, abort and alert, so someone can find the transaction holding the lock.

Each failed attempt blocks traffic for at most the lock_timeout window. Without the timeout, the block lasts until the long transaction finishes.

Batch work inside a request does not fit a request budget. If a handler updates many rows in one transaction and is cancelled, the whole transaction rolls back. All of that work is lost and the next attempt starts from zero. Move this work to a background job that commits in chunks and records its progress, so a timeout costs one chunk instead of the whole run.

When should you avoid tight timeouts, and what are the alternatives?

Tight budgets have their own failure modes. A statement_timeout close to normal latency turns ordinary variance into errors. Examples include a cache-cold query after a restart, a plan change after ANALYZE, or a load spike that stretches every query. A query that was slow is now a failed request. A tight lock_timeout does the same for hot-row contention: updates to a shared counter or a queue head now reach users as errors instead of short waits. Neither setting fixes the underlying cause. The missing index, the N+1 loop and the long transaction are still there; the timeout only limits how long they can hurt you.

Roll out in stages. Start with a generous role-level ceiling well above observed worst-case latency. Count cancellations by SQLSTATE (57014 for query_canceled, 55P03 for lock_not_available) and by endpoint. Tighten per-request budgets one endpoint at a time, and only after its count stays near zero.

The payoff is in incidents. Without budgets, one blocker holds a lock while requests pile up behind it, and on-call has to inspect pg_stat_activity live to find it. With per-request budgets, the waiting requests fail within seconds and each error is tagged with its endpoint, so the culprit shows up in logs and the pile-up clears without anyone intervening.

How can per-request timeouts save engineering time?

The time savings come from turning open-ended, hands-on investigation into bounded failures that carry their own context. The effort moves from live firefighting to reading logs.

Incidents resolve themselves more often. Without database-side deadlines, a lock pile-up or a runaway query lasts until someone finds the blocking session and cancels or terminates it. With statement_timeout and lock_timeout in place, the stuck statements are cancelled by Postgres, their locks are released on rollback, and pooled connections come back. On-call still investigates, but usually after the system has already recovered rather than while it is down.

Failures stay local to one endpoint. When one slow endpoint can no longer hold pool connections indefinitely, it stops exhausting the pool for everyone else. An incident that would have looked like "the whole site is slow" shows up as errors on one endpoint. That narrows the search before anyone opens a dashboard.

Triage starts from a specific error. A tagged cancellation tells you which endpoint and request was affected, and whether the cause was slow execution (57014) or lock contention (55P03). That separates two investigations that otherwise look the same from the outside: tuning a query plan, and finding the transaction that holds a lock. Engineers skip the step of reconstructing what was running at the time.

Migrations need less babysitting. A migration with a short lock_timeout and a retry loop either succeeds or fails with a clear error. It does not silently block a hot table while someone watches pg_stat_activity and decides whether to kill it. That makes it practical to run schema changes during normal hours with less coordination.

Regressions surface before they become outages. A new N+1 loop or a lost index on one endpoint shows up as a rising cancellation count for that endpoint. The team that owns it can fix it in normal working time instead of during an incident.

Illustrative estimate (assumed inputs, not a measured result)

• Assumed: a team has 2 incidents per month in which a blocked or runaway query degrades the shared pool.
• Assumed: each incident takes 3 engineers 1 hour to locate the blocking session and clear it by hand: 3 engineer-hours.
• Assumed: with per-request budgets, the pile-up clears without intervention, and follow-up from tagged logs takes 1 engineer 1 hour.
• Under these assumptions, the saving is (3 − 1) × 2 = 4 engineer-hours per month, before counting the time other teams lose to site-wide slowness.

Put your own incident history into the same calculation. The one-time cost is threading the deadline through the data access layer and adding the error mapping. That cost is mostly paid once, in the code where transactions start.

How do you monitor which endpoints hit their query budgets?

Tag every statement with the request

A cancellation is only useful if you can trace it back to the request that caused it. Set application_name inside the transaction with SET LOCAL, using something like the endpoint name plus the request ID. It shows up in pg_stat_activity and, if you add %a to log_line_prefix, in every server log line. Keep it short, because Postgres truncates application_name to 63 bytes. A SQL comment carrying the same ID also works, and it survives connection poolers that reset session state. The comment only appears where the statement text is logged.

Count cancellations by cause

Log each timeout in the application with the endpoint, request ID and SQLSTATE, then count by endpoint and code. The two codes mean different things:

• 57014 (query_canceled) means the statement ran past statement_timeout, so execution was slow. A manual pg_cancel_backend produces the same code.
• 55P03 (lock_not_available) means lock_timeout fired, so the statement was blocked by another transaction.

If 55P03 errors climb on a single endpoint, look for the transaction that holds the lock. Tuning the slow query will not help.

See requests that come close to the budget

Cancellations only show requests that failed. Set log_min_duration_statement to roughly half the per-request budget so you also see statements that finish just under the limit. Enable log_lock_waits so that waits longer than deadlock_timeout are logged along with the blocking process. While an incident is in progress, filter pg_stat_activity on wait_event_type = 'Lock' and group by application_name. That shows which endpoints are stuck waiting on locks and which are actively executing.

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 postgres 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.