PostgreSQL Advanced Indexing: Beyond Basic B-Tree Indexes

Most developers index foreign keys and WHERE clause columns. But PostgreSQL's indexing capabilities go far beyond basic B-tree indexes. The right index type can transform a 10-second query to 10 milliseconds.

Understanding Index Internals

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'pending'
AND created_at > NOW() - INTERVAL '7 days';

Key metrics:

• Actual Rows vs Plan Rows: Large divergence = stale statistics
• Buffers hit vs read: Cache hit ratio
• Actual Total Time: True execution cost

Partial Indexes: Your Secret Weapon

Index only a subset of rows—dramatically smaller and faster:

-- 95% of orders are 'completed', so index only pending ones
-- Result: 20x smaller index

CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- ~10MB vs ~500MB for full index on 50M rows

-- Query MUST match the index predicate
SELECT * FROM orders
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '1 hour';

-- Soft delete pattern
CREATE INDEX idx_users_active ON users(email)
WHERE deleted_at IS NULL;

Covering Indexes: Eliminate Heap Fetches

-- Query needs: id, email, name
SELECT id, email, name
FROM users
WHERE email LIKE 'john%' AND status = 'active';

-- Covering index: INCLUDE non-filter columns
CREATE INDEX idx_users_covering ON users(email, status)
INCLUDE (id, name);
-- Result: Index Only Scan (0 heap fetches!)
-- 3-10x faster than regular Index Scan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, name FROM users
WHERE email LIKE 'john%' AND status = 'active';
-- Look for: "Index Only Scan" + "Heap Fetches: 0"

Expression Indexes

-- Case-insensitive email search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Date-truncated time series
CREATE INDEX idx_events_date ON events(DATE_TRUNC('day', created_at));
SELECT DATE_TRUNC('day', created_at) as day, COUNT(*)
FROM events
WHERE DATE_TRUNC('day', created_at) >= '2026-01-01'
GROUP BY 1;

-- JSON field indexing
CREATE INDEX idx_orders_priority ON orders((metadata->>'priority'));
SELECT * FROM orders WHERE metadata->>'priority' = 'high';

GIN Indexes for Full-Text Search and JSONB

-- Full-text search setup
ALTER TABLE articles ADD COLUMN search_vector tsvector;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Ranked search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql & indexing') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- JSONB containment
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);
SELECT * FROM products WHERE attributes @> '{"color": "blue"}';

-- Array overlap
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags && ARRAY['postgresql', 'performance'];

BRIN Indexes for Time-Series

Block Range Index: 500x smaller than B-tree for ordered data:

-- Only 1MB for 100M rows (vs 500MB B-tree)
CREATE INDEX idx_events_brin ON events
USING BRIN(created_at) WITH (pages_per_range = 32);

-- Efficient range scans on append-only time-series
SELECT * FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- BRIN only works if data is physically ordered!

GiST Indexes for Spatial and Ranges

-- PostGIS spatial queries
CREATE INDEX idx_stores_location ON stores USING GIST(location);
SELECT name FROM stores
WHERE ST_DWithin(location::geography,
  ST_MakePoint(-122.4194, 37.7749)::geography, 10000);

-- Automatic conflict prevention with exclusion constraints
CREATE TABLE reservations (
  room_id int,
  during daterange,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
-- Prevents double-booking at the database level!

Monitoring

-- Find unused indexes
SELECT tablename, indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild without locking
REINDEX INDEX CONCURRENTLY idx_orders_status;

Decision Guide

Every index slows writes. Measure actual query patterns before over-indexing.