A default configuration is PostgreSQL’s conservative, installation-time baseline: settings chosen so the server will start and work on a wide range of machines. It is not a claim that those settings fit a production host, workload, memory budget, or concurrency level. section 1.4.1 section 6.1
The common mistake is treating an immediately usable server as a production-ready one. PostgreSQL can accept client connections directly after installation, but its defaults are almost certainly unsuitable for most production scenarios and can leave available resources underused. section 1.4.1 section 6.13
Treat initial configuration as production due diligence rather than one-time tuning:
- Test and stage against the actual database host instead of assuming generic values are appropriate. section 6.1
- Size memory settings with query types, their memory demands, and expected client concurrency in mind. section 6.13
- Account for the host’s limits; incorrect memory allocation can harm performance. section 6.13
- Revisit choices as workloads change rather than selecting values permanently. section 6.1
Three settings illustrate why defaults need context. shared_buffers controls memory PostgreSQL uses for buffers holding disk blocks or operational data. work_mem supplies non-shared memory to individual operations. maintenance_work_mem supplies memory for maintenance work such as CREATE INDEX and VACUUM. The cited baseline uses only 128 MB for shared_buffers and 4 MB for work_mem, reflecting portability rather than a particular production workload. section 6.1
shared_buffers = 3GB #1 work_mem = 128MB #2 maintenance_work_mem = 512MB #3
Example replacement values used for a restore test; they demonstrate workload-specific adjustment, not universal settings. section 6.1
Measure a restore before and after
A pgbench database was dumped, recreated, and restored with the default postgresql.conf values in 44m15. After changing shared_buffers, work_mem, and maintenance_work_mem and restarting for shared_buffers, the restore took 26m17—less than 60% of the original time. The comparison shows why a representative measurement matters; it does not establish those values as generally correct. section 6.1
Memory is only part of the operating picture. Excessive concurrency can reduce performance under MVCC, while idle connections consume overhead. Idle-in-transaction and long-running sessions can block other queries and delay autovacuum; a connection pooler such as PgBouncer in Transaction Mode is an option when mostly idle connections cannot be avoided. section 6.13
TIP: Do not “fix” a constrained system by weakening or disabling autovacuum. Any short-lived resource saving can be undone by bloat, inaccurate optimizer statistics, and forced anti-wraparound work. section 6.13
