Clustered vs Non-Clustered Indexes

A clustered index determines the physical order of the rows in the table itself. There can be at most one per table, because the rows can only be stored in one order. A non-clustered index is a separate structure that holds a copy of the indexed columns plus a pointer back to the row, and a table can have many of them.

The consequence is that a clustered index is not a separate thing that can be dropped — it is the table's storage. This is why InnoDB tables always have a clustered index, whether you asked for one or not: if you declare an INTEGER PRIMARY KEY, that becomes the clustered index; if you do not, InnoDB adds a hidden one on a row id. And it is why you cannot have a table without a clustered index, because there would be no order to store the rows in.

SQLite works the same way at the storage level. A table with an INTEGER PRIMARY KEY stores the rows in a B-tree keyed by that column, and the primary key lookup is a search of that tree. The rowid is the clustered structure and every other index points back into it.

The practical differences come down to three things. A clustered index makes range scans on its key fast, because the matching rows are physically adjacent. A non-clustered index is a narrower structure — it holds only the indexed columns plus the row pointer — so it is smaller and more likely to fit in memory. And a non-clustered index lookup is a two-step operation: find the key in the index, then follow the pointer to the row, which is a second random access.

Here is the same thing as runnable code:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  email VARCHAR(200) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, email, amount) VALUES
  (1, 1, '[email protected]',  120.00),
  (2, 2, '[email protected]', 75.25);

-- The primary key lookup uses the clustered structure (the rowid B-tree).
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE id = 1;

-- A non-clustered index on email gets its own structure with row pointers.
CREATE INDEX idx_orders_email ON orders (email);

EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE email = '[email protected]';

Badly, and this is one of the most consequential storage decisions in a schema.

With a clustered index, rows are stored in primary-key order. If the primary key is a monotonically increasing integer, every new row is appended at the end of the B-tree, which means every insert touches the same few leaf pages. Those pages stay hot in the buffer cache, and the write cost is close to constant. If the primary key is a random UUID, every insert lands in a random position in the tree. Each insert may have to split a page and move rows around, the affected pages are scattered across the whole file, and none of them stay hot in cache. The result is write amplification, random I/O and a much smaller effective buffer cache hit rate for the same amount of memory.

The effect is usually invisible on a table with thousands of rows and brutal on one with billions. It shows up as insert latency that degrades as the table grows, and as a table that is much larger on disk than the data needs — because of the free space left behind by page splits.

There is a security dimension too. A sequential integer key means that anyone who can read a page can infer how many rows exist and which ids are missing, and it means sequential enumeration is trivial. A random key hides both — at the cost of the write performance.

The way to have both is to separate the two roles. Use a random, non-guessable value as the primary key for security and for write distribution, and put a separate, monotonically increasing integer column in the table as the clustered key. MySQL's InnoDB makes this explicit with the AUTO_INCREMENT clause — the column marked AUTO_INCREMENT becomes the clustered index even if it is not the primary key. PostgreSQL has no clustered index in the same sense, so the equivalent is to make the sequence-backed integer the primary key and put the random value in a unique column, or to accept sequential ids and protect them differently.

The two plans look different because the work is different: one is a search of the table's own storage, the other is a search of a separate index followed by a lookup of the row.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  email VARCHAR(200) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, email, amount) VALUES
  (1, 1, '[email protected]',   120.00),
  (2, 2, '[email protected]',  75.25),
  (3, 1, '[email protected]',  340.50);

-- Primary key lookup: a search of the clustered rowid structure.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE id = 2;

-- Secondary index lookup: a search of the index, then the row.
CREATE INDEX idx_orders_email ON orders (email);

EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE email = '[email protected]';

SELECT id, email, amount FROM orders WHERE email = '[email protected]';

The first plan says SEARCH orders USING INTEGER PRIMARY KEY (rowid=?). The second says SEARCH orders USING INDEX idx_orders_email (email=?). The final query returns one row: id 2, [email protected], 75.25.

The word to notice in the first plan is INTEGER PRIMARY KEY. SQLite is telling you that it used the table's own B-tree keyed by the rowid, which is the clustered structure — no separate index was involved, because in SQLite the rowid primary key and the clustered index are the same thing. The second plan names an index explicitly, because that index is a separate structure holding the email values and the row pointers back into the table.

One detail that shows up in real plans is the word COVERING. If a query needs only the indexed columns, the engine can answer it entirely from the index and never touches the table — and the plan says USING COVERING INDEX. That is the cheapest possible access path, and it is why a composite index on the columns a query actually selects can be dramatically faster than a single-column index with a table lookup afterwards.