Clustered vs Non-Clustered — SQL

Clustered vs Non-Clustered Indexes

Not all indexes are structured the same way. The distinction between clustered and non-clustered indexes describes whether an index determines the actual physical order rows are stored in on disk, or whether it is a separate structure that simply points back to rows stored elsewhere. Understanding the difference explains why a table can have only one clustered index but many non-clustered ones, and why the choice of clustered key matters so much for performance.

Clustered index: the data itself is sorted

A clustered index determines the physical order in which rows are stored. The table's data pages are arranged according to the clustered key, similar to how a phone book's paper pages are physically sorted by last name — there is only one possible physical order, so a table can have exactly one clustered index. In most databases, if a table has a primary key, that primary key becomes (or backs) the clustered index by default.

SQL Server: primary key creates a clustered index by default

SQL

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,   -- backed by a clustered index by default
  customer_id INT,
  order_date  DATE,
  order_total NUMERIC(10, 2)
);

-- Rows are physically stored in order_id order on disk,
-- so range scans on order_id are especially efficient
SELECT * FROM orders WHERE order_id BETWEEN 1000 AND 1050;

Non-clustered index: a separate pointer structure

A non-clustered index is a separate structure, stored apart from the table's actual data, containing the indexed column's values sorted along with a pointer back to the corresponding row. It is like the index at the back of a textbook: the book's pages are not physically reordered to be alphabetical by topic, but a separate list at the back tells you exactly which page to flip to. A table can have many non-clustered indexes, one for each column or column combination worth optimizing.

Adding a non-clustered index

SQL

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Uses the non-clustered index to find matching rows,
-- then follows the pointer back to the full row data
SELECT * FROM orders WHERE customer_id = 42;

Aspect	Clustered index	Non-clustered index
Physical row order	Determines it — data is stored sorted by this key	Does not affect it — a separate structure
How many per table	At most one	Many
Lookup after finding a match	None needed — the data is right there	Follow a pointer back to the actual row
Best for	The primary access pattern / range scans on the key	Additional lookup patterns beyond the clustered key

PostgreSQL's clustering model is different

PostgreSQL does not maintain a clustered index automatically the way SQL Server or MySQL/InnoDB do. Every index in PostgreSQL, including the one backing a primary key, is a separate structure pointing back to the table's heap storage — closer to what other databases call a non-clustered index. PostgreSQL does offer a one-time CLUSTER command that physically reorders a table's rows to match a chosen index, but that ordering is not automatically maintained as the table changes afterward; it has to be re-run periodically to keep the physical order in sync. If you are coming from SQL Server or MySQL, do not assume PostgreSQL's primary key behaves like an always-maintained clustered index.

Choosing a clustered key well matters more than it might seem, because every non-clustered index in databases that store row pointers as the clustered key (rather than a physical row address) ends up including that clustered key internally. A large, frequently-changing clustered key can bloat every other index on the table, which is one of several reasons a small, stable, ever-increasing key (like an auto-incrementing integer) is often preferred as a clustered/primary key over something like a long, mutable text column.

A clustered index determines the physical storage order of a table's rows; a table can have only one.
A non-clustered index is a separate structure of sorted keys and pointers back to the actual rows; a table can have many.
In SQL Server and MySQL/InnoDB, a primary key typically becomes the clustered index by default.
PostgreSQL does not maintain an always-on clustered index — its indexes, including the primary key's, all point back to a separately-stored heap.