DataTerminology.comNEW
An index that defines the physical order of rows in a table, organizing data to optimize range scans and key lookups.
DESCRIPTION
A clustered index stores table rows in the same order as the index key, making range queries and ordered scans efficient. Many engines allow one clustered index per table because it dictates physical row order, often aligned with a primary key. Clustered indexes reduce bookmark lookups but can increase write costs when rows must be moved or split. In some systems (e.g., PostgreSQL), clustering is a one-time physical reordering rather than a maintained structure. Proper key choice and maintenance are essential for performance.
ALTERNATIVE DEFINITIONS
Microsoft Learn
A clustered index determines the physical order of data in a table.
Core definition emphasizing physical ordering in SQL Server.
Wikipedia
A clustered index is a special type of index in which the logical order of the key values determines the physical order of the corresponding rows in a table.
General definition across database systems.
PostgreSQL Documentation
CLUSTER instructs PostgreSQL to physically reorder data in a table to match the order of an index.
Describes clustering as a command, not a maintained index, in PostgreSQL.
RELATED TERMS
B-Tree Heap Table Primary Key Row Store Query Optimization Table Scan Fill Factor Index Maintenance
COMPETING TERMS
Nonclustered Index
RELATIONS
- Broader: Index
KNOWLEDGE AREAS
Data Storage & Operations Data Engineering Data Warehousing & BI
TARGET AUDIENCE
Data Engineer Data Architect Software Developer BI Specialist Data Modeler
EXTERNAL SOURCES
- Wikipedia - Clustered index
- Microsoft Learn - Clustered indexes explained
- PostgreSQL - CLUSTER command reference
TOP QUESTIONS
When should I use a Clustered Index?
Use it when queries frequently filter or sort by a key with range scans, like date or identity columns.
Category: Guidance
What’s the main benefit of a Clustered Index?
Faster range queries and ordered scans because rows are stored in index key order.
Category: Relevancy
What’s the trade-off of a Clustered Index?
Higher write costs and page splits on inserts or updates that affect key order; careful maintenance is needed.
Category: Risks
How many Clustered Indexes can a table have?
Typically one, since it defines the table’s physical row order.
Category: Basics
Does PostgreSQL maintain a Clustered Index automatically?
No. PostgreSQL’s CLUSTER is a one-time reorder; ongoing maintenance requires re-clustering or vacuuming strategies.
Category: Comparison
How do I choose a clustered key?
Choose a stable, increasing key to minimize fragmentation and page splits, like an identity or a timestamp.
Category: Best practices
How does a Clustered Index affect nonclustered indexes?
Nonclustered indexes often reference clustered keys as row locators, impacting storage and lookup costs.
Category: Interaction
How do I monitor Clustered Index health?
Track fragmentation, page splits, and scan performance; rebuild or reorganize as needed.
Category: Observability
What competes with a Clustered Index design?
Heap tables or columnstore storage may be better for append-only analytics or wide aggregates.
Category: Alternatives