What is the difference between a clustered and non-clustered index?

Q: What is the difference between a clustered and non-clustered index?

**Target Audience:** Developers who know what an index is and now need the storage-level picture that interviewers probe with "clustered vs non-clustered". **Difficulty:** Hard **Engines:** SQLite 3.45.0 through sql.js 1.10.2 (runnable examples) and PostgreSQL 16.15 (`ctid`, `CLUSTER`, heap-page counts). SQL Server and MySQL InnoDB behaviour is cited from their documentation; I could not run either here. > **How to read this doc:** Plain language first, then a `📌 Interview term:` callout with the exact vocabulary. Every plan and row location below is from a real run; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A phone book is sorted by surname, and the entries **are** the book: once you find "Sharma" you are already looking at the address and the number. That is a clustered index. There can only be one, because the pages of a printed book can only be in one order. Now the library adds a card file sorted by street. Each c

The problem

Question presented to candidate: "What is the difference between a clustered and a non-clustered index? Why can a table have only one clustered index, what does a non-clustered index actually store, and how does this look in SQL Server, MySQL InnoDB and PostgreSQL?"

What a strong answer should cover:

A clustered index is the table: its leaf pages hold the full rows, stored in key order, so there can be only one; a non-clustered (secondary) index is a separate structure whose leaves hold the key plus a pointer to the row
What the pointer is: in SQL Server the clustering key (or a RID on a heap), in InnoDB the primary key, in PostgreSQL a physical tuple address (ctid); a secondary lookup is therefore two searches on InnoDB and SQL Server clustered tables
Consequences: range scans and ORDER BY on the clustering key read neighbouring pages; a wide or random clustering key (for example a random UUID) bloats every secondary index and causes page splits
PostgreSQL has no clustered indexes: tables are heaps and every index is secondary; CLUSTER reorders the table once and is not maintained, and it takes an ACCESS EXCLUSIVE lock
SQLite: ordinary tables are B-trees keyed by rowid (an INTEGER PRIMARY KEY is the rowid), and WITHOUT ROWID tables are clustered on their primary key

Code / implementation expected: Optional — plans or physical row locations that show the difference between a lookup by the clustering key and by a secondary index.

Schema used by the examples:

clustered-index

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know what an index is and now need the storage-level picture that interviewers probe with "clustered vs non-clustered". Difficulty: Hard Engines: SQLite 3.45.0 through sql.js 1.10.2 (runnable examples) and PostgreSQL 16.15 (ctid, CLUSTER, heap-page counts). SQL Server and MySQL InnoDB behaviour is cited from their documentation; I could not run either here.

How to read this doc: Plain language first, then a 📌 Interview term: callout with the exact vocabulary. Every plan and row location below is from a real run; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A phone book is sorted by surname, and the entries are the book: once you find "Sharma" you are already looking at the address and the number. That is a clustered index. There can only be one, because the pages of

The solution is hidden so you can try first

12 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite rowid table: lookup by the clustering key vs by a secondary index vs a covering lookup

SQLSQLite WITHOUT ROWID: the table is clustered on its primary key, so a prefix search comes back sorted

PostgreSQL: CLUSTER orders the heap by the index once; the next UPDATE moves the row to a new place

CREATE TABLE pg_products AS SELECT * FROM products ORDER BY price_cents;
ALTER TABLE pg_products ADD PRIMARY KEY (id);
CLUSTER pg_products USING pg_products_pkey;
UPDATE pg_products SET price_cents = 4500 WHERE id = 2;
SELECT id, name, ctid FROM pg_products ORDER BY id;

PostgreSQL: after CLUSTER a 2,000-row range touches 25 table pages

CREATE TABLE events AS
SELECT g AS id, repeat('x', 60) AS payload
FROM generate_series(1, 100000) AS g
ORDER BY md5(g::text);
ALTER TABLE events ADD PRIMARY KEY (id);
CLUSTER events USING events_pkey;
VACUUM ANALYZE events;
SET enable_indexscan = off;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE id BETWEEN 1 AND 2000;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.