Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
Clustered Index vs Non-Clustered Index:
In Simple Words:
- Clustered Index: Determines the physical on-disk storage order of table rows (only 1 per table, usually Primary Key). Leaf nodes contain the full row data.
- Non-Clustered Index: A separate index structure whose leaf nodes contain the index key and a pointer back to the actual table row.
sql
-- In PostgreSQL, you can physically cluster a table using an existing index:
CLUSTER orders USING idx_orders_user_created;