Clustered Index

• What it does: Rearranges the entire table based on the indexed column. The table itself becomes a sorted haystack.

• Example: Your users table’s id (primary key) is the default clustered index in most databases. The data is stored in order of id, like a bookshelf sorted by ISBN.

Database-Specific Clustered Index Implementations

MySQL (InnoDB)

• Primary key is the clustered index.
• If no primary key, the first NOT NULL unique key becomes the clustered index.
• If no valid unique key, a hidden system column (row_id) is used:
  • row_id is a 6-byte integer, auto-incremented per row.
  • Unique only within the table.

PostgreSQL

• No direct clustered index concept.
• Uses CLUSTER command to physically reorder the table.
• Indexes are always non-clustered.

Key Recommendation

Always consult your database’s docs—implementations vary wildly!

Non-Clustered Index

• What it does: Creates a separate structure (B-tree) that points to the actual data. The haystack stays messy, but the cheat sheet tells you where to find the hay.
• Example: Indexing username:

sql CREATE INDEX idx_users_username ON users(username); -- Non-clustered index

• Why it’s chaotic
  • You can create dozens of these, but writes slow down like a turtle.
  • Double the work: Queries check the B-tree first, then jump to the actual data.

Key Differences (Because You’ll Forget)

When to Use Which (Or Else) 🚨 

1. Clustered Index:
   • Use for columns you query in ranges (e.g., id, dates).
2. Non-Clustered Index:
   • Use for columns you filter/search frequently (e.g., username, email).

​​​​​What happens:

• Clustered index: Grabs data directly—table is sorted by id.
• Non-clustered index: Checks the B-tree, finds the id, then fetches data from the heap.

Trade Offs

• Clustered Index Inserts: Inserting a row with a mid-table id forces the database to shift data physically—like cutting in line at a concert.
• Non-Clustered Index Updates: Changing a username? The database must update both the table AND the index.

TL;DR

• Clustered Index = The table is physically sorted.
• Non-Clustered Index = A map pointing to the data’s location.
• Use both, but don’t go overboard!

Conclusion

That's it. This article outlined the pros and cons of clustered and non-clustered index, and when to use which.

This article was written by Ahmad Adel. Ahmad is a freelance writer and also a backend developer.