Application-Side Pools and Server-Side State #

A request sets work_mem to 256 MB for a heavy report, returns its connection to the pool, and the next request — a trivial lookup — inherits the setting. On a pool of a hundred connections under load, that is an out-of-memory event nobody can trace to a line of code, because the code that set it ran an hour ago in a different request.

Pooling and plan caching in general are covered in connection pooling and the plan cache; the PgBouncer-specific constraints are in PgBouncer and protocol-level prepared statements. This page is about the pool inside the application.

The Condition #

An application pool — HikariCP, psycopg’s ConnectionPool, asyncpg’s pool, pgx, ActiveRecord’s pool — hands out a long-lived server session and takes it back when the request finishes. The session is not reset in between unless something resets it, and a PostgreSQL session carries state:

Most pools guard the last of these by rolling back on return. Few guard the rest by default, and the ones that do so with DISCARD ALL throw away the plan cache along with everything else — which is correct for safety and expensive for performance.

The plan-relevant part is the tension: cached plans are session state you want to keep, and settings, temp tables and locks are session state you want to discard. Anything that resets a connection wholesale cannot distinguish them.

What survives a pool checkout A table of session state. SET without LOCAL persists and should be scoped with SET LOCAL instead. Prepared statement plans persist and should be kept. Temporary tables persist and should be dropped or created with ON COMMIT DROP. Session advisory locks persist and should be released explicitly. Open transactions should be rolled back on return. persists? what to do SET work_mem yes use SET LOCAL prepared plans yes keep: that is the point temp tables yes ON COMMIT DROP advisory locks yes release explicitly LISTEN yes UNLISTEN on return open transaction yes rollback on return DISCARD ALL resets all six, including the one you wanted

Annotated Evidence #

The leak, seen from the server:

SELECT pid, application_name, setting_source.name, setting_source.setting
FROM pg_stat_activity, LATERAL (SELECT name, setting FROM pg_settings WHERE name = 'work_mem') setting_source
WHERE application_name = 'reports';

More directly, from inside a session that has just been handed out:

SHOW work_mem;
SELECT count(*) FROM pg_prepared_statements;
SELECT relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'pg_temp%';
SELECT locktype, objid FROM pg_locks WHERE locktype = 'advisory';
 work_mem
----------
 256MB        ← set by a previous request, never reset

 count
-------
    41        ← the plan cache: this we want to keep

 relname
------------------
 tmp_report_rows   ← left behind; the next CREATE TEMP TABLE with this name fails

 locktype | objid
----------+--------
 advisory | 841204  ← held since a previous request that crashed

And the effect on a plan that had nothing to do with the report:

HashAggregate  (actual rows=204 loops=1)
  Batches: 1  Memory Usage: 204800kB
-- a small aggregation given a 256 MB budget, times a hundred connections
Three leaks and their signatures Three items are annotated. A settings value differing from the server default on a freshly checked out connection indicates a SET that was never scoped. A temporary table existing at the start of a request indicates a previous request left it behind. An advisory lock held with no active holder indicates a session-scope lock never released. SHOW work_mem returns 256MB on checkout a SET that outlived its request pg_temp table exists before any create previous request left it advisory lock held, no active query session lock never released all three are invisible in the code of the request that suffers

Step-by-Step Resolution #

  1. Use SET LOCAL for anything transactional. It reverts at commit or rollback, which is exactly the scope a request has. A work_mem raise for one report belongs inside that report’s transaction.

  2. Put durable settings where they belong. A search_path or statement_timeout that should always apply to an application is a role default (ALTER ROLE app SET …) or a connection-string option, not something set per request — the drift described in session parameter drift.

  3. Create temporary tables with ON COMMIT DROP, so they cannot outlive the transaction that made them.

  4. Use transaction-scoped advisory locks (pg_advisory_xact_lock) rather than session-scoped ones, so a crashed request cannot leave a lock behind.

  5. Configure the pool’s reset behaviour deliberately. Rolling back on return is essential. DISCARD ALL is safe but drops the plan cache; use it only if the application cannot guarantee the discipline above.

  6. Set a maximum connection lifetime. Recycling connections every half hour bounds any leak that does slip through, and spreads reconnection cost evenly.

  7. Verify by probing a freshly checked-out connection for non-default settings, leftover temp tables and held advisory locks — a cheap health check to run in staging under load.

Where should this setting live? Three cases. A setting needed for one statement or transaction belongs in SET LOCAL inside that transaction. A setting that always applies to the application belongs in a role default or the connection string. A setting needed for one long-lived session belongs in an explicit SET paired with an explicit reset. how long should this setting apply? one transaction SET LOCAL reverts at commit always, for this app role default or DSN set once one session, deliberately SET plus explicit RESET pair them the failure case is a bare SET with no reset

Before and After #

-- BEFORE: bare SET in a request handler
SET work_mem = '256MB';   -- leaks to every later request on this connection

-- AFTER: transaction-scoped
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT … ;                -- the heavy report
COMMIT;                   -- work_mem reverts automatically

Reset without losing the plan cache #

DISCARD ALL is the blunt instrument: it deallocates prepared statements, drops temporary tables, releases session locks, resets all settings and unlistens everything. For an application that cannot guarantee clean state it is the right default, and the cost is that every connection starts cold and re-plans everything it needs.

The targeted alternatives are DISCARD TEMP, DISCARD SEQUENCES, RESET ALL and UNLISTEN *, which together cover everything except prepared statements. Issuing those on connection return keeps the plan cache while removing the state that leaks, and most pool libraries allow a custom reset query for exactly this purpose.

Whether the plan cache is worth preserving depends on the workload. For an application issuing a few dozen statement shapes at high rate, the cache is valuable and the targeted reset is worth configuring. For one issuing a wide variety of ad-hoc statements, the cache saves little and DISCARD ALL costs nothing worth measuring — and the guarantee it provides is worth more than the planning time it spends.

Pool size and plan memory #

One more consequence of the application pool is easy to miss: every pooled connection is a backend, and every backend holds its own copy of whatever state it carries. Cached plans are the largest part of that on many systems, and a plan over a heavily partitioned table is not small.

The arithmetic is simple and frequently unpleasant. A pool of two hundred connections, each caching fifty prepared statements, each plan averaging a megabyte because the schema has a thousand partitions, is two hundred megabytes of plan cache per backend and forty gigabytes across the pool — memory that was never budgeted because it appears in no configuration file.

Two habits keep it bounded. Size the pool from the server’s actual concurrency capacity rather than from the application’s request concurrency; a pool much larger than the core count queues work inside PostgreSQL instead of outside it, which is slower and uses more memory. And treat a large per-statement plan as a schema signal, exactly as in EXPLAIN MEMORY and planning buffers: it usually means queries are being planned over far more relations than they need.

Common Pitfalls #

Bare SET in request handlers. The setting outlives the request. Diagnostic signal: a connection with non-default settings at checkout. Fix: SET LOCAL.

Session-scoped advisory locks. A crashed request leaves the lock held until the connection is recycled. Diagnostic signal: advisory locks with no active query. Fix: transaction-scoped locks.

DISCARD ALL on every checkout with heavy preparation. The plan cache never survives. Diagnostic signal: planning time high despite prepared statements. Fix: targeted resets.

Unbounded connection lifetimes. Leaks accumulate indefinitely. Diagnostic signal: behaviour worsening the longer a process runs. Fix: a maximum lifetime.

Frequently Asked Questions #

What state does a pooled connection carry between requests? #

Settings changed with SET, prepared statements and their plans, temporary tables, session advisory locks, LISTEN registrations, held cursors and any open transaction. None of it is reset automatically unless the pool is configured to reset it.

Should I use DISCARD ALL when returning a connection to the pool? #

It is the safest option and it discards the plan cache along with everything else. Where preparation matters, use DISCARD TEMP, RESET ALL and UNLISTEN * instead, which clean the leaking state and keep prepared statements.

Why did work_mem change for a query that never set it? #

An earlier request on the same pooled connection issued a bare SET. Use SET LOCAL so the value reverts when the transaction ends.

Up: Connection Pooling and the Plan Cache