Database configuration is the set of PostgreSQL settings that determines how the server uses resources and responds to operational demands. Treating the defaults as production tuning is a common mistake: they aim to run on almost any reasonably capable machine, not to deliver the best result for a particular workload. section 6.1 section 6.0
A faster restore after changing settings
A pgbench database was dumped and restored with the default configuration in 44 minutes 15 seconds. Setting shared_buffers to 3GB, work_mem to 128MB, and maintenance_work_mem to 512MB reduced the restore to 26 minutes 17 seconds. shared_buffers required a PostgreSQL restart before the new value took effect. This is basic production due diligence, not a universal tuning recipe: appropriate values depend on the server and workload. section 6.1
Use operational evidence to find configuration problems before they become incidents:
- Check replication errors against actual replication connections and the max_wal_senders limit. section 7.3.1
- Investigate connection exhaustion by comparing legitimate application demand with excessive connection creation; a connection pooler such as PgBouncer may be appropriate. section 7.3.1
- Treat repeated temporary-file logs as evidence that work_mem may be too low for some queries, causing slower disk-based work. section 7.3.1
- Assess configuration alongside schema, data, activity, statements, logs, and performance rather than changing settings in isolation. section 11.3.3
shared_buffers = 3GB #1 work_mem = 128MB #2 maintenance_work_mem = 512MB #3
Inspect the concrete memory settings used in the restore comparison, then compare them with the resulting timed restore. section 6.1
