Why Increasing shared_buffers Didn’t Fix My PostgreSQL Sort: Part 2

Disk spills, external merge sorts, and the biggest caching myth in PostgreSQL

A Quick Recap

**(From Blog 1)**

In the previous article, I doubled my server RAM.

PostgreSQL barely got faster.

Even with more memory available at the OS level, the execution plan didn’t change. The ORDER BY still spilled to disk, and performance gains were marginal at best.

⁉️ That result naturally raised the next question: if OS memory didn’t help, what about PostgreSQL’s own cache?

❌ More system RAM alone does not fix PostgreSQL sort performance

“The next Move: Increase shared_buffers"

If PostgreSQL is slow, most tuning guides point to one parameter:

⠀ ⠀ ⠀ ⠀ ⠀ ⠀⠀󠀠󠀠󠀠 ⠀⠀ ⠀ ⠀ ⠀ ⠀ ⠀❝ shared_buffers❞⠀ ⠀ ⠀ 󠀠⠀ ⠀

The assumption is simple: if PostgreSQL can cache more data internally, expensive operations like large ORDER BY sorts should stop hitting disk.

Group B Experiment — Bigger shared_buffers

Scenario B1

In Scenario B1, only one variable changed (shared_buffers). Every other factor remained identical to Group A.

| Item           | Scenario A1 | Scenario A2 | Scenario B1  |
| -------------- | ----------- | ----------- | -----------  |
| System RAM     | 10 GB       | 20 GB       |  20 GB       |
| shared_buffers | 2560 MB     | 2560 MB     |   5 GB  🟢   |
| work_mem       | 4 MB        | 4 MB        |   4 MB       |
| Index          | ❌ No       | ❌ No        |  ❌ No       |
| Query          | Same        | Same        |   Same       |
| Dataset size   | ~6.9 GB     | ~6.9 GB     | ~6.9 GB      |

Now in Scenario B1, only one thing changed:

╰┈➤ shared_buffers doubled from 2.5 GB → 5 GB

🎯 What This Scenario Is Testing

This scenario isolates one question:

If PostgreSQL is given more internal cache, will a large ORDER BY stop spilling to disk — without changing sort memory or query shape?

We are not:

☰ Changing work_mem

☰ Adding indexes

☰ Changing query shape

So any behavioral change must come from shared_buffers alone.

🔍 Query Used for All Tests

To keep results comparable with Blog 1, the exact same query was used:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, category, amount,payload FROM sb_big ORDER BY payload;

⏱️ Execution Time

At first glance, the execution time in Scenario B1 looked… encouraging.

“It got faster — so shared_buffers worked?”

With shared_buffers doubled from 2.5 GB → 5 GB, the query completed slightly faster than in Group A.

But execution time alone doesn’t tell the full story.

| Scenario | System RAM   | shared_buffers | Execution Time |
| -------- | ------------ | -------------- | -------------- |
|     A1   |   10 GB      |   2.5 GB       | ~90 sec        |  
|     A2   |   20 GB      |   2.5 GB       | ~69 sec        | 
|     B1   |   20 GB      |     5 GB       | ~72 sec  🟢    |

✅ While execution time changed slightly, the execution plan and sort behavior did not — which matters far more than raw runtime.

— — —

This is the same pattern we saw in Group A: small runtime gains without any change in execution strategy.

💾 Sorting Behavior — Still Disk-Based

Sort Method: external merge
Disk: ~968 MB per worker

Despite:

• 20 GB system RAM
• 5 GB PostgreSQL shared memory

The sort still spills to disk.

shared_buffers has zero influence on whether a sort fits in memory.

*shared_buffers helps PostgreSQL read data faster, not reorder data differently.*

🚧 Temp File Usage — No Meaningful Reduction

+----------+------------+----------------+-------------+--------------+
| Scenario | System RAM | shared_buffers | temp read   | temp written |
+----------+------------+----------------+-------------+--------------+
| A1       | 10 GB      | 2.5 GB         | ~1,090,000  | ~1,090,000   |
| A2       | 20 GB      | 2.5 GB         | ~1,090,000  | ~1,090,000   |
| B1       | 20 GB      | 5 GB           | 1,089,595   | 1,090,601    |
+----------+------------+----------------+-------------+--------------+

🔍 Observation from the Temp File Usage

ㅤㅤⓘ 󠀠 Temp file usage remained identical across all three scenarios

⠀ ⠀ⓘ 󠀠 Doubling system RAM did not reduce temp I/O

ㅤㅤⓘ 󠀠 Despite Doubling shared_buffers , did not reduce temp I/O

⠀ ⠀ⓘ 󠀠 PostgreSQL still had to write ~1 GB of sort data per worker to disk

⚠️ The sort never moved into memory.

Temp files are not a side effect — they are the core symptom of disk-based sorting.

As we see:

Sort Method: external merge
temp read ≈ temp written ≈ millions of blocks

It means:

PostgreSQL is still bottlenecked by work_mem, not by cache size. No amount of extra caching can change this behavior.

Conclusión

• If temp file usage doesn’t drop, the sort problem is still unsolved.
• Group B proves that increasing shared_buffers improves caching efficiency — but it does nothing to prevent disk-based sorting. For ORDER BY, the bottleneck isn’t cache size; it’s sort memory.

And that leads directly to the next question🤔❓:

Which PostgreSQL memory setting actually controls sort behaviour?And that leads directly to **Part 3 of this series**:

Group C (Part 3)— Which PostgreSQL memory setting actually controls sort behaviour?

In the next blog, we isolate work_mem — and for the first time in this series, PostgreSQL’s sort behaviour truly changes.

PostgreSQL #Postgres #work_mem #shared_buffers #Database #SQL