Clustered vs Non-clustered Indexes

An index can either be the table — rows stored physically in key order inside the leaf — or sit beside it, holding only keys and pointers back to a separate heap. That single structural choice decides how fast your primary-key lookups, range scans, and secondary-index queries run, and it differs sharply between Postgres and MySQL InnoDB.

Intermediate indexes clustered-index non-clustered-index index-organized-table clustering-factor innodb postgres-cluster Created 2026-06-06 · Updated 2026-06-06

Key Components

Clustered index: An index whose leaf nodes are the table rows, physically stored in key order. There is no separate heap — the table is the index. Also called an "index-organized table." A table can have at most one.
Non-clustered (secondary) index: A separate structure whose leaf holds only the indexed key plus a pointer to the actual row. The table's physical order is unrelated to it. A table can have many.
Heap: An unordered pile of rows (Postgres's default table storage). Rows land wherever there is free space; indexes point into it via a physical row address.
TID (tuple identifier): Postgres's physical row pointer — a (page, offset) address stored in a non-clustered index leaf, used to jump directly to the row in the heap.
Clustering factor: A measure of how well the table's physical row order matches an index's logical order. High correlation means range scans touch few, adjacent pages; low correlation means scattered random I/O.

Concrete Example

The defining difference shows up in where a query has to look. Consider a users table clustered by id with a secondary index on email.

-- MySQL InnoDB: the table is ALWAYS clustered on the PK.
-- The full rows live in the PK B-tree leaves — no separate heap exists.
CREATE TABLE users (
  id    BIGINT PRIMARY KEY,     -- clustering key: rows physically sorted by id
  email VARCHAR(255),
  name  VARCHAR(255),
  KEY idx_email (email)         -- secondary index
);

-- A PK lookup returns the whole row from the leaf — zero extra fetch:
SELECT * FROM users WHERE id = 3;        -- one B-tree descent, done.

-- A secondary lookup is a DOUBLE lookup:
SELECT * FROM users WHERE email = 'a@x.com';
--   (1) descend idx_email  -> finds the PK value, e.g. id = 3
--   (2) descend the clustered PK B-tree for id = 3 -> finally gets the row
-- Two B-tree traversals per matched row.

The secondary index does not store a physical pointer to the row. In a clustered table, rows move as the B-tree splits pages, so any stored physical address would go stale. Instead the secondary index stores the clustering key (id=3), and that key is re-looked-up in the clustered index — hence the second descent.

-- Postgres takes the other path: heap + separate (non-clustered) indexes.
-- There is no permanently clustered table. CLUSTER is a ONE-TIME reorder:
CLUSTER users USING users_pkey;   -- physically rewrite the heap into PK order, ONCE
-- New/updated rows go to free space afterward, so the ordering DECAYS.
-- CLUSTER takes an ACCESS EXCLUSIVE lock (blocks reads + writes) -> maintenance op.

In Postgres, the secondary (and primary) index leaf holds a TID pointing into the heap. A lookup is one index descent plus one direct jump to the heap page — never a second B-tree descent.

Visual Model

Think of a non-clustered index as the index at the back of a textbook: terms are sorted, each with a page number you flip to. The clustered index is a dictionary: the entries are the content, in sorted order — you find the word and the definition is right there, no flipping. But a dictionary sorts only one way. To find words by some other property you need a small back-index that gives you the word (the clustering key), which you then look up in the dictionary again — that second lookup is exactly the secondary-index cost.

Step through the two lookup paths below: a clustered PK lookup (one descent) versus a secondary-index lookup in a clustered table (descent, then a second PK descent).

Step 1 of N

The structural trade-off in one table — Postgres (heap) versus MySQL InnoDB (clustered on PK):

Property	Postgres (heap)	InnoDB (clustered PK)
PK lookup cost	descent + heap fetch	row in leaf, index-only
Secondary lookup	descent + direct TID jump	two B-tree descents
Clustering	manual CLUSTER, decays	permanent, auto-maintained
Secondary points via	physical TID	the PK (clustering key)
Fat PK impact	indexes unaffected	bloats every secondary index

Deeper — Edge Cases & Gotchas

The clustering factor matters even in Postgres. Postgres never permanently clusters, but each index still has a correlation with the heap's physical order. An index on created_at over an append-only table is well-correlated — consecutive index entries map to physically adjacent rows, so a range scan sweeps a few sequential pages. An index on a random value (UUIDv4) is poorly correlated — consecutive index entries scatter across the heap, so each row is a fresh random page. The planner may even abandon the index and do a Seq Scan.

Anti-pattern: Using a random UUIDv4 as a primary key on a high-write or range-scanned table.

-- Random PK: each insert lands at an unpredictable key position.
CREATE TABLE events (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- UUIDv4 = random
  ts    TIMESTAMPTZ,
  payload JSONB
);

-- A "recent events" range scan:
SELECT * FROM events WHERE ts > now() - interval '1 hour' ORDER BY ts;

Random keys collapse the clustering factor. In a heap (Postgres), the harm isn't where the row lands but that index order no longer correlates with physical order, so range scans do one random I/O per row. In a clustered table (InnoDB) it's worse: a random key forces mid-tree inserts, triggering page splits and fragmentation — hurting insert throughput too. Point lookups stay fine; range scans and insert throughput suffer most. Prefer a compact monotonic key: a BIGINT sequence or a time-ordered UUIDv7.

Don't claim "Postgres tables are clustered on the PK" — it's false. Postgres is always heap + separate indexes. CLUSTER table USING index rewrites the heap into that index's sorted order as a one-shot operation that is not maintained; ordering decays as rows change, and the command holds an ACCESS EXCLUSIVE lock that blocks all reads and writes — strictly a maintenance-window task. InnoDB, by contrast, keeps PK order permanently and automatically.

The fat-PK tax in InnoDB. Because every secondary index stores the clustering key in its leaves, a wide primary key (say, a long string) is duplicated into every secondary index, bloating all of them. This is a concrete reason to keep the InnoDB PK compact and monotonic.

The practical rule. A table with a single access path is best as a clustered / index-organized table — every lookup is index-only and range scans are sequential. A table with many access paths is often better as a heap with covering indexes; Postgres's heap model bets that most tables have several access patterns rather than one dominant one.

Test Yourself

What lives in the leaf node of a clustered index versus a non-clustered index?

Why can a table have many non-clustered indexes but at most one clustered index?

In InnoDB, why does a secondary index store the PK instead of a physical pointer, and what does that cost on a secondary lookup?

Is a Postgres table clustered on its primary key? What does CLUSTER do, and how does it differ from InnoDB?

Why might random UUIDv4 primary keys hurt versus sequential keys, and which operations suffer most?