PostgreSQL ships with default settings designed to run on minimal hardware. On a production server with 16–128 GB of RAM, these defaults leave 90% of your hardware unused. This guide explains every key parameter, the formula behind it, and the recommended value for your server size.
Want instant results? Use our free PostgreSQL Config Generator — a modern PGTune alternative that runs in your browser.
1. Memory Configuration
shared_buffers
What it does: PostgreSQL's dedicated memory cache for table and index data pages. The single most impactful parameter for query performance.
Formula: 25% of total system RAM, with adjustments for very large systems.
On servers with more than 64 GB RAM, going above 25% to 40% can help for workloads with large working sets. However, PostgreSQL relies heavily on the OS page cache, so allocating too much to shared_buffers starves it. Sweet spot: 25–40%.
Common mistake: Setting shared_buffers to 50%+ of RAM. This causes double-buffering (data in both PG and OS cache) and can reduce performance.
effective_cache_size
What it does: Tells the query planner how much memory is available for caching, including both shared_buffers AND the OS page cache. Doesn't allocate any memory — purely advisory.
Formula: 75% of total system RAM.
If set too low, the planner avoids index scans in favor of sequential scans — dramatically hurting OLTP performance.
work_mem
What it does: Controls memory each sort, hash join, or aggregation can use before spilling to disk.
Formula: (RAM - shared_buffers) / (max_connections × 3)
The ×3 factor accounts for multiple operations per query. If you see "Sort Method: external merge Disk" in EXPLAIN ANALYZE, work_mem is too low.
Warning: work_mem is per-operation, not per-connection. A complex query with 5 joins uses 5 × work_mem. With 200 connections, aggressive values can cause OOM kills.
maintenance_work_mem
Formula: RAM / 16, capped at 2 GB. Used for VACUUM, CREATE INDEX, ALTER TABLE. Can be set higher than work_mem because few maintenance ops run simultaneously. More memory = faster VACUUM and index creation.
2. Storage Configuration
random_page_cost
Default: 4.0 (assumes HDD). SSD: Set to 1.1. This is one of the most impactful changes for SSD servers — the planner will prefer index scans far more aggressively.
effective_io_concurrency
HDD: 2. SSD: 200. Tells PostgreSQL how many concurrent I/O operations storage can handle.
3. Checkpoint & WAL
wal_buffers: 64 MB for most servers. checkpoint_completion_target: 0.9 (default in PG 14+) — spreads checkpoint I/O over 90% of the interval. max_wal_size: 4 GB for web, 8 GB for write-heavy OLTP, 16 GB for bulk data warehouse loads.
4. Parallelism
Web apps with many simple queries benefit less from parallelism. Data warehouses with complex aggregations can see 10–20x speedups on multi-core servers.
5. Quick Reference: Settings by Server Size
| Parameter | 4 GB | 16 GB | 32 GB | 64 GB |
|---|---|---|---|---|
| shared_buffers | 1 GB | 4 GB | 8 GB | 16 GB |
| effective_cache_size | 3 GB | 12 GB | 24 GB | 48 GB |
| work_mem | 5 MB | 20 MB | 40 MB | 80 MB |
| maintenance_work_mem | 256 MB | 1 GB | 2 GB | 2 GB |
| wal_buffers | 32 MB | 64 MB | 64 MB | 64 MB |
| max_wal_size | 2 GB | 4 GB | 4 GB | 8 GB |
Values assume web application workload with ~200 connections on SSD. For OLTP or data warehouse, use our PostgreSQL Config Generator.
Generate Your Config Automatically
Instead of calculating each parameter manually, use our free PostgreSQL Config Generator. Enter RAM, CPUs, storage type, and workload — get a complete optimized postgresql.conf in seconds. A modern, browser-based PGTune alternative with explanations for every setting.