Connection Scaling, Poolers, max_connections, Session State, and Transaction Pooling

Learning outcomes

ServiceHub grows from 40 application clients to 2,000 short-lived clients. The first reaction is to set max_connections=2000. PostgreSQL can be configured for many connections, but each client connection maps to a backend process and max_connections also sizes shared resources. High connection counts can therefore increase memory, process scheduling, lock-table/shared-memory pressure and startup/teardown churn even when most sessions are idle.

1. One client connection maps to one backend process

SELECT name, setting, context, source, pending_restartFROM pg_settingsWHERE name IN (  'max_connections',  'reserved_connections',  'superuser_reserved_connections',  'max_worker_processes',  'autovacuum_worker_slots',  'max_wal_senders')ORDER BY name;SELECT backend_type, count(*)FROM pg_stat_activityGROUP BY backend_typeORDER BY backend_type;

PostgreSQL uses a supervisor/postmaster plus one backend process per client session. Parallel workers, autovacuum, WAL senders and other background processes are additional processes and are not interchangeable with ordinary connection slots.

2. max_connections has shared-resource consequences

max_connections is a startup setting. PostgreSQL sizes some shared resources directly from it, so raising it can increase shared-memory/semaphore requirements even if the extra slots are usually empty. A standby must have a compatible connection capacity relative to its primary for hot-standby operation.

SHOW max_connections;SHOW shared_memory_size;SHOW num_os_semaphores;SELECT count(*) FILTER (WHERE backend_type='client backend') AS client_backends,       count(*) FILTER (         WHERE backend_type='client backend'           AND state='active'       ) AS active_client_backendsFROM pg_stat_activity;

Capacity planning should distinguish client concurrency from simultaneously useful database execution concurrency. A pooler can accept many client sockets while limiting server backends to a smaller measured database concurrency.

3. There is no universal “10 MB per connection” constant

Every backend process has baseline process/address-space overhead plus dynamically allocated memory contexts, temporary buffers if used, catalog caches, query executor memory and extension/runtime state. The operating system also accounts shared pages differently from private resident memory.

SELECT name, ident, parent,       total_bytes,       total_nblocks,       free_bytes,       used_bytesFROM pg_backend_memory_contextsORDER BY total_bytes DESCLIMIT 25;

This view describes the current backend's PostgreSQL memory contexts. It is not the backend's full OS Resident Set Size (RSS), and it is not a safe fixed multiplier for all sessions.

# Linux examples:ps -o pid,ppid,rss,vsz,comm -C postgres# or:top -p "$(pgrep -d, postgres)" 

Get-Process postgres -ErrorAction SilentlyContinue |  Select-Object Id,WorkingSet64,PrivateMemorySize64,CPU

4. Reserved connection slots preserve an emergency path

SHOW max_connections;SHOW reserved_connections;SHOW superuser_reserved_connections;SELECT rolname,       pg_has_role(rolname,'pg_use_reserved_connections','USAGE')         AS can_use_reserved_slotsFROM pg_rolesWHERE rolcanloginORDER BY rolname;

When free slots fall into the reserved region, roles with pg_use_reserved_connections plus superusers retain access; the final superuser_reserved_connections slots remain superuser-only. Do not give every application the reserved-slot role or the emergency reserve ceases to be a reserve.

5. Wrong scaling strategy: raise max_connections until clients stop failing

SELECT state,       count(*) AS sessions,       percentile_cont(0.5) WITHIN GROUP (         ORDER BY EXTRACT(EPOCH FROM (clock_timestamp() - state_change))       ) AS median_state_age_secondsFROM pg_stat_activityWHERE backend_type = 'client backend'GROUP BY stateORDER BY sessions DESC;

6. A pooler multiplexes client connections onto server connections

PgBouncer is a popular open-source third-party pooler, not PostgreSQL core. The mandatory lab remains core-only; PgBouncer behavior below is based on its current official feature map and must be rechecked against the deployed pooler version.

7. Inventory application session state before transaction pooling

SET application_name = 'ch22_session_state_demo';SET search_path = pg_catalog, app;CREATE TEMP TABLE ch22_temp_state (  key text PRIMARY KEY,  value text) ON COMMIT PRESERVE ROWS;INSERT INTO ch22_temp_state VALUES ('cart','open');PREPARE ch22_prepared(integer) ASSELECT count(*) FROM app.ch22_perf WHERE tenant_id = $1;LISTEN ch22_notifications;SELECT pg_advisory_lock(220022);SELECT current_setting('search_path'),       (SELECT count(*) FROM ch22_temp_state) AS temp_rows;EXECUTE ch22_prepared(5);

Every object/state above belongs to one backend session. A transaction pool can assign a different PostgreSQL backend after commit, so software that expects those session-local effects to persist must be redesigned or use session pooling/direct connections.

8. Current PgBouncer transaction-pooling feature boundaries

This distinction matters because older blanket prepared-statement guidance for transaction pooling is now too broad. PgBouncer added protocol-level prepared-statement tracking in modern releases; SQL-level PREPARE remains a different session-state feature.

9. Clean the direct-session state correctly

SELECT pg_advisory_unlock(220022);UNLISTEN ch22_notifications;DEALLOCATE ch22_prepared;DROP TABLE IF EXISTS ch22_temp_state;RESET search_path;RESET application_name;

In production, a session pooler typically runs a reset discipline before handing a backend to another client. Transaction pooling instead requires applications not to depend on unsupported session state in the first place.

10. Optional PgBouncer capacity model

[databases]servicehub_lab = host=127.0.0.1 port=55432 dbname=servicehub_lab[pgbouncer]pool_mode = transactionmax_client_conn = 1000default_pool_size = 40reserve_pool_size = 5max_prepared_statements = 200

The numbers are examples, not tuning recommendations. Size server pools from observed active database concurrency and latency, then monitor client wait time and server saturation. A pooler can protect PostgreSQL from connection storms; it cannot create CPU, storage bandwidth or lock-free transactions.

11. Final Chapter 22 cleanup

DROP FUNCTION IF EXISTS app.ch22_bucket(integer);DROP TABLE IF EXISTS app.ch22_perf CASCADE;

Authoritative references

Performance settings are hardware-, concurrency-, plan-, operating-system-, and version-sensitive. These primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Connections and Authentication / max_connections
• PostgreSQL 18 — How Connections Are Established
• PostgreSQL 18 — Kernel Resources
• PostgreSQL 18 — pg_backend_memory_contexts
• PgBouncer — Pooling Feature Map
• PgBouncer — Configuration / max_prepared_statements