# Clustered vs Non-clustered index > A clustered index stores table rows at the leaves of the index tree; a non-clustered index is a separate structure containing keys and row locators, sometimes with extra columns. Choose the clustering key for your most important row-access pattern, then add non-clustered indexes for other queries. These are complementary tools, and their exact behavior depends on the database engine. Source: https://sysround.com/compare/clustered-vs-non-clustered-index Updated: 2026-11-05 ## Side by side | | Clustered | Non-clustered index | |---|---|---| | Leaf contents | Table rows in the common row-store model | Index keys, row locators, and optional included columns | | Number per table | At most one clustered row organization | Multiple, subject to engine limits | | Range reads | Nearby keys group full rows in leaf-page order | Nearby index entries; row fetches may be scattered | | Fetching full rows | Rows available at the matching leaves | May require another lookup unless the index covers the query | | Storage | The leaf level is the table's row storage | Additional storage alongside the table | | Write cost | Inserts and key changes affect row placement | Each affected index adds write and logging work | | Key width | Wide keys can enlarge secondary row locators | Wide keys and included columns enlarge this index | | Typical role | Primary row-access path and locality | Alternative filters, ordering, and covering queries | ## The differences that matter Clustering changes where full rows live in the index structure. If messages are clustered by (conversation_id, message_id), a conversation's messages occupy a nearby range of leaf pages. A non-clustered index on those columns can find the same messages, but fetching their bodies may require separate row lookups. Logical page order does not guarantee contiguous placement on disk. A non-clustered index can avoid row lookups when it covers the query. An index on (account_id, created_at) that also contains status and amount can serve a transaction-list query without fetching full rows. This can be preferable to changing the table's clustering key, especially when another workload needs the existing layout. The clustering key affects more than one access path. In InnoDB, secondary indexes store the primary key as their row locator; SQL Server non-clustered indexes on clustered tables use the clustering key. A wide or frequently changing clustering key can therefore increase storage and maintenance across multiple indexes. Both structures add write costs, but the costs differ. Random inserts into a clustered B-tree can split pages containing table rows, while increasing keys concentrate inserts near the right edge, which can become a contention point. Additional non-clustered indexes add maintenance, log volume, and cache pressure on every affected write. Name the database before making guarantees. InnoDB organizes rows by its clustered key, usually the primary key; SQL Server supports clustered tables and heaps. PostgreSQL's CLUSTER command rewrites a table using an index but does not maintain that physical order as rows change. Neither index type guarantees query result order without ORDER BY. ## Choose Clustered when - Your dominant query reads ranges of full rows, such as recent messages within a conversation. - You have a narrow, stable key that gives useful row locality without making secondary indexes excessively large. - Fetching many matching rows through a secondary index would cause substantial scattered row access. - Your chosen engine supports maintained clustering, and the read benefits justify its insert and update behavior. ## Choose Non-clustered index when - You need additional access paths without changing the table's existing row organization. - A selective query retrieves only a small number of rows, making individual row lookups acceptable. - A covering index can serve a frequent query using a small subset of the table's columns. - Several workloads filter or sort by different keys, so one clustered ordering cannot serve them all. ## Worked example: Fetching the latest messages in a conversation Assume a chat table has 100 million messages averaging 1 KB each, and its main query fetches the latest 50 messages for one conversation. In a clustered row-store engine, clustering by (conversation_id, message_id) lets the database seek to that conversation's range and read nearby leaf pages containing the message rows. If rows are instead clustered by a global message ID, a non-clustered index on (conversation_id, message_id) finds the right entries, but fetching 50 bodies may require scattered row lookups. Including message bodies could cover the query, but would substantially enlarge the secondary index. Choose based on measured read frequency and write cost, and use ORDER BY message_id DESC with a matching pagination predicate. ## In the interview - Start with concrete queries: equality filters, range predicates, ORDER BY, selected columns, and page size. Propose column order from those access patterns rather than saying that clustered indexes are faster. - State the engine assumption. Explain whether a secondary index points to a heap row or stores a clustering key, because that changes lookup cost and index size. - Describe both the read path and the write path. Expect questions about page splits, insertion hotspots, key updates, and the number of indexes maintained per write. - Estimate matching rows and row width, then distinguish a covering scan from a scan followed by row lookups. Validate with execution plans and representative data rather than promising a fixed speedup. - Separate local indexing from distributed placement. Clustering by conversation does not choose a shard; explain the shard key separately and discuss hot conversations if relevant. ## Common mistakes - Treating the options as mutually exclusive. A table can use clustered storage plus multiple non-clustered indexes. - Assuming a primary key is always clustered. That depends on the engine and table definition. - Claiming a non-clustered index always needs a table lookup, or that a clustered index always wins. Coverage, selectivity, row width, and caching change the result. - Equating clustered order with physically contiguous disk storage or guaranteed result order. Use ORDER BY when the result must be ordered. ## Interview questions where it comes up - [Design a chat app like WhatsApp](https://sysround.com/questions/design-whatsapp): Conversation history needs an ordered range-access path, with a concrete trade-off between clustered locality and secondary-index row lookups. - [Design a payment system](https://sysround.com/questions/design-payment-system): Account transaction history needs efficient ordered reads while ledger writes must maintain a carefully limited set of indexes. - [Design Google Photos](https://sysround.com/questions/design-google-photos): Library metadata queries benefit from owner-and-time indexes without putting large media objects into the database row layout. - [Design a distributed job scheduler](https://sysround.com/questions/design-job-scheduler): Finding due jobs requires a time-based access path that may differ from the table's stable job-identity key. ## FAQ ### Can a table have both clustered and non-clustered indexes? Yes. In engines that support clustered row storage, a table can have one clustered organization and multiple non-clustered indexes. The clustered structure stores the rows, while the other indexes provide additional access paths. ### Is a primary key always a clustered index? No. InnoDB normally clusters rows by the primary key, while SQL Server allows a primary key to be non-clustered. PostgreSQL primary keys create unique indexes but do not maintain the table in primary-key order. ### Which is faster, a clustered or non-clustered index? It depends on the query and storage layout. Clustered access often helps when reading ranges of full rows, while a small covering non-clustered index can read fewer pages for a narrow query. Compare execution plans, row lookups, logical reads, and write overhead using representative data. ## Related concepts - [Database indexing](https://sysround.com/concepts/database-indexing) - [B-trees](https://sysround.com/concepts/b-tree) - [Write amplification](https://sysround.com/concepts/write-amplification) - [Partitioning strategies](https://sysround.com/concepts/data-partitioning) - [Pagination](https://sysround.com/concepts/pagination) --- SysRound (https://sysround.com) is an AI practice tool for system design interviews: a voice or typed AI interviewer, a shared whiteboard, then feedback on every skill and a model answer. Its feedback is AI-generated and can be wrong.