secondary-index · shipping

Secondary index

A secondary index is a separate access structure that supports searches without defining the table's main row-storage organization.

Related Observatory article: Read on hesela.com

Secondary describes separation from the table's row storage, not whether an index enforces a primary key. All PostgreSQL indexes are secondary in this sense. A covering index may supply query values without a heap read, but visibility checks can still require one. [1]

For an InnoDB table with a declared primary key, secondary records include that key as the locator for the clustered row. A wide primary key therefore enlarges secondary-index storage too. [2]

SQLite secondary indexes use rowid for ordinary tables and primary-key columns for WITHOUT ROWID tables. In a WITHOUT ROWID secondary index, a key column already present with the same collation is not repeated in the locator suffix. The locator representation is engine-specific, not universally a physical address. [3]

Relations

related
Clustered index, B-tree, SQLite WITHOUT ROWID table, Covering index, Index-only scan (PostgreSQL)

Citations

  1. PostgreSQL 18: Index-Only Scans and Covering Indexes (accessed 2026-11-18)
  2. MySQL 8.4: Clustered and Secondary Indexes (accessed 2026-11-18)
  3. SQLite file format: table storage and secondary-index row locators (accessed 2026-11-18)
Sources & caveats
  • Terminology is scoped by storage organization rather than SQL constraint names. Existing SQLite metadata controls illustrate row locators; they do not measure total index bytes or a speedup.

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

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

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