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.
On this page
“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
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 order | By primary key | Insertion order, unordered |
| Secondary index pointer | Primary key value | Physical row location |
| Range scan on the PK | Very fast, sequential | Needs the index, then heap fetches |
| Cost of a wide PK | Copied into every secondary index | Only inside the PK index |
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.
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.
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.

/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.