Postgres Internals in Simple Words: One Example to Understand Tables, Pages, Tuples, and Indexes

For the longest time, words like "heap," "tuple," "CTID," and "page" kept showing up whenever I read about Postgres — and they confused me every single time. So I sat down with one small example and traced the whole picture. Here it is, in the simplest words I could manage. No jargon without an explanation.

1. Start with a table

Imagine a small shop. You create a table:

items(item_id, price)

One row: item 100 costs $10.

When you create a table, Postgres creates one file on disk for it. All of this table's data will live inside that file. That's it — a table is a file.

2. The file is made of pages

Think of the file as a notebook. It is divided into fixed-size pages. Every page is exactly 8KB, no more, no less. Pages are numbered: page 0, page 1, page 2, and so on.

[file: items]
[ page 0 (8KB) ][ page 1 (8KB) ][ page 2 (8KB) ]...

When Postgres needs a row, it doesn't read the whole file. It jumps straight to the right page number — like opening a notebook directly on page 5 instead of flipping from page 1.

3. Inside a page: tuples

Each page holds rows. Postgres calls a row a tuple — it's just a slightly more accurate word for "row," nothing scary.

Each page also keeps a tiny list at the top, called line pointers, which simply record where each tuple sits inside the page. Think of it as a table of contents for that one page.

4. Every tuple has an address: the CTID

Every tuple's address is two numbers: (page number, line number).

• (0, 0) → page 0, first row
• (0, 2) → page 0, third row
• (1, 5) → page 1, sixth row

This address is called the CTID. It's like a house address: street (page) + house number (line). Once Postgres knows the CTID, it jumps exactly to the row. Very fast.

5. All the pages together = the heap

Heap is just the name for all the pages where the table's actual data lives — whether those pages are sitting on disk or loaded in memory.

So remember this simple equation:

table's file = heap = where your real data is

6. The index is a separate shortcut

An index is a different file. You can think of it as a shortcut list: it maps a key to a CTID.

Say you search for item_id = 100. The index looks it up and says: "it's at (0, 2)." Postgres jumps straight there. One quick jump — even if the table is 7GB big. That is why searching with an index is fast: no full scan, just one jump.

7. The twist: Postgres never overwrites

Here's the part that surprised me most.

You update the price from $10 to $20. You'd expect Postgres to erase $10 and write $20 in the same spot. It doesn't. It never updates in place.

Instead, it writes a brand-new tuple (a new version of the row) in some free space, with a new address like (0, 3) — and adds a new entry in the index pointing to it.

Now the index has two entries for item 100:

• 100 → (0, 0) … the old $10 version
• 100 → (0, 3) … the new $20 version

The old row is still sitting there. Nothing was erased.

8. So which version do you see?

Good question — and this is the cleverest part.

Every tuple carries two tiny numbers:

• xmin → which transaction created this version
• xmax → which transaction replaced or deleted this version (0 means "still alive")

Your own query also runs inside a transaction, which has a number. Postgres compares the numbers and shows you the version that was alive when your transaction started.

• Your query started after the update → you see $20.
• Your transaction started before the update and is still running → you still see $10.

Same table, two readers, two different answers — and both are correct. This system is called MVCC (multi-version concurrency control). It's how Postgres lets many people read and write at the same time without blocking each other.

9. Old versions pile up — that's what vacuum is for

The old $10 tuple doesn't vanish. It sits there as a dead tuple until no running transaction could possibly still need it. Then a background cleaner called vacuum sweeps it away.

But if some transaction runs for a very long time, dead tuples keep piling up and the table gets bloated — bigger on disk than the real data justifies. That's why database people care so much about vacuum.

10. The takeaway

The video I learned this from called Postgres's design "fantastic and miserable" — and now I get it:

• Fantastic, because a CTID lookup is a single fast jump to the exact row.
• Miserable, because every update secretly creates a new row plus a new index entry, which costs maintenance and cleanup.

If you remember one line, remember this:

In Postgres, an UPDATE is really an INSERT of a new version — plus a quiet goodbye to the old one.

---

That's the whole picture — file, pages, tuples, CTID, heap, index, and MVCC — through one little shop table. If any of these words scared you before, I hope they don't anymore.