Tune shared buffers and work_mem

Your PostgreSQL server feels sluggish. Queries that should return in milliseconds crawl, and EXPLAIN shows Seq Scan after Seq Scan. You've indexed everything you can think of, yet performance still disappoints. The culprit is often not your queries or your schema — it's how PostgreSQL uses memory. Two settings, shared_buffers and work_mem, control how your database caches data and sorts or joins it. Tune them poorly and you leave performance on the table or crash your server. Tune them well, and your queries fly.

The problem this lesson solves

PostgreSQL is a relational database, but it's also a process that runs on a physical machine with finite RAM. By default, PostgreSQL uses conservative memory settings that work on almost any hardware — but those defaults are terrible for modern servers. The default shared_buffers is 128MB, which is fine for a laptop demo but leaves gigabytes of RAM unused on a production box. The default work_mem is 4MB, which forces PostgreSQL to spill sorts and hash joins to disk far too often. Disk I/O is orders of magnitude slower than RAM, so every spill is a hidden performance killer.

If you've ever run a query that sorts 10 million rows and watched it take minutes, you've felt the pain of untuned work_mem. If you've seen your database sit at 10% CPU while your OS cache handles most reads, you've suffered from a too-small shared_buffers. This lesson solves exactly that: you'll learn how to set these two knobs correctly so your PostgreSQL instance uses memory the way it was designed to — for speed.

Core concept / mental model

Think of PostgreSQL's memory as a layered system, like a kitchen.

• shared_buffers is the countertop — the space where PostgreSQL places frequently used data pages so it can grab them instantly. Every read and write goes through this shared cache. If the countertop is tiny, you constantly run to the pantry (disk) to get ingredients.
• work_mem is the workspace for a single chef (query operation). It's the space used for sorting, hashing, and merging. If the workspace is too small, the chef spills onto the floor (disk) — slow and messy.

Two crucial details separate beginners from pros:

1. shared_buffers is shared — every backend process, every query, every connection uses the same buffer pool. It's the database's global cache.
2. work_mem is per-operation — it's not a global pool. If you have 100 concurrent sorts, each can use up to work_mem. Set it too high and you'll exhaust your RAM with many simultaneous operations.

Here's the mental model in practical terms:

• More shared_buffers = more cache hits = fewer disk reads.
• More work_mem = more sorts/joins in memory = fewer disk spills.
• Both have hard limits dictated by your total RAM and concurrency.

How it works step by step

Tuning these parameters is not magic — it's a systematic process. Here's the general method.

Step 1: Know your hardware

Check your server's total RAM and disk type. This determines your ceiling. Use free -h on Linux.

Step 2: Set shared_buffers

The rule of thumb:

• 25% of total RAM for dedicated database servers (up to a point).
• Never exceed 8GB on most systems due to PostgreSQL's internal addressing and lock contention.
• On Windows or virtualized environments, stay closer to 25% but be conservative.

Why 25%? PostgreSQL relies on the operating system's page cache as a secondary layer. Setting shared_buffers too high (e.g., 60% of RAM) causes double-caching — data lives in both PostgreSQL's cache and the OS cache, wasting memory.

Step 3: Set work_mem

work_mem is trickier because it's per-operation. The formula:

1. Estimate the maximum number of concurrent sort/hash operations you expect (roughly the number of active connections doing complex queries).
2. Start with a modest value like 16MB or 32MB.
3. Increase gradually while monitoring disk spill events in EXPLAIN ANALYZE output.
4. Balance against total RAM: work_mem * max_concurrent_operations should not exceed available RAM.

Step 4: Apply and verify

Edit postgresql.conf, reload, and test with EXPLAIN ANALYZE.

Hands-on walkthrough

Let's put theory into practice. Suppose you have a server with 16GB RAM and you're running a typical web application with about 20 concurrent connections.

1. Inspect current settings

First, see what you're working with:

SHOW shared_buffers;
SHOW work_mem;

Output:

 shared_buffers 
----------------
 128MB
(1 row)

 work_mem 
----------
 4MB
(1 row)

Classic defaults.

2. Calculate target values

• shared_buffers = 25% of 16GB = 4GB
• work_mem = start with 32MB (a safe middle ground)

3. Edit postgresql.conf

sudo nano /etc/postgresql/15/main/postgresql.conf

Change these lines:

shared_buffers = 4GB
work_mem = 32MB

4. Apply the changes

Restart PostgreSQL (required for shared_buffers; work_mem can be reloaded):

sudo systemctl restart postgresql

Or reload for work_mem only:

sudo systemctl reload postgresql

5. Verify and test

Check the new values:

SHOW shared_buffers;
SHOW work_mem;

Output:

 shared_buffers 
----------------
 4GB
(1 row)

 work_mem 
----------
 32MB
(1 row)

Now run a query that benefits from more memory. Imagine you're sorting a large table:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY created_at;

Compare the output before and after the change. Look for lines like:

Sort Method: quicksort  Memory: 29kB
Sort Method: external merge  Disk: 16kB

If you see external merge or Disk:, your work_mem is too small for that query.

6. Fine-tune work_mem per query

You can also set work_mem per session for specific heavy queries:

SET work_mem = '128MB';

Or per user/database:

ALTER ROLE myapp SET work_mem = '64MB';

Compare options / when to choose what

There's no single "best" value — it depends on your workload. Here's a comparison of common approaches.

Considerations:

• Many concurrent queries — lower work_mem to avoid memory exhaustion.
• Large sorts/joins — raise work_mem but monitor concurrency.
• Read-heavy workload — higher shared_buffers reduces disk I/O.
• Write-heavy workload — too much shared_buffers increases checkpoint overhead; stick to 25%.

Pro tip: Use pg_stat_statements to see which queries use the most temporary disk files. Those are the ones that need more work_mem.

Troubleshooting & edge cases

Symptom: Out of memory errors

If PostgreSQL crashes with out of memory, your work_mem * max_concurrent_queries exceeds available RAM. Fix: reduce work_mem or limit connections with max_connections.

Symptom: Sort spills to disk despite high work_mem

Check if your query is using multiple sort operations. Each one gets its own work_mem allocation, so a single complex query can use several times work_mem. Consider restructuring the query or increasing work_mem further.

Symptom: Database uses too little RAM

If shared_buffers is set to 50% of RAM, you may see poor performance due to double caching. The OS cache is also caching the same pages. Stick to ~25% and trust the OS cache for the rest.

Edge case: Windows

On Windows, shared_buffers beyond ~1GB can be counterproductive due to overhead. Keep it modest and rely on OS caching.

Edge case: High concurrency

With hundreds of connections, even work_mem = 32MB can exhaust memory. Use a connection pooler like PgBouncer to reduce connections, or lower work_mem drastically.

What you learned & what's next

You now understand the two most impactful memory settings in PostgreSQL:

• shared_buffers — the global cache for data pages; set to ~25% of RAM.
• work_mem — per-operation memory for sorts and joins; set based on concurrency and workload.

You know how to inspect defaults, calculate appropriate values, apply changes, and verify improvements. Most importantly, you know when to be aggressive and when to be conservative — the sign of a true PostgreSQL professional.

Next in the track, you'll build on this foundation by learning about query planning and indexes — how to design schemas and indexes that make the most of the memory you've configured. Armed with tuned buffers and workspace, your indexes will work even harder. Keep going!