Clustered index
A clustered index stores a table's rows in a key-ordered index structure that serves as the table's main storage organization.
InnoDB uses the primary key for clustering, otherwise the first UNIQUE index whose key columns are all NOT NULL, otherwise a generated hidden row identifier. [1]
Clustering and the logical primary-key constraint are different concepts. PostgreSQL indexes are separate from the heap, even when enforcing a primary key. CLUSTER physically reorders that heap once; subsequent writes do not maintain the clustering order. [2] [3]
SQLite WITHOUT ROWID uses a primary-key index b-tree for the table itself. Record payload can occur in internal cells as well as leaves, so leaf-only storage is not a universal property of clustered layouts. [4]
Storage order is not a SQL result-order guarantee. SQLite requires an ORDER BY clause when a defined output order is needed. [5]
B-trees do not imply row-oriented storage: Graefe's columnar design uses common logical row tags across separate trees. Its slot-derived tag compression depends on consecutive tags; gaps require additional handling. [6]
Relations
Citations
- MySQL 8.4: Clustered and Secondary Indexes
- PostgreSQL 18: Index-Only Scans and Covering Indexes
- PostgreSQL 18: CLUSTER, one-time ordering and locking
- SQLite file format: table storage and secondary-index row locators
- SQLite SELECT: ORDER BY and undefined result order
- Goetz Graefe: Efficient columnar storage in B-trees, sections 1 and 3-4
Sources & caveats
- Cross-engine source synthesis, scoped to MySQL 8.4, PostgreSQL 18 and SQLite's documented file format. No new engine execution or performance measurement.