Role and Database Level Setting Overrides #

postgresql.conf says work_mem = 16MB. The application’s sessions report 256 MB. Nobody in the team set it, the deployment configuration does not mention it, and a psql session shows 16 MB — which is why the investigation goes in circles for a day before someone queries pg_db_role_setting and finds a line added during an incident two years ago.

Drift generally is covered in session parameter drift. This page is about the precedence order that decides which value wins, and how to trace it.

The Condition #

A GUC’s effective value is resolved from several sources, later ones overriding earlier:

  1. Compiled-in default.
  2. postgresql.conf, and files it includes.
  3. ALTER SYSTEM, stored in postgresql.auto.conf — which overrides postgresql.conf, surprising anyone who edits the latter and reloads.
  4. Database default — ALTER DATABASE … SET.
  5. Role default — ALTER ROLE … SET, and the more specific ALTER ROLE … IN DATABASE … SET.
  6. Connection options — the options parameter in the connection string, or PGOPTIONS.
  7. Session SET, then SET LOCAL within a transaction.

Role-in-database settings beat plain role settings, which beat database settings. That is a five-level hierarchy before any application code runs, and three of the levels are stored in catalogs that nothing in a repository reflects.

The planner-relevant part is that several of these settings change plan choice directly: work_mem, random_page_cost, effective_cache_size, enable_*, jit, max_parallel_workers_per_gather, plan_cache_mode. Two sessions with different values genuinely get different plans for the same query, which is why “it is fast for me” is so often true and so rarely useful.

Where a value can come from Six layers in precedence order. The compiled default is overridden by postgresql.conf, which is overridden by ALTER SYSTEM, then by database defaults, then by role defaults, then by connection options, and finally by SET within the session. The most specific setting that exists is the one that applies. postgresql.conf the file people read ALTER SYSTEM overrides the file database, then role defaults stored in catalogs connection options and SET per session the two middle layers are the ones nobody looks at

Annotated Evidence #

Where the value came from:

SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings WHERE name IN ('work_mem', 'random_page_cost', 'jit');
      name        | setting | unit |     source      |    sourcefile
------------------+---------+------+-----------------+-------------------
 work_mem         | 262144  | kB   | database        |
 random_page_cost | 1.1     |      | configuration f | postgresql.conf
 jit              | off     |      | user            |
-- source 'database' means ALTER DATABASE; 'user' means ALTER ROLE
-- neither has a source file, because neither lives in one

The catalog that holds them:

SELECT coalesce(d.datname, 'ALL') AS database,
       coalesce(r.rolname, 'ALL') AS role,
       s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles    r ON r.oid = s.setrole;
 database | role       |                setconfig
----------+------------+------------------------------------------
 appdb    | ALL        | {work_mem=256MB}
 ALL      | app_report | {jit=off,statement_timeout=30min}
 appdb    | app_web    | {statement_timeout=10s,work_mem=8MB}
-- three layers, none of them in any repository

And what it does to a plan:

-- with work_mem = 8MB (app_web)
Hash  Batches: 32  Memory Usage: 8192kB      -- spilling to disk

-- with work_mem = 256MB (the database default)
Hash  Batches: 1   Memory Usage: 198204kB    -- in memory
-- identical query, identical data, different role
Three places to look Three items are annotated. The source column in pg_settings names which layer supplied the value, with database meaning ALTER DATABASE and user meaning ALTER ROLE. The pg_db_role_setting catalog lists every such override. EXPLAIN with the SETTINGS option records the non-default values that produced a particular plan. pg_settings.source = 'database' or 'user' a catalog override pg_db_role_setting rows every role and database default EXPLAIN (SETTINGS) on the plan what produced this plan sourcefile is empty for catalog-sourced values

Step-by-Step Resolution #

  1. Ask pg_settings first. source, sourcefile and sourceline name the origin exactly. An empty sourcefile with a source of database or user means a catalog override.

  2. List every override with the pg_db_role_setting query above. On most systems this is a short list containing at least one surprise.

  3. Capture settings with plans. EXPLAIN (SETTINGS) lists non-default values, so a stored plan records the configuration that produced it — essential for the comparisons in plan regression detection.

  4. Compare environments by diffing the overrides, not the configuration files. Staging and production frequently differ only in catalog settings nobody has looked at.

  5. Decide where each setting belongs. Machine-wide sizing (shared_buffers, effective_cache_size) belongs in the configuration file. Workload-specific values (work_mem, statement_timeout, jit) belong on roles, one role per workload.

  6. Put the catalog settings in version control as a migration, so ALTER ROLE app_web SET work_mem = '8MB' is reviewable rather than folkloric.

  7. Remove overrides that no longer have a reason. An override with no explanation is a liability; if nobody can say why it exists, test removing it.

One setting, four sources, four outcomes With the compiled default of 4 megabytes the report takes 84 seconds. With the configuration file value of 16 megabytes it takes 31 seconds. With the database default of 256 megabytes it takes 4 seconds. With a role default of 8 megabytes, which overrides the database default, it takes 62 seconds. compiled default 4MB 84 s postgresql.conf 16MB 31 s database default 256MB 4 s role default 8MB 62 s the last row wins over the one above it, which surprises people

Before and After #

-- BEFORE: an unexplained override
pg_settings: work_mem = 262144 kB, source = database, sourcefile = (null)
-- nobody knows why; it applies to every role without its own value

-- AFTER: explicit, per workload, in a migration
ALTER DATABASE appdb RESET work_mem;
ALTER ROLE app_web    SET work_mem = '8MB';
ALTER ROLE app_report SET work_mem = '256MB';

Reading the settings the application actually has #

Every check in this page must be run through the application’s own connection path, because that is the only way the role, database and connection options are the real ones. A psql session as a superuser has different role defaults and often a different database, so it answers a different question.

The cheapest way to arrange that is a diagnostic endpoint or a startup log line that runs a small query and records the result:

SELECT name, setting, source FROM pg_settings
WHERE source NOT IN ('default', 'configuration file') ORDER BY name;

Logging that at connection establishment makes drift visible the moment it appears, rather than during the incident it causes. It is a handful of rows on a healthy system, and the rows that appear unexpectedly are exactly the ones worth knowing about.

Which settings belong at which level #

A useful way to keep the hierarchy manageable is to decide, once, what kind of thing each level expresses.

The configuration file expresses the machine. shared_buffers, max_connections, effective_cache_size, max_worker_processes, WAL sizing and checkpoint behaviour describe the hardware and the cluster, not any particular query. They change when the machine changes, and they belong in whatever manages the server’s configuration.

Role defaults express the workload. work_mem, statement_timeout, lock_timeout, jit, plan_cache_mode and search_path all differ legitimately between a web request, a background job and a report, and roles are the mechanism that lets them. One role per workload is a small amount of setup that removes most of the need for per-request settings entirely.

Database defaults express something about the database itself, which is rarer than it looks. A setting applied to a whole database usually turns out to have been meant for one workload inside it, and it is worth converting to a role default when found.

SET LOCAL expresses one operation’s exception. A migration that needs a longer lock timeout, or a single report that needs more memory, is a per-transaction override and should look like one.

Settings that reshape plan choice — the enable_* flags most of all — belong at none of these levels as a permanent value, for the reasons in enable flags left off in sessions.

Common Pitfalls #

Editing postgresql.conf when ALTER SYSTEM has been used. The file value is overridden. Diagnostic signal: a reload that changes nothing. Fix: check postgresql.auto.conf.

Assuming the database default wins. Role defaults override it. Diagnostic signal: a database-wide setting that some roles ignore. Fix: check role settings too.

Diagnosing from a superuser psql session. Different role, different defaults. Diagnostic signal: values that do not match the application’s behaviour. Fix: query through the application.

Untracked catalog overrides. They survive every deploy and appear in no repository. Diagnostic signal: environments differing with identical configuration files. Fix: manage them as migrations.

Frequently Asked Questions #

Why is work_mem different from what postgresql.conf says? #

Something more specific overrides it: ALTER SYSTEM, a database default, a role default, a connection option or a session SET. pg_settings.source names which one.

What is the precedence order for PostgreSQL settings? #

Compiled default, then postgresql.conf, then ALTER SYSTEM, then database defaults, then role defaults (with role-in-database most specific), then connection options, then SET and SET LOCAL in the session.

How do I find every role and database level setting? #

Query pg_db_role_setting, joined to pg_database and pg_roles. It lists every ALTER DATABASE SET and ALTER ROLE SET in the cluster.

Up: Session Parameter Drift