📚Clustered vs non-clustered

One primary sort order vs many lookup maps

In SQL Server, the clustered index is how rows are ordered on data pages (often the primary key). Non-clustered indexes store key columns plus a pointer into that layout. PostgreSQL keeps a heap plus separate indexes, conceptually closer to “all non-clustered,” with the PK as a unique B-tree.

⚖️At a glance

Same relation; different physical story.

Aspect	Clustered	Non-clustered
Row storage	Leaf pages are the table (SQL Server)	Leaf stores keys + RID / clustering key lookup
Count per table (SQL Server)	One clustered index	Many
Wide clustering key	Bloats every non-clustered leaf that references it	Prefer narrow keys; push width to INCLUDE

SQL

SQL Server: clustered PK by default

CREATE TABLE dbo.plates (
  plate_id INT NOT NULL PRIMARY KEY CLUSTERED,
  barcode VARCHAR(32) NOT NULL
);

CREATE NONCLUSTERED INDEX ix_plates_barcode ON dbo.plates (barcode);

✓Illustrative result set

result
CREATE TABLE
CREATE INDEX

Random UUID as clustering key on hot inserts

Random insert order fragments pages and drives splits, read latency looks fine until maintenance and backups hurt.

Align clustering with dominant range access

Prefer clustering keys that match time-series or tenant ranges when that is how you scan; keep the key narrow and teach the team how bookmark lookups behave on your engine.

Design angle

Pick clustering for the access pattern you cannot afford to lose (large range scans), not only for surrogate aesthetics, then tune fillfactor and rebuild policies when load shape shifts.

TL;DR

In SQL Server terms, the clustered index is the table's row order on disk (one per table); a nonclustered index is a separate B-tree whose leaf entries point to rows (key lookups / RID lookups). Cluster on the range or join key you scan most; add nonclustered indexes for selective filters, every extra index speeds some reads but slows writes and rebuilds.