A configuration you can hand over · PostgreSQL

A PostgreSQL cluster that has been running for four years has a configuration nobody fully understands. Values were set during incidents, copied from a blog post, inherited from a previous host, or chosen carefully by somebody who has left. They all look identical in the file.

The practical consequence is that nobody dares change any of them, because there is no way to tell a load-bearing value from a superstition. That is the state this lesson exists to prevent.

Separate what you chose from what you inherited

The first question about any cluster is short: what is not at its default?

Read-only / Safeevery value somebody actually chose— Captured on 18.6, abbreviated. The row count depends entirely on how the cluster was started — this one returned 33. On an inherited production cluster this list is the whole review.

$ psql -U postgres -c "SELECT name, setting, source FROM pg_settings WHERE source <> 'default' ORDER BY source, name"

            name            |      setting       |       source
----------------------------+--------------------+--------------------
application_name           | psql               | client
autovacuum_worker_slots    | 16                 | configuration file
DateStyle                  | ISO, MDY           | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix              | configuration file
listen_addresses           | *                  | configuration file
max_connections            | 100                | configuration file
max_wal_size               | 1024               | configuration file
shared_buffers             | 16384              | configuration file
wal_level                  | replica            | command line
config_file                | .../postgresql.conf| override
... (this cluster: 33 rows in total)

That list is the actual configuration. Everything else is upstream’s choice, and upstream’s choices are frequently better than a half-remembered adjustment.

The corollary is worth stating: a parameter you have not deliberately chosen should not appear in your files at all. A postgresql.conf containing two hundred commented and uncommented lines copied from a template obscures the nine decisions that were actually made.

A layout that shows ownership

postgresql.conf                  # stock, plus one line: include_dir = 'conf.d'
conf.d/
├── 10-connections.conf          # max_connections, and why
├── 20-memory.conf               # shared_buffers, work_mem, maintenance_work_mem
├── 30-wal.conf                  # max_wal_size, checkpoint settings
├── 40-autovacuum.conf           # autovacuum thresholds for this workload
├── 50-logging.conf              # what we log and why we do not log more
└── 90-host-overrides.conf       # generated per host; the only machine-specific file

Three properties make this worth the small setup cost.

Ownership is visible in the filename. A change to autovacuum touches one file. A reviewer reading a diff sees the subject before reading the content.

Order is explicit. Files are read in name order and later wins, so 90-host-overrides.conf beating 20-memory.conf is a stated rule rather than an accident of file position.

The stock file stays stock. Upgrades ship a new postgresql.conf with new parameters and updated comments. If your only edit is the include_dir line, adopting the new one is trivial. If your decisions are scattered through it, every upgrade is a merge.

Record the reason, not just the value

A value without a reason cannot be reviewed, and reviewing is the whole point of writing it down.

# 30-wal.conf

# max_wal_size raised from the 1GB default on 2026-08-27.
#
# Why: pg_stat_checkpointer showed num_requested at 2,847 against
#      num_timed at 41, so checkpoints were being forced by WAL volume
#      rather than paced by checkpoint_timeout. The bulk ingestion job
#      introduced in July generates roughly 6GB of WAL per hour.
#
# Trade: recovery after an unclean shutdown replays more WAL, measured
#        at ~90s on this hardware. Accepted; documented in the DR plan.
#
# Revisit if: the ingestion job is removed, or pg_wal capacity changes.
max_wal_size = 8GB

Four lines: what changed, the evidence that prompted it, the cost accepted, and the condition that would make it wrong. The last one is the most valuable and the most often omitted — it is what lets a future engineer decide whether the value is still correct without re-deriving the original analysis.

Copying a tuning configuration from another cluster or a web calculator

Both are tempting and both fail in the same way: they transplant values without transplanting the reasoning, so the receiving cluster now has decisions nobody made.

The specific hazards are concrete. shared_buffers and work_mem sized for a host with different memory. max_connections sized for a different application. Autovacuum thresholds tuned for a table that does not exist here. Any of these may be harmless, and there is no way to tell which, because the reason did not come with the value.

Web calculators and rules of thumb are a reasonable starting point for a cluster with no measurements yet, and this course says so explicitly in Part XI. The difference between a starting point and a configuration is that a starting point is recorded as one, with the measurement that would replace it named. “25% of RAM, pending measurement of the working set” is a defensible line in a file. “shared_buffers = 16GB” with no comment, on a host nobody remembers sizing, is not.

Detecting drift

The intended configuration is in a repository. The running configuration is in a server. They diverge, and the divergence is invisible until it matters.

# What the running server is actually using, in a comparable form
psql -U postgres -tAc \
  "SELECT name || ' = ' || setting
     FROM pg_settings
    WHERE source NOT IN ('default','client','session')
    ORDER BY name" > /tmp/running.txt

# Anything set out of band, bypassing the managed files entirely
psql -U postgres -c \
  "SELECT name, setting, sourcefile FROM pg_settings
    WHERE sourcefile LIKE '%auto.conf'"

# Any per-role or per-database setting, which lives in a catalogue
# rather than in any file and is therefore invisible to a file diff
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"

The second and third queries are the ones a file-based drift check misses entirely. postgresql.auto.conf is written by ALTER SYSTEM and is not in your repository. pg_db_role_setting is a catalogue table and is not a file at all. Both override the managed configuration, and both are the natural residue of incidents.

Where each kind of setting belongs

Setting	Belongs in	Because
`shared_buffers`, `max_connections`	A managed file	Restart-only, host-specific, reviewed
`max_wal_size`, autovacuum thresholds	A managed file	Workload decisions worth recording
`statement_timeout` for a workload	A role setting	Applies to an identity, not a machine
`work_mem` for a reporting role	A role setting	The exception, not the default
`application_name`	The connection string	Identifies the client, and it is free
A per-query memory bump	A session `SET`	Bounded to the statement that needs it

application_name deserves the mention. It costs nothing, it appears in pg_stat_activity and in the log, and it converts “some session is blocking the deployment” into “the invoice reconciliation job is blocking the deployment”. Setting it in every application’s connection string is among the highest-value, lowest-effort things in this course.

Why the stock postgresql.conf is mostly comments, and why that matters at upgrade time

The file initdb generates is roughly 32,000 bytes and around 900 lines, of which the overwhelming majority are commented-out parameters with their default values and a short description.

This is deliberate documentation: the file is a catalogue of what can be set, with the defaults visible. It is genuinely useful for browsing, and it is a poor place to record decisions, because a decision written among nine hundred lines of commentary is not discoverable.

It matters at upgrade time. Each major version ships a revised stock file with new parameters, removed ones, and updated comments. A cluster whose decisions live inside a modified copy of that file faces a three-way merge at every upgrade, between the old stock file, the new stock file, and the local modifications — and merge conflicts in a configuration file are resolved under time pressure by people who did not write the original.

A cluster whose only modification is a single include_dir line at the end faces no merge at all: take the new stock file, append one line, done. The decisions are untouched in conf.d/ and are reviewed separately against the new version’s release notes.

This is the same reasoning that keeps application configuration out of vendor files everywhere else, and PostgreSQL’s include_dir exists to support it.

Production discipline

Keep postgresql.conf stock plus one include_dir line. It makes every major upgrade an append rather than a merge.
Put decisions in numbered fragments so ownership is visible in the filename and precedence is a stated rule.
Record the evidence, the trade accepted, and the revisit condition beside every non-default value. A value without a reason cannot be reviewed and therefore will never be changed.
Audit with source <> 'default'. That list is the real configuration; everything else is upstream’s choice.
Check postgresql.auto.conf and pg_db_role_setting in every drift check. Neither is a file in your repository and both override it.
Set application_name in every connection string. It costs nothing and it names the session in pg_stat_activity and the log.

Cross-course references

Git, CI/CD & GitOps — Part LXXV (Drift) and Part LXXXVIII (Infrastructure GitOps) cover reconciling intended against actual state, which is the general form of this lesson’s drift section.
Ansible for Production Sysadmins — Part XVII (Templates) and Part XXXVI (Drift) cover generating the conf.d fragments and detecting divergence from them.
Linux for Production Sysadmins — Part LXXIII (Change) and Part LXXIV (Drift) cover the host-level version of the same discipline.