Buffers and JIT: cache, IO, and query compilation

Capsule description

The same query with the same plan can take 5ms or 500ms depending on two factors that don't appear in the structural plan:

1. Was the data in RAM or did it have to be read from disk? The Buffers: section tells you.
2. Did PostgreSQL compile the query with JIT? That appears as a JIT: section at the end of the plan.

Without these two signals, two things that happen often become a mystery:

• "Why does the first time I run the query take 200ms and the following ones 5ms?" → Buffers (cold cache vs warm cache).
• "Why does this simple OLTP query inexplicably take 80ms when it used to be 5ms?" → JIT accidentally activated on a short query, where the compilation costs more than the execution.

This capsule breaks down:

• shared hit vs shared read vs shared dirtied: what each one means, how to compute the cache hit ratio.
• Local and temp buffers: when they appear, what they tell you about your query.
• JIT compilation: what it does, when it's activated by default, when to disable it.
• CPU-bound vs IO-bound diagnosis: the most important decision these numbers let you make.

By the end, you'll be able to look at the Buffers: section of any plan and diagnose whether the bottleneck is disk or computation. It's the last technical skill before closing out the module with visual tools (capsule 07) and the project (capsule 08).

Mental model: the shared buffer cache is PostgreSQL's "hot memory"

PostgreSQL doesn't read directly from disk every time it needs a page. It has a shared buffer cache (shared_buffers, default 128MB but typically configured to 25% of RAM) — a shared memory area where it keeps the most-used pages.

When a query needs a page:

1. Is it in shared_buffers? Yes → it reads it from RAM (shared hit). Microseconds.
2. No? → it reads it from disk (or, ideally, from the operating system's OS page cache, which is also RAM but not controlled by PostgreSQL). It counts as shared read. Milliseconds.

                        ┌────────────────────────────────────┐
                        │  Your query requests page X        │
                        └─────────────┬──────────────────────┘
                                      │
                                      ▼
                        ┌────────────────────────────────────┐
                        │  Is X in shared_buffers (RAM)?     │
                        └─────────────┬──────────────────────┘
                                      │
                       ┌──────────────┴──────────────┐
                       │                              │
                      YES                            NO
                       │                              │
                       ▼                              ▼
              ┌─────────────────┐         ┌──────────────────┐
              │  shared hit++   │         │  shared read++   │
              │  ~1μs (RAM)     │         │  ~0.1-10ms       │
              └─────────────────┘         │  (disk or OS     │
                                          │   page cache)    │
                                          └──────────────────┘

Cache hit ratio = shared hit / (shared hit + shared read).

• 100% hit ratio = everything in RAM, CPU-bound query.
• 50% hit ratio = half the pages hit disk, partially IO-bound query.
• 0% hit ratio = everything from disk, fully IO-bound query (typical in queries that scan tables larger than RAM).

Diagnosis:

• High cache hit ratio + slow query → CPU-bound. Solutions: simplify the query, better indexing to reduce processed rows, parallelization.
• Low cache hit ratio + slow query → IO-bound. Solutions: more RAM (shared_buffers), warming, better indexing to touch fewer pages, partitioning.

Without this distinction, you apply the wrong solution to the wrong problem.

The Buffers: section in detail

With EXPLAIN (ANALYZE, BUFFERS) <query>, each node adds lines like:

Buffers: shared hit=812 read=24 dirtied=180 written=120 local hit=4 read=2 temp read=850 written=850

Let's go through each category.

Shared buffers (reading tables and indexes)

For read queries, you mainly look at hit and read. For write queries, you add dirtied.

Local buffers (temporary tables)

Buffers: local hit=4 read=2

They appear only if the query uses CREATE TEMP TABLE or internal temporary tables. Rare in typical OLTP queries.

Temp buffers (sorts/hashes that spill to disk)

Buffers: shared hit=200, temp read=850 written=850

This is a warning sign. If you see significant temp read/written, your query is using disk for operations that ideally fit in RAM. Typical solution: raise work_mem (per-session, not global) or rewrite the query.

-- For this session, raise work_mem
SET work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS) <your query>;
-- If temp_read/written disappears or drops, the problem was the default work_mem (4MB).

Caveat: work_mem is per sort/hash operation, not per query. A query with 3 sorts and 2 hashes can use 5x work_mem. Raising it globally to 64MB with many concurrent connections can saturate RAM. Raising it per session (SET) or per user is safer.

Example: reading Buffers in a complete plan

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT b.id, b.title
FROM books b
JOIN authors a ON a.id = b.author_id
WHERE a.name = 'tolkien';

Hash Join  (cost=12.34..1850.20 rows=22 width=18) (actual time=0.245..1.823 rows=22 loops=1)
  Hash Cond: (b.author_id = a.id)
  Buffers: shared hit=812
  ->  Seq Scan on public.books b  (cost=0.00..1620.00 rows=100000 width=22)
      (actual time=0.013..1.245 rows=100000 loops=1)
        Buffers: shared hit=810
  ->  Hash  (cost=12.32..12.32 rows=1 width=4)
      (actual time=0.142..0.142 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        Buffers: shared hit=2
        ->  Seq Scan on public.authors a  (cost=0.00..12.32 rows=1 width=4)
            (actual time=0.118..0.140 rows=1 loops=1)
              Filter: (name = 'tolkien'::text)
              Rows Removed by Filter: 4999
              Buffers: shared hit=2
Planning:
  Buffers: shared hit=42
Planning Time: 0.412 ms
Execution Time: 1.901 ms

Reading the buffers:

• Total query: shared hit=812, shared read=0 → 100% cache hit. CPU-bound query.
• Seq Scan books: 810 hit (the table is in cache).
• Seq Scan authors: 2 hit (small table, everything fits in 2 pages).
• Hash Join: 0 buffers of its own (it inherits them from the children).
• Planning: 42 hit (the planner consulted pg_class, pg_attribute, etc., all cached).

Diagnosis: the query touches ~812 pages (~6.3 MB of data). Everything in RAM. Time almost entirely CPU. If it were slow, you wouldn't solve it with more RAM — you'd solve it with fewer pages to touch (better indexing, a more selective query).

Cache miss: example

If the table is cold (you just restarted, or the database is larger than shared_buffers):

Seq Scan on books  (cost=0.00..1620.00 rows=100000 width=22)
  (actual time=0.812..18.245 rows=100000 loops=1)
  Buffers: shared hit=20 read=790

• 20 hit + 790 read.
• Cache hit ratio: 20 / (20 + 790) = 2.5%.
• IO-bound. The time rose from 1.2ms to 18ms — the 790 disk reads dominate.

If you run the same query immediately afterward:

Seq Scan on books
  (actual time=0.012..1.245 rows=100000 loops=1)
  Buffers: shared hit=810

Now 100% cache hit (the pages are already in shared_buffers after the first read). This is the difference between "cold cache" and "warm cache".

Implication for benchmarks: the first execution of a query after a restart is going to be dramatically slower. For an honest baseline, always warm up (run the query 3-5 times before measuring, discard the first run). Capsule 03 of module 1 covered this from another angle.

JIT Compilation: the double-edged sword

Starting with PostgreSQL 11, the executor can compile parts of the query to native code (LLVM) at runtime. This is what's called JIT (Just-In-Time) compilation.

When it activates: automatically, when the query's estimated total_cost exceeds jit_above_cost (default 100,000).

What it's for: it speeds up expression evaluation, filter predicates, tuple deserialization. Designed for long OLAP (analytical) queries where spending 50-200ms compiling once and saving hundreds of ms in execution is a good trade.

When it's counterproductive: OLTP queries the planner overestimated. If a query estimated cost=200,000 but really returns 10 rows in 5ms, JIT compiles anyway and adds 50-100ms of overhead. The "fast" query becomes "mysteriously slow".

What JIT looks like in the plan

At the end of the output:

JIT:
  Functions: 12
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.621 ms, Inlining 8.245 ms, Optimization 32.455 ms, Emission 18.234 ms, Total 59.555 ms
Execution Time: 245.812 ms

Reading:

• 12 compiled functions.
• The 4 options (Inlining, Optimization, Expressions, Deforming) are active.
• Total JIT timing: ~60ms (Generation + Inlining + Optimization + Emission).
• Execution Time: 245ms total.

If the "real" query (without JIT) was 100ms, JIT added 60ms to save (in theory) time on the evaluation. If the net gain is <60ms, JIT got in the way.

When to disable JIT

Typical cases:

1. Frequent OLTP queries the planner overestimates. If all your short queries have 50ms of JIT overhead, disable it globally or per session.
2. Before/after a benchmark to see how much JIT contributed. If the time is similar with/without JIT, JIT doesn't help.

-- Per session
SET jit = off;

EXPLAIN (ANALYZE, BUFFERS) <query>;

-- Re-enable
SET jit = on;

-- Globally (postgresql.conf)
jit = off

Or raise the threshold so it only activates on truly expensive queries:

ALTER SYSTEM SET jit_above_cost = 500000;  -- from 100k to 500k
SELECT pg_reload_conf();

Detecting a JIT problem

Quick heuristic: if your plan has JIT: and the total JIT timing is >20% of the Execution Time, JIT is probably not helping — it's getting in the way.

JIT:
  Total 60 ms
Execution Time: 80 ms

→ JIT is 75% of the total time. The "real" query took 20ms. JIT stole 60ms from you. Disable it.

CPU-bound vs IO-bound diagnosis: the key decision

This is the most important part of the capsule. When a query is slow, the first question the plan has to answer is: is the bottleneck CPU or IO? They have opposite solutions.

CPU-bound case

Buffers: shared hit=10000 read=0
Execution Time: 250 ms

• Cache hit ratio: 100%.
• Time: 250ms.
• No disk involved. The time goes to computation: filters, sorts, aggregates, joins.

Solutions (covered in following modules):

• More selective indexes to reduce processed rows (module 3).
• Correct eager loading to avoid N+1 (module 4).
• JIT (if it's OLAP) or disabling JIT (if it's short OLTP).
• More cores / parallelization.
• Rewriting complex queries.

IO-bound case

Buffers: shared hit=200 read=10000
Execution Time: 2500 ms

• Cache hit ratio: 200 / 10200 = ~2%.
• Time: 2500ms.
• The bottleneck is reading 10,000 pages (80MB) from disk.

Solutions:

• More RAM for shared_buffers (more fits in cache).
• Warming (running critical queries at startup to preheat the cache).
• Indexes that reduce the pages read (an Index Scan touches fewer pages than a Seq Scan).
• Partitioning (segmenting large tables so each query touches only one partition).
• SSDs (if you're still on HDD).
• pg_prewarm extension for explicit warming.

Mixed case

Buffers: shared hit=5000 read=3000 temp read=850 written=850
Execution Time: 1200 ms

• 62% cache hit (mixed).
• There's disk activity for the table (3000 reads) and for sorts/hashes (temp read/written).
• Time: 1200ms.

Diagnosis:

• Partially IO-bound due to the table (3000 reads).
• Partially IO-bound due to temp files (operations that didn't fit in work_mem).

Double solution:

• Raise work_mem to avoid the spill to disk.
• Improve indexing to reduce pages read from the table.

Why does this matter in real work?

1. Distinguishing CPU-bound from IO-bound is 50% of the diagnosis.

Without knowing whether your query is slow due to disk or CPU, you'll apply the wrong solution. Raising shared_buffers doesn't speed up a CPU-bound query; adding indexes doesn't speed up a query that already hit disk with random fetches.

2. Detecting JIT overhead in OLTP queries.

JIT enabled by default in PG 11+ is good for analytical warehouses but a bad default for OLTP APIs. Detecting it in plans and disabling it selectively can recover 50-100ms in short queries.

3. Deciding work_mem tuning.

temp read/written in plans is the direct signal that work_mem is small. It's the concrete input for raising it.

4. Conversations with SREs / DBAs about infrastructure.

"We need more RAM" is an expensive conversation. Going from "I think so" to "95% of the top queries have a cache hit ratio <30%, that's why we need to raise shared_buffers" changes the conversation. Buffers are the evidence.

5. Analyzing regressions due to cold cache.

After a restart or failover, the cache is cold. The first queries are slow until it warms up. If you see a latency spike post-deploy, looking at the buffers of the slow queries confirms "it's cold cache, it'll normalize" vs "there's a real problem".

Traps and common mistakes

Mistake 1 (omission): not including BUFFERS in the incantation

Symptom: "I ran EXPLAIN ANALYZE, I don't understand why the query is slow."

Why it happens: without BUFFERS, you can't distinguish CPU vs IO. The diagnosis becomes guesswork.

How to fix it: always EXPLAIN (ANALYZE, BUFFERS, VERBOSE). Capsule 02 already said it, but here you saw it live.

Mistake 2 (interpretation): assuming shared read = 0 means "fast"

Symptom: "Cache hit 100%, it should be instant."

Why it's sometimes wrong: 100% cache hit only says "I didn't read disk". But you can have 50,000 hits (~400MB of pages in RAM) — each hit costs CPU to process, decode, evaluate filters. CPU-bound.

How to fix it: look at both: shared hit/read to diagnose IO, actual time for total time. 100% cache hit + high time = CPU-bound.

Mistake 3 (operational): the first execution as a benchmark

Symptom: "This query takes 200ms, it's bad." You re-run: "Now 5ms, what happened?"

Why it happens: the first execution with cold cache. The pages weren't in shared_buffers, they had to be read from disk.

How to fix it: warmup. Run the query 3-5 times, discard the first. For serious benchmarking (module 1), this is standard.

Mistake 4 (conceptual): raising shared_buffers for CPU-bound queries

Symptom: "The query is slow, I'm going to raise shared_buffers to 4GB."

Why it's sometimes wrong: if your query already has a 100% cache hit ratio, more RAM doesn't speed it up. It's CPU-limited. Raising shared_buffers can even make it worse (beyond a certain point, PostgreSQL dedicates less memory to the OS page cache, work_mem, etc.).

How to fix it: decide based on evidence. Low cache hit ratio → more shared_buffers helps. High cache hit ratio + high time → other solutions (indexing, query rewrite, JIT).

Mistake 5 (subtle): JIT in OLTP queries

Symptom: "This query became inexplicably slow since the upgrade to PG 12."

Why it happens: the upgrade activated JIT. The query was 5ms; with JIT compiling for 50ms it's now 55ms. If that query runs 10,000 times per minute, you add up minutes of JIT overhead.

How to fix it: look at whether the plan has a JIT: section. If the JIT timing is comparable to the real execution, disable JIT (per-session or by raising jit_above_cost).

Mistake 6 (conceptual): high global work_mem

Symptom: "I raised work_mem to 256MB in postgresql.conf to avoid spills, now the database runs out of RAM under high traffic."

Why it happens: work_mem is per sort/hash operation, not per query. A query with 3 sorts × 256MB × 100 connections = 76GB of RAM at peak. Catastrophic.

How to fix it: keep the global work_mem reasonable (16-64MB). Raise it per session (SET work_mem = '256MB') in specific jobs that need it (reports, ETL, migrations). More in module 7.

Exercises

Exercise 1: cold cache vs warm cache

Make sure your DB has the books table with data. Restart PostgreSQL (or use DISCARD ALL + restart if you have it in Docker to refresh shared_buffers). Capture the plan of the same query 3 consecutive times. Compare buffers and time across executions.

-- (Doesn't work like a real restart, but it helps)
DISCARD ALL;

docker restart postgres-container

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM books WHERE author_id = 42;

Seq Scan on books  ... actual time=2.412..18.234 rows=22 loops=1
  Buffers: shared hit=20 read=790
Execution Time: 18.512 ms

Seq Scan on books  ... actual time=0.024..7.812 rows=22 loops=1
  Buffers: shared hit=810
Execution Time: 7.834 ms

Seq Scan on books  ... actual time=0.018..7.523 rows=22 loops=1
  Buffers: shared hit=810
Execution Time: 7.545 ms

Exercise 2: cache hit ratio on a large query

Take a query that scans the whole books table. Compute the cache hit ratio and diagnose whether it's IO-bound or CPU-bound.

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(published_year) FROM books;

Aggregate  (cost=2120.00..2120.01 rows=1 width=40) (actual time=12.412..12.413 rows=1 loops=1)
  Buffers: shared hit=810
  ->  Seq Scan on books  (cost=0.00..1620.00 rows=100000 width=4)
        (actual time=0.012..6.512 rows=100000 loops=1)
        Buffers: shared hit=810
Execution Time: 12.523 ms

Buffers: shared hit=50 read=760
Execution Time: 145 ms

Exercise 3: force temp read/written

Set work_mem artificially low and run a query with a large sort. You'll see temp read/written. Then raise work_mem and observe that they disappear.

-- We lower work_mem to force a spill
SET work_mem = '64kB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books ORDER BY title;

Sort  (cost=2520.00..2620.00 rows=100000 width=22) (actual time=85.412..98.812 rows=100000 loops=1)
  Sort Key: title
  Sort Method: external merge  Disk: 4520kB
  Buffers: shared hit=810, temp read=560 written=565
  ->  Seq Scan on books  (cost=0.00..1620.00 rows=100000 width=22) ...
Execution Time: 105.234 ms

SET work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books ORDER BY title;

Sort  (cost=2520.00..2620.00 rows=100000 width=22) (actual time=18.412..28.812 rows=100000 loops=1)
  Sort Key: title
  Sort Method: quicksort  Memory: 7150kB
  Buffers: shared hit=810
Execution Time: 32.234 ms

RESET work_mem;

Exercise 4: detect JIT in a query

Make sure you have PostgreSQL 11+. Run a query with a high cost (>100,000 estimated). Look at whether JIT appears and how much time it adds.

-- Make sure JIT is enabled
SHOW jit;
-- on
SHOW jit_above_cost;
-- 100000

-- Create a larger table to exceed the threshold
CREATE TABLE big_books AS SELECT * FROM books;
INSERT INTO big_books SELECT * FROM books;
INSERT INTO big_books SELECT * FROM books;  -- ~300k rows, higher cost

ANALYZE big_books;

EXPLAIN (ANALYZE, BUFFERS)
SELECT b1.title, b2.title FROM big_books b1 JOIN big_books b2 ON b1.author_id = b2.author_id;

JIT:
  Functions: 12
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.621 ms, Inlining 8.245 ms, Optimization 32.455 ms, Emission 18.234 ms, Total 59.555 ms
Execution Time: 1245.812 ms

JIT:
  Total 60 ms
Execution Time: 80 ms

-- Try disabling
SET jit = off;
EXPLAIN (ANALYZE, BUFFERS) <same query>;
-- Verify that JIT: no longer appears

Exercise 5: compare plans with/without JIT

Take a short OLTP query (that returns <100 rows). Run it with JIT enabled and disabled. Is there a significant difference?

-- JIT default
SET jit = on;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books WHERE id = 12345;
-- Index Scan using books_pkey on books  (cost=0.42..8.44 rows=1 width=22)
--   (actual time=0.018..0.020 rows=1 loops=1)
-- Execution Time: 0.045 ms
-- (no JIT — cost too low)

-- Even if JIT is on, it doesn't activate because cost (8.44) < jit_above_cost (100000)
-- To force it:
SET jit_above_cost = 0;  -- force JIT on any query

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books WHERE id = 12345;
-- Index Scan ... actual time=0.020..0.022 rows=1 loops=1
-- JIT:
--   Total 35 ms
-- Execution Time: 35.512 ms  ← !

-- Back to normal
SET jit_above_cost = 100000;
SET jit = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books WHERE id = 12345;
-- Execution Time: 0.045 ms

Exercise 6: diagnose IO-bound vs CPU-bound in real plans

Capture the plans of three different queries in your database. For each one: compute the cache hit ratio, identify whether it's IO-bound or CPU-bound, and propose the next diagnostic step (not the solution).

## Query 1: SELECT * FROM books WHERE author_id = 42

**Plan summary:**
- Root node type: Seq Scan
- Buffers: shared hit=810, read=0
- Execution Time: 7.8 ms
- Cache hit ratio: 100%

**Diagnosis:**
- ✅ CPU-bound (everything in cache).
- ✅ But the seq scan touches the whole table (810 pages) to return 22 rows.
- Next step: investigate whether an index on `books(author_id)` would reduce the pages touched (module 3).

---

## Query 2: SELECT * FROM books ORDER BY title (low work_mem)

**Plan summary:**
- Root node type: Sort
- Buffers: shared hit=810, temp read=560 written=565
- Execution Time: 105 ms

**Diagnosis:**
- Mixed. CPU for the Seq Scan + IO for the sort's temp files.
- Cache hit of the scan: 100%, but the sort spilled to disk.
- Next step: raise `work_mem` for this query (`SET work_mem = '64MB'`) and validate the improvement.

---

## Query 3: SELECT COUNT(*), AVG(price) FROM big_table (cold cache)

**Plan summary:**
- Root node type: Aggregate
- Buffers: shared hit=200, read=4800
- Execution Time: 320 ms

**Diagnosis:**
- IO-bound. Cache hit ratio ~4%.
- 4800 pages read from disk (~38 MB).
- Next step: warmup (run the query a few times to warm the cache) or evaluate whether the table fits in the current `shared_buffers` (module 7).

Summary and next step

In this capsule you learned:

• shared hit = pages read from RAM (fast). shared read = pages read from disk (slow).
• Cache hit ratio = hit / (hit + read). High + slow = CPU-bound. Low + slow = IO-bound. Opposite diagnoses.
• temp read/written is a warning sign: the sort/hash spilled to disk. Raising work_mem per-session fixes it.
• JIT compilation activates automatically if total_cost > jit_above_cost (100,000 default). It speeds up long OLAP queries. It gets in the way of short OLTP queries.
• Detecting problematic JIT: if the JIT timing is >20% of the Execution Time, JIT isn't helping.
• Cold cache vs warm cache: the first execution of a query after a restart is dramatically slower. For honest benchmarks, warmup is mandatory.
• The Buffers: section is as important as actual time — without it, the diagnosis is half-done.

Before moving on you should be able to:

• Compute the cache hit ratio of any plan
• Identify temp read/written and know what to do
• Detect JIT in the plan and decide whether it's contributing or getting in the way
• Distinguish CPU-bound vs IO-bound in a query and propose a solution direction (not the specific solution yet)

Next capsule — Visualization tools. You now know how to read plans in plain text. But plans with 30+ lines, nested joins, and subqueries are unreadable by eye. You'll learn to use explain.depesz.com, explain.dalibo.com, and pgMustard to visualize complex plans: see the tree as a tree, identify problematic nodes with color coding, and share analysis with colleagues via permanent links. It's the last skill before the module's capstone project (capsule 08).

Resources

1. PostgreSQL Documentation — Resource Consumption / shared_buffers — the official reference on shared_buffers and how it's sized.
2. PostgreSQL Documentation — Just-In-Time Compilation (JIT) — complete coverage of JIT, when it activates, how to configure it.
3. PostgreSQL Documentation — work_mem — settings and considerations for work_mem.
4. Hubert "depesz" Lubaczewski — "Buffers in EXPLAIN" — the classic coverage of the Buffers section.
5. Andres Freund — "JIT Compilation in PostgreSQL" (talk) — from the author of JIT in PostgreSQL. A deep and honest explanation of when it helps and when it doesn't.
6. PostgreSQL Wiki — Tuning Your PostgreSQL Server — the official wiki guide for tuning buffers, work_mem, etc.
7. pg_buffercache extension — inspect what's in shared_buffers in real time (useful for understanding what's cached).
8. Bruce Momjian — "PostgreSQL Performance Optimization" (slides) — a section on shared_buffers vs OS cache, how to size it.

Module 2 — Database Performance & Query Tuning Guide