01 · The rule
The database reads from RAM, not directly from storage.
Your data rests in files on an SSD or another storage device. But PostgreSQL does not inspect a row while it is still sitting there. First, the part of the file containing that row must be in RAM. Only then can PostgreSQL read it and answer the query.
PostgreSQL therefore reserves an area of RAM called the buffer pool. Think of it as a temporary holding area for pieces of the database that are being used. A piece already in the buffer can be read immediately. A missing piece must first be copied from storage into the buffer, and is then read from there.
So both paths end in the same place: the data is read from RAM. The difference is whether the needed piece was already waiting there or had to make the trip from storage first.
02 · Why the rule matters
Why does reading from RAM make such a difference?
Every read ends the same way: the row is read from a page in RAM. What differs is how that page got there. There are three cases, and they are nowhere near each other.
- The page is already in RAM. It is sitting in the buffer pool from an earlier query. Nothing is fetched. The row is read immediately, in about 100 nanoseconds.
- The page is on a local SSD. It must first be copied from the drive into a free slot in the buffer pool, and only then read. That copy takes about 100 microseconds — roughly a thousand times longer than reading a page already in memory.
- The page is on network storage. The same copy happens, but the request and the page must also cross a network first. About 1 millisecond — roughly ten thousand times longer.
The same shape, in everyday terms: RAM is the book already open on your desk. A local SSD is the library downstairs — you must leave the desk, fetch it, and come back. Network storage is a library in another town. In all three cases you finally read at the desk. Only the fetching changes.
| Where the page is | What has to happen first | Approximate wait | Compared with RAM |
|---|---|---|---|
| RAM | Nothing—it is already there | ~100 nanoseconds | 1× |
| Local SSD | Copy the page into a buffer slot | ~100 microseconds | ~1,000× |
| Network storage | Cross a network, then copy it in | ~1 millisecond | ~10,000× |
If one RAM read were stretched to one second, the comparable SSD trip would take about 17 minutes, and a network-storage trip about 2 hours and 47 minutes. These are rough orders of magnitude, not a benchmark or a promise. Hardware, caching, queueing, access patterns, and network distance can change the real result. For a deeper treatment, see Brendan Gregg’s Systems Performance, 2nd Edition.
03 · The page buffer
Pages are the real currency of database reads.
PostgreSQL does not move one row at a time. It groups rows into fixed-size pieces called pages. A PostgreSQL page is 8 KB. When one row is needed, the whole page containing that row is brought into RAM.
So one trip brings back a page, and a page brings back all the rows stored on it. If a page holds ten rows, fetching one of them costs exactly the same as fetching all ten. The other nine arrive free. Whether that is a bargain or waste depends entirely on whether you wanted them.
PostgreSQL keeps a lookup table recording which page currently occupies which slot, so a later request for any row on a resident page is answered from memory instead of going to disk again. That is what makes a cache a cache.
How many pages fit?
Take a buffer pool of 1 GB and divide it by the 8 KB page size:
Buffer pool1 GB
1 GB ÷ 8 KB per page
Page slots≈ 130,000
About 130,000 pages resident at once. At roughly eight rows per page, that is somewhere near a million rows held in memory. A useful number to carry: a gigabyte of buffer pool is about a million rows, give or take the size of your rows.
Treat every number here as an estimate, not a measurement. A real table has far more columns
than this example, so a real row is usually much larger than 1 KB — which means fewer rows per
page and fewer rows resident than the arithmetic above suggests. Row width, column types, and
NULL handling all move the figure. Check yours with
SELECT pg_size_pretty(pg_relation_size('your_table')); and
SHOW shared_buffers; rather than trusting the example. The
page layout documentation
explains what else occupies the 8 KB besides your data.
04 · One request, two paths
If the page is missing, the database goes to fetch it.
Suppose the row we want lives on page 17. PostgreSQL first looks for page 17 in the buffer. What happens next depends on whether the page is already there.
Page found · no trip
Page 17 is already in the buffer.
- PostgreSQL finds the slot holding page 17.
- It reads the requested row from that page in RAM.
- The answer is returned.
This is called a buffer hit.
Page missing · disk trip
Page 17 is not in the buffer.
- PostgreSQL sends a request for page 17 to disk.
- The disk reads page 17 and sends the page back.
- PostgreSQL places the returned page into a buffer slot in RAM.
- PostgreSQL reads the requested row from that page and returns the answer.
This is called a buffer miss. The trip is the expensive part.
But what if every buffer slot is already occupied when a missing page needs to enter? PostgreSQL must choose an older page to remove. That is where the working set and eviction enter the story.
05 · Working set and eviction
Hot pages stay. Cold pages make room.
The working set is simply the group of pages the current queries keep asking for. If a dashboard repeatedly reads pages 3, 8, and 17, those pages are its working set. Because they are requested often, we call them hot pages.
A small real example. A shop has five years of orders on disk, but the checkout screen only ever reads today’s orders, and the staff dashboard only ever reads this week’s. Five years of data might be 200,000 pages; one week of it might be 800. Those 800 are the working set. The other 199,200 pages are still in the table, still on disk, and almost never touched.
That is the useful part: the working set is decided by your queries, not by the size of your table. A huge table with a narrow working set stays fast. A small table that every query scans end to end does not.
To make room, PostgreSQL uses a clock sweep. Each buffer slot has a small score. Reading its page raises the score. When space is needed, a clock hand moves through the slots. A page with a score above zero loses one point and stays for now. A page already at zero is removed, and the incoming page takes its place.
Frequent requests keep hot pages alive. Unused pages cool down and make room. If the whole working set fits, later requests are mostly buffer hits. If it does not fit, useful pages may be removed before they are needed again, causing repeated disk trips. That is thrashing.
The Old Dragon “A hot page is not crowned. It survives because the vault keeps calling its name.”