What each Postgres timeout limits, the error codes they raise, where to set them so migrations and reports are not caught by limits meant for API queries, and how lock_timeout keeps a schema change from stalling every request.

Postgres enforces no query or session timeouts by default, so a query can run for hours and a session can hold locks inside an open transaction until someone notices. A forgotten BEGIN in a debugging session can take an API down without a single error in its logs.
Five settings fix this, and each one limits something different.
All of them are described in the client connection defaults reference, and all accept values like '5s' or '2min', with 0 meaning disabled. The last three end the whole session, so connection pools must handle a closed connection gracefully, and idle_session_timeout in particular tends to fight with application pools that keep idle connections open on purpose.
A timeout applies at the narrowest scope that sets it, and the scopes from widest to narrowest are server, database, role, session and transaction:
-- Per database: every new session in this database
ALTER DATABASE app SET statement_timeout = '5s';
ALTER DATABASE app SET idle_in_transaction_session_timeout = '60s';
-- Per role: a reporting role gets more time
ALTER ROLE reporting SET statement_timeout = '5min';
-- Per transaction: one known-slow query
BEGIN;
SET LOCAL statement_timeout = '30s';
SELECT ...;
COMMIT;
SET LOCAL lasts until the end of the transaction and is the right tool for exceptions. Plain SET lasts for the session, which behind a pool means it leaks to whichever request gets that connection next, so avoid it in application code.
The most valuable timeout for avoiding outages is lock_timeout during schema changes. An ALTER TABLE needs an ACCESS EXCLUSIVE lock, and if a long query is reading the table, the ALTER waits. While it waits, every new query on that table queues behind it, because lock requests are granted in order, as the explicit locking docs describe. A two second migration turns into minutes of failed requests.
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN gift_message text;
With lock_timeout, the migration fails after three seconds instead of blocking traffic, and you retry it later. That is a far better failure than an outage.
A timeout aborts the transaction, so the application must roll back and decide whether to retry. Lock timeouts are usually worth retrying once with a short delay; statement timeouts usually are not, since the same query will take the same time. The error codes appendix lists both, and returning a specific error to the client beats a generic 500 for anyone debugging later.
Best fit: statement_timeout and idle_in_transaction_session_timeout per database, SET LOCAL for exceptions, and lock_timeout on every migration that takes a strong lock.
Encore is a backend framework with built-in infrastructure, and the per-database timeouts fit into the service's migrations, which run as the role that owns the database, both on the local Postgres that encore run starts and on deploy. The handler then maps the two timeout codes to clear API errors:
Run in your terminal to get started locally.
-- orders/migrations/2_timeouts.up.sql
DO $$
BEGIN
EXECUTE format('ALTER DATABASE %I SET statement_timeout = %L', current_database(), '5s');
EXECUTE format('ALTER DATABASE %I SET idle_in_transaction_session_timeout = %L', current_database(), '60s');
END $$;
package orders
import (
"context"
"errors"
"encore.dev/beta/errs"
"encore.dev/storage/sqldb"
)
var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"})
type CancelParams struct {
Reason string `json:"reason"`
}
//encore:api auth method=POST path=/orders/:id/cancel
func Cancel(ctx context.Context, id int64, p *CancelParams) error {
tx, err := db.Begin(ctx)
if err != nil {
return err
}
defer tx.Rollback()
// Fail fast if a long-running job holds the row instead of queueing the request.
if _, err := tx.Exec(ctx, `SET LOCAL lock_timeout = '2s'`); err != nil {
return err
}
if _, err := tx.Exec(ctx,
`UPDATE orders SET status = 'cancelled', cancel_reason = $2 WHERE id = $1 AND status = 'open'`,
id, p.Reason); err != nil {
return mapTimeout(err)
}
return tx.Commit()
}
func mapTimeout(err error) error {
var pgErr *sqldb.Error
if errors.As(err, &pgErr) {
switch pgErr.DatabaseCode {
case "55P03": // lock_not_available
return &errs.Error{Code: errs.Unavailable, Message: "order is being updated, try again"}
case "57014": // query_canceled, statement_timeout
return &errs.Error{Code: errs.DeadlineExceeded, Message: "query took too long"}
}
}
return err
}
A test can hold a row lock in one transaction and call Cancel in another to check that it returns Unavailable after two seconds rather than hanging, all against the local database. The databases docs cover transactions and the error type.
An AI coding agent that adds an endpoint touching a hot table can include SET LOCAL lock_timeout and the error mapping in the same change, so the failure mode is decided in code review rather than discovered in an incident.
Best fit: services that want timeouts set with the schema and timeout errors returned as clear API errors.
Set statement_timeout and idle_in_transaction_session_timeout per database in a migration, give reporting roles their own limits, and put SET lock_timeout at the top of every migration that alters a busy table. Map the two error codes in your data access layer so callers get a clear, retryable error.
statement_timeout cancels any statement that runs longer than the limit, whether it is computing or waiting. lock_timeout only limits how long a statement waits to acquire a lock, so it fails fast when a table is locked without cutting off legitimately long queries.
The statement fails with SQLSTATE 57014, query_canceled, and the message canceling statement due to statement timeout. A lock_timeout raises 55P03, lock_not_available, and the transaction must be rolled back in both cases.
No. A server-wide value also applies to migrations, backups and maintenance. Set it per database or per application role with ALTER DATABASE or ALTER ROLE, and raise it with SET LOCAL inside a transaction for the few queries that need longer.
Use errors.As to get a *sqldb.Error and check its DatabaseCode for 57014 or 55P03, then return an errs.Error with a suitable code such as errs.DeadlineExceeded or errs.Unavailable so the client sees a clear failure instead of a generic internal error.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.