Lock Contention → Connection-Pool Exhaustion

 

A playbook for a common production incident: a long-running batch job holds row locks, small OLTP transactions block waiting on those locks, and because each blocked transaction keeps holding its pool connection while it waits, the application connection pool drains and new requests fail with connection-timeout errors.

Key insight: pool exhaustion here is a symptom. The disease is lock contention + a long-held transaction. Enlarging the pool only lets more threads pile onto the same locks — it delays the wall, it doesn't remove it. Fix the lock-holding time first; treat pool settings as a safety net.

What happens, step by step

1. A batch job opens a session and takes row locks (UPDATE, DELETE, SELECT ... FOR UPDATE) inside a long transaction, and holds them (large batch, or autocommit off with no intermediate commits).
2. Small OLTP transactions that want the same rows block, waiting on the batch's locks.
3. Each blocked transaction is holding a pool connection while it waits — the connection is parked on a lock wait, doing no work.
4. Blocked requests pile up → maximumPoolSize is reached → new requests can't get a connection → SQLTransientConnectionException ... request timed out (or equivalent).

Batch txn (holds locks, long)                 ████████████████████████ (minutes)
  OLTP #1 wants same row  → blocked, holds conn   ░░░░░░░░░░░░░░░░ (waiting)
  OLTP #2 wants same row  → blocked, holds conn    ░░░░░░░░░░░░░░░ (waiting)
  OLTP #3 ... → pool exhausted → new requests fail to even get a connection ✗

Fixes — from root cause outward

Apply these roughly in order. The earliest items remove the cause; the later items contain the blast radius and fail fast.

1. Shorten how long the batch holds locks (most important)

• Chunk the batch into small transactions. Instead of one transaction locking a million rows, commit every N rows (e.g. 1k–10k). Each chunk acquires and releases locks quickly, so OLTP waits milliseconds, not minutes.

• Keyset-paginate the batch so each chunk is bounded:

  -- process in bounded chunks, committing between each
  UPDATE orders SET status = 'archived'
  WHERE id > :last_id
  ORDER BY id
  LIMIT 5000
  RETURNING id;   -- remember max(id) as :last_id for the next chunk, then COMMIT
  

• Keep the critical section tight — acquire lock, write, commit. Never hold a transaction open across app logic, network calls, or user think-time.

2. Make the batch yield instead of blocking OLTP

• Let the batch skip rows that OLTP is currently using, coming back to them later rather than queuing ahead of users:

  SELECT id FROM work_queue
  WHERE status = 'pending'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 5000;
  

• Give the batch a short lock_timeout so it backs off and retries instead of camping on a contended lock:

  SET lock_timeout = '2s';   -- batch session: fail fast, retry the chunk later
  

• Use a consistent lock acquisition order across batch and OLTP to avoid deadlocks.

3. Stop blocked waiters from hogging pool connections

Cap how long any transaction waits so a blocked connection returns to the pool quickly instead of parking. Set these on the OLTP side (session, or in the pooler/proxy init query):

SET lock_timeout = '3s';                          -- fail the statement if it can't lock in time
SET idle_in_transaction_session_timeout = '10s';  -- kill forgotten open transactions
SET statement_timeout = '30s';                    -- backstop for runaway statements

A fail-fast request frees its connection, so the pool doesn't drain. The user gets a retriable error instead of a 30-second hang that exhausts the pool.

4. Isolate the batch from OLTP (most effective structural fix)

A batch and interactive traffic should almost never share one pool.

• Run the batch through a separate connection pool / separate DB user / separate RDS Proxy endpoint (or a direct connection), with its own small cap (e.g. 2–4 connections). Then even if the batch misbehaves, it cannot consume the OLTP pool's connections — the blast radius is contained.
• On RDS/Aurora, point a read-mostly batch at a reader endpoint to keep it off the OLTP writer path entirely.

5. Reduce the contention itself (schema / design)

• Prefer a work-queue with SKIP LOCKED so workers never fight over the same rows.
• Consider optimistic concurrency (version column + retry) instead of pessimistic FOR UPDATE for OLTP paths.
• Partition data so the batch and OLTP touch different partitions / key ranges.

6. Pool settings — safety net, not the cure

• Set a sane connectionTimeout (e.g. 2–5 s, not 30 s) so threads fail fast when the pool is drained, surfacing the problem instead of hanging the whole app.
• Enable leak detection / maxLifetime so a stuck connection is eventually reclaimed (HikariCP leakDetectionThreshold).
• Do NOT reflexively raise maximumPoolSize. A bigger pool behind the same lock just means more backend connections all stuck on the same wait, pushing the exhaustion down to the database's max_connections.

Interaction with a server-side pooler (RDS Proxy / PgBouncer)

If this traffic runs behind RDS Proxy, note that a long row-locking transaction — and especially SELECT ... FOR UPDATE or session state held across statements — can pin the proxy connection for the whole batch, so the pinned backend is unavailable to other clients for the duration. That makes the batch-isolation fix (#4) doubly important: give the batch its own path so its pinning can't starve the shared OLTP multiplexing. For how pinning works, see pgbouncer-vs-rds-proxy.

With PgBouncer transaction mode, a long transaction holds its server connection for the whole transaction, so chunking (#1) directly improves pool reuse there too.

Diagnosis cheat-sheet

Find the blocking chain and the culprit while it's happening:

-- Who is blocking whom?
SELECT blocked.pid        AS blocked_pid,
       blocked.query      AS blocked_query,
       blocking.pid       AS blocking_pid,
       blocking.query     AS blocking_query,
       blocking.state     AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Long-running / idle-in-transaction sessions (the usual batch culprit)
SELECT pid, state, now() - xact_start AS xact_age, now() - query_start AS query_age, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start NULLS LAST;

Pool-side signals:

• HikariCP: hikaricp_connections_pending > 0 under normal load, and SQLTransientConnectionException ... request timed out.
• RDS Proxy: DatabaseConnectionsCurrentlySessionPinned rising (pinning), plus DatabaseConnectionsBorrowLatency / connection borrow timeouts.
• PgBouncer: SHOW POOLS; — cl_waiting > 0 means clients are queued for a server connection.

TL;DR priority order

1. Chunk the batch so it holds locks briefly (commit every N rows).
2. Give the batch a lock_timeout and/or SKIP LOCKED so it yields to OLTP.
3. Give OLTP a lock_timeout so blocked requests fail fast and return their connection.
4. Run the batch on its own isolated pool/endpoint so it can't starve OLTP.
5. Only then, tune pool connectionTimeout / leak detection — never just enlarge the pool.

Application-Framework Connection Pools for PostgreSQL

Client-side connection pools built into application frameworks and drivers (HikariCP, SQLAlchemy, pgx, node-pg, Npgsql). These complement — and sometimes conflict with — a server-side pooler (PgBouncer or RDS Proxy).

For how these pools interact with RDS Proxy pinning, see pgbouncer-vs-amazon-rds-proxy.

The core principle: don't fight the server-side pooler

An application pool keeps connections open and idle for reuse. A transaction-mode pooler (PgBouncer transaction mode, or RDS Proxy) wants connections released between transactions so a backend can be reused by another client.

Two rules keep them compatible:

1. Pooled connections must be pin-free / stateless. No session SETs run on checkout, no driver-level named prepared statements, no session-lifetime temp tables. If a pooled connection trips a pin trigger, RDS Proxy pins the backend and — because the app pool never closes the connection — the pin never releases.
2. Right-size the local pool; don't over-provision it. Let the server-side pooler do the fan-in. Size the application pool to the instance's real request concurrency (small, but not so small that threads block on connectionTimeout — see the HikariCP sizing note below), and give connections a finite lifetime so any accidental pin eventually rotates out.

The two biggest pin/break triggers to control at the framework level are:

• SET statements run on connection setup/checkout → move them to the proxy initialization query (RDS Proxy) or server_reset_query / app-side removal (PgBouncer), so the pool itself issues none.
• Driver auto-prepared (named) statements → disable them for transaction-mode poolers. PgBouncer transaction mode does not track client prepared statements by default (pre-1.21), and on RDS Proxy a named prepared statement pins the session on PostgreSQL.

Quick reference — disable named prepared statements per driver

Stack	Setting to avoid pinning / breakage	Notes
Java / pgjdbc + HikariCP	prepareThreshold=0 (JDBC URL or datasource prop)	Disables server-side prepared statements. Also avoid connectionInitSql with SETs.
Python / psycopg3	prepare_threshold=None	psycopg3 auto-prepares by default — the classic footgun under transaction pooling.
Python / asyncpg (incl. SQLAlchemy async)	statement_cache_size=0	Also disable prepared-statement name reuse; SQLAlchemy passes this via connect_args.
Go / pgx	QueryExecModeSimpleProtocol (or avoid QueryExecModeCacheStatement)	Do not use explicit prepared statements; Deallocate is a SQL query the pooler won't track.
Node.js / node-pg	Don't pass a name to queries	node-pg only creates named prepared statements when you give the query a name.
.NET / Npgsql	Max Auto Prepare=0 (the default) and avoid NpgsqlCommand.Prepare()	Npgsql auto-prepare is off by default, so the risk is enabling it or calling .Prepare() explicitly.

HikariCP (Java)

# JDBC URL — disable server-side prepared statements for transaction pooling
jdbc:postgresql://proxy-endpoint:5432/app?prepareThreshold=0

# HikariCP config
maximumPoolSize=10          # see sizing note below — NOT a universal value
minimumIdle=10              # tip: set == maximumPoolSize for a fixed-size pool
connectionTimeout=30000     # ms a thread waits for a free conn before failing
maxLifetime=600000          # 10 min — ensure accidental pins eventually rotate out
idleTimeout=120000
# DO NOT use connectionInitSql to run `SET ...` — that pins every pooled
# connection on RDS Proxy. Move those settings to the proxy init query instead.
# connectionInitSql=SET search_path=app   <-- avoid

Sizing the pool (important — don't just copy 10)

maximumPoolSize must match this app instance's real concurrency, not the database's capacity. The pool is deliberately small (small pools improve DB throughput — see HikariCP's sizing guide), but too small means application threads block waiting for a connection and you get latency/timeouts even when the database and proxy have spare capacity. That is an application-side bottleneck, not a database one.

What actually happens when the pool is exhausted (all connections busy):

• A thread requesting a connection blocks for up to connectionTimeout (default 30 s).
• If none frees up in time, HikariCP throws SQLTransientConnectionException: ... connection is not available, request timed out.
• The app does not silently open more connections — maximumPoolSize is a hard cap.

So size it from load, not from a round number. HikariCP's rule of thumb for a CPU/IO-bound DB workload:

pool size ≈ (CPU cores × 2) + effective_spindle_count

More practically, size to peak concurrent in-flight queries per instance, then validate against metrics:

• Watch HikariCP's hikaricp_connections_pending (threads waiting) and connectionTimeout exceptions. If pending > 0 under normal load, the pool is too small — raise it.
• Remember the fan-in math across the fleet: instances × maximumPoolSize must stay well under the database's max_connections (and, with RDS Proxy, under MaxConnectionsPercent). A pooler helps here, but only if pooled connections stay pin-free (see §2.6 of the comparison guide).
• If you need more app-side concurrency than the DB can back, that is exactly the signal to put a server-side pooler (RDS Proxy / PgBouncer) in front — raise the app pool to serve request concurrency and let the pooler fan many app connections into a smaller backend set.

If you genuinely need a per-connection setting, prefer putting it in the RDS Proxy initialization query (uniform for all backends) rather than connectionInitSql.

SQLAlchemy (Python)

from sqlalchemy import create_engine

# psycopg3 (sync): disable auto-prepared statements
engine = create_engine(
    "postgresql+psycopg://app@proxy-endpoint:5432/app",
    connect_args={"prepare_threshold": None},
    pool_size=5,            # size to instance concurrency (see HikariCP sizing note)
    max_overflow=5,
    pool_recycle=600,       # recycle so stray pins release
    pool_pre_ping=True,
)

# asyncpg (async): disable the statement cache
# create_async_engine(
#     "postgresql+asyncpg://app@proxy-endpoint:5432/app",
#     connect_args={"statement_cache_size": 0},
# )

Avoid running SET in a connect/checkout event listener — that is the SQLAlchemy equivalent of HikariCP connectionInitSql and will pin.

pgx (Go)

cfg, _ := pgxpool.ParseConfig("postgres://app@proxy-endpoint:5432/app")

// Use the simple protocol so pgx doesn't auto-prepare named statements.
cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

cfg.MaxConns = 10          // size to instance concurrency (see HikariCP sizing note)
cfg.MaxConnLifetime = 10 * time.Minute

pool, _ := pgxpool.NewWithConfig(ctx, cfg)

Do not use QueryExecModeCacheStatement behind a transaction-mode pooler, and avoid explicit conn.Prepare(...) — pgx's Deallocate is a SQL statement the pooler can't intercept.

node-pg (Node.js)

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: 'postgres://app@proxy-endpoint:5432/app',
  max: 10,                       // size to instance concurrency (see HikariCP sizing note)
  idleTimeoutMillis: 30000,
  maxLifetimeSeconds: 600,       // recycle connections
});

// Safe: unnamed query → no server-side prepared statement, no pin.
await pool.query('SELECT * FROM orders WHERE id = $1', [id]);

// Avoid: a `name` creates a named prepared statement that pins on RDS Proxy.
// await pool.query({ name: 'get_order', text: '...', values: [id] });

Npgsql (.NET)

Npgsql is the standard ADO.NET provider for PostgreSQL and has its own built-in connection pool (enabled by default; NpgsqlDataSource is the modern entry point). Unlike psycopg3, automatic prepared statements are off by default (Max Auto Prepare=0), so the main things to control are not enabling them, not calling .Prepare(), and not running SETs on each connection.

using Npgsql;

var connString = new NpgsqlConnectionStringBuilder
{
    Host = "proxy-endpoint",
    Port = 5432,
    Database = "app",
    Username = "app",

    // Pooling: size to instance concurrency (see HikariCP sizing note).
    Pooling = true,
    MaxPoolSize = 10,
    MinPoolSize = 0,

    // Recycle connections so an accidental pin eventually releases.
    ConnectionIdleLifetime = 120,   // seconds idle before pruning
    ConnectionLifetime = 600,       // max total lifetime (seconds)

    // Leave auto-prepare OFF (default) for transaction-mode poolers / RDS Proxy.
    MaxAutoPrepare = 0,
}.ToString();

await using var dataSource = NpgsqlDataSource.Create(connString);

// Safe: an unprepared command → no server-side prepared statement, no pin.
await using var cmd = dataSource.CreateCommand("SELECT * FROM orders WHERE id = $1");
cmd.Parameters.AddWithValue(id);
await using var reader = await cmd.ExecuteReaderAsync();

// Avoid: cmd.Prepare() / cmd.PrepareAsync() creates a server-side prepared
// statement that pins the session on RDS Proxy (PostgreSQL).

Do not run SETs via an NpgsqlDataSourceBuilder connection-init callback (the .NET equivalent of HikariCP connectionInitSql); move those into the RDS Proxy initialization query instead. If you must set per-connection state, be aware it pins.

Verifying it works

• RDS Proxy: watch the CloudWatch metric DatabaseConnectionsCurrentlySessionPinned. If it rises to roughly your app-pool size, your pooled connections are pinning — revisit the settings above.
• PgBouncer: check SHOW POOLS; / SHOW STATS; on the PgBouncer admin console; a healthy transaction-mode pool shows far fewer server connections than client connections.