PostgreSQL Tuning: shared_buffers, work_mem, effective_cache_size

Default PostgreSQL configuration is tuned for modest hardware and is inefficient on modern servers. shared_buffers = 128MB, work_mem = 4MB — these settings leave 95% of memory unused. For example, a server with 32 GB RAM using default settings uses only 128 MB for cache — the database idles while queries lag. Proper tuning yields at least 30% performance gain and reduces disk subsystem load. We configure based on your profile: OLTP, analytics, or mixed. Our team has completed 50+ successful projects, with a guarantee of results.

PostgreSQL tuning is not just "set numbers higher" — it's understanding how the planner uses memory, how caching works, and how to avoid I/O bottlenecks. Adjusting shared_buffers, work_mem, and effective_cache_size is fundamental but critical. Incorrect configuration leads to swapping or RAM underutilization. Our engineers analyze your server and workload to find optimal values.

Problems We Solve

Insufficient work_mem for Analytics

Slow reports due to sorts spilling to disk. A typical query with ORDER BY on a large table takes minutes when the plan shows disk sort. This is fixed by increasing work_mem for specific queries or creating covering indexes.

Incorrect effective_cache_size

The planner chooses sequential scans over index scans because it thinks the cache is small. Setting effective_cache_size to 75% of RAM immediately increases index scan usage.

shared_buffers Conflict with OS Cache

Too large shared_buffers (above 25% RAM) competes with the operating system's cache, reducing cache hit ratio. Checking via pg_buffercache helps find the optimum.

How We Do It

We use a proven methodology: audit current configuration, analyze query plans, and tune parameters to the workload. Example: an e-commerce site with 10,000 queries per minute. After tuning, report execution time dropped from 5 minutes to 20 seconds, cache hit ratio rose from 97% to 99.8%.

Tool Stack

• PostgreSQL 14–17
• pgtune for initial estimation
• pg_buffercache for buffer monitoring
• EXPLAIN ANALYZE for query analysis
• pg_stat_statements for identifying heavy queries

Tuning Process

1. Audit current configuration and workload profile
2. Collect metrics: cache hit ratio, buffer usage, query plans
3. Set shared_buffers — 25% RAM for dedicated server
4. Set work_mem — 4–64 MB for OLTP, 256 MB–1 GB for analytics
5. Set effective_cache_size — 75% RAM
6. Optimize planner: random_page_cost for SSD, parallel query parameters
7. Configure checkpoint and WAL for disk type (SSD/HDD)
8. Monitor hit rate and pg_buffercache after changes
9. Document all changes
10. 30-day guarantee: if performance doesn't improve, we re-tune for free

Memory Parameter Tuning

How to Set shared_buffers for OLTP?

The database's global page cache for all processes. For a dedicated server — 25% RAM. On a 32 GB server, that is 8 GB. Above 25% may conflict with OS cache. Check if shared_buffers is sufficient via hit ratio: if cache_hit_ratio < 99%, either shared_buffers is small or the working set doesn't fit in memory. Use pg_buffercache to see which tables and indexes occupy the buffer. Increase shared_buffers to up to 25% RAM, but no more than 8 GB on Linux due to architectural limits.

What to Do When cache_hit_ratio Is Low?

If cache_hit_ratio is below 99%, tuning is needed. Check shared_buffers — may need increase. Also consider adding indexes. For analytical queries, increasing work_mem may help. Use the query from the code block to check hit rate.

Why a Small work_mem Is Often Better Than a Large One

work_mem is memory per sort/hash join operation within a query. If a query has 3 sort nodes, it can consume 3 × work_mem. At 100 concurrent connections with heavy queries, usage could be 100 × 3 × work_mem. Too high a value causes swapping. A common mistake: setting 64 MB globally, while 100 connections with 4 sorts each = 100 × 4 × 64 MB = 25.6 GB. Start with 16 MB, increase for specific queries via SET LOCAL. For OLTP workloads, high work_mem leads to memory overuse and performance degradation due to swapping. Our method: analyze query plans, identify sorts on disk, increase work_mem only for problematic queries.

effective_cache_size: A Simple Hint to the Planner

A hint to the planner about available OS cache + shared_buffers. For a 32 GB server: 24 GB. Influences the choice between index scan and seq scan. Does not reserve memory but is critical for correct plan selection. More details in the official PostgreSQL documentation. Recommended setting: 75% of RAM.

maintenance_work_mem: For Maintenance Operations

For VACUUM, CREATE INDEX, ALTER TABLE. Increase only during maintenance. A value of 2 GB suffices for most tasks. Do not keep it high permanently — it saves memory.

Tuning the Planner and Performance Monitoring

Cost Parameters and Parallel Queries

# Cost model for SSD random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0 (default) seq_page_cost = 1.0 # Parallel queries (PostgreSQL 9.6+) max_parallel_workers_per_gather = 4 max_parallel_workers = 8 parallel_tuple_cost = 0.1 parallel_setup_cost = 1000.0 

Monitoring Hit Rate and Buffer Cache

-- Cache hit ratio SELECT sum(heap_blks_hit) AS heap_hit, sum(heap_blks_read) AS heap_read, round( sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2 ) AS cache_hit_ratio FROM pg_statio_user_tables; -- Buffer usage details CREATE EXTENSION IF NOT EXISTS pg_buffercache; SELECT c.relname, count(*) AS buffers, round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = c.relfilenode GROUP BY c.relname ORDER BY buffers DESC LIMIT 20; 

If cache_hit_ratio < 99% — tuning shared_buffers or adding an index is needed.

Tuning by Workload: OLTP, Analytics, Mixed

Checkpoint and WAL

checkpoint_completion_target = 0.9 checkpoint_timeout = 15min max_wal_size = 4GB fsync = on synchronous_commit = on 

Workload Profile Comparison

Practical Example: Sort Optimization

A query slowly executes ORDER BY on a large table — sort goes to disk via temporary file:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM events WHERE user_id = 1 ORDER BY created_at DESC LIMIT 100; 

If output shows "Sort Method: external merge Disk: 45678kB" — need an index or more work_mem.

CREATE INDEX CONCURRENTLY idx_events_user_date ON events(user_id, created_at DESC) INCLUDE (id, event_type, payload); 

Applying Changes

After changing parameters, run SELECT pg_reload_conf(); to apply.

Common PostgreSQL Tuning Mistakes

Conclusion

Proper PostgreSQL tuning yields significant performance gains and infrastructure savings. We guarantee at least 30% improvement or we re-tune for free within 30 days. Contact us for a consultation and order professional PostgreSQL tuning.