---
topic: system-design
author: Crashtech Editorial
published: Nov 13, 2026 · read: 4 min
---

Clustered, Primary and Secondary Indexes: Why Your Primary Key Choice Echoes Everywhere

In some databases the primary key decides how rows are stored and what every secondary index points to. A random UUID key makes that expensive.

– –

“Use a UUID as the primary key” is common advice that is fine in some engines and quietly expensive in others. The difference comes down to one design question: does the primary key also decide where rows physically live?

Three kinds of index, three jobs

Primary index created with the primary key
Enforces uniqueness and gives the fastest identity lookup. Textbooks distinguish dense indexes (one entry per row) from sparse ones (one entry per block of rows), with sparse used where rows are stored in key order.
Clustered index decides physical order
The table itself is stored in the index’s order. Only one is possible. Great for range scans and ordered reads, because adjacent keys are adjacent on disk.
Secondary index a separate lookup structure
Holds copies of the indexed columns plus a pointer back to the row. It does not change storage order. You can have many, each costing write time and space.

Where the engine matters

In InnoDB-style engines, the table is the primary key index. A secondary index entry stores the indexed columns plus the primary key value as its pointer. A query through a secondary index therefore does two lookups: find the primary key in the secondary index, then find the row by walking the clustered index.

In PostgreSQL, rows live in an unordered heap. Every index, including the primary key’s, points to a physical row location. The CLUSTER command can reorder the table once by an index, but new rows do not stay in that order, so it is a maintenance step, not a property.

Clustered-by-PK engines (InnoDB)Heap-based (PostgreSQL)
Row storage orderBy primary keyInsertion order, unordered
Secondary index pointerPrimary key valuePhysical row location
Range scan on the PKVery fast, sequentialNeeds the index, then heap fetches
Cost of a wide PKCopied into every secondary indexOnly inside the PK index
Why the primary key becomes a design decision

In a clustered-by-PK engine, the key is stored in every secondary index entry. A 36-character string key, or a 16-byte UUID, makes each secondary index larger and slower than a compact integer key, and it makes the table bigger too.

Advertisement

The random UUID problem

Insert rows with random keys into a key-ordered structure and each insert lands somewhere in the middle. The engine splits pages to make room, leaves them half empty, and touches scattered pages, which hurts cache locality. Insert with monotonically increasing keys and new rows append at the end, pages fill completely and the working set stays hot.

Practical options when you want globally unique IDs:

  • Use an auto-increment or sequence key as the clustered primary key, and expose a separate public UUID in a uniquely indexed column.
  • Use time-ordered identifiers (the newer sortable UUID variants, or sortable IDs generated from a timestamp plus entropy) so inserts stay roughly sequential.
  • If you must use random keys, accept bigger indexes and more fragmentation, and plan maintenance.

Covering indexes dodge the second lookup

When a secondary index contains every column a query needs, the engine answers from the index alone and skips the trip back to the table. That is why adding one more column to an index (an INCLUDE column or a composite key) can turn a two-step lookup into a one-step one. It costs index size and write time, so apply it to hot queries only.

How to decide

Do

Choose a compact, monotonic primary key in clustered-by-PK engines, keep public identifiers in a separate unique column, and add covering columns to indexes behind your hottest queries.

Don't

Assume “primary key” means the same thing in every database. Check whether your engine clusters on it, and what its secondary indexes store.

Takeaway

An index is not just “a faster way to find rows”. In clustered engines the primary key is the table’s layout and the pointer inside every other index. Pick it deliberately: small, stable, and increasing, and your secondary indexes and your inserts will both stay lean.

Advertisement

Frequently asked questions

What is a clustered index?

An index that determines the physical order of rows in the table. Because data can be stored in only one order, a table can have only one clustered index. It is excellent for range queries and ordered scans because neighbouring keys sit on neighbouring pages.

How is a secondary index different from a primary index?

A secondary index is a separate structure holding indexed column values plus pointers to the rows. It does not change how rows are stored. In engines that cluster on the primary key, such as InnoDB, those pointers are the primary key values, so a secondary lookup needs a second lookup through the primary key.

Why are random UUID primary keys a problem in clustered tables?

Random keys insert at arbitrary positions in the key-ordered structure, causing page splits, fragmentation and poor cache locality. Sequential or time-ordered keys append near the end and stay compact. A wide key also gets copied into every secondary index, so size matters twice.

Does PostgreSQL have clustered indexes?

Not in the same sense. PostgreSQL stores table rows in an unordered heap and its indexes point to row locations. The CLUSTER command physically reorders a table by an index once, but it is not maintained as new rows arrive, so it must be rerun if you rely on it.

Sources & further reading

/* Comments */