DB Internals AtlasPostgreSQL 18.6 Home About

Background processes

Client backend

Every client connection gets its own backend process. It waits for a query, runs it, sends the result back and waits again, until the client disconnects.

In plain words

For every connection, the postmaster starts a dedicated backend process. It belongs to that one client for the whole session: it parses, plans and runs your SQL, keeps your session settings and your private memory (sorts, hash tables, caches).

A backend is almost always in one of three states. idle: waiting for the next command. active: running a query. idle in transaction: a transaction was opened with BEGIN, but the application is not sending anything, and has not committed yet.

That last state is the troublemaker. The open transaction keeps its locks, so other sessions may queue behind it, and once it has changed data (or if it runs in REPEATABLE READ mode) it holds back the "oldest visible row" point, so vacuum cannot clean up dead rows anywhere in the database. A forgotten BEGIN in an app can make tables bloat for hours.

You can stop a backend politely in two strengths: cancel stops only the current query, terminate ends the whole session.

For DBAs and operators

The command loop

After authentication the backend sits in ReadCommand() waiting on the socket (wait_event = ClientRead, state idle). For each message it parses, analyses, rewrites, plans and executes (state active), streams rows back (ClientWrite if the client is slow to read) and returns to the loop. Before it waits again, it sends ReadyForQuery with a status byte: I idle, T in transaction, E failed transaction (idle in transaction (aborted)).

Seeing backends

SELECT pid, usename, datname, client_addr, state,
       wait_event_type, wait_event,
       now() - xact_start  AS xact_age,
       now() - state_change AS in_state_for,
       backend_xmin, left(query, 60) AS query
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
 ORDER BY xact_start NULLS LAST;

query is the last statement, even when the session is idle. In ps the title is postgres: user database host(port) state, for example postgres: app shop 10.0.0.5(51234) idle in transaction, or the command tag (SELECT, UPDATE) while active. Blockers are found with pg_blocking_pids(pid).

Why idle in transaction hurts

  • Locks: row and table locks taken by the transaction are held until it ends. A later ALTER TABLE then queues behind it, and every new query on that table queues behind the ALTER.
  • xmin horizon: backend_xid (once the transaction has written something) and backend_xmin (in REPEATABLE READ, or with an open cursor/snapshot) stop vacuum from removing rows deleted after that point. Look for tuples: … are dead but not yet removable in autovacuum logs.
  • Connections: the slot counts against max_connections.

Set idle_in_transaction_session_timeout (per role or database) as a safety net:

FATAL: terminating connection due to idle-in-transaction timeout

Cancel or terminate

pg_cancel_backend(pid)pg_terminate_backend(pid [, timeout])
SignalSIGINTSIGTERM
EffectCurrent statement fails; session stays; an open transaction becomes abortedSession ends; transaction rolled back; locks released
Client seesERROR: canceling statement due to user requestFATAL: terminating connection due to administrator command

Both are checked at the next interrupt check, so a backend stuck in an uninterruptible system call (for example a hung NFS read) may not react. Never use kill -9: the postmaster then restarts the whole server (see postmaster). Members of pg_signal_backend can signal non-superuser backends; the role pg_signal_autovacuum_worker (new in PostgreSQL 18) allows signalling autovacuum workers.

Typical tickets

  • Hundreds of idle connections: each costs memory and makes snapshots slower; use a pooler.
  • wait_event_type = Lock: follow pg_blocking_pids() to the head of the queue, very often an idle in transaction session.
  • wait_event = ClientWrite: the client is not reading its results fast enough (network or app problem).

For developers: inside the source

Where it lives

  • backend_startup.c: BackendMain() → BackendInitialize() (startup packet, SSL/GSS negotiation) → PostgresMain().
  • postgres.c: the command loop, signal handlers and ProcessInterrupts().
  • postinit.c: InitPostgres() attaches to the database; PerformAuthentication() calls ClientAuthentication() in auth.c.
  • pquery.c: portals (PortalRun()).
  • backend_status.c: the PgBackendStatus entry behind pg_stat_activity.

The command loop

PostgresMain() loops forever. Before blocking, it checks the transaction state and reports it: pgstat_report_activity(STATE_IDLEINTRANSACTION, …) and arms the idle_in_transaction_session_timeout timer, or STATE_IDLE plus idle_session_timeout; it also calls pgstat_report_stat(false) to flush pending statistics, then ReadyForQuery(). ReadCommand() blocks on the socket, and the first byte selects the branch:

  • PqMsg_Query → exec_simple_query(): pg_parse_query() → pg_analyze_and_rewrite_fixedparams() → pg_plan_queries() → CreatePortal() → PortalRun().
  • PqMsg_Parse / PqMsg_Bind / PqMsg_Execute → exec_parse_message(), exec_bind_message(), exec_execute_message() (extended protocol, prepared statements).
  • PqMsg_Terminate → clean exit.

pg_stat_activity.state comes from st_state in PgBackendStatus; the query text is st_activity_raw, truncated to track_activity_query_size. Writers bump a change counter around updates so readers never need a lock.

Cancel, terminate and interrupts

pg_cancel_backend() and pg_terminate_backend() (signalfuncs.c) go through pg_signal_backend() and send SIGINT or SIGTERM. The handlers StatementCancelHandler() and die() only set QueryCancelPending / ProcDiePending and InterruptPending; the real work happens in ProcessInterrupts() the next time code reaches CHECK_FOR_INTERRUPTS() (miscadmin.h). That is why a loop in C code without that macro cannot be cancelled. client_connection_check_interval is also handled there, via pq_check_connection().

Hooks along the path

ClientAuthentication_hook, post_parse_analyze_hook, planner_hook, ExecutorStart_hook (and Run/Finish/End) and ProcessUtility_hook let an extension observe or change each stage; pg_stat_statements.c and auto_explain.c are good examples.

Experimenting

  • timeouts.spec covers statement and lock timeouts; src/test/modules/libpq_pipeline exercises the extended protocol.
  • log_statement_stats, debug_print_parse / debug_print_plan and log_min_messages = debug5 show what the loop does per message.

Parameters that shape this

max_connections idle_in_transaction_session_timeout idle_session_timeout statement_timeout transaction_timeout lock_timeout track_activities track_activity_query_size client_connection_check_interval work_mem

Related topics

Postmaster Life of a query Locking and deadlocks Snapshots and visibility VACUUM Wire protocol Local (per-backend) memory Client authentication