clustered-index · shipping · also Clustering index

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.

Related Observatory article: Read on hesela.com

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

related
Secondary index, B-tree, SQLite WITHOUT ROWID table

Citations

  1. MySQL 8.4: Clustered and Secondary Indexes (accessed 2026-11-18)
  2. PostgreSQL 18: Index-Only Scans and Covering Indexes (accessed 2026-11-18)
  3. PostgreSQL 18: CLUSTER, one-time ordering and locking (accessed 2026-11-18)
  4. SQLite file format: table storage and secondary-index row locators (accessed 2026-11-18)
  5. SQLite SELECT: ORDER BY and undefined result order (accessed 2026-11-18)
  6. Goetz Graefe: Efficient columnar storage in B-trees, sections 1 and 3-4 (accessed 2026-11-18)
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.

JSON: /data/terms/clustered-index.json

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

Cite this page
Hesela team. Clustered index. Hesela. 2026-11-18. https://hesela.dev/terms/clustered-index/