How Indexes Work — B-tree, Hash and Index Types

An index is a separate data structure that PostgreSQL maintains alongside a table, organised to make specific queries faster. Without an index, finding rows that match a WHERE condition requires reading every row in the table — a sequential scan. With an index on the right column, PostgreSQL can jump directly to the matching rows — an index scan. The tradeoff: indexes make reads faster but writes slower (every INSERT, UPDATE, and DELETE must also update all relevant indexes). Understanding how different index types work helps you choose the right index for each query pattern in your FastAPI application.

B-tree Indexes — The Default #

-- B-tree (balanced tree) is the default index type
-- Supports: =, <, >, <=, >=, BETWEEN, IN, IS NULL, IS NOT NULL, LIKE 'prefix%'

-- Create a B-tree index (default)
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness AND adds index for fast lookups)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- What queries can use this index:
-- WHERE status = 'published'           ✓ equality
-- WHERE created_at > '2025-01-01'     ✓ range
-- WHERE status IN ('draft', 'published') ✓ in-list
-- WHERE email LIKE 'alice%'           ✓ prefix LIKE
-- WHERE email LIKE '%alice%'          ✗ leading wildcard — index not used!
-- WHERE LOWER(email) = 'alice@...'    ✗ function applied — index not used!
-- ORDER BY created_at DESC             ✓ covers sort, avoids filesort

Index Types Overview #

GIN Indexes — Arrays, JSONB and Full-Text #

-- GIN (Generalised Inverted Index) — for composite values
-- Each element is indexed separately

-- Array containment queries
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['python'];    -- ✓ uses GIN index

-- JSONB queries
CREATE INDEX idx_posts_metadata ON posts USING GIN (metadata);
SELECT * FROM posts WHERE metadata @> '{"difficulty": "beginner"}';   -- ✓ uses GIN

-- Full-text search
CREATE INDEX idx_posts_fts ON posts USING GIN (
    to_tsvector('english', title || ' ' || body)
);
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'fastapi & tutorial');

Expression Indexes #

-- Index on a function result — useful for case-insensitive queries
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Now this query uses the index:
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Without this index, LOWER(email) = '...' would do a sequential scan
-- because the index on email(col) is built on the original values,
-- not the lowercased values

-- Index for slug generation pattern
CREATE INDEX idx_posts_title_slug ON posts(LOWER(REGEXP_REPLACE(title, '\W+', '-', 'g')));

-- Index for date-based queries
CREATE INDEX idx_posts_published_month ON posts(DATE_TRUNC('month', published_at));
SELECT * FROM posts
WHERE DATE_TRUNC('month', published_at) = '2025-08-01';

Common Mistakes #

Mistake 1 — Function call on indexed column prevents index use #

❌ Wrong — LOWER() prevents the index on email from being used:

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- index on email not used — sequential scan!

✅ Correct — create an expression index OR use ILIKE:

CREATE INDEX idx_users_email_lower ON users(LOWER(email));   -- expression index
-- OR store email as lowercase always and query with =

Mistake 2 — Missing index on foreign key column #

❌ Wrong — FK without index causes sequential scan on large table:

-- posts.author_id REFERENCES users(id) — no index on author_id!
SELECT * FROM posts WHERE author_id = 5;   -- scans all 1M posts!

✅ Correct — always index FK columns:

CREATE INDEX idx_posts_author_id ON posts(author_id);   -- ✓

Mistake 3 — Over-indexing write-heavy tables #

❌ Wrong — 12 indexes on a high-write events table:

-- Every INSERT into events must update 12 index structures
-- Bulk imports that should take 5 seconds take 60 seconds!

✅ Correct — audit and drop unused indexes regularly:

SELECT indexname, idx_scan FROM pg_stat_user_indexes
WHERE relname = 'events' ORDER BY idx_scan;
-- Drop indexes with idx_scan = 0 (never used)

Quick Reference #