DEV Community: Jishnu Saha

How Does a Database Let Everyone Read and Write at Once?

Jishnu Saha — Sat, 19 Sep 2026 13:54:00 +0000

In the previous post we dug into how a database physically stores your rows — pages, slotted pages, heap files. Near the end, one small detail slipped by: when you delete a row, it doesn't actually disappear from disk right away. It sits there, marked in a way that some transactions can still see it and others can't.

That wasn't a quirk. It's the visible edge of one of the most important ideas in modern databases: MVCC — Multi-Version Concurrency Control. It's the machinery that lets hundreds of transactions read and write the same table at the same time without stepping on each other, and it's the reason SELECT on a busy table doesn't grind to a halt just because someone else is mid-UPDATE.

In this post we'll build that picture from the ground up: the problem MVCC solves, the one rule that makes it work (UPDATE is secretly a delete plus an insert), the tiny "snapshot" each transaction carries, and the arithmetic that decides which version of a row you're allowed to see.

The problem: what happens when everyone touches the same row?

Imagine a balance row that a hundred transactions want to read while one transaction is busy changing it. How do you keep everyone's view consistent without chaos?

The obvious answer is locking: whoever touches a row locks the door, and everyone else waits their turn. It's safe — nobody ever sees a half-finished change — but it's slow. Readers wait for writers, writers wait for readers, and the database spends its life stuck in traffic jams.

MVCC makes a completely different bet: never let two people fight over the same copy. Instead of one contended row that everyone queues for, the database keeps multiple versions of the row alive at once, and hands each transaction the version that is correct for its point in time.

The headline result is worth memorizing:

Readers never block writers, and writers never block readers.

The one thing that isn't free: two writers trying to change the same row still have to take turns. But that's a much narrower bottleneck than "everybody waits for everybody."

The core idea: an append-only ledger

The mental model to carry through the rest of this post is a ledger book with one iron rule: we never erase anything immediately.

To change a record, we don't overwrite it. We cross out the old line ("invalid as of transaction #200") and write a fresh copy below it ("valid as of transaction #200").
Every visitor who walks in gets a numbered ticket and jots down a quick note: which earlier visitors had already finished and left at the moment they arrived.
"But won't the ledger fill with crossed-out lines forever?" Eventually a cleanup step removes the lines nobody can possibly still need — but that's a story worth its own post.

Here's how the analogy maps onto real PostgreSQL terms — keep this table handy:

Analogy	Real PostgreSQL term
Ticket / badge number	Transaction ID (XID)
Valid from #N	Hidden column `xmin`
Invalid as of #M	Hidden column `xmax`
The note you jot on entry	Visibility snapshot
The janitor	VACUUM

Every transaction, on its first write, grabs the next number from an ever-increasing counter: 100, 101, 102, and so on. That number is its XID.

What a row really is: tuples and hidden columns

A single version of a row is called a tuple. And every tuple secretly carries a few hidden system columns that you never created and never see in SELECT * — but they're physically stored on disk, and you can ask for them by name:

SELECT xmin, xmax, ctid, id, balance FROM accounts WHERE id = 1;

Three of them do the heavy lifting:

xmin — the XID of the transaction that created this version. Think of it as the row's birth certificate: "born from transaction N."
xmax — the XID of the transaction that killed this version, via an update or a delete. 0 means it's still alive. This is the death certificate.
ctid — the physical location of the tuple as (page, slot) — literally where on disk it sits. This one is the odd member of the group, and the next section explains why.

So xmin and xmax are the birth and death certificates of a row version. Almost everything else in this post is just the rule that decides which certificates "count yet."

Where those hidden columns actually live

Calling them "hidden columns" invites a wrong mental picture: two separate filing cabinets, one where Postgres keeps its own bookkeeping and one where your data lives. It isn't like that at all, and the real layout quietly explains a lot.

There is one store, not two

A tuple is a single contiguous record. It sits inside one of the 8 KB pages we met in the previous post, and it's laid out front to back: a tuple header of roughly 23 bytes, then your own columns immediately behind it.

xmin and xmax aren't looked up in some side structure — they're the first few bytes of the very same record that holds id and balance. The header carries a little more than the two we care about (a couple of command IDs, some status flags, a bitmap marking which of your columns are NULL), but the shape is the point:

"Hidden system columns" are not a separate system. They're the front of every row.

This is also why the visibility check is so cheap. Deciding whether you're allowed to see a row needs no second lookup anywhere — the instant Postgres has the row in hand, it already has the certificates it needs to judge it. One record, one read.

`ctid` is an address, not a field

ctid is the exception, and the distinction matters: it isn't stored in the tuple at all.

ctid is (block number, slot number) — the tuple's location. A thing's location doesn't need to be written inside it, the same way a house's address isn't painted on its living-room wall. When Postgres hands you a ctid, it assembles the value from context: it knows which block it's reading and which slot it followed to get there.

To see where that slot number comes from, look inside a page. The first post sketched the slotted-page layout — a header, a slot array growing down, row data growing up. Here's that same page with the part that matters now:

That slot array is a line-pointer array: real, physically-stored entries of 4 bytes each, packed near the top of the page just after its 24-byte header. Each line pointer holds the byte offset and length of an actual tuple body further down.

So ctid = (0, 2) means: block 0, second entry in that block's line-pointer array — follow that entry to find the bytes. The slot number is an index into a table of pointers, not an offset into the page.

Why the extra hop is worth it

Pointing at a pointer looks like pure overhead. It buys something valuable: tuple bodies can move around inside a page without their addresses changing.

When the cleanup process later compacts a page — sliding surviving tuples together to consolidate the free space left by dead ones — it moves the bodies and rewrites the offsets inside the line pointers. Slot 2 still means slot 2. It just points somewhere new. Everything that referenced (0,2) stays correct without being touched.

Without that indirection, tidying up a single page would mean hunting down and rewriting every reference to every tuple in it.

`ctid` is not a row identity

One practical warning follows directly. A ctid is a physical, right-now address, and it changes whenever the tuple moves — which happens more than you'd think. An UPDATE writes the new version at a different location entirely (we're about to see exactly that), and a full table rewrite relocates everything.

So use your primary key when you need to identify a row across time. id is a logical identity that survives every version; ctid only tells you where one particular version happens to be sitting at this moment.

One last wrinkle, because it's a fair thing to trip over: the tuple header does contain a ctid-shaped field. But its job isn't to record this tuple's own address — it's a forward pointer to the newer version of the row, which is how Postgres walks old → new along a chain of versions. It's allowed inside the record precisely because it describes a different tuple. (When there's no newer version yet, it simply points back at this one — which is why the diagram above shows a live tuple pointing at its own slot.)

The golden rule: UPDATE = DELETE + INSERT

Here's the single most important implementation fact, and the one that surprises people most:

Postgres never updates a row in place. An UPDATE is internally an invalidation of the old version plus an insert of a new version.

When you run UPDATE accounts SET balance = 90 WHERE id = 1, Postgres does not find the 100 on disk and overwrite it with 90. Instead it:

Stamps the old tuple's xmax with the current XID — "invalid as of #200."
Writes a brand-new tuple with the new balance, xmin = the current XID ("born at #200") and xmax = 0 ("still alive").

The old version physically stays on disk — it's just marked ended. Any reader who was mid-transaction never notices; their point-in-time view still points at the old version. And notice the two tuples have different ctids: they are two distinct physical rows, not one row edited twice.

DELETE is the same move, minus the insert. It only stamps xmax on the existing tuple and stops — no new row is written. The row is marked dead now, physically removed later. This is exactly the behavior we saw in the last post: a deleted row isn't erased right away, it's handed a death certificate — and the cleanup that eventually reclaims it is a topic for a later post.

Put the two side by side and the pattern is clear: an UPDATE stamps the old tuple and appends a new one; a DELETE just stamps and stops. Neither one ever touches the original bytes in place — which is exactly why a reader mid-transaction keeps seeing the row as it was.

One consequence worth internalizing: updating a single field copies the whole row. UPDATE users SET name = 'Bob' WHERE id = 1 copies id, email, bio, created_at — everything — into a fresh tuple, even though only name changed. That's why updating a row with a big text column is expensive, and why a one-byte change still leaves behind a full-size dead tuple. (There are mitigations — very large values live out-of-line in TOAST and can be shared; the HOT optimization skips extra index work when no indexed column changed — but the mental model holds: an update writes a whole new row.)

Reads create nothing

If writes pile up versions, what about reads? This part is critical:

A SELECT creates nothing. No copy, no version, no new row. Ever.

Versions are born only from INSERT, UPDATE, and DELETE — only when data actually changes. Ten transactions reading the same row produce zero new copies. All ten look at the same single physical tuple. It's a pointer situation — "everyone reads the same page of the same book" — not "everyone gets a photocopy."

There's one nuance. That tuple lives on disk in an 8 KB page. To read it, Postgres loads that page into RAM (the shared buffer cache) once, and every reader shares that one cached copy. So:

Copies created by reading: zero.
Times the page is loaded from disk into RAM: once, then shared by everyone.

The takeaway: the memory and storage cost of MVCC comes entirely from the write side (dead versions), never from the read side.

That cache deserves more than a passing mention, though. It is where every read your database serves actually happens.

Where a read actually happens: the shared buffer cache

Notice what hasn't appeared yet in this post: a disk read. Not for the tuple, not for the visibility check, not for the SELECT. Every read PostgreSQL serves comes out of memory, and the shared buffer cache is the machinery that makes that true.

Postgres reads pages, not rows

Start with the unit of work. There is no "fetch me one row" operation anywhere in the storage layer. The smallest thing Postgres will move is an 8 KB page — the same page from the previous post. Asking for a single 60-byte tuple pulls in the entire 8 KB block that happens to contain it.

That sounds wasteful, and it isn't. Storage devices and the operating system beneath them are built to move blocks, not bytes — fetching 8 KB costs very nearly what fetching 60 bytes would. And the neighbours that came along for free are usually the next thing you want, including other versions of the row you just read.

One cache, shared by every connection

So where does that page land? In the shared buffer cache: one block of shared memory that Postgres carves out at startup and divides into slots exactly one page wide. A single slot is a buffer, and how many exist is set by shared_buffers — which ships deliberately small (128 MB) and is usually the first setting raised on a real server.

The word shared is doing literal work. Postgres runs one operating-system process per connection, and those processes do not each keep a private cache. The buffer pool lives in memory mapped into all of them at once — a hundred connections, one pool.

Beside it sits the buffer table: a hash table whose key is "which file, which block number" and whose value is "which slot is holding it." That is the index that turns "I need block 5 of accounts" into a memory address.

Two paths, and one of them is very fast

When a query needs block 5 of accounts, it hands the request to the buffer manager, and exactly one of two things happens.

Cache hit. The buffer table already has an entry for that block. The backend bumps the buffer's pin count — "I'm using this, don't take it away" — and reads the bytes where they sit. No system call, no I/O, nothing. This is the path a healthy database is on the overwhelming majority of the time.

Cache miss. No entry for that block, so it has to be read in — and it needs a slot to be read into. Here the fixed part of shared_buffers starts to matter: the number of slots is decided at startup and never changes. Postgres does not go ask the operating system for more memory when it wants to cache another page.

So "isn't there free memory somewhere?" has two answers, depending on how long the server has been up:

Just after startup, yes. Slots nobody has used yet sit on a free list, and the buffer manager simply takes one. That state lasts about as long as it takes your queries to touch shared_buffers worth of data — often a few minutes.
From then on, no. Every slot holds some page. "Find a slot" now means "take one away from the page currently sitting in it." That is eviction, and the page picked to be thrown out is called the victim.

Choosing the victim is the clock sweep. The buffer manager walks the pool in a circle, and each buffer it passes either has been used since the last time around — in which case its usage counter is decremented and it survives this pass — or has a counter already at zero, in which case it's the victim. It's a cheap approximation of "least recently useful" that costs nothing on the hit path.

One step remains before the slot can be reused. If the victim is clean, disk already holds an identical copy, so it can simply be overwritten and forgotten. If it's dirty — carrying changes not yet written out — that page must be written to disk first, and the incoming read waits on it. Only then does the 8 KB read happen, the buffer table gets its new entry, and the page is finally in memory.

That pin from the hit path is worth separating from the locks this post has been discussing, because the two aren't the same kind of thing at all. They guard different things and answer different questions:

Mechanism	What it guards	The question it answers
Lock	the row	may I read or change this data?
Pin	the slot	may the buffer manager reuse this memory?

A lock is concurrency control — it's about your data, and MVCC's whole achievement is that readers need almost none of them. A pin is memory management, and it excludes nobody: it's a counter, so ten backends can pin the same buffer at once, and the pin itself stops none of them from reading the tuples inside. All it tells the clock sweep is this slot is in use, don't hand it to another page right now. Pins are held for as long as a scan is looking at a page and dropped immediately after.

Think of a library. A lock decides who may write in the book. A pin only means the book is open on someone's desk, so the librarian mustn't reshelve it yet. (Stopping two people scribbling on the same page at the same moment is a third mechanism again — a very short-lived internal lock on the page's contents.)

Three consequences worth knowing:

If every slot is pinned, the sweep gives up after a full lap and the query fails outright with no unpinned buffers available — it never waits for one. That takes a pathologically small pool: pins last moments, so a real server always has unpinned slots to spare.
A giant sequential scan does not get to flush your cache. Postgres confines large scans to a small ring buffer — a few hundred KB that it recycles — so one nightly report can't evict the pages your live traffic depends on.
Underneath the buffer cache sits the operating system's own page cache. Even a "miss" is often served from RAM rather than from an actual device.

Writes go through the same door

One loop worth closing, because it's tempting to picture an UPDATE as "a write to disk." It isn't — not at the moment you run it.

An UPDATE reaches its page through the buffer cache exactly like a read does. It stamps the old tuple's xmax, writes the new tuple into free space on that same page if it fits, and marks the buffer dirty. The page itself is written out later, on a background process's schedule. What makes your commit durable in the meantime isn't that page write at all — it's the WAL (write-ahead log), a compact sequential record of the change that is flushed before the transaction is told it succeeded. The heap page catches up in its own time.

So a hot row can be read and updated thousands of times while its page never leaves memory — each commit sends a compact WAL record to disk, not eight kilobytes of page.

One copy, many answers

Now the part that earns this section its place in an MVCC post.

Ten transactions reading the same row hold ten pins on the same buffer. Not ten copies — ten pointers into one set of bytes, and those bytes contain every version of that row that hasn't been cleaned up yet. Each of those transactions then applies its own snapshot to the identical bytes, and they can legitimately reach different conclusions about which version they're allowed to see.

The bytes are shared. The interpretation is per-transaction.

That is the whole trick, and it's why "readers never block writers" costs so little memory. There is no private copy to make and no per-transaction view to materialize — just one page in RAM holding all the versions, and a handful of numbers per transaction deciding which of them counts.

Snapshots: what a transaction actually carries

So the table is a jumble of versions — some alive, some dead, some created by transactions that haven't even committed yet. When your query reads a row and finds several versions of it, how does it pick the right one?

It uses a snapshot: the note you jotted down the instant your transaction began. And here's the beautiful part — a snapshot copies nothing. It's essentially three numbers capturing "who had finished at the moment I started":

Piece	Meaning
`xmin` (of snapshot)	Oldest transaction still running when I started. Anything older is definitely finished.
`xmax` (of snapshot)	Next XID not yet handed out. Anything ≥ this hadn't started → can't be visible to me.
in-progress list	The exact XIDs that were running at my start moment.

A snapshot for a 5-row table and a 5-billion-row table is the same size — it doesn't scale with your data, because it's just three values. A billion transactions could fly by; your snapshot is still those three numbers, applied lazily to each row your query actually touches, at the moment it touches it.

To do the check, Postgres also consults the commit log (pg_xact) — a record of, for every XID, whether it committed, aborted, or is still in progress. And to avoid re-checking, it caches the answer right on the tuple as hint bits the first time someone looks.

That last detail has a surprising consequence, now that we know where a tuple sits while it's being read: the hint bit is written onto the page in the shared buffer cache, which marks that page dirty and eventually sends it to disk. So a plain SELECT really can cause writes. It still creates no new version — the same tuple just got a note stapled to it.

The visibility rule

Now the payoff. A tuple version is visible to your snapshot when both of these hold:

Its xmin (creator) committed before your snapshot — or the creator is your own current transaction. AND
Its xmax (deleter) is 0, or belongs to a transaction that had not committed as of your snapshot (or aborted). In other words: it hasn't been killed by anyone who already finished.

Said plainly: a version is visible if "its creator has committed (or it's me)" and "it hasn't been deleted by anyone who's already committed" — all relative to your frozen snapshot.

Let's run it. Take a row id = 1 with three versions on disk. Your snapshot was taken at a moment when transaction 90 has committed and transaction 150 has started but not yet committed:

v0 (born 80, died 90) — NOT visible. Its creator 80 committed, fine. But it was killed by 90, and 90 committed before your snapshot. Rule 2 fails → it's gone for you.
v1 (born 90, died 150) — VISIBLE. ✅ Creator 90 committed (rule 1 ✓). Its killer is 150, which is still in-progress — so from your snapshot's point of view, that death hasn't happened yet (rule 2 ✓). Still alive → visible.
v2 (born 150) — NOT visible. Created by 150, which hadn't committed when your snapshot was taken. Rule 1 fails → this version doesn't exist yet from your point of view.

Three physical versions, and exactly one passes the lens. That's not luck. A row's versions form a chain where each version's xmax equals the next version's xmin — the death of the old is the birth of the new, a single atomic event by a single transaction. Committed transactions cleanly slice up the timeline, your snapshot draws one line across it, and exactly one version straddles that line.

So the right way to think about it isn't "which version will my query grab?" It's: the snapshot is a fixed lens — three numbers — carried across the whole query, and for each row independently, exactly one version (or none) passes through it. The same three numbers apply to every row you touch, whether you scan one row or a million.

The same lens, applied to a whole table

That single-row walk-through can make it look like the snapshot works one row at a time. It doesn't — it's one set of three numbers applied to every version your query touches. So let's scale it up. Here's a whole heap on disk: five different ids, seven physical tuples all jumbled together — some alive, some dead, some not yet born. We run a plain SELECT id, value FROM accounts with no WHERE at all.

Same style of snapshot as before: transactions {60, 70, 80, 90} have committed, and {100, 130, 150} are still running (nothing numbered 151 or higher has started yet).

Apply those exact same three numbers to every version, one row at a time:

id = 1 has three versions. (0,1) was killed by 90, which committed → dead. (0,2) was born by 90 (committed) and killed by 150 (still running, so that death doesn't count yet) → visible: "A-mid." (0,3) was born by 150, still running → doesn't exist yet.
id = 2 — (0,4) born by 70 (committed), never deleted → visible: "B."
id = 3 — (0,5) born by 100, still running → invisible, so this row doesn't appear at all.
id = 4 — (0,6) born by 60 (committed), killed by 130 (still running — that death doesn't count) → visible: "D."
id = 5 — (0,7) born by 150, still running → invisible, so this row vanishes too.

Seven physical tuples collapse into a clean, consistent three-row answer. Two of the five ids disappear completely — simply because their only version isn't visible to this snapshot yet. That's the whole payoff: one tiny snapshot, applied uniformly, hands every transaction a coherent point-in-time view of the entire table — no locks, no coordination, no matter how much churn is happening around it.

The one catch

MVCC's gift — no locks between readers and writers — isn't free. Every update leaves a dead version behind, and on a heavily-updated table those pile up and quietly grow the table's file on disk — crowding live rows out of the buffer cache on the way. Reclaiming that space so it can be reused is a whole topic on its own, which we'll pick up in a later post.

Bringing it all together

Here's the whole picture, step by step:

The naive way to stay consistent under concurrency is locking, which makes readers and writers wait on each other. MVCC avoids that by keeping multiple versions of every row.
A single version is a tuple, carrying hidden columns: xmin (born from which transaction), xmax (killed by which transaction, 0 if alive), and ctid (its physical (page, slot)).
Physically, a tuple is one contiguous record — a ~23-byte header holding xmin/xmax followed straight by your own columns, so the visibility check needs no second lookup. ctid is the exception: an address, not a stored field. Its slot number indexes the page's line-pointer array, and that indirection is what lets tuples shift within a page without breaking anything that points at them.
Postgres never updates in place: an UPDATE stamps the old tuple's xmax and appends a brand-new tuple. A DELETE is the same, minus the insert. Reads create nothing at all.
Nothing is read from disk row by row. Whole 8 KB pages are pulled into the shared buffer cache — one pool of memory that every connection shares — and all readers of a row work from that single cached copy. The bytes are shared; each transaction's snapshot decides what they mean.
Each transaction carries a tiny snapshot — three numbers describing who had finished when it started — and applies the visibility rule to decide which single version of each row it's allowed to see.
All those extra versions come at a cost — dead versions accumulate — and reclaiming that space is a story for a later post.

The result is the promise we started with: on a busy table, your SELECT and someone else's UPDATE sail past each other, each looking at exactly the version that's true for its own moment in time — no locks, no waiting, no traffic jam.

What Is RAG, and Why Does Your LLM Need It?

Jishnu Saha — Mon, 07 Sep 2026 15:30:00 +0000

Ask an LLM about your company's refund policy and it will answer.

Fluently. Confidently. And often wrongly — because it has never read your refund policy, and nothing in it can tell you that.

RAG is the standard fix for that gap.

RAG stands for Retrieval-Augmented Generation, which sounds heavier than it is. It isn't a model, a framework, or a product you install. It's one idea: before you ask the LLM a question, go and find the few paragraphs that actually answer it, and hand those paragraphs with the question to the LLM.

That's the whole concept. Everything after this is detail about how you find those few paragraphs — and that detail is what decides whether the answers are any good.

Here's the shape of the finished system. It'll make sense by the end of the post; there's nothing you need to work out from it now.

We'll get there slowly. First, why the two obvious alternatives don't work. Then the pipeline one step at a time, from a PDF sitting on disk to a grounded answer coming back to a user.

The problem: a model that has never seen your data

An LLM does exactly one thing: it predicts the next token, over and over. Everything it "knows" is a statistical residue of the text it was trained on. That gives it three specific blind spots.

Anything after its training cutoff. It doesn't know what shipped last week.
Anything private. Your internal wiki, your policy handbook, this particular user's transaction history — none of it was in the training set, and none of it should be.
The fact that it doesn't know. This is the one that hurts. A bare model can't check whether it actually knows something. There is no part of it that stops and says "I'm not sure about this one." So when a question arrives that it can't answer, it does what it always does: predicts the most plausible-sounding next token. The result is a hallucination — a confident, well-formed, completely invented answer.

Say you're building a support assistant for a bank, and a customer asks:

Does the platinum card waive the annual fee?

A bare LLM will happily produce an answer. It will sound authoritative. It may even be right, by luck. But nothing in the system connects that sentence to your actual fee schedule, and nobody — including the model — can tell the difference between the lucky case and the invented one.

Two fixes that seem obvious, and why they fall short

"Fine-tune the model on our documents"

Fine-tuning continues training the model on your data, adjusting its weights. It's a real technique, but it's the wrong tool for this job:

It's slow and expensive, and you have to redo it every time a document changes. Your policy handbook gets edited on a Tuesday afternoon; you are not retraining a model that afternoon.
It teaches behaviour better than it teaches facts. Fine-tuning is excellent at "always answer in this tone, in this format, following these conventions." Getting a specific number to reliably come back out is a much shakier proposition.
There are no sources. Once a fact is baked into the weights, it's mixed in with everything else. You can't show the user where the answer came from, and you can't audit it.
You can't take it back. If a document is deleted, or a customer revokes consent, or one team's data should never be visible to another team — weights don't support deletion or access control.

"Just paste all our documents into the prompt"

More tempting, because context windows keep getting bigger. But the context window is a shared, finite budget, and everything competes for it: your system prompt, the chat history so far, whatever documents you've pasted in, the user's message — and the tokens the model still has to generate. The model's own answer is counted in the same window.

Even setting the ceiling aside:

Cost and latency scale with tokens. You pay for every token you send, on every single request, whether or not it was relevant.
Attention isn't free. Transformer self-attention cost grows roughly with the square of the input length. A very long prompt isn't just more expensive — it's slower.
Models lose things in the middle. Give a model a huge block of text and it pays most attention to the beginning and the end. Facts buried in the middle get quietly under-weighted. This is common enough to have a name: the lost-in-the-middle problem.
Almost all of it is noise. For any one question, 99.9% of your documentation is irrelevant. You'd be paying — in money, latency, and attention — to show the model 500 pages so it can use one paragraph.

And there's the insight the whole pattern is built on:

You don't need to give the model all of your documents. You need to give it the three paragraphs that answer this question.

Which reframes the problem entirely. "Find the few relevant passages in a big pile of text" isn't a language-model problem at all. It's a search problem. RAG is what you get when you put a search engine in front of the model.

What RAG actually is

Three steps, which is where the name comes from:

Step	What happens
Retrieve	Search your own data for the passages most relevant to this question
Augment	Paste those passages into the prompt, alongside the question
Generate	Let the model answer from the passages in front of it

The useful way to think about it: RAG changes the model's job from "recall a fact" to "read this passage and answer the question." The first is unreliable and unverifiable. The second is something language models are genuinely excellent at.

It's the difference between a closed-book and an open-book exam. You're not asking the student to have memorized the textbook. You're handing them the right page.

Notice what falls out of that for free:

The answer is grounded — you can cite the source, because you know exactly which chunks you passed in.
Updating knowledge means updating a document, not retraining a model.
Access control is possible, because retrieval is a query you can filter.
"I don't know" becomes achievable: if retrieval comes back with nothing relevant, you can instruct the model to say so.

The two halves of a RAG system

Every RAG system is really two pipelines that meet at a database:

Indexing runs offline. You do it once, ahead of time, and then again whenever your documents change. It turns a pile of documents into something searchable by meaning.
Answering runs online, on every single question. It searches that index, builds a prompt, and calls the model.

People often conflate the two and end up confused about why some step exists. Keep them separate in your head. The rest of this post walks each one.

Part 1 — Indexing: making documents searchable

Step 1: Extract the text

PDFs, Word documents, HTML pages, Notion exports, database rows — whatever the source, you first need plain text out of it, ideally with some structure preserved (headings, page numbers, section titles). Hold onto that structure. You'll want it later, both for chunking and for citations.

Step 2: Chunk it

You don't index whole documents. You split them into small pieces called chunks.

One term first, since the next line leans on it: an embedding model reads a piece of text and turns it into a list of numbers that captures what the text means. Those numbers — not the words themselves — are what you search. Step 3 covers it properly; for now, that's all you need.

Two reasons to chunk:

Embedding models have token limits. Like an LLM, they can only take in so much text at once. You can't hand a 200-page manual to one.
Retrieval works much better on small, focused pieces. A chunk about one topic produces a clean signal. A chunk covering nine topics is muddy — it's vaguely similar to everything and strongly similar to nothing.

The naive approach is to cut every N tokens, and it goes wrong immediately, because meaning doesn't respect token counts:

Chunk 1: …Premium customers can waive the annual

Chunk 2: credit card fee if they maintain…

Neither chunk states the rule. Whichever one gets retrieved produces a wrong or useless answer. Good chunking is both semantic-aware (split on natural boundaries — headings, sections, paragraphs, FAQ entries) and token-aware (respect the embedding model's limit).

And you add overlap: consecutive chunks share a small region at the seam.

With chunk 1 covering tokens 0–500 and chunk 2 covering 450–950, chunk 2 starts before chunk 1 ended. The sentence that got cut in half now appears whole inside chunk 2. The idea survives the boundary.

A reasonable starting point:

Setting	Typical value
Chunk size	200–500 tokens
Overlap	50–100 tokens

Both directions have a failure mode. Chunks that are too large give you noisy retrieval and expensive prompts. Chunks that are too small lose the surrounding context that made them meaningful. Between those, the sweet spot depends on your documents — this is one of the highest-leverage things to tune, and it gets a post of its own.

Step 3: Turn each chunk into an embedding

Here's the piece that makes semantic search possible.

An embedding is a list of numbers — a vector — that represents the meaning of a piece of text. Text with similar meaning gets similar numbers. That's the entire trick.

"puppy"  → [ 0.12,  0.85, -0.23, ... ]
"dog"    → [ 0.14,  0.82, -0.21, ... ]   # very close to "puppy"
"rocket" → [-0.91,  0.01,  0.74, ... ]   # nowhere near either

Once meaning is expressed as coordinates, "how related are these two pieces of text?" becomes plain geometry: measure the angle between the two vectors. That measurement is cosine similarity — a small angle means closely related, a wide angle means unrelated.

An embedding model is a different kind of model from the LLM you're chatting with:

	Input	Output
LLM	text	text
Embedding model	text	a vector of numbers

Real embeddings typically have 768 to 3072 dimensions, not the three shown above. And like LLMs, embedding models have their own token limits — another reason chunks have to be small.

Step 4: Store the vectors, with their metadata

Each chunk goes into a vector database as three things bundled together:

the vector — what you search by
the original text — what you'll actually put in the prompt
the metadata — everything else you know about it

That metadata is not an afterthought. Typical fields:

user_id, tenant_id, document_id     # who is allowed to see this
source, category, tags              # what it is
page_number, chunk_index            # where it came from, for citations
created_at, updated_at              # how fresh it is

For the storage layer itself you have options, and the right one usually comes down to what you're already running:

pgvector — a Postgres extension. If your app is already on Postgres, start here.
Pinecone, Weaviate, Qdrant, Milvus — purpose-built vector databases, usually with hybrid search and filtering built in.
FAISS — Meta's library. In-memory and self-hosted; no server or persistence of its own.

And that's the whole indexing pipeline. Note that all of it happens before any user asks anything.

Part 2 — Answering: what happens on every question

Now a question arrives. Here's what runs, in order.

Step 1: Rewrite the query (optional, but it earns its keep)

Real questions don't arrive on their own. They arrive partway through a conversation, and half their meaning is in the turns above them.

Here's a support chat a few messages in:

User: my new debit card arrived yesterday

Assistant: Great — you can activate it in the app, under Cards → Activate.

User: done that. i'm at an ATM now, it's not my bank's

Assistant: Thanks. What happens when you put the card in?

User: my card is not working here

Take that last message on its own and it barely says anything. Which card? Where is "here"? What does "not working" mean? Embed those six words and you'll search for something close to meaningless.

Read the whole conversation, though, and nothing about it is vague. It's a debit card, at another bank's ATM, and it's being refused. The context was never missing — it just wasn't in the one sentence you were about to search with.

So you make a quick call to a small, cheap model: given this conversation, rewrite the user's last message as a standalone search query.

debit card declined at another bank's ATM

Now retrieval has something to work with. Two details matter:

The rewritten query is used for retrieval only. It never goes into the prompt as the user's message.
You store the original in chat history. The user's own wording, tone, and phrasing stay intact.

Step 2: Embed the query

Run the question through the exact same embedding model you used on your chunks. Every time.

That "same model" part is the rule that matters more than any other in RAG. In a moment you're going to compare this one query vector against all your stored chunk vectors — and two vectors can only be compared if the same model produced them. Different embedding models arrange meaning differently, so the same sentence lands in a different spot.

Get this wrong and nothing crashes. There's no error to catch. You just get back chunks that have nothing to do with the question, from a system that still looks like it's working. It's also why swapping embedding models later isn't a config change: you have to re-embed and re-index everything you've already stored.

Step 3: Search — and filter first

Now you find the nearest vectors. Two things are happening at once:

Vector search compares the query's vector against the stored chunk vectors and returns the top-k nearest — the passages closest in meaning. This is what people mean by semantic search: matching on meaning rather than on keywords. Two texts can share no words at all and still be near-identical in vector space. (Semantic search is the concept; vector search is how it's implemented.)

Metadata filtering constrains that search using the structured fields you stored alongside each vector. This is not optional polish — it does three jobs:

Security. WHERE user_id = :current_user is what stops one customer's documents from surfacing in another customer's answer. Pure similarity search has no notion of who is asking.
Efficiency. Shrinking the candidate set before the expensive similarity comparison makes the search faster.
Relevance. Similarity is not the same as correctness. A chunk can be semantically close and still be from the wrong document, the wrong product, or last year's version.

Step 4: Rerank

Vector search is fast and approximate, so its top results might be good but noisy. A reranker is a second, more precise model that reads the query and each candidate chunk together and scores how well each one actually answers the question.

The usual shape:

retrieve top 20 (fast, approximate)
        ↓
rerank all 20 (slower, precise)
        ↓
keep the best 3–5

You're spending one extra cheap call to get fewer but better chunks. That pays for itself three ways: higher relevance, fewer tokens in the prompt, and less room for the model to be led astray by an irrelevant passage.

Step 5: Build the prompt

Now assemble what you're actually sending. A typical RAG prompt has four parts:

System prompt — the rules. This is where you make grounding explicit:

   You are a banking support assistant.
   Answer only using the provided context.
   If the context does not contain the answer, say you do not know.
   Do not give financial advice.

Chat history — trimmed or summarized, so it doesn't grow without bound.
Retrieved chunks — the passages you just fetched, clearly delimited so the model can tell them apart from the conversation.
The user's message — the original one, not the rewritten one.

And then the constraint that catches people out: you have to leave room for the answer. The context window covers input and output. If your model's window is 20,000 tokens and you want it to be able to write 3,000 tokens of answer, your input has to stop at 17,000.

The dangerous part is that chat history grows on its own. A conversation that fit comfortably on turn 3 can crowd out the answer entirely by turn 40. So you count tokens before you send, and when you're getting close, you trim: keep the last N messages verbatim and summarize everything older, drop to fewer retrieved chunks, or compress the chunks themselves.

Step 6: Generate

Send it. The model reads the passages and answers from them.

One thing worth being explicit about: retrieved chunks are not stored. They exist for exactly one API call. The next message runs the whole retrieval pipeline again and gets whatever chunks that question needs. Chat history persists; retrieved context doesn't.

The whole thing, end to end

Following one question all the way through:

#	Stage	What it produces
1	User sends a message	`"my card is not working here"`
2	Query rewrite	`"debit card declined at another bank's ATM"`
3	Embed the query	`[0.18, -0.22, 0.91, …]`
4	Vector search + metadata filter	20 candidate chunks, scoped to this user
5	Rerank	the best 4
6	Build the prompt	system rules + history + 4 chunks + original message
7	Token budget check	trim history if the input is crowding the answer
8	LLM call	a grounded answer, with a source you can cite

Everything from step 2 to step 8 typically runs in a couple of seconds, and steps 2 through 5 are just plumbing — ordinary code, ordinary database queries. That's most of what a RAG system is.

What RAG fixes, and what it doesn't

It's worth being clear about the boundary, because RAG is often oversold.

What it fixes:

Private and current knowledge, without touching the model.
Citations, because you know exactly which chunks you passed in.
Cheap updates — edit the document, re-index that document.
Access control, via metadata filtering.
Far fewer hallucinations, because the answer is anchored to text in front of the model.

What it doesn't fix:

Bad retrieval. If the right chunk isn't in the top results, the model can't use it properly — and it will often answer anyway, from whatever it did get. This is the single most common source of bad RAG answers, and it's a retrieval bug wearing a hallucination costume.
Bad chunking. A rule severed across a boundary is unretrievable no matter how good the rest of your stack is — and retrieval hands over whole chunks, so nothing downstream can put it back together. Where the cuts go is decided once, at indexing time.
Long, unfocused context. Stuff 30 chunks in and lost-in-the-middle comes back.
Ambiguous questions. If nobody can tell what the user meant, retrieval can't either.
A model's eagerness to be helpful. Models would rather answer than say "I don't know." Your system prompt has to explicitly authorize the second option.
Keeping the index in sync. When a source document changes, its chunks and embeddings are stale until you do something about it. Doing that efficiently — without re-embedding an entire corpus over a one-line edit — is a genuinely interesting problem, and a topic on its own.

Which leaves one rule worth writing on the wall:

RAG quality is retrieval quality. The generation step is rarely the bottleneck. When a RAG system gives a bad answer, the bug is almost always upstream of the LLM.

Recap

An LLM predicts tokens. It has never seen your data, and it can't tell you when it doesn't know something.
Fine-tuning is the wrong tool for facts. Pasting everything into the prompt doesn't scale, and buries the signal in noise.
RAG retrieves the few relevant passages, augments the prompt with them, and lets the model generate from what's in front of it. It turns "recall a fact" into "read this and answer."
Indexing (offline): extract text → chunk with overlap → embed → store vectors with metadata.
Answering (per question): rewrite → embed → filter and search → rerank → build the prompt within the token budget → generate.
The same embedding model must be used on both sides.
Metadata filtering is a security boundary, not just a performance tweak.
Always reserve context-window space for the answer.

How Does a Database Actually Store Your Data?

Jishnu Saha — Thu, 20 Aug 2026 10:00:32 +0000

Every backend engineer has used queries like SELECT * FROM users WHERE id = 42 a thousand times without thinking twice. It just works. But have you ever wondered what actually happens between hitting enter and getting your row back?

It's easy to picture a table as a neat spreadsheet sitting on disk, and the database as something that just "goes and grabs the row." In reality, there are a few layers of structure in between. Once you understand them, you'll actually know how data is stored on disk, how the database reads from disk, and why adding an index makes a query faster — instead of just knowing that it does.

In this post, we'll build that picture step by step: starting from a simple limitation of disks, moving to how rows are packed together, how those packs are organized into files, and finally why a query with no index has to do so much extra work.

The problem: disks can't read one row at a time

You'd think a database could just say "give me row 42" and the disk would hand over exactly those bytes. But that's not how disks (or SSDs) work.

Disks and the operating system move data in fixed-size chunks called blocks — often 4KB at a time. There's no way to read just one byte, or just one row. If your row lives anywhere inside a 4KB block, the database has to read that whole block, whether it needed 5 bytes or 4000.

Because of this, every database defines its own storage unit that matches this reality. That unit is called a page.

A page is the smallest chunk of data a database reads or writes in one go.
PostgreSQL & SQL Server use 8KB pages, MySQL/InnoDB uses 16KB.
Almost everything a database does — caching, locking, saving to disk — works in terms of pages, not individual rows.

So here's the first idea to hold onto: a database never reads just one row from disk. It reads the whole page that row lives in, then picks the row out of that page.

How rows are packed inside a page

A page holds many rows, but rows aren't all the same size. A users row with a short name takes less space than one with a long bio. So how do you fit rows of different sizes neatly into a fixed 8KB box?

Most databases solve this with something called a slotted page. Think of a page as split into three parts:

A header at the top. It's just a small note that says how many rows are in this page and where the free space currently starts.
A slot array right after the header. Each "slot" is a tiny pointer that says: the row for this slot starts at this exact spot in the page, and is this many bytes long. The slot array grows downward as more rows are added.
The actual row data, packed in from the bottom of the page and growing upward.

As if two people filling a room from opposite ends: the slot array fills in from the top, the row data fills in from the bottom, and the free space in the middle keeps shrinking as both sides grow.

Why are we going through this trouble instead of just listing rows one after another? Because it gives every row a short, stable address: (page number, slot number). Postgres calls this a CTID. Oracle calls it a ROWID. Later, when we build an index, this is exactly the address the index stores — not the row itself, just this small pointer to jump in.

This setup also explains something that surprises a lot of people: when you delete a row, it doesn't actually disappear from the disk right away. It stays on the disk but not every transaction can see it.

This is really just a glimpse of MVCC, which is a deep topic on its own — and it's exactly what the next post digs into: How Does a Database Let Everyone Read and Write at Once?

One more simple rule: a page always belongs to exactly one table. A page will never hold some users rows mixed with some orders rows.

How does the database decide where a new row goes?

When you insert a row, the database doesn't just store it onto the newest page. It checks a small lookup table called the free space map, which tracks which pages still have room, and drops the row into the first page that fits. Only when no page has enough space to store a row that we are trying to insert then database create a brand-new page.

This can leave small gaps or some unused space in the existing pages — that's normal, and it's called internal fragmentation. A page might not have room for a big row, but that same leftover space works fine to insert a smaller row later.

Databases also purposely leave a little extra empty space(10% - 20%, configurable) on each page, controlled by a setting called fillfactor. This spare room is used for HOT updates. This is also a separate topic worth covering when we will learn database indexing.

This is one reason a table's file on disk is usually bigger than the raw size of its data — you're seeing the data plus some intentional extra space in the disk.

There's one exception worth knowing: what if a single value — like a huge block of text — is too big to fit on any page at all? In that case, the database stores that oversized value separately, in what's called an overflow page, and just leaves a small pointer to it in the original row. PostgreSQL calls this TOAST. Other databases have their own version of the same idea.

Heap files: a pile of pages

Zoom out one level. A table isn't just one page — it can be made of millions of them. How does the database keep track of all those pages?

The simplest approach, and the default one most databases use, is called a heap file. Think of it as an unordered pile of pages, numbered in the order they were created: page 0, page 1, page 2, and so on.

Here's how the pile grows:

A brand-new, empty table starts as just page 0, with nothing in it.
As rows come in, page 0 fills up.
Once page 0 has no more room or can store a new insert, the database adds page 1.
Once page 1 fills up too, it adds page 2. And so on.

A full table scan is exactly this: reading page 0, then page 1, then page 2, all the way through the last page, checking every row along the way.

The page numbers don't tell you where the pages physically are

Here's a small but important detail. The database numbers its pages neatly — 0, 1, 2, 3 — but where those pages actually sit on the physical disk is decided by the operating system, not the database.

When the database asks for one more page, the OS just hands back whatever free space it currently has. Sometimes that's right next to the last page. Sometimes it's in a totally different spot on the disk. So "page 5 comes after page 4" is true in the database's bookkeeping, but it says nothing about them being physically next to each other on the disk.

Each table gets its own separate pile

Just like a single page can't mix two tables, one heap file never mixes pages from two tables either. Each table has its own pile, and its own numbering, starting from 0:

users table → its own page 0, page 1, page 2...
menus table → a completely separate page 0, page 1, page 2...

These piles never merge, and they grow independently. If you scan the users table, the database only touches users pages — it never even looks at menus pages.

How inserts really pick a page

Tying this back together: when you insert a row, the database looks at the free space map for any page with room — not necessarily the latest one — and drops the row into an open slot there. Find a spot, insert. That's it.

This simple rule is what makes inserts fast. And it's not permanent filling — once rows are deleted from a page and then cleaned up by vacuum, that space shows up again in the free space map, available for the next insert.

Why "no index" means "check every single page"

This simplicity has a real cost when it's time to find a row. Say you run:

SELECT _ FROM user WHERE id = 42;

With no index, the database has no idea which page holds the row id = 42.

Remember — when we insert new rows, these land wherever there's free space, not in any particular order. So the only way to be sure it finds the right row is to check every page, one by one. This is a full table scan, and the amount of work grows directly with the number of pages in the table.

This is exactly the problem an index solves. An index (usually a structure called a B+tree) is a separate, sorted list that maps values — like id = 42 — directly to a (page, slot) address. Instead of checking every page, the database can jump straight to the right one. The actual row data still lives in the heap, in one place; the index is just a fast shortcut pointing into it. A table can have several indexes, each built for a different kind of lookup.

A table with no indexes at all means every single lookup, no matter how specific, has to scan the whole thing.

And also worth mentioning here, an having index does not mean the query will always use the index; database query optimizer plans how it will run the query and will use the index or not. We will discuss this in database indexing.

Walking through a scan, step by step

Take a query like SELECT * FROM users WHERE age = 30, with no faster way to find matching rows. Here's what the database does:

Read page 0. Check every row on it for age = 30.
Read page 1. Check every row on it.
Repeat, page after page, all the way to the last page.

Notice it can't stop early just because it found one match on page 3 — there might be more matching rows on page 50. So it has to check every page to be sure it hasn't missed any.

Why this gets slower as the table grows

The amount of work scales directly with the size of the table. Double the number of rows, and you roughly double the number of pages — and roughly double the time the scan takes.

A small table, with only a few pages, scans almost instantly.
A huge table, with millions of pages, means reading that much data off disk. A 20GB table means the scan reads 20GB. There's no shortcut hiding inside the heap itself.

The real cost is best measured in pages read, not rows checked, since many rows share one page:

cost ≈ number of pages × cost of reading one page

The part that's easy to get wrong

Here's the key thing to understand: a full table scan is slow on a big table, not because the reading itself is done badly. It's slow simply because there's so much to read.

Imagine flipping through every page of a book to find one specific word.

Flipping the pages in order — page 1, then 2, then 3 — is actually the fastest possible way to flip through a book.
What makes the search slow isn't a bad way of flipping. It's that the book has a million pages, and you have to look at all of them.

A full table scan works the exact same way. The database reads pages in order — 0, 1, 2, 3 — which is also the fastest possible pattern for reading off disk, since the disk can just hand over one page after another without jumping around. So the way it reads isn't the problem. The problem is simply the amount it has to read.

Now add in the fact that disks are much slower than memory: a full scan means:

Go through page by page
- Read all data in the page(page read means disk read) and move the data from disk to memory
- This moving data from disk to memory is the slowest part of this flow
- Disks are slow(SSDs are faster than hard drives, but still take some time to read; not as fast as memory)
- In memory than it checks all the rows of that page
Small table → small amount to move → fast.
Big table → large amount to move → slow.

No matter how neatly the pages are read, reading a lot of pages always takes a lot of time.

Bringing it all together

Here's the full picture, step by step:

Disks read and write in fixed-size blocks, so a database defines its own matching unit: the page.
Rows of different sizes are packed into fixed-size pages using a slotted page layout — a header, a slot array that grows down, and row data that grows up. This gives every row a stable (page, slot) address.
A table's pages are collected into a heap file: an unordered, ever-growing pile, numbered in order but not necessarily sitting next to each other on the physical disk.
Inserts are simple on purpose — find any page with room, drop the row in — which keeps writes fast, but scatters a table's rows across the pile with no particular order.
That scattering is exactly why a lookup with no index has no choice but to check every page — a full table scan. It reads pages in the most efficient order possible, but it still has to read all of them.

Next up: we saw that a deleted row lingers on disk, and that inserts drop rows wherever there's space. Both are fingerprints of MVCC — the mechanism that lets many transactions read and write the same table at once without blocking each other. That's the whole subject of the next post: How Does a Database Let Everyone Read and Write at Once?

DEV Community: Jishnu Saha

How Does a Database Let Everyone Read and Write at Once?

The problem: what happens when everyone touches the same row?

The core idea: an append-only ledger

What a row really is: tuples and hidden columns

Where those hidden columns actually live

There is one store, not two

ctid is an address, not a field

Why the extra hop is worth it

ctid is not a row identity

The golden rule: UPDATE = DELETE + INSERT

Reads create nothing

Where a read actually happens: the shared buffer cache

Postgres reads pages, not rows

One cache, shared by every connection

Two paths, and one of them is very fast

Writes go through the same door

One copy, many answers

Snapshots: what a transaction actually carries

The visibility rule

The same lens, applied to a whole table

The one catch

Bringing it all together

What Is RAG, and Why Does Your LLM Need It?

The problem: a model that has never seen your data

Two fixes that seem obvious, and why they fall short

"Fine-tune the model on our documents"

"Just paste all our documents into the prompt"

What RAG actually is

The two halves of a RAG system

Part 1 — Indexing: making documents searchable

Step 1: Extract the text

Step 2: Chunk it

Step 3: Turn each chunk into an embedding

Step 4: Store the vectors, with their metadata

Part 2 — Answering: what happens on every question

Step 1: Rewrite the query (optional, but it earns its keep)

Step 2: Embed the query

Step 3: Search — and filter first

Step 4: Rerank

Step 5: Build the prompt

Step 6: Generate

The whole thing, end to end

What RAG fixes, and what it doesn't

Recap

How Does a Database Actually Store Your Data?

The problem: disks can't read one row at a time

How rows are packed inside a page

How does the database decide where a new row goes?

Heap files: a pile of pages

The page numbers don't tell you where the pages physically are

Each table gets its own separate pile

How inserts really pick a page

Why "no index" means "check every single page"

Walking through a scan, step by step

Why this gets slower as the table grows

The part that's easy to get wrong

Bringing it all together

`ctid` is an address, not a field

`ctid` is not a row identity