Heap-Organized Table With Separate Indexes Versus Index-Organized Table Clustered by Primary Key
TradeoffDatabase InternalsSeniorStaffUpdated
Choosing between Postgres-style heap tables and MySQL/InnoDB-style index-organized tables (clustered on PK) for a new schema.
Short answer: Index-organized table clustered by primary key. When most reads are by PK or PK-range, clustering rows in PK order makes the leaf pages the rows.
The Two Options
A
Heap-organized table with separate indexes
B
Index-organized table clustered by primary key
Drill This: Your Pick First
The Better Pick
Option B
Index-organized table clustered by primary key
When most reads are by PK or PK-range, clustering rows in PK order makes the leaf pages the rows. Range scans are sequential and cache-friendly, and the PK index has zero extra indirection.
When the Other One Wins
Heap-organized table with separate indexes
Workloads dominated by secondary-index lookups suffer in IOT, because secondary indexes store the PK (not a row pointer) and need a second lookup. Heap tables with multiple covering secondary indexes win there.