Tune Memory Settings

You’ve been running PostgreSQL for a while, and performance is starting to crawl. Queries that used to return in milliseconds now take seconds, and you suspect it’s not the SQL itself—it’s the memory settings. PostgreSQL’s default configuration is deliberately conservative, designed to run on a laptop, not to squeeze every bit of performance from your production server. In this lesson, you’ll learn how to safely and effectively tune memory settings for performance, turning your database from a sluggish default into a tuned machine.

The problem this lesson solves

Default PostgreSQL settings are like a car with the parking brake on. The shared_buffers default is a meager 128MB, work_mem is just 4MB, and effective_cache_size is a guess that’s often too low. When your database grows and workloads intensify, these defaults cause:

• Excessive disk I/O: Every query that doesn’t fit in cache has to hit the disk, which is orders of magnitude slower than memory.
• Slow sorting and joining: Operations like ORDER BY or JOIN spill to disk when work_mem is too small.
• Poor index scans: The planner underestimates available cache, leading to inefficient index usage.
• Cache thrashing: Multiple backends contend for the same small shared buffer pool, causing frequent evictions.

If you’re seeing high disk read rates or slow queries despite good indexes, your memory settings are likely the bottleneck. This lesson gives you a practical, methodical approach to tuning the three most impactful parameters: shared_buffers, work_mem, and effective_cache_size.

Core concept / mental model

Think of PostgreSQL’s memory as a multi-layered cache system, like a kitchen with different storage areas:

• Shared buffers are the pantry—a common stash all backends (queries) can access, holding recently used data pages.
• Work memory is the chef’s prep counter—a private workspace for sorting and joining, allocated per operation.
• Effective cache size is the estimate of the entire kitchen’s storage, including the pantry and the refrigerator (OS cache).

(In words: shared_buffers → OS cache → disk)

When you tune memory settings for performance, you’re deciding how much of the meal (data) you want on-hand in each layer. Too little, and you’re constantly running to the store (disk). Too much, and you’re blocking the kitchen with bulky items that rarely get used.

The operating system also caches file reads, so PostgreSQL’s shared_buffers and the OS cache work together. This is why effective_cache_size matters: it tells the planner how much total cache is available for index scans.

How it works step by step

Tuning memory isn’t about setting one value—it’s a sequence of informed decisions. Here’s a step-by-step approach:

1. Check your current settings with SHOW or pg_settings. Know your baseline.
2. Set shared_buffers to about 25% of your total server RAM (on dedicated DB servers), capped at safe upper limits.
3. Set work_mem based on expected operation complexity—bigger for complex queries, but beware of high concurrency.
4. Set effective_cache_size to estimate OS cache plus shared_buffers, usually 50-75% of total RAM.
5. Adjust maintenance_work_mem for vacuum and index maintenance operations—higher is better during maintenance windows.
6. Apply changes and test using EXPLAIN (ANALYZE, BUFFERS) to observe buffer usage and query plans.
7. Iterate—monitor performance, watch for problems, and fine-tune values.

Important parameter details

• shared_buffers: The main cache for data pages. Set to 25% of RAM. On Linux, values above 32GB require a restart and special shared memory settings. In a container, it must be lower than the container’s memory limit.
• work_mem: Per-operation memory for sorts and joins. Starts at 4MB; increase for warehouses or complex reports, but consider the product of work_mem × concurrent sorts.
• effective_cache_size: A planner hint, not allocated memory. Set to 50-75% of RAM to encourage index scans.
• maintenance_work_mem: Memory for VACUUM, CREATE INDEX, etc. Set to 50-100MB for small DBs, up to 1GB for large ones. Applies to autovacuum as well.

Pro tip: Always reason in terms of total RAM and workload. A tiny VM with 1GB RAM shouldn’t use 4GB settings—you’ll OOM or cripple the OS.

Hands-on walkthrough

Let’s put this into practice on a server with 8GB of RAM. We’ll start by checking current settings, then change them and observe the impact.

1. View current settings

Run this as a superuser:

SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
SHOW maintenance_work_mem;

Example output:

shared_buffers          | 128MB
work_mem               | 4MB
effective_cache_size   | 524288
maintenance_work_mem   | 64MB

Note that effective_cache_size is displayed in pages (8KB each by default). 524288 pages = 4GB.

2. Set recommended values

Use ALTER SYSTEM to persist changes, then reload:

ALTER SYSTEM SET shared_buffers = '2GB';  -- 25% of 8GB
ALTER SYSTEM SET work_mem = '32MB';       -- 8× default for complex queries
ALTER SYSTEM SET effective_cache_size = '6GB'; -- 75% of RAM
ALTER SYSTEM SET maintenance_work_mem = '256MB'; -- for larger maintenance
SELECT pg_reload_conf();

3. Verify and observe the plan

Restart if the change requires it (shared_buffers on some platforms). Then, run an EXPLAIN on a heavy query:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at;

Before and after tuning, notice the Buffers: line—it shows shared hit vs shared read. Fewer reads and more hits mean better cache usage.

4. Test with a realistic workload

If you have pgbench installed, run a quick benchmark:

pgbench -i -s 20 mydb
time pgbench -c 8 -j 8 -T 60 mydb

Run it before and after tuning. When you tune memory settings for performance, you should see higher transactions per second (tps) and lower latency.

Compare options / when to choose what

Not every setting suits every scenario. Here’s a comparison to guide your choices:

Key trade-off: Increasing work_mem per operation can multiply quickly. With 100 concurrent queries, even 32MB each means 3.2GB of memory pressure. Balance is critical.

Troubleshooting & edge cases

• PostgreSQL won’t start after increasing shared_buffers: On Linux, you may need to raise kernel shmmax/shmall. Run sysctl -w kernel.shmmax=<bytes> and add to /etc/sysctl.conf. In Docker, increase the container’s memory limit.
• Out of memory (OOM): If you see “out of memory” errors, your work_mem is too high relative to connection count. Reduce it or tune connection limits. Use max_connections × work_mem as a worst-case estimate.
• No performance improvement: Check if the database is actually hitting memory limits. Use pg_stat_database (see blks_read vs blks_hit) and EXPLAIN (BUFFERS). Maybe your workload is I/O-bound elsewhere (e.g., disk slow, not cache miss).
• Autovacuum runs too often or too slow: maintenance_work_mem affects vacuum speed and dead-tuple cleanup. If it’s too low, vacuum takes longer and can lag behind workload.

Pro tip: Always benchmark before and after using a realistic workload. Don’t guess—measure the changes.

What you learned & what's next

You now know how to tune memory settings for performance by adjusting shared_buffers, work_mem, effective_cache_size, and maintenance_work_mem. You can check current values, set them via ALTER SYSTEM, and observe the effect with EXPLAIN (BUFFERS). You understand the trade-offs between memory per operation and concurrency, and you know how to troubleshoot common issues like OOM and startup failures.

Your next step is to learn about checkpoint tuning or max_connections, which build on this memory foundation. With memory tuned, you’ll want to look at query parallelization to further cut latency. Keep those metrics in mind as you move forward—consistent performance demands both correct settings and ongoing monitoring.