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.

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';

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.

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