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

-- B-TREE (default, handles = < > BETWEEN ORDER BY)
CREATE INDEX idx_price ON products USING btree(price);
-- HASH (equality only)
CREATE INDEX idx_token ON sessions USING hash(token);
-- GIN (full-text search, JSONB, arrays)
CREATE INDEX idx_search ON articles USING gin(to_tsvector('english', title || ' ' || body));
CREATE INDEX idx_tags ON products USING gin(tags);
CREATE INDEX idx_payload ON events USING gin(payload);
-- BRIN (time-series, monotonically increasing)
CREATE INDEX idx_created ON events USING brin(created_at);

5. Internal Architecture

Index Type Decision Matrix:
Operator  │ B-Tree │ Hash │ GIN  │ GiST │ BRIN
=         │  │  │  │  │  < >      │  │  │  │  │  BETWEEN   │  │  │  │  │  @@ (FTS)  │  │  │  │  │  @> (JSON) │  │  │  │  │  Size (1M rows): B-tree ~30MB, Hash ~15MB, GIN ~50MB, BRIN ~0.1MB

6. Visual Explanation

7. Practical Example

-- JSONB search with GIN
CREATE INDEX idx_events_payload ON events USING gin(payload);
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

-- Full-text search with GIN
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_fts ON articles USING gin(search_vector);
SELECT title FROM articles WHERE search_vector @@ to_tsquery('database & performance');


-- BRIN for time-series (1000x smaller than B-tree)
CREATE INDEX idx_logs_ts ON logs USING brin(created_at);

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?

CREATE INDEX idx_docs ON documents USING gin(content);
SELECT * FROM documents WHERE content @@ to_tsquery('database');

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.