Definition
- Unique index – A logical constraint plus a lookup structure that guarantees no two rows share the same value in the indexed columns; its purpose is correctness and fast equality lookup, and a table can have many.
- Clustered index – A physical storage decision that defines the order in which the table's rows are stored on disk; the leaf level of the clustered index is the table itself, and there can be at most one per table.
- Independence – The two concepts are orthogonal: an index can be unique but not clustered, clustered but not unique, both, or neither.
- InnoDB behavior – The primary key is by default both unique and clustered, and every secondary index stores the clustered key as its row pointer.
- PostgreSQL behavior – The primary key is a unique non-clustered index over a heap;
CLUSTERreorders once but the order drifts with writes.
At the National Bank of India, a unique index is a rule written on a form: "no two accounts may have the same PAN number." The clerk checks the rule and rejects duplicates, and the bank can enforce many such rules at once (unique PAN, unique email, unique Aadhaar). A clustered index is how the physical account folders are arranged in the vault: the folders are stored in account-number order, one single arrangement, because a folder can physically sit in only one place. You can have many rulebooks (unique indexes) but only one physical ordering of the folders (clustered index).
Understanding the Concepts
What is a unique index?
A unique index serves two jobs. First, it enforces a business rule: the engine rejects any insert or update that would create a duplicate in the indexed columns. Second, because the values are unique, equality lookups are extremely fast and the planner knows at most one row can match.
-- National Bank of India: unique PAN for compliance
CREATE UNIQUE INDEX ux_accounts_pan ON accounts(pan);
-- Unique email for login
CREATE UNIQUE INDEX ux_accounts_email ON accounts(email);
-- Both enforce different uniqueness rules on the same table
INSERT INTO accounts (account_id, customer_name, pan, email, balance)
VALUES ('SAV3001', 'Priya Sharma', 'ABCDE1234F',
'priya@email.com', 50000.00);
-- This fails: duplicate PAN
INSERT INTO accounts (account_id, customer_name, pan, email, balance)
VALUES ('SAV3002', 'Amit Verma', 'ABCDE1234F',
'amit@email.com', 30000.00);
-- ERROR: duplicate key value violates unique constraint "ux_accounts_pan"
You can have many unique indexes on one table. Each enforces a different uniqueness rule.
What is a clustered index?
A clustered index dictates that the table rows are stored sorted by the clustered key. The leaf pages of the clustered index are the data pages themselves. Because rows can only be physically ordered one way, there is exactly one clustered index per table.
-- In InnoDB, the PRIMARY KEY is the clustered index
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY, -- clustered
customer_name VARCHAR(100),
pan VARCHAR(10),
balance DECIMAL(15,2)
) ENGINE=InnoDB;
-- Reaching the primary key gives the full row immediately
SELECT * FROM accounts WHERE account_id = 'SAV2001';
-- One traversal: root -> internal -> leaf (which IS the row)
Range scans on the clustered key are very fast because the data is physically contiguous.
-- Range scan on the clustered key: data is contiguous on disk
SELECT * FROM accounts
WHERE account_id BETWEEN 'SAV2000' AND 'SAV2100';
-- Fast: reads contiguous pages, no random I/O
How are they different?
| Feature | Unique Index | Clustered Index |
|---|---|---|
| Purpose | Correctness: no duplicate values | Physical layout: row storage order |
| Count per table | Many | Exactly one |
| Enforces uniqueness | Yes | Not necessarily |
| Stores full rows | No (key + pointer) | Yes (leaf = row data) |
| Range scan speed | Needs bookmark lookup per row | Very fast (data is contiguous) |
| Example | Unique PAN, unique email | Primary key in InnoDB |
Can an index be clustered but not unique?
Yes. A clustered index does not require uniqueness. For example, a log table clustered by date has many rows with the same timestamp.
-- SQL Server: clustered index on event_date, not unique
CREATE CLUSTERED INDEX ix_audit_date ON audit_log(event_date);
-- Many events share the same timestamp
INSERT INTO audit_log (event_date, event_type, details)
VALUES ('2026-10-25 14:30:00', 'LOGIN', 'Rajesh logged in');
INSERT INTO audit_log (event_date, event_type, details)
VALUES ('2026-10-25 14:30:00', 'TRANSFER', 'Priya transferred funds');
-- Both succeed: clustered but not unique
Can an index be unique but not clustered?
Yes. Most unique indexes are non-clustered. In InnoDB, the unique PAN index is a separate B-tree pointing back to the clustered primary key.
-- Unique PAN index is non-clustered
-- Its leaf stores: [pan | account_id (clustered key pointer)]
CREATE UNIQUE INDEX ux_accounts_pan ON accounts(pan);
-- Lookup by PAN: two traversals
SELECT * FROM accounts WHERE pan = 'ABCDE1234F';
-- 1. Seek ux_accounts_pan -> find account_id = 'SAV2001'
-- 2. Seek clustered index -> fetch full row for 'SAV2001'
How do you combine both in InnoDB?
In InnoDB, the primary key is both unique and clustered by default. This is the most common combination.
-- Primary key = unique + clustered
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY, -- unique + clustered
account_id VARCHAR(20) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Secondary indexes are unique but NOT clustered
CREATE UNIQUE INDEX ux_txn_ref ON transactions(reference_number);
-- This is a separate B-tree that stores [reference_number | txn_id]
How does PostgreSQL differ?
PostgreSQL does not keep a table permanently clustered. Its primary key is a unique non-clustered index over a heap table. The CLUSTER command reorders the heap once, but the order drifts as rows are inserted and updated.
-- PostgreSQL: CLUSTER reorders once
CLUSTER accounts USING accounts_pkey;
-- After CLUSTER, accounts are physically sorted by account_id
-- But new inserts go to the end of the heap, breaking the order
-- You must re-CLUSTER periodically (and it takes an exclusive lock)
What should a senior engineer know?
In InnoDB, every secondary (non-clustered) index stores the clustered key as its row pointer. A wide clustered key bloats every secondary index.
-- Bad: 36-byte string primary key
CREATE TABLE payments_bad (
payment_id CHAR(36) PRIMARY KEY, -- 36 bytes in every secondary index
amount DECIMAL(15,2)
);
-- Good: compact 8-byte primary key
CREATE TABLE payments_good (
payment_id BIGSERIAL PRIMARY KEY, -- 8 bytes in every secondary index
amount DECIMAL(15,2)
);
Another nuance: a unique partial index enforces conditional uniqueness.
-- Each customer can have at most one primary account
CREATE UNIQUE INDEX ux_one_primary
ON accounts(customer_id)
WHERE is_primary = true;
-- Allows multiple non-primary accounts per customer
-- but exactly one primary account
What are common interview follow-up questions?
- Why does InnoDB embed the primary key in every secondary index?
- What is a covering index and how does it avoid the bookmark lookup?
- When would you cluster a table by a non-primary-key column?
- How does a unique constraint differ from a unique index?
Visual Overview
┌─────────────────────────────────────────────────┐ │ Unique Index vs Clustered Index │ ├─────────────────────────────────────────────────┤ │ │ │ Unique Index (correctness rule): │ │ ┌────────────────────────────┐ │ │ │ ux_accounts_pan │ │ │ │ Leaf: [PAN | account_id] │ │ │ │ Rule: no duplicate PAN │ │ │ │ Count: many per table │ │ │ └────────────────────────────┘ │ │ │ │ Clustered Index (physical order): │ │ ┌────────────────────────────┐ │ │ │ accounts_pkey (InnoDB) │ │ │ │ Leaf: [account_id | name │ │ │ │ | pan | balance] │ │ │ │ Leaf IS the row data │ │ │ │ Count: exactly one │ │ │ └────────────────────────────┘ │ │ │ │ Combination Matrix: │ │ ┌──────────┬──────────┬──────────┐ │ │ │ │ Unique │ Not Unique│ │ │ ├──────────┼──────────┼──────────┤ │ │ │Clustered │ InnoDB │ Log table│ │ │ │ │ PK │ by date │ │ │ ├──────────┼──────────┼──────────┤ │ │ │Non-clust.│ PAN idx │ Branch │ │ │ │ │ Email idx│ idx │ │ │ └──────────┴──────────┴──────────┘ │ └─────────────────────────────────────────────────┘
Key Takeaway
A unique index and a clustered index solve completely different problems. A unique index is a correctness guarantee: it ensures no two rows share the same value in the indexed columns, so the database itself rejects duplicates where no application bug can slip past, and it also makes equality lookups fast because the planner knows at most one row matches. A table can have many unique indexes – unique PAN, unique email, unique mobile number all at once.
A clustered index is a physical storage decision: it defines the order in which the rows are actually written to disk, so the leaf level of the clustered index is the table data itself. Because rows can only be physically arranged one way, there is exactly one clustered index per table, and reaching a clustered key hands back the whole row with no secondary bookmark lookup, which makes range scans on that key very fast. The two concepts are independent – you can have a clustered index that is not unique, like a log table clustered by date with duplicate timestamps, and many unique indexes that are all non-clustered.
At the National Bank of India Rajesh used both deliberately. The accounts table had a clustered primary key on account id for fast id ranges that return full rows directly, plus two separate non-clustered unique indexes on PAN and email so compliance was guaranteed at the database level even under racing requests. The audit log, by contrast, had a clustered but non-unique index on event date, because it is queried by date range and many events share the same timestamp.
The nuance Pankaj always adds is that in InnoDB every secondary index stores the clustered key as its row pointer, so a wide clustered key silently bloats every other index and doubles the traversals on a secondary lookup. That is exactly why the team kept the clustered key compact. He also notes that PostgreSQL differs: its primary key is really a unique non-clustered index over a heap, and its CLUSTER command only reorders once rather than keeping the table permanently clustered.
Remember one sentence: unique is a correctness rule about values you can have many of, clustered is the single physical order of the rows you can have only one of.
---