Forem: Mohammad Atif

[Boost]

Mohammad Atif — Sat, 28 Feb 2026 19:22:04 +0000

Clustered vs Non-Clustered Index in SQL(Complete Guide with Examples)

Mohammad Atif ・ Feb 28

#database #sql #clusteredvsnonclusteredindex #postgres

Clustered vs Non-Clustered Index in SQL(Complete Guide with Examples)

Mohammad Atif — Sat, 28 Feb 2026 18:56:53 +0000

1. Introduction: Why Do We Need Indexes?

Speed and efficiency are key when it comes to querying a database. An index is a data structure that acts as a guide for your database, giving you quick access to a particular data set to speed up your queries significantly.

To understand indexes, let’s consider a couple of analogies.

The Book: You don’t want to go through each page of a book to find a particular chapter or data; instead, you look in the back of the book to find that chapter quickly.
The Hotel: You don’t want to knock on every hotel room on every floor to find room 5001; instead, you look at a map in the lobby to quickly locate that room.

2. The Foundation: Data Pages and The Heap Structure

Before we move on to indexes, we should know how SQL stores data logically as well as physically.

Data Pages:

When you create a new table in your SQL database and start adding data to it, SQL doesn’t store data as a spreadsheet; instead, it stores data in data files on your hard disk, divided into fixed-size 8-Kilobyte blocks called “Pages.”

A Data Page Consists Of:

Page Header: This contains metadata related to that particular page.
Data Rows: This contains your actual data. The amount of data that you can store here is determined by your data size.
Offset Array: This is a small internal map that quickly tracks where your data starts on that particular page.

The Heap Structure (No Index)

If you don't create an index on your table, your data is stored in a Heap Structure.

Write Logic: When new data is added to your table, SQL just throws it onto the next available Data Page in the order it was written. They're not sorted.

Read Logic (Full Table Scan): Because your data is unsorted, when you want to read a particular record, say Customer ID 14, SQL doesn't know where it is. It has to perform a Full Table Scan, reading every single Data Page until it finds your record.

Trade-off: Fast write speed (no sorting necessary), terrible read speed.

3. The Clustered Index

A Clustered Index changes everything about how your data is stored on your hard disk.

Physical Sorting:

A Clustered Index on a particular column (e.g., User_ID) causes SQL to physically sort ALL existing data pages on that column from lowest to highest.

The B-Tree Logic:

SQL uses a default data structure called a B-Tree (Balanced Tree) to navigate this sorted data:

Root Node (Top): An index page that contains a pointer to intermediate pages.
Intermediate Nodes: Index pages that contain a pointer to a particular set of data (e.g., "IDs 1-10 go left, IDs 11-20 go right").
Leaf Nodes (Bottom): In a Clustered Index, this is your actual Data Page.

Characteristics: Limit: You can only have ONE Clustered Index per table because your data can only be sorted in ONE way.

Ideal Candidate:

Primary Keys are perfect for Primary Keys. They are unique, and they are never updated. (Updating a clustered index column requires the database to move the row to maintain the sort order, which is very resource intensive).

4. The Non-Clustered Index

A Non-Clustered Index makes reads faster but never changes the physical order of the underlying table.

The Pointer Structure

When you create a non-clustered index, SQL leaves the actual Data Pages exactly as they are, whether they are a Heap or sorted by a clustered index. It simply creates an entirely separate B-Tree structure.

The B-Tree Logic

Leaf Nodes: Unlike the clustered index, these leaf nodes are Index Pages, not Data Pages. These leaf nodes are simply a list of the actual index values, along with a Row Identifier (RID) [00:17:42]. This RID is an exact address, like File ID, Page Number, and then an actual Offset, which points to where the actual row lives in the actual table.

Navigation: To retrieve your actual row, SQL uses the non-clustered index B-Tree, navigates down to the leaf node, finds the RID, and then makes one "jump" to the actual Data Page to retrieve the actual row.

Characteristics

Limit: You may have multiple non-clustered indexes on one single table.
Ideal Candidate: Columns you often use in your WHERE clause, like "Last_Name," or often use in your JOIN clause.

5. Direct Comparison: Clustered vs. Non-Clustered

6. SQL Syntax and Implementation

Creating indexes in SQL Server uses straightforward syntax. By default, if Primary Keys are defined, SQL automatically creates a clustered index for them and uses B-Tree as default data structure.

Creating a Clustered Index:

CREATE CLUSTERED INDEX idx_customers_id ON sales.customers (customer_id);

Creating a Non-Clustered Index:

CREATE NONCLUSTERED INDEX idx_customers_lastname ON sales.customers (last_name);
(Note: If you do not specify "NONCLUSTERED", SQL Server defaults to Non-Clustered.)

I have another post in which I have talked about how to achieve best unique identifier key for your table's cluster index for the fastest Read/Write. Click Me to redirect to the relevant post.

PostgreSQL Primary Keys-Why Your UUID Choice Matters

Mohammad Atif — Tue, 23 Dec 2025 04:53:46 +0000

Before diving in

So before diving into the topic there are some base information that we need to be aware of.

1. PostgreSQL indexes

PostgreSQL has various types of indexes and different indexes have different strengths and weaknesses. PostgreSQL uses B-Tree index for indexing the primary key.

Important: PRIMARY KEY and UNIQUE constraints create an index by default.

2. What is a B-Tree?

The term “B-Tree” is short for “balanced tree” indicating that the distance between each node and the root is consistent across all levels. Furthermore, the root and its parent nodes can have more than two children, which effectively minimizes the depth of the tree, enhancing search efficiency.

Let’s see the main aspects about B-Trees:

Main characteristics

Node Flexibility: Nodes can have more than two children, typically varying from a minimum of two to a maximum defined by the tree order (M).
Height Management: Automatically adjusts to maintain a height of logM N, optimizing search operations.
Sorting Order: Maintains data in sorted order, ensuring the smallest values are on the left and the largest on the right.
Uniformity and Efficiency: All leaf nodes are at the same level to ensure efficiency and consistency, and there are no empty subtrees above the leaf nodes.

What it is best at

Totally ordered data
Equality, range, sorting

Advantages

Supports =, <, <=, >, >=
Supports ORDER BY natively
Supports UNIQUE / PRIMARY KEY
Supports multi-column indexes
Can do index-only scans
Backward scan supported
Very stable performance
Balanced tree → predictable depth

Disadvantages

Inefficient for:
- Full-text search
- Arrays
- JSON path queries
- Geometric data
Not useful for highly unselective columns (e.g. boolean)

Performance characteristics

Lookup: O(log N)
Range scan: extremely efficient
Insert cost: moderate (page splits)
Update cost: moderate
Storage: medium

Typical use cases

Primary keys
Foreign keys
Dates & timestamps
Numeric ranges
ORDER BY + LIMIT queries
Pagination

Important observation

Notice any pattern — if we somehow get data that is already sorted we could use that to our advantage as we could predict how the data would be stored thus, performing quick lookups along with other features such as equality, range, and other discussed above.

But at the same time if the data stored has no correlation making these other important functionalities go to waste.

3. Types of unique identifiers and their features

So if we use a time based UUID we can still avoid collision while leveraging full potential of B-Tree indexing.

Since every newly created UUID would be greater than the previous UUID it would be stored in the rightmost node of the tree which PostgreSQL optimizes for us.

What changes in practice?

Now for lookups both of them would give the result in nearly same time with nearly same index size but the magic happens next.

Search object created at a particular day and time from index without using another index on createdAt key.
If we need to debloat our data set due to storage cost we can even remove createdAt key.
Search objects with time based range.
Since all new UUIDs are inserted in rightmost node, insert cost is reduced.
Improved VACUUM and bloat behavior.
More predictable pagination.

When IDs are time-ordered:


  WHERE id > last_seen_id

Closing thought

In simpler terms time based UUIDs transforms a liability into a useful performance commodity.