Tables Store Rows. So What’s Actually Inside a PostgreSQL Index?

You probably have a clear picture of a table: rows of data, one after another. A books table holds books. A users table holds users.

But what about an index? You create one, queries get faster, and that’s about all most people know. What is actually stored in it?

The short answer: an index stores a sorted list of values, and each value comes with the location of its row in the table. That’s it. No full rows, no copies of your data. Just “this value lives over there.”

Let’s look inside one and see.

Start With a Table

Create a small table and add three books, deliberately out of order:

CREATE TABLE books (
    id     int,
    title  text,
    price  numeric
);

INSERT INTO books VALUES
    (3, 'Linux Basics',     29),
    (1, 'PostgreSQL Guide', 45),
    (2, 'Bash Tricks',      19);

Now ask PostgreSQL to show a hidden column called ctid:

SELECT ctid, * FROM books;

 ctid  | id |      title       | price
-------+----+------------------+-------
 (0,1) |  3 | Linux Basics     |    29
 (0,2) |  1 | PostgreSQL Guide |    45
 (0,3) |  2 | Bash Tricks      |    19

Two things to notice.

First, the table isn’t sorted. Rows are stored roughly in the order they arrived. PostgreSQL calls the table storage a heap, because it really is just a pile of rows.

Second, every row has an address. ctid is the row’s physical location, written as (page, slot):

• (0,1) means page 0, slot 1.
• (0,2) means page 0, slot 2.

A page is an 8 KB block on disk. A table is a long series of these pages, and each page holds as many rows as fit.

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

Think of the table as a warehouse. Pages are shelves, slots are positions on each shelf, and ctid is a sticker that says “shelf 0, spot 2.”

Now Add an Index

CREATE INDEX books_id_idx ON books (id);

Conceptually, here is what PostgreSQL just built:

 id (sorted) | row address
-------------+-------------
      1      |   (0,2)
      2      |   (0,3)
      3      |   (0,1)

That’s the whole idea. Two columns:

1. The indexed value, kept in sorted order.
2. The ctid, which says where the full row lives in the table.

Notice what’s not there: no title, no price. The index only knows about id and where to find each row.

It works just like the index at the back of a book:

PostgreSQL ........ 214
Replication ....... 187
VACUUM ............ 302

The book index doesn’t repeat the whole chapter. It gives you the word and a page number. A database index gives you a value and a row address.

Don’t Take My Word for It: Look Inside

PostgreSQL ships an extension called pageinspect that lets you read raw index pages. You need superuser rights, and it’s best used on a test database:

CREATE EXTENSION pageinspect;

The table and the index are stored in separate files, and each file numbers its pages from 0.

In the table file, block 0 already holds rows. In a B-tree index file, block 0 is a header page PostgreSQL uses for bookkeeping, and the real entries start at block 1. Our tiny index fits on one page, so let’s read the index’s block 1:

SELECT itemoffset, ctid, data
FROM bt_page_items('books_id_idx', 1);

 itemoffset | ctid  |          data
------------+-------+-------------------------
          1 | (0,2) | 01 00 00 00 00 00 00 00
          2 | (0,3) | 02 00 00 00 00 00 00 00
          3 | (0,1) | 03 00 00 00 00 00 00 00

There it is, exactly as described:

• data is the indexed value in raw bytes. 01 is the number 1, 02 is 2, 03 is 3. The extra zeros are padding.
• ctid points to the row in the table, not in the index. (0,2) means block 0 of the table file, slot 2. That’s why you see block 0 here even though we’re reading block 1 of the index.
• The entries are sorted by value, even though we inserted the rows as 3, 1, 2.

How PostgreSQL Uses This During a Query

Run:

SELECT * FROM books WHERE id = 2;

With the index, PostgreSQL does this:

1. Searches the index for the value 2. Because the entries are sorted, it can jump straight there instead of reading everything.
2. Reads the address: (0,3).
3. Goes to the table, page 0, slot 3, and fetches the full row: 2 | Bash Tricks | 19.

Step 3 is important. The index alone doesn’t have title or price, so PostgreSQL has to visit the table to get them.

Without the index, PostgreSQL has to check every row in every page. That’s called a sequential scan. On three rows it doesn’t matter. On ten million, it’s the difference between milliseconds and minutes.

Why It’s Called a B-tree

Our example fits on one page. A real index on millions of rows needs thousands of pages. PostgreSQL arranges them as a tree, called a B-tree:

                 [ root: 1..500 | 501..1000 ]
                   /                    \
     [ 1..250 | 251..500 ]      [ 501..750 | 751..1000 ]
        /          \               /           \

[leaf pages: value → ctid, value → ctid, …]

• Leaf pages at the bottom hold the actual entries: value plus ctid, as we saw above.
• Upper pages only hold signposts like “values 1–250 are down this way.”

Finding any value takes just a few hops from the top down, even in a huge table. A B-tree on a billion rows is usually only four or five levels deep.

It’s like a library: floor, then aisle, then shelf, then book. You never walk past every shelf.

What Happens When You Update a Row?

This part surprises people. Update a book’s price:

UPDATE books SET price = 31 WHERE id = 3;
SELECT ctid, * FROM books;

 ctid  | id |      title       | price
-------+----+------------------+-------
 (0,2) |  1 | PostgreSQL Guide |    45
 (0,3) |  2 | Bash Tricks      |    19
 (0,4) |  3 | Linux Basics     |    31

The row moved from (0,1) to (0,4). PostgreSQL doesn’t overwrite rows in place. It writes a new version of the row and marks the old one as dead. This is how it lets other sessions keep reading the old version safely while you update.

So what about the index?

• If an indexed column changed, PostgreSQL adds a new index entry pointing to the new row.
• If no indexed column changed, like here where we only changed price, PostgreSQL can often skip touching the index. This is called a HOT update (Heap-Only Tuple). The old index entry still points to (0,1), and that slot now holds a forwarding pointer to (0,4).

Either way, dead entries pile up over time. VACUUM cleans them out of both the table and the index. That’s one reason indexes on busy tables can grow larger than you’d expect.

Can the Index Answer a Query by Itself?

Sometimes, yes. If a query only needs columns that are in the index:

SELECT id FROM books WHERE id = 2;

PostgreSQL may use an Index Only Scan and skip the table. That’s only possible when the table pages are marked “all visible,” which VACUUM takes care of. The reason is that the index doesn’t store whether a row version is still alive. That information lives only in the table.

You can also add extra columns to the index on purpose, so more queries can skip the table:

CREATE INDEX books_id_price_idx ON books (id) INCLUDE (price);

Now the index stores id, price, and ctid. A query like SELECT price FROM books WHERE id = 2 can be answered from the index alone.

Quick Recap: Table vs. Index

	Table (heap)	B-tree index
What it stores	Full rows, every column	Indexed column values plus row addresses (ctid)
Order	Roughly insertion order	Sorted by indexed value
Structure	Flat list of pages	Tree of pages
Knows if a row is still alive?	Yes	No, has to check the table
Purpose	Hold the data	Find the data fast

Summary

A table is a pile of rows, each with an address called ctid. An index is a sorted list of values, each paired with a ctid, and organized as a tree so PostgreSQL can find any value in a few steps.

So when you create an index, you aren’t copying your table. You’re building a well-organized directory that points into it. That directory takes disk space and has to be updated on writes, which is why you add indexes for the queries you actually run, not to every column.