What is Clustering with Respect to Keys?
In databases, clustering refers to how the physical storage of rows in a table is organized on disk based on a specific index—usually the primary key or another indexed column.
Clustered Index
A clustered index is an index that determines the physical order of data in a table.
- There is only one clustered index per table because rows can only be stored in one physical order.
- The table data itself is stored in the order of the clustered index.
- In most relational databases:
- MySQL (InnoDB): The primary key is always the clustered index (no PK → first UNIQUE NOT NULL index → else a hidden row id). You can’t cluster on any other column.
- SQL Server: Lets you choose any clustered key (defaults to the PK).
- PostgreSQL: Tables are heaps and are never truly clustered.
CLUSTERis a one-off rewrite under an ACCESS EXCLUSIVE lock; the order isn’t maintained for new or updated rows.CLUSTER table_name USING index_name;
Example
Consider a table of employees:
| emp_id (PK) | name | salary |
|---|---|---|
| 101 | Alice | 90,000 |
| 102 | Bob | 70,000 |
| 103 | Carol | 85,000 |
If emp_id is the clustered index:
- The rows are stored physically ordered by emp_id.
If we create a clustered index on salary (possible in SQL Server; not in InnoDB, where only the PK clusters; in Postgres only as a one-off CLUSTER):
- The table is reorganized so that rows are stored in order of salary.
Benefits of Clustering
- Faster range queries: If you query a range of values (e.g., salary > 80,000), the database reads contiguous pages on disk.
- Fewer I/O operations: Because related rows are stored together, fewer disk reads are needed.
- Efficient sorting: Since data is already sorted physically, queries with ORDER BY can avoid extra sorting steps.
Drawbacks of Clustering
- Slower inserts and updates: Because the DB must maintain the order, inserting a new row may require shifting other rows.
- Only one per table: You can only cluster on one key.
Difference Between Clustered and Non-Clustered Index
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Physical order | Determines physical order of data | Does not affect physical row order |
| Number per table | Only one | Multiple allowed |
| Storage | Table and index are stored together | Index and table stored separately |
| Access | Faster for range queries | Requires additional lookup (to get full row) |
Summary
Clustering in databases is the physical organization of data based on a particular key (usually via a clustered index), which helps in improving query performance—especially for range scans and ordered queries.