3.1Performance-related parameters

PostgreSQL performance parameters rarely act in isolation—memory, disk I/O, and WAL writes all interact, so changing one often shifts another's behavior. The Gold exam probes not just what each parameter means, but the interactions: why a default is what it is, and what else moves when you change it.

3.1.1Memory-related parameters

• shared_buffers is the size of PostgreSQL's dedicated shared buffer, separate from the OS cache. The default is conservative (often 128MB-equivalent); in practice the rule of thumb is to start around 25% of physical memory. Setting it too large causes wasteful double-caching with the OS cache, and changing it requires a server restart (its context is postmaster).
• huge_pages controls whether Linux huge pages (larger memory units, e.g. 2MB vs. the default 4KB) are used for shared_buffers (try/on/off). By reducing page-table entries and TLB misses, it boosts performance for large shared_buffers setups, but the OS must have huge pages reserved in advance; with insufficient reservation, on fails to start while try (the default) falls back to regular pages.
• work_mem caps the working memory one operation within a query (sort, hash join, hash aggregate, etc.) may use. The default is small (around 4MB); since usage multiplies by concurrent queries times operations per query, raising it carelessly risks memory exhaustion. When insufficient, the operation spills to on-disk temp files (detectable via log_temp_files), degrading performance.
• maintenance_work_mem caps the working memory available to maintenance operations like VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY (default around 64MB). The standard practice is to set it larger than work_mem, since maintenance operations run with low concurrency and benefit from generous temporary memory—especially for VACUUM's dead-tuple-ID tracking and index-build sorting.

3.1.2WAL, checkpoint, and lock-related parameters

• wal_level controls how much information is recorded in WAL (minimal/replica/logical). Replication requires at least replica; logical replication requires logical. minimal is for standalone servers with the smallest WAL volume, but cannot support archiving or replication. The default is replica.
• fsync controls whether writes to WAL and data files are flushed to disk with a guaranteed sync (default on). Turning it off speeds up writes but risks data corruption on crash—never change it in production (a temporary flip during bulk initial loads is sometimes done, but it must be restored afterward).
• checkpoint_timeout is the maximum interval between checkpoints (default 5 minutes). max_wal_size is the WAL volume threshold that triggers a checkpoint (default 1GB). Whichever is reached first triggers the checkpoint. Overly frequent checkpoints raise I/O load, so write-heavy environments typically extend both (e.g., checkpoint_timeout=15min, max_wal_size=4GB).
• checkpoint_completion_target controls what fraction of the interval until the next checkpoint the checkpoint's writes are spread over (default 0.9 in PostgreSQL 14; 0.5 in 12/13). Closer to 1 smooths out I/O spikes more, though it should not be set so high that it crowds the next checkpoint. wal_keep_size is the minimum amount of WAL segments retained before being sent to a standby (guards against gaps during replication lag; default 0 means no guaranteed retention).
• deadlock_timeout is how long a lock wait waits before triggering deadlock detection (default 1 second). Too short causes ordinary contention to be mistaken for deadlocks, adding detection overhead; too long delays detection and worsens client response times. Since deadlocks are assumed to be rare, the default is tuned toward infrequent checking.

Real tuning decisions require tracing the chain of causation between parameters. Consider a report that "batch bulk-load writes are slow." The first suspect should be frequent checkpoints: heavy INSERTs pile up WAL quickly, hitting the default 1GB max_wal_size and triggering checkpoints in rapid succession, causing I/O spikes. Widening max_wal_size to 4GB or 8GB while keeping checkpoint_completion_target at 0.9 (or tuning it further for the environment) eases both checkpoint frequency and per-checkpoint write concentration at once. Separately, bulk COPY/INSERT workloads sometimes also involve index updates whose sort step is governed by work_mem, not maintenance_work_mem—so it is important not to conflate the two. On the other hand, if the complaint is "VACUUM ANALYZE is slow after the bulk load finishes," the suspect should be insufficient maintenance_work_mem: the default 64MB cannot hold the full dead-tuple-ID list for a large table, forcing multiple passes, so the standard practice is to temporarily raise it per-session, e.g. SET maintenance_work_mem = '1GB', before running VACUUM. Also, casually enlarging shared_buffers does not necessarily improve performance due to double caching with the OS page cache, and should be considered alongside effective_cache_size (which tells the planner "how much is effectively available including OS cache"). On the locking side, the instinct to set deadlock_timeout extremely short to "catch deadlocks early" tends to backfire, since ordinary lock waits end up repeatedly paying the deadlock-check cost. Frequent deadlocks are more often a symptom of inconsistent lock-acquisition order in the application—a case where redesigning acquisition order should be considered before touching parameters at all.

3.1.3Section summary

• Memory: shared_buffers (restart required; ~25% rule of thumb) / work_mem (per-operation; watch multiplicative use) / maintenance_work_mem (VACUUM etc.; conventionally larger than work_mem) / huge_pages (requires OS-side pre-reservation)
• Checkpoints fire on whichever of checkpoint_timeout / max_wal_size comes first; checkpoint_completion_target smooths I/O. wal_level is chosen per replication needs; fsync=off is forbidden in production. deadlock_timeout must not be set too short