Introduction

PostgreSQL is a powerful open-source relational database, but its default configuration is intentionally conservative to run on minimal hardware. To get the best performance for your workload, you need to tune several key parameters in the postgresql.conf file.

This guide covers the most important PostgreSQL configuration settings and how to calculate optimal values based on your server's available RAM and workload type.

Memory Settings

Memory configuration is the most impactful area for PostgreSQL performance. Here are the key parameters:

shared_buffers

This is PostgreSQL's dedicated memory cache for table and index data. It's the most important memory setting.

Recommended value: 25% of total system RAM (but no more than 8GB on most systems)

# For a server with 16GB RAM:
shared_buffers = 4GB

Start with 25% of RAM. On systems with very large amounts of RAM (64GB+), you may cap this at 8-16GB since the operating system's file cache also helps.

work_mem

Memory used for internal sort operations and hash tables before writing to temporary disk files. Each query can use multiple work_mem buffers, so be careful not to set this too high.

Recommended value: Total RAM / max_connections / 4

# For a server with 16GB RAM and 100 connections:
work_mem = 40MB

For OLAP workloads with complex queries and few connections, you can increase this significantly. For OLTP with many concurrent connections, keep it lower.

maintenance_work_mem

Memory for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

Recommended value: 5-10% of RAM (up to 1-2GB)

maintenance_work_mem = 1GB

Since these operations typically run one at a time, you can set this higher than work_mem.

effective_cache_size

This doesn't allocate memory; it tells the query planner how much memory is available for caching (PostgreSQL buffers + OS file cache). Helps the planner decide whether to use index scans.

Recommended value: 50-75% of total system RAM

# For a server with 16GB RAM:
effective_cache_size = 12GB

Connection Settings

max_connections

Maximum number of concurrent connections to the database. Each connection consumes memory even when idle.

Recommended value: Only as many as you actually need

max_connections = 100

Rather than increasing this to thousands, use a connection pooler like PgBouncer. A good rule of thumb: set this to 2-3x your CPU cores for CPU-bound workloads.

WAL Settings

Write-Ahead Logging (WAL) settings affect write performance and crash recovery.

wal_buffers

Shared memory for WAL data that hasn't been written to disk yet.

Recommended value: 3% of shared_buffers (up to 64MB)

wal_buffers = 64MB

PostgreSQL 9.1+ auto-tunes this, but you can set it explicitly.

checkpoint_completion_target

Spreads checkpoint I/O over a longer period to reduce disk spikes.

Recommended value: 0.9

checkpoint_completion_target = 0.9

max_wal_size

Maximum size of WAL files between automatic checkpoints. Higher values reduce checkpoint frequency but increase recovery time.

Recommended value: 1GB-4GB depending on write load

max_wal_size = 2GB
min_wal_size = 1GB

Quick Configuration by RAM Size

Here's a quick reference for common server sizes:

4GB RAM Server:

shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
work_mem = 10MB

16GB RAM Server:

shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
work_mem = 40MB

64GB RAM Server:

shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
work_mem = 160MB

Applying Changes

After modifying postgresql.conf, some settings require a restart while others only need a reload:

# Reload configuration (for most settings):
sudo systemctl reload postgresql

# Restart (required for shared_buffers, max_connections):
sudo systemctl restart postgresql

Check current values with:

SHOW shared_buffers;
SHOW work_mem;
-- Or view all settings:
SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

Conclusion

PostgreSQL tuning isn't one-size-fits-all. Start with these recommendations based on your RAM, then monitor performance using tools like pg_stat_statements and EXPLAIN ANALYZE. Adjust based on your actual workload—OLTP applications need different settings than data warehouses.

For automated tuning suggestions, check out PGTune which generates configuration based on your hardware specs.