Clustered vs Non-Clustered Indexing | Dataford Interview Questions

Explain the difference between clustered and non-clustered indexing and how they impact query performance at TikTok.

Use PostgreSQL terminology and inspect the catalog metadata to support the explanation. Distinguish PostgreSQL's clustering flag from a continuously maintained clustered index.

Output

One row per valid index on video_events
Columns: index_name, access_method, and clustering_status
Sort by index_name ascending

Schema

pg_index

Column	Type	Description
indexrelid	OID	OID of the index relation
indrelid	OID	OID of the indexed table relation
indisclustered	BOOLEAN	Whether the index is marked as the table's clustering index
indisvalid	BOOLEAN	Whether the index is valid for query planning

pg_class

Column	Type	Description
oidPK	OID	Object identifier for the relation
relname	NAME	Relation name
relkind	CHAR	Relation kind, such as table or index
relam	OID	OID of the relation access method

pg_am

Column	Type	Description
oidPK	OID	Object identifier for the access method
amname	NAME	Access method name, such as btree or hash

Your question is Clustered vs Non-Clustered Indexing. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem