Session Parameter Drift: When GUCs Differ Between Connections #

Two application connections run the identical statement with the identical parameters, and one is fast while the other spills to disk or picks a different index. Nothing in the SQL explains it. The cause is session parameter drift: the runtime configuration (GUCs) in effect on the two backends has diverged, and because a plan is a function of its session GUCs, the divergence produces different plans and different performance.

This page shows the sources of drift, how to audit the effective value and its origin, and how to pin settings so every connection plans the same way. It is the configuration-focused branch of the runtime environment story.

Which GUCs Change the Plan #

Not every setting affects planning, but the ones that do are exactly the ones that drift. The high-impact set:

GUC drift matrix across connections A grid with three connection rows and four GUC columns. Each cell holds the value that connection has for that setting. Highlighted cells mark where a connection diverges from the intended baseline, such as a low work_mem or an altered search_path. Connection work_mem search_path jit random_pc conn 1 64MB app, public off 1.1 conn 2 4MB app, public off 1.1 conn 3 64MB tenant2, public on 1.1 highlighted cells = drift from baseline → same SQL, different plan

Where Drift Comes From #

Drift is not random; it enters through a fixed set of layers, each of which can override the one below it:

Auditing the Effective Value and Its Source #

The authoritative view is pg_settings, whose source and context columns tell you not just the value but where it came from.

-- What is in effect on THIS backend, and where did each value originate?
SELECT name, setting, unit, source, context
FROM   pg_settings
WHERE  name IN ('work_mem','effective_cache_size','random_page_cost',
                'search_path','statement_timeout','jit');
        name         | setting | unit |      source       |  context
---------------------+---------+------+-------------------+-----------
 work_mem            | 4096    | kB   | role              | user
 search_path         | app,... |      | session           | user
 random_page_cost    | 1.1     |      | configuration file| sighup
-- source=role  → an ALTER ROLE ... SET work_mem is overriding the config default.
-- source=session → a runtime SET changed search_path on THIS backend only.
-- source=configuration file → the postgresql.conf value is still in force.

To compare across live connections and tie a value to an application, join pg_stat_activity:

-- Spot connections whose effective work_mem differs from the intended 64MB.
SELECT a.pid, a.application_name, a.usename,
       s.setting AS work_mem_kb, s.source
FROM   pg_stat_activity a
CROSS JOIN LATERAL (
  SELECT setting, source FROM pg_settings WHERE name = 'work_mem'
) s
WHERE  a.datname = 'appdb';
-- Note: pg_settings reflects the querying backend; use current_setting()
-- inside each app connection, or auto_explain, to capture per-backend drift.

Annotated: The Same Query, Two Outcomes #

The clearest evidence of drift is a sort that spills on one connection and stays in memory on another. Same query, same data, different work_mem.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total_amount) AS lifetime
FROM   orders
GROUP  BY customer_id
ORDER  BY lifetime DESC;
-- Connection with work_mem = 4MB:
Sort  (cost=48210.0..48960.0 rows=300000 width=16)
      (actual time=980.4..1120.7 rows=300000 loops=1)
  Sort Key: (sum(total_amount)) DESC
  Sort Method: external merge  Disk: 8776kB
  -- external merge + Disk: → the sort SPILLED; work_mem too small here

-- Connection with work_mem = 64MB (identical SQL):
Sort  (cost=41200.0..41950.0 rows=300000 width=16)
      (actual time=305.2..362.9 rows=300000 loops=1)
  Sort Key: (sum(total_amount)) DESC
  Sort Method: quicksort  Memory: 41800kB
  -- quicksort + Memory: → fully in RAM, ~3x faster on identical data

The only difference is the effective work_mem. Reading these spill signals in depth is covered in sort and hash node analysis; the same memory budget also governs whether a hash join stays single-batch. The detecting work_mem drift across sessions page turns this into a repeatable canary check.

Which source wins Values are layered from the configuration file at the base through database and role defaults, the connection string, and finally a runtime SET, which overrides everything below it. postgresql.conf ALTER DATABASE … SET ALTER ROLE … SET connection options SET / SET LOCAL — wins pg_settings.source names the winning layer drift is almost always the top layer arriving on some connections and not others

Numbered Diagnostic Workflow #

Step 1 — Enumerate the sources of each GUC.

-- Find every role- and database-level override that could cause drift.
SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL;
SELECT datname, datconfig FROM pg_db_role_setting s
  JOIN pg_database d ON d.oid = s.setdatabase;   -- per-db/role overrides

Combined with the pg_settings source column, this shows every layer that can set the value.

Step 2 — Pin the intended value at role or database level.

-- Make the value deterministic for every connection of this role/db.
ALTER ROLE app_user SET work_mem = '64MB';
ALTER DATABASE appdb SET search_path = 'app, public';
-- Applied at connection time; no reliance on the app issuing SET.

Step 3 — Verify with a canary query.

Run the spill-sensitive query above on a freshly checked-out connection and confirm Sort Method: quicksort (in memory), not external merge (spilled). A canary that changes plan under drift is your regression detector.

Step 4 — Monitor for regressions.

-- Periodically snapshot effective settings per active connection.
SELECT pid, application_name,
       current_setting('work_mem')     AS work_mem,
       current_setting('search_path')  AS search_path
FROM   pg_stat_activity
WHERE  datname = 'appdb' AND state <> 'idle';
SET outlives the transaction; SET LOCAL does not A plain SET persists for the rest of the session and therefore leaks to the next client that borrows the pooled backend, while SET LOCAL reverts at commit. SET your transaction …and every client that borrows this backend next SET LOCAL your transaction reverted at commit — nothing leaks behind a pooler this is the difference between a diagnostic and an incident

Common Pitfalls #

search_path changes which schema’s table is hit. Diagnostic: two connections use different indexes, or one hits a table the other does not, for an unqualified name. Fix: schema-qualify names in the query, or pin search_path at the role level so multi-tenant connections cannot resolve to the wrong schema.

SET leaking into pooled connections. Diagnostic: a GUC intermittently has a value no one set on this connection. Fix: replace runtime SET with SET LOCAL (transaction-scoped) or with a role/database default; a plain SET persists on the backend and the pooler may hand that backend to another client.

jit = on adds latency to short queries. Diagnostic: EXPLAIN ANALYZE shows a JIT block with Generation and Inlining time on a query returning few rows. Fix: raise jit_above_cost, or set jit = off for OLTP roles; JIT only amortizes on long CPU-bound analytical queries.

Per-operation work_mem multiplication. Diagnostic: raising work_mem to fix one spill causes memory pressure because a query has several sorts/hashes. Fix: work_mem is per operation, so a plan with three hash nodes can use 3 × work_mem; pin a moderate value and raise it with SET LOCAL only for the specific heavy query.

geqo_threshold changing plans on many-way joins. Diagnostic: a query with many joined tables produces a different plan on two connections with different geqo_threshold or geqo settings. Fix: the genetic query optimizer is non-deterministic above the threshold; align geqo_threshold across connections or raise it so the exhaustive planner runs for that join count.

Drift is a configuration-management problem that presents as a performance problem, and that mismatch is why it takes so long to diagnose. Every layer that can set a GUC — the configuration file, a database default, a role default, the connection string, and a runtime SET — is individually reasonable, and the effective value is simply the last one applied. Nothing is broken; the layers just disagree, and only some connections received the disagreement.

The audit is mechanical once you know where to look. pg_settings reports both the current value and the layer that produced it, and comparing that across backends identifies not just which connections differ but why. A value sourced from session on a connection nobody deliberately configured is a leak, almost always from a SET issued for a diagnostic and never reset, surviving on a pooled backend long after the session that issued it went away.

Prevention is more valuable than detection here, and it comes down to three habits. Anything durable belongs at role or database level, where it applies uniformly and is visible in the catalog. Anything temporary belongs in a SET LOCAL inside a transaction, so it reverts on commit regardless of what happens to the connection. And anything a pooler hands back to the pool should be reset, so the next borrower starts from a known state rather than inheriting whatever the previous one left behind.

Two settings deserve particular care because their effects are so easy to misattribute. search_path drift changes which physical table an unqualified name resolves to, which produces not a slow query but a query against different data. work_mem drift changes whether memory-hungry nodes spill, which produces identical plans with wildly different execution times — the case that sends people looking for a query problem that does not exist.

Auditing one connection and declaring the fleet consistent. Drift is by definition a difference between connections, so a single sample can only ever confirm what one backend believes. The useful query compares current_setting across backends, or reads pg_settings on each of several connections opened the way the application opens them. Diagnostic signal: a confident “the setting is correct” based on one psql session. Fix: sample several connections, and sample them through the pooler rather than beside it.

Fixing the value without fixing the source. Issuing a corrective SET on the affected connection restores the intended behaviour until that backend is recycled, at which point the original mechanism reasserts itself. Diagnostic signal: the same drift returning after a deployment or a pooler restart. Fix: find the layer named in pg_settings.source and correct it there — usually a role default, a connection-string option, or application code issuing a session-level SET.

Assuming a deployment resets everything. Restarting the application replaces client connections, but a pooler’s server-side connections can outlive several deployments, carrying whatever session state they accumulated. Diagnostic signal: drift that survives an application restart and disappears only when the pooler is restarted. Fix: configure the pooler to recycle server connections periodically, and treat a long-lived backend as state that needs managing rather than as a free resource.

Leaving a diagnostic override in place after the investigation. An enable_seqscan = off or an inflated work_mem set during a debugging session is the single most common source of drift, precisely because it was applied deliberately and forgotten immediately. Diagnostic signal: a GUC whose value nobody on the team recognises, sourced from session. Fix: make SET LOCAL inside an explicit transaction the default habit for every diagnostic, so the override cannot outlive the question it was answering.

Frequently Asked Questions #

Why does the same query spill to disk on one connection but not another? #

The two connections have different effective work_mem. A sort or hash that fits in memory on a connection with a large work_mem spills to a temporary file on a connection with a small one, shown as Sort Method: external merge with a Disk: figure. The difference usually comes from an ALTER ROLE SET, a connection-string option, or a SET that leaked into one pooled connection but not the other. Pinning work_mem at the role level removes the divergence.

How do I find where a session setting came from? #

Query pg_settings and read the source and context columns. source tells you whether the value came from the configuration file, a per-database default, a per-role default, the client connection, or a runtime SET. Compare current_setting() across connections to detect drift, and use pg_stat_activity to tie a setting to a specific backend and application. That trail almost always leads straight to the layer that introduced the drift.

What is the difference between SET and SET LOCAL for GUC drift? #

SET changes a GUC for the rest of the session, so behind a pooler it can leak into the next client that reuses the backend. SET LOCAL scopes the change to the current transaction and reverts on commit or rollback, which makes it safe for pooled connections. Persistent, deterministic settings should be pinned with ALTER ROLE or ALTER DATABASE instead of either runtime form, so no connection depends on the app issuing the right SET.

Up: Runtime Environment