The precedence ladder beyond the files · PostgreSQL

The configuration files decide what the server starts with. They do not decide what a given session actually runs with, because four more levels sit above them, each more specific than the last, and each able to override.

This is why “we set work_mem to 4 MB in postgresql.conf” is not an answer to “how much memory can a query allocate”. It is a statement about the default.

The five levels

flowchart TD
    A["Server configuration\npostgresql.conf + auto.conf"] --> B["Per-database\nALTER DATABASE ... SET"]
    B --> C["Per-role\nALTER ROLE ... SET"]
    C --> D["Per-role-in-database\nALTER ROLE ... IN DATABASE ... SET"]
    D --> E["Session\nSET, or the connection string"]
    E --> F["The value the query uses"]

More specific wins. A per-role setting beats a per-database setting; a session SET beats everything.

Watching it resolve

Two settings were attached at different levels on a live cluster: a work_mem on the shop database, and a statement_timeout on the reporting_ro role.

ALTER DATABASE shop SET work_mem = '32MB';
ALTER ROLE reporting_ro SET statement_timeout = '5min';

Both are stored in one catalogue:

Read-only / Safepg_db_role_setting, joined to readable names— A null database means all databases; a null role means all roles.

$ psql -U postgres -c 'SELECT coalesce(d.datname, ...) AS database, coalesce(r.rolname, ...) AS role, s.setconfig FROM pg_db_role_setting s ...'

    database     |     role     |        setconfig
-----------------+--------------+--------------------------
shop            | (all roles)  | {work_mem=32MB}
(all databases) | reporting_ro | {statement_timeout=5min}
(2 rows)

The full query is worth keeping, because the raw catalogue stores OIDs and zero rather than names and nulls:

psql -U postgres -c \
  "SELECT coalesce(d.datname, '(all databases)') AS database,
          coalesce(r.rolname, '(all roles)')     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
    ORDER BY 1, 2"

Now connect as reporting_ro to shop and ask what is in force:

Read-only / Safethe source column names the level that won— Captured from a live cluster. Note the two different sources.

$ psql -U reporting_ro -d shop -c "SELECT name, setting, source FROM pg_settings WHERE name IN ('work_mem','statement_timeout')"

       name        | setting |  source
-------------------+---------+----------
statement_timeout | 300000  | user
work_mem          | 32768   | database
(2 rows)

source = database means the per-database setting supplied it. source = user means the per-role setting did — the value name is user for historical reasons and it means role, which is a genuine trap when reading this column quickly.

A session SET then beats both:

Read-only / Safea session SET wins over everything— source becomes session. Nothing the server or the DBA configured constrains this.

$ psql -U reporting_ro -d shop -c "SET work_mem='99MB'; SELECT name, setting, source FROM pg_settings WHERE name='work_mem'"

SET
 name   | setting | source
----------+---------+---------
work_mem | 101376  | session
(1 row)

What this is genuinely good for

Per-role settings are the mechanism for bounding a workload without touching anybody else, and they are underused.

-- A reporting role that cannot hold a transaction open all night
ALTER ROLE reporting_ro SET statement_timeout = '5min';
ALTER ROLE reporting_ro SET idle_in_transaction_session_timeout = '60s';

-- ...and may use more working memory, because its queries sort large sets
ALTER ROLE reporting_ro SET work_mem = '64MB';

-- A migration role that may take longer to acquire a lock, but not forever
ALTER ROLE migrator SET lock_timeout = '5s';
ALTER ROLE migrator SET statement_timeout = '30min';

Each of those is a policy attached to an identity, applied at connection time, requiring no application change and no server restart. The statement_timeout and idle_in_transaction_session_timeout pair is the single most valuable application of this feature, and Part IV and Part VII both return to why.

Per-database settings suit properties of the data rather than the consumer — a database whose workload is analytical wanting a different default_statistics_target, for instance.

Relying on a server-wide work_mem as a memory guarantee

A capacity calculation that multiplies max_connections by the server’s work_mem produces a number, and the number is a floor rather than a ceiling.

Any session can raise its own work_mem with SET, because the parameter is user context and requires no privilege. An ORM, a migration tool or a developer’s connection string can do it, and frequently does. A per-role setting can raise it for every connection that role makes. Neither is visible in postgresql.conf.

Worse, work_mem is per operation, not per session, so one query with several sorts and hashes can allocate it several times over. The arithmetic in Part XI works this through properly.

The practical defence is to look at where values actually come from rather than at the file: query pg_db_role_setting for role and database overrides, and sample pg_settings from a real application session rather than from your own psql.

Removing a setting

Each level has its own reset, and using the wrong one silently does nothing:

ALTER DATABASE shop RESET work_mem;
ALTER ROLE reporting_ro RESET statement_timeout;
ALTER ROLE reporting_ro IN DATABASE shop RESET work_mem;
ALTER ROLE reporting_ro RESET ALL;        -- every setting for that role
RESET work_mem;                            -- this session only

The failure worth knowing: running ALTER DATABASE shop RESET work_mem when the setting was attached to a role removes nothing and reports success. pg_db_role_setting is where you check which level actually holds it, and it is the first thing to read rather than the last.

When each level is applied, and why a session SET survives a reload

The levels are not consulted on every query. They are applied in order at defined moments, which explains several behaviours that otherwise look inconsistent.

The server configuration is loaded at startup and re-read on SIGHUP. When a reload happens, the server recomputes each parameter’s value — but only for sources at or below the configuration-file level.

The per-database and per-role settings are read from pg_db_role_setting during backend startup, in the initialisation step between the fork and ReadyForQuery that Part I described. This is why changing a role’s setting does not affect sessions that role already has open: they read the catalogue once, at connection time, and are not watching it.

A session SET writes into the backend’s own memory and is marked as having session provenance. When a reload arrives, the server will not overwrite a value the session set for itself, because the session level is more specific than the file level.

This was confirmed on a live cluster by changing the server default underneath a session that had already set its own value:

Read-only / Safea session SET is not disturbed by a reload — and RESET proves the reload landed— Captured 2026-08-27 in a single psql session.

$ psql -U postgres  # one session, five statements

SET work_mem = '77MB';
 name   | setting | source
----------+---------+---------
work_mem | 78848   | session

ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();   -->  t

-- after the reload, the session is unchanged
 name   | setting | source
----------+---------+---------
work_mem | 78848   | session

RESET work_mem;
 name   | setting |       source
----------+---------+--------------------
work_mem | 16384   | configuration file

The final two lines are the informative ones. The reload genuinely applied — the new 16 MB value was there the whole time — it was simply masked by the more specific session level. RESET removed the mask and the new value appeared immediately.

That is the answer to “did my reload work?” when a session appears unaffected: it worked, and the session is overriding it.

RESET removes the session’s own value and lets the next-most-specific level show through, which is why RESET and SET ... TO DEFAULT do not necessarily give you the value in postgresql.conf — they give you whatever the role and database levels say.

Production discipline

Read source before concluding anything about a value. It names the level in one word and turns a puzzling number into a located one.
Remember source = user means the role level, not the session. The session level reports as session.
Check pg_db_role_setting before editing a file. A per-role or per-database override makes a file change invisible for the sessions that matter.
Use per-role statement_timeout and idle_in_transaction_session_timeout to bound workloads. It needs no application change and no restart, and it is the cheapest control in this course.
Never treat a server-wide work_mem as a ceiling. It is user context, any session can raise it, and it is allocated per operation rather than per session.
Reset at the level that set it. Resetting the wrong level reports success and changes nothing.

Cross-course references

Secrets, PKI & Certificate Management — Part XII (Secret management platforms) covers least-privilege policy design, and the per-role settings here are the database expression of the same idea.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting per-session settings so that the gap between the configured default and the effective value is visible.
Git, CI/CD & GitOps — Part LXXV (Drift) covers detecting configuration that lives outside the repository, which per-role settings in a catalogue very much are.