ReviseAlgo Logo

Indexing

B-Tree, Hash, and GIN Indexes

Understanding standard and advanced database indexing strategies.

Last Updated: June 15, 2026 • 17 min read

1. Introduction

PostgreSQL supports multiple index types optimized for different query patterns. B-Tree handles equality and range queries. Hash is for equality-only lookups. GIN handles full-text search, JSONB, and arrays. GiST handles geometric data. BRIN handles time-series. Choosing the right type is critical.

2. Why It Matters

  • Query pattern matching: Each index type excels at specific patterns. Wrong type = poor performance.
  • Storage efficiency: BRIN indexes on time-series can be 1000x smaller than B-tree equivalents.
  • Advanced queries: GIN enables JSONB containment, array operations, and full-text search.

3. Real-World Analogy

A library has different lookup systems: alphabetical card catalog (B-tree), barcode scanner for exact ISBN (Hash), subject keyword index (GIN), and date-stamped new arrivals shelf (BRIN).

4. How It Works

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Using Hash when B-tree works better

Hash indexes only support equality. B-tree handles equality AND range. Use Hash only for pure equality on high-cardinality columns.

GIN write overhead

GIN indexes are 2-3x larger and slower to update. Use fastupdate = on for write-heavy tables.

9. Quick Quiz

Q1: Which index for a 100-billion row log table filtered by timestamp?

Answer: BRIN. Timestamps are monotonically increasing, so BRIN stores only block-level min/max. 100B rows might need only 10MB vs 3GB for B-tree.

10. Scenario-Based Challenge

Challenge: Product Search Engine Indexing

Design indexes for: exact SKU lookup, price range filtering, full-text search, tag filtering (array), and category+price combined filtering.

11. Debugging Exercise

GIN index on TEXT isn't working with @@ operator. Why?

Fix: Index to_tsvector('english', content) instead of raw content.

12. Interview Questions

Q1: When would you choose BRIN over B-tree?

A: When the column is physically correlated with disk order (timestamps, auto-increment IDs). BRIN stores min/max per block range — for 1TB tables, BRIN might use 1MB vs 10GB for B-tree.

Q2: How does GIN handle JSONB queries?

A: GIN creates an inverted index of all key-value pairs. Containment queries look up each pair and intersect results — much faster than scanning all documents.

13. Production Considerations

  • GIN fastupdate: Enable pending list batching to reduce write amplification on high-insert tables.
  • BRIN autosummarize: Enable auto-updating of block range summaries for new inserts.
  • Multiple index types: Use B-tree for IDs, GIN for JSONB, BRIN for timestamps on the same table.
  • pg_trgm extension: Adds trigram GIN indexes for LIKE/ILIKE queries.