Types of Indexes in SQL – Complete Guide for Interviews

Indexes were one of those topics that confused me a lot during interview prep. I knew they make queries faster, but when someone asked "What type of index would you use for full-text search?" or "When to choose BRIN over B-tree?", I went blank.

After digging deep (and getting a few rejections ), I finally got it. Today, I'm sharing everything I learned about types of indexes in SQL (focusing on PostgreSQL, since it's the most common in modern apps and Hasura).

We'll cover the main types, when to use each, pros/cons, and real examples. All with practice queries you can run live on sqlpractice.in.

Let's master indexes together!

What is an Index? (Quick Recap)

An index is like a book's table of contents — it helps the database find data quickly without scanning every row (full table scan).

Without index → slow queries on large tables. With right index → queries 10–100x faster.

Indexes are created automatically for PRIMARY KEY and UNIQUE constraints.

Main Types of Indexes in PostgreSQL

PostgreSQL has 6 major index types. Here's a comparison chart to start:

1. B-Tree (Default & Most Common)

What it is: Balanced tree structure — the default index type in Postgres.

Best for:

• Equality (=) and range queries (>, <, >=, <=, BETWEEN)
• ORDER BY, GROUP BY
• Most common columns (ids, dates, numbers, strings)

Pros: Versatile, supports most operations

Cons: Larger size than specialized indexes

Interview Question: "Why is B-tree the default?" Answer: Handles most use cases efficiently.

2. Hash Index

Best for: Strict equality (=) queries only (no ranges).

Pros: Faster for exact matches, smaller than B-tree Cons: No range support, not crash-safe in older versions (now improved)

Use rarely — B-tree usually better.

3. GIN (Generalized Inverted Index)

Best for: Composite values — arrays, JSONB, full-text search.

Pros: Super fast for contains (@>) and array operations Cons: Larger index, slower writes

4. GiST (Generalized Search Tree)

Best for: Geometric data, full-text search, complex types (points, circles, polygons).

5. BRIN (Block Range Index)

Best for: Very large tables with naturally ordered data (e.g., timestamps, sequential IDs).

Pros: Tiny index size, fast creation Cons: Less precise than B-tree (good for range scans on huge tables)

Perfect for time-series data.

6. SP-GiST (Space-Partitioned GiST)

Best for: Unbalanced data (phone numbers, IP addresses, trees).

Less common in interviews.

Common Interview Questions

1. "When would you use GIN over B-tree?"
2. What is an Index? Why do we use it?
3. Explain Composite Index and Leftmost Prefix Rule.
4. "Explain BRIN index with example."
5. "Difference between B-tree and Hash index?"
6. "How do indexes affect INSERT performance?"
7. What are the disadvantages of having too many indexes?
8. Difference between Clustered and Non-Clustered Index.
9. How do you find missing indexes or unused indexes in PostgreSQL?
10. How does an index affect INSERT/UPDATE performance?

Practice Questions (Easy to Hard)

• Create B-tree index on product price
• Search products with tag 'electronics' (GIN)
• Find orders from last month (BRIN on date)

Final Thoughts

Indexes are a trade-off between read speed and write speed. The art is knowing which index to create and when to avoid it.

Start by adding B-tree indexes on frequently used WHERE and JOIN columns. Then slowly explore Composite, Partial, and GIN indexes as needed.

Practice all index questions live →