Scrolls
6 min read
On this page

Configuration Tuning

The Settings That Actually Matter

PostgreSQL ships with conservative defaults designed to run on minimal hardware. A fresh installation allocates 128 MB for shared buffers, assumes spinning disks, and allows 100 connections. These defaults are safe but leave enormous performance on the table for modern servers with gigabytes of RAM and SSDs.

The good news: tuning Postgres is not a dark art. A handful of settings account for 90% of the performance difference between a default installation and a properly tuned one. Get these right and you have covered most of what matters.

shared_buffers: The Database Cache

shared_buffers is the amount of memory Postgres uses for its internal buffer cache. Every page read from disk goes through shared_buffers. If the page is already in the cache, the disk read is avoided entirely.

# postgresql.conf
shared_buffers = 8GB    # 25% of total RAM is the starting point

The rule of thumb: set shared_buffers to 25% of total system RAM. On a server with 32 GB of RAM, start with 8 GB. On a server with 64 GB, start with 16 GB.

Why not more? Because the operating system also caches files. Postgres reads go through both shared_buffers and the OS page cache. Setting shared_buffers to 50% or more of RAM leaves too little for the OS cache, which often hurts overall performance.

-- Check current shared_buffers
SHOW shared_buffers;

-- Check if shared_buffers is effective (cache hit ratio)
SELECT
    sum(heap_blks_hit) AS cache_hits,
    sum(heap_blks_read) AS disk_reads,
    round(100.0 * sum(heap_blks_hit) /
        greatest(sum(heap_blks_hit) + sum(heap_blks_read), 1), 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

If your cache hit ratio is below 99%, consider increasing shared_buffers (assuming you have the RAM). If it is already 99%+, increasing shared_buffers will have diminishing returns.

Changes to shared_buffers require a restart.

effective_cache_size: The Planner Hint

effective_cache_size does not allocate memory. It tells the query planner how much total memory is available for caching, including both shared_buffers and the OS page cache. This influences whether the planner chooses index scans (which benefit from caching) or sequential scans.

# postgresql.conf
effective_cache_size = 24GB   # 75% of total RAM

Set it to 75% of total RAM. On a 32 GB server, use 24 GB. This tells the planner that most of the server's memory is available for caching data, which encourages index usage.

Setting this too low makes the planner pessimistic about caching, leading to more sequential scans. Setting it too high does not cause harm because it does not allocate anything, but it makes the planner overly optimistic.

-- Check current setting
SHOW effective_cache_size;

This setting can be changed without a restart (reload only).

work_mem: Per-Operation Sort Memory

work_mem is the amount of memory available for each sort operation, hash table, or bitmap in a query. When a sort exceeds work_mem, Postgres spills to disk, which is dramatically slower.

# postgresql.conf
work_mem = 16MB   # Start conservative

The catch: work_mem is per operation, not per query and not per connection. A single complex query with 5 sort/hash operations and 3 parallel workers could use up to 5 * 3 * work_mem = 240 MB from a 16 MB setting. Multiply that by your connection count and you can exhaust server memory.

-- See how work_mem affects a query
SET work_mem = '4MB';
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at;
-- Look for "Sort Method: external merge Disk" (bad - spilling to disk)

SET work_mem = '64MB';
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at;
-- Look for "Sort Method: quicksort Memory" (good - in-memory sort)

Start with 4-16 MB globally. For specific expensive queries, set it higher at the session or transaction level:

-- Increase work_mem for a specific reporting query
SET LOCAL work_mem = '256MB';
SELECT department, avg(salary) FROM employees GROUP BY department ORDER BY avg(salary) DESC;
RESET work_mem;

maintenance_work_mem: For Maintenance Operations

maintenance_work_mem controls memory for VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, and similar maintenance operations. These operations benefit enormously from more memory.

# postgresql.conf
maintenance_work_mem = 2GB   # Can be much higher than work_mem

Set it to 1-2 GB on servers with 32+ GB of RAM. Maintenance operations run less frequently than queries, so the memory is only used briefly. Higher values dramatically speed up index creation and VACUUM.

# A large CREATE INDEX with default maintenance_work_mem might take 30 minutes
# With 2GB, it might take 5 minutes

-- Check current setting
SHOW maintenance_work_mem;

This is separate from work_mem for good reason. You want work_mem conservative (many concurrent operations) and maintenance_work_mem generous (few concurrent operations, each benefits from more memory).

max_connections: Lower Than You Think

The default max_connections is 100. Many people increase it to 500 or 1000. This is almost always wrong.

# postgresql.conf
max_connections = 100    # Keep this low, use connection pooling

Each Postgres connection is a separate OS process consuming 5-10 MB of memory. At 500 connections, that is 2.5-5 GB just for connection overhead, before any queries run. Additionally, Postgres performance degrades with many concurrent active connections due to lock contention, context switching, and snapshot management.

The correct architecture:

Application (1000 concurrent users)
    |
    v
PgBouncer (pool of 20-50 connections)
    |
    v
PostgreSQL (max_connections = 100)

With connection pooling, 20-50 actual database connections handle thousands of application requests. Set max_connections to 2-4x your pooler's pool size to leave room for administrative connections, monitoring, and replication.

-- See current connection usage
SELECT count(*) AS used, max_conn AS total,
       count(*) * 100 / max_conn AS usage_pct
FROM pg_stat_activity,
     (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') AS mc
GROUP BY max_conn;

random_page_cost: Adjust for SSDs

random_page_cost tells the planner how expensive a random disk read is relative to a sequential read. The default is 4.0, which assumes spinning disks where random I/O is 4x slower than sequential I/O.

# postgresql.conf
random_page_cost = 1.1    # For SSDs
# random_page_cost = 4.0  # For spinning disks (default)

On SSDs, random reads are nearly as fast as sequential reads. Set random_page_cost to 1.1 for SSD storage. This encourages the planner to use index scans more aggressively, which is the right choice on fast storage.

If you leave this at 4.0 on an SSD-backed server, the planner overestimates the cost of index scans and falls back to sequential scans unnecessarily.

-- Check current setting
SHOW random_page_cost;

-- Change without restart
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

Other Important Settings

wal_buffers

# postgresql.conf
wal_buffers = 64MB   # Default is often too low, -1 means auto (1/32 of shared_buffers)

WAL buffers hold WAL data before it is written to disk. The auto setting (-1) works for most cases. If you have a write-heavy workload, explicitly setting 64 MB ensures enough buffer space.

checkpoint_completion_target

# postgresql.conf
checkpoint_completion_target = 0.9   # Spread checkpoint I/O over 90% of the interval

This spreads checkpoint writes over the checkpoint interval, avoiding I/O spikes. The default of 0.9 (Postgres 14+) is correct. On older versions, change it from 0.5 to 0.9.

max_wal_size & min_wal_size

# postgresql.conf
max_wal_size = 4GB    # Allow more WAL before forcing a checkpoint
min_wal_size = 1GB    # Keep at least this much WAL

Higher max_wal_size means fewer checkpoints, which reduces I/O spikes but increases recovery time after a crash. 4-8 GB is a good starting point for busy systems.

huge_pages

# postgresql.conf
huge_pages = try    # Use huge pages if available

Huge pages (2 MB instead of 4 KB) reduce TLB misses for shared_buffers. On Linux, configure the OS to provide huge pages, then set this to 'try' or 'on'.

The Quick-Start Configuration

For a dedicated Postgres server with 32 GB RAM and SSD storage:

# postgresql.conf
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 16MB
maintenance_work_mem = 2GB
max_connections = 100
random_page_cost = 1.1
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
huge_pages = try

For a shared server with 8 GB RAM:

# postgresql.conf
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 8MB
maintenance_work_mem = 512MB
max_connections = 50
random_page_cost = 1.1

How to Apply Changes

-- Check if a setting requires restart
SELECT name, setting, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections', 'random_page_cost');

      name       | setting |  context
-----------------+---------+-----------
 shared_buffers  | 1048576 | postmaster   -- requires restart
 work_mem        | 4096    | user         -- can be changed per session
 max_connections | 100     | postmaster   -- requires restart
 random_page_cost| 4       | user         -- can be changed with reload

# Apply changes that only need a reload
sudo systemctl reload postgresql

# Apply changes that require a restart
sudo systemctl restart postgresql

Common Pitfalls

  • Increasing max_connections instead of adding connection pooling. 500 direct connections will perform worse than 50 pooled connections. Always pool.
  • Setting work_mem too high globally. A 256 MB work_mem with 100 connections and parallel queries can exhaust server RAM. Keep it low globally and raise it per session when needed.
  • Leaving random_page_cost at 4.0 on SSDs. This causes the planner to avoid index scans that would be fast on your hardware.
  • Allocating too much to shared_buffers. More than 25-40% of RAM starves the OS page cache and can hurt total performance. More is not always better.
  • Tuning without measuring. Change one setting at a time and measure the effect. Benchmark before and after. Use pg_stat_statements to track query performance changes.
  • Copying configuration from the internet without understanding it. A configuration tuned for a 256 GB analytics server will be wrong for an 8 GB application server. Understand each setting relative to your hardware.
  • Forgetting to restart after changing postmaster-level settings. shared_buffers, max_connections, and wal_buffers require a full restart, not just a reload.

Key Takeaways

  • shared_buffers at 25% of RAM and effective_cache_size at 75% of RAM are the starting points that cover most cases.
  • work_mem should be conservative globally (4-16 MB) and raised per session for expensive queries. It is per operation, not per connection.
  • maintenance_work_mem can be generous (1-2 GB) because maintenance operations are infrequent.
  • max_connections should be low (50-200) with connection pooling in front. More connections is not better.
  • random_page_cost should be 1.1 for SSD storage. Leaving it at 4.0 penalizes index scans unnecessarily.
  • Tune one setting at a time, measure the effect, and use pg_stat_statements to track changes in query performance.