Types of Indexes

Not all indexes are the same. Different query patterns need different index types. Let’s walk through each one and when it makes sense.

Hash Indexes

A hash index uses a hash function to map values to buckets. It’s incredibly fast for exact equality lookups (=), but completely useless for range queries (>, <, BETWEEN).

Think of it like a dictionary where we hash the word to find its page. Great for “find this exact word”, terrible for “find all words between A and D”.

-- PostgreSQL supports hash indexes
CREATE INDEX idx_users_email_hash ON users USING hash (email);

-- This uses the hash index (equality)
SELECT * FROM users WHERE email = 'manish@example.com';

-- This CANNOT use the hash index (range query)
SELECT * FROM users WHERE email > 'a' AND email < 'm';

In practice, B-Tree indexes handle equality lookups almost as fast AND support range queries too. So hash indexes are rarely used in PostgreSQL. But they’re the default in Redis and other key-value stores.

Composite (Compound) Indexes

A composite index is an index on multiple columns. The column order matters a lot because of the leftmost prefix rule.

The leftmost prefix rule says: a composite index on (a, b, c) can be used for queries filtering on:

• a alone
• a and b together
• a, b, and c together

But NOT for:

• b alone
• c alone
• b and c together

-- Composite index on status and created_at
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Uses the index (leftmost prefix)
SELECT * FROM orders WHERE status = 'shipped';
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-01-01';

-- Does NOT use the index (skips leftmost column)
SELECT * FROM orders WHERE created_at > '2024-01-01';

Pro tip: Put the most selective column (the one that filters out the most rows) first.

Covering Indexes

A covering index includes all the columns that a query needs. The database can answer the query entirely from the index without going to the actual table. This is called an index-only scan and it’s the fastest type of query.

-- If we frequently run this query:
SELECT email, name FROM users WHERE email = 'manish@example.com';

-- We can create a covering index using INCLUDE
CREATE INDEX idx_users_email_cover ON users(email) INCLUDE (name);

-- Now the database doesn't need to read the table at all
-- Everything it needs is in the index

Partial Indexes

A partial index only indexes a subset of rows based on a condition. Smaller index = faster lookups + less storage.

-- Only index active users (skip the millions of deactivated accounts)
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;

-- Only index unprocessed orders
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

This is perfect when we have a large table but only query a small subset of rows frequently.

Unique Indexes

A unique index ensures no duplicate values exist in the indexed column(s). Primary keys automatically create a unique index.

-- Ensure no duplicate emails
CREATE UNIQUE INDEX idx_users_unique_email ON users(email);

-- Composite unique index (combination must be unique)
CREATE UNIQUE INDEX idx_enrollment ON enrollments(student_id, course_id);

-- Attempting a duplicate insert will fail
INSERT INTO users (email) VALUES ('manish@example.com');  -- OK
INSERT INTO users (email) VALUES ('manish@example.com');  -- ERROR: duplicate

Full-Text Indexes (GIN / GiST)

Regular B-Tree indexes can’t search inside text efficiently. For full-text search, we need specialized indexes.

GIN (Generalized Inverted Index) — best for full-text search, JSONB, and array columns. It builds an inverted index (word → list of documents containing it).

GiST (Generalized Search Tree) — best for geometric data, range types, and fuzzy text search.

-- PostgreSQL: GIN index for full-text search
CREATE INDEX idx_articles_search ON articles
  USING gin(to_tsvector('english', title || ' ' || body));

-- PostgreSQL: GIN index on a JSONB column
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- MySQL: FULLTEXT index
CREATE FULLTEXT INDEX idx_articles_ft ON articles(title, body);

Quick Reference

In simple language, choosing the right index type for our query pattern can make a massive difference. Don’t just slap a B-Tree on everything — think about what queries we’re running and pick the right tool.