PostgreSQL Indexing: B-tree, GIN, Partial and Expression Indexes

Indexes are the single biggest lever for query performance. The right one turns a full table scan into a lookup of a few pages; the wrong ones slow down every write for nothing.

B-tree: the default

CREATE INDEX orders_customer_idx ON orders (customer_id);

Works for =, <, >, BETWEEN, ORDER BY and prefix LIKE 'abc%'.

Composite indexes: column order matters

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);

-- Uses the index fully: equality on the first column, range/sort on the second
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Put columns used with equality first, then range or sort columns.

Partial indexes

-- Only 2% of orders are pending, and that's what the dashboard queries
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

-- Enforce uniqueness only among active rows
CREATE UNIQUE INDEX users_email_active_uq ON users (lower(email)) WHERE deleted_at IS NULL;

Expression indexes

CREATE INDEX users_lower_email_idx ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower('Asha@Example.com');  -- matches the expression exactly

GIN: JSONB, arrays and full-text search

CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type": "signup"}';

CREATE INDEX posts_tags_idx ON posts USING gin (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

Covering indexes

CREATE INDEX orders_customer_cover_idx ON orders (customer_id) INCLUDE (status, total_paise);
-- Can answer from the index alone (index-only scan)

Housekeeping

-- Build without blocking writes on a busy table
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);

-- Indexes that are never used (since stats were last reset)
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

Every index costs disk space and slows inserts and updates. Add them for real queries, verify with EXPLAIN, and drop the ones nothing uses.