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
- "When would you use GIN over B-tree?"
- "Explain BRIN index with example."
- "Difference between B-tree and Hash index?"
- "How do indexes affect INSERT 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)