A Configuration Baseline That Isn't Cargo Cult

Postgres ships with defaults chosen so that the server starts on a laptop with a gigabyte of RAM and a stock kernel. That is a reasonable goal for a default and a terrible description of pg-primary. Perhaps a dozen settings genuinely change behaviour on a real server, each for a reason you can state in one sentence, and the several hundred others should be left exactly where they are.

What follows is Cartwheel's actual postgresql.conf, restricted to the lines that differ from the defaults, each with the reasoning attached. Two rules govern the file itself. It lives in version control next to the application, so a change has an author and a date. And every non-default line carries a comment explaining why it is there, including the lines that record a setting considered and left alone.

Memory

The memory block was argued in full two topics ago and appears here as the file sees it: shared_buffers = 16GB for a quarter of the machine's RAM, effective_cache_size = 48GB as a planner input that allocates nothing, a deliberately small global work_mem with the large grants issued per role, maintenance_work_mem = 2GB so index builds and vacuum are not artificially slow, and autovacuum_work_mem pinned separately because three workers would otherwise inherit the 2 GB each.

Storage and the Cost Model

random_page_cost defaults to 4.0, and that number is a measurement of a spinning disk: a random seek costing roughly four times a sequential page read. pg-primary has NVMe, where a random read and a sequential read differ by a small factor rather than a fourfold one, so the default systematically overprices every index scan and biases the planner toward reading whole tables. Setting it to 1.1 corrects the exchange rate while keeping a slight preference for sequential access, which is still real even on flash. seq_page_cost stays at 1.0 because it is the anchor the entire unit is defined against, and every other constant is expressed as a multiple of it.

A pair of storage settings changed in 18 and are worth knowing exist. effective_io_concurrency now defaults to 16, where it was 1 in 17, which is an acknowledgement that storage has had queue depth for a decade; maintenance_io_concurrency moved to 16 alongside it. More visibly, 18 introduced asynchronous I/O with io_method, which defaults to worker and runs three background io_workers; builds on Linux with the right support also offer io_uring, and sync restores the pre-18 behaviour. Leave all three alone until a benchmark on your own storage says otherwise. What the planner then does with a cheaper random page, and how the constant flips a scan node, is Chapter 9's material. The constants belong in this file rather than in individual queries.

shared_buffers = 16GB                # 25% of 64 GB (restart)
effective_cache_size = 48GB          # planner input; allocates nothing
work_mem = 8MB                       # per node per backend; raised per role
maintenance_work_mem = 2GB           # index builds, VACUUM, ALTER TABLE
autovacuum_work_mem = 512MB          # else 3 workers inherit 2GB each
huge_pages = try                     # 'on' once the reservation is verified

random_page_cost = 1.1               # NVMe, not a 2005 spindle
# seq_page_cost stays 1.0 — it defines the unit
# effective_io_concurrency: default is 16 since 18, left alone
# io_method: 'worker' + 3 io_workers is the 18 default, left alone

Note what the comments do in the second half of that block: they record settings that were considered and deliberately not changed. Three of the six lines there set nothing at all, and they are the ones that will still be answering questions in two years.

WAL and Checkpoints

Each line here gets a one-sentence justification and none of the underlying argument, which belongs to Chapter 12. max_wal_size goes from its 1 GB default to 16 GB so that checkpoints are triggered by the clock rather than by running out of WAL space; the 77% forced-checkpoint ratio from the statistics views is exactly the symptom. checkpoint_timeout goes from five minutes to fifteen so there are fewer of them. wal_compression is turned on, where the build supports lz4, trading CPU for WAL volume on a write-heavy workload. And checkpoint_completion_target = 0.9 has been the default since 14, so the line in the file changes nothing and can go.

Autovacuum

The autovacuum section takes two changes, and Chapter 7 carries the reasoning for both. The cost limit is raised so that autovacuum is allowed to use the I/O bandwidth the machine actually has instead of the throttle that suits a shared virtual server; the effective default is 200, and on this hardware it is far too polite. Per-table autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor overrides go on orders, inventory and delivery_events, because 20% of 40 million rows is a lot of dead tuples to accumulate before anything starts. Everything else in the autovacuum section keeps its defaults, which are well chosen for tables that are not the three biggest in the database.

max_wal_size = 16GB                  # checkpoints timed, not forced (ch12)
min_wal_size = 2GB                   # recycle segments, do not churn (ch12)
checkpoint_timeout = 15min           # fewer, larger, smoother (ch12)
wal_compression = lz4                # CPU for WAL volume (ch12)
# checkpoint_completion_target = 0.9 — already the default since 14; delete this line
# synchronous_commit stays 'on' — a durability decision, not a knob (ch12)

autovacuum_vacuum_cost_limit = 2000  # default is 200; let vacuum use the disk (ch7)
# per-table overrides live in the migration, not here:
#   ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

The commented-out ALTER TABLE is there for a reason. Per-table storage parameters are schema, not configuration: they belong in a migration where they are reviewed and versioned with the table. The note is there because \d+ orders is the only other place they show up.

Logging and Observability

A cluster you cannot see is a cluster you cannot tune, and the observability lines are the cheapest in the file. log_min_duration_statement = '500ms' puts slow statements in the log with no extension required. log_lock_waits = on writes a line whenever a session waits longer than deadlock_timeout for a lock, which is how a migration stalled behind a long query becomes visible rather than mysterious. log_temp_files = 0 names the statements behind every spill. log_autovacuum_min_duration = 0 logs every autovacuum, where the default of 10 minutes logs only the slow ones. track_io_timing = on feeds the read and write times into four different views. And log_checkpoints, like the completion target, has been on by default since 15 and is in the file only as a note.

shared_preload_libraries = 'pg_stat_statements,auto_explain'  # restart
pg_stat_statements.max = 10000       # default 5000; watch info.dealloc
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_timing = off        # the expensive half of log_analyze

log_min_duration_statement = '500ms'
log_lock_waits = on                  # default off; fires at deadlock_timeout
log_temp_files = 0                   # default -1; names every spill
log_autovacuum_min_duration = 0      # default 10min since 15
track_io_timing = on                 # default off; measure with pg_test_timing
log_line_prefix = '%m [%p] %u@%d app=%a '  # default omits user, db, app
# log_checkpoints — on by default since 15; nothing to set

The log_line_prefix line matters more than it looks. The default is a timestamp and a process id, which is enough to order events and nothing else; adding user, database and application name is what lets you answer "which service issued this" from a log line six weeks later, and it costs a few bytes per entry.

Safety Rails and the Ones to Question

The timeouts are the highest-value lines in this whole topic, and none of them belong in postgresql.conf. The documentation says so directly for statement_timeout, lock_timeout and transaction_timeout: setting them globally is not recommended, because a single value cannot be right for the checkout path, a nightly report and a migration at the same time. Set them per role instead, where each one describes a workload.

ALTER ROLE cartwheel_app       SET statement_timeout = '5s';
ALTER ROLE cartwheel_app       SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE cartwheel_analytics SET statement_timeout = '10min';
ALTER ROLE cartwheel_migrator  SET lock_timeout = '3s';
ALTER ROLE cartwheel_migrator  SET statement_timeout = '30min';
ALTER ROLE cartwheel_app       SET transaction_timeout = '60s';  -- since 17

Each line encodes a decision somebody would otherwise have to make during an incident. Five seconds is longer than any checkout statement should ever take; thirty seconds of idle-in-transaction is more than enough for an application that is not broken, and cutting it off protects the vacuum horizon for the entire cluster. The migrator gets a short lock_timeout and a long statement_timeout, which is the pairing that turns a blocked ALTER TABLE into a retry instead of a queue. transaction_timeout, added in 17, closes the remaining hole: a transaction that stays open for an hour while issuing a fast statement every few seconds satisfies both of the other two timeouts and is caught by neither.

Then there are the settings that get copied without being understood. fsync = off and full_page_writes = off improve every benchmark number and make an unclean shutdown unrecoverable: legitimate for a throwaway load test, catastrophic when the file gets committed. synchronous_commit is a durability choice about how much recently committed work you are prepared to lose, not a performance knob, and Chapter 12 puts a number on it. And jit is on by default, activating whenever a plan's estimated cost exceeds jit_above_cost, which is 100,000. A moderately complex OLTP statement can cross that threshold and spend longer compiling than the compiled code saves; where it does, the switch belongs on the application role rather than on the cluster.

That is the chapter's result for Cartwheel. Memory sized to the hardware instead of to a package default, 400 client connections multiplexed onto 40 server ones through pgbouncer-01, two extensions turning "the site feels slow" into a ranked list with a plan attached, a dozen counters on a dashboard and five of them wired to a pager. What none of it fixes is delivery_events: 1.2 billion rows, 700 GB, growing by 4 million rows a day, with a DELETE-based retention policy that took eleven hours the last time it ran. No setting in this file makes that table manageable, which is where Chapter 11 begins.