Buffer Pool

By the end of this lesson you'll understand why the first run of a query is slow and the second is instant — and you'll be able to measure your database's cache hit ratio, reason about how to size the buffer pool, and tell apart the memory knobs that actually matter ( shared_buffers / innodb_buffer_pool_size , work_mem , effective_cache_size ).

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Picture a busy kitchen. The walk-in fridge is the disk: it holds everything , but every trip there is slow. The countertop is the buffer pool — a small, fast space right next to the chef where the most-used ingredients sit ready to grab.

The first time the chef needs eggs, they walk to the fridge (a slow disk read ) and bring them to the counter. Every time after that, the eggs are already on the counter — instant (a cache hit ). When the counter fills up, the chef clears off whatever hasn't been touched in a while to make room ( eviction ). And if they crack and whisk some eggs, that bowl is "dirty" — it must eventually be written back so the fridge's copy stays correct (a dirty page flushed at a checkpoint ).

Databases don't read your table one row at a time. They read it in fixed-size chunks called pages (8 KB in PostgreSQL, 16 KB by default in MySQL/InnoDB). A page is the smallest unit of data the engine moves between disk and memory.

The buffer pool is a big slab of RAM where the database keeps recently-used pages so it doesn't have to go back to disk. In PostgreSQL it's called shared_buffers ; in MySQL/InnoDB it's the buffer pool , sized by innodb_buffer_pool_size . Same idea, different name.

When a query needs a page, the engine first checks the pool. If the page is there, that's a cache hit (fast, no disk). If not, it's a miss — the page is read from disk and copied into the pool so the next read is a hit. That's the whole secret behind "first query slow, second query fast".

The single most useful number for memory health is the cache hit ratio : of all the page lookups your database did, what fraction were already in memory? The formula is simply hits / (hits + reads) , where reads means "had to go to disk".

PostgreSQL tracks these counters per table in the system view pg_statio_user_tables — heap_blks_hit for pages found in the pool and heap_blks_read for pages read from disk. Sum them up and you get your whole-database ratio.

Read that result like a doctor reads a pulse: 99%+ is healthy for a transactional database. A ratio that's drifting down toward 90% usually means your working set — the slice of data your queries actually touch — no longer fits in the buffer pool, so the engine keeps fetching pages from disk.

Fill in the blanks so the query divides hits by total lookups. The expected answer is in the comments so you can check yourself.

MySQL's InnoDB engine has the exact same mechanism under a different name. The pool is sized by innodb_buffer_pool_size , and on a dedicated database server the convention is to give it roughly 70–80% of total RAM (higher than PostgreSQL's 25%, because InnoDB does its own caching rather than leaning on the operating system).

InnoDB reports two counters: Innodb_buffer_pool_read_requests (every page lookup) and Innodb_buffer_pool_reads (the lookups that missed and went to disk). The hit ratio is 1 − reads / read_requests .

Reads are only half the story. When you UPDATE a row, the engine changes the page in the buffer pool first , not on disk. That modified-in-memory page is now a dirty page : the version in RAM is newer than the version on disk.

So how is your change safe if the server loses power before the dirty page reaches disk? The WAL (Write-Ahead Log — InnoDB calls it the redo log) is the answer. Before the change is acknowledged, a tiny record of it is appended to the WAL, a sequential file that's extremely fast to write. If the server crashes, the database replays the WAL on restart to reconstruct any dirty pages that never got flushed. The rule is in the name: write the log ahead of the data .

A checkpoint is the periodic, batched job that flushes dirty pages from the pool down to the data files and marks that portion of the WAL as no longer needed. Checkpoints are spread out on purpose (PostgreSQL's checkpoint_completion_target ) so the disk isn't slammed with a write storm all at once.

The buffer pool is finite, so eventually it fills. To load a new page, the engine must evict an old one. Both PostgreSQL and InnoDB use a variant of LRU — Least Recently Used — meaning the page that hasn't been touched for the longest time is the first to go.

This is why your hottest data tends to stay resident: every time a page is read, it's marked as recently used, so popular pages survive while one-off scans age out. It's also why a giant SELECT * over a cold table can briefly hurt — it can flood the pool with pages you'll never reuse, evicting genuinely hot ones (InnoDB's "midpoint insertion" LRU is specifically designed to resist exactly this).

Back to the kitchen: the chef keeps salt and oil on the counter all shift because they're touched constantly, but that exotic spice used once at lunch gets cleared off to make room. LRU is just "clear off whatever you haven't reached for in the longest time".

Beginners often think every memory setting is "the cache". It isn't. There are three knobs worth knowing, and confusing them is the most common tuning mistake:

No SQL to run here — just reason about the numbers. Fill in the two blanks using the 25% / 75% conventions for a 64 GB box.

Q: Why is the first run of a query slow but the second is fast?

The first run reads pages from disk (a cache miss) and copies them into the buffer pool. The second run finds those same pages already in RAM (cache hits), so it skips the slow disk I/O entirely — often 50–100x faster.

Q: Should I just make the buffer pool as big as possible?

No. In PostgreSQL, going past ~40% of RAM starves the OS file cache it also depends on, so ~25% is the sweet spot. InnoDB caches more itself, so 70–80% is normal there. Either way, leave room for the OS, connections, and per-query work_mem .

Q: Is effective_cache_size the same as shared_buffers ?

No — and this trips up almost everyone. shared_buffers actually reserves RAM to cache pages. effective_cache_size reserves nothing; it's a number that tells the planner how much cache likely exists so it favours index scans. Setting it high is free.

Q: My hit ratio is great but writes feel slow. Why?

The buffer pool speeds up reads; writes are bounded by the WAL and checkpoints. If checkpoints bunch up you get write spikes — spread them out (raise checkpoint_completion_target , tune max_wal_size ) so dirty pages flush gradually rather than all at once.

Put it all together — a brief, a blank canvas, and the expected shape of the result in the comments. Write it, then run it against a real Postgres instance to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary