Postgres indexing in practice: B-tree, GIN, BRIN, and when each one wins

The default advice — 'add an index' — is where most teams stop, and it's why so many production databases are full of indexes that are never used while the queries that hurt still seq-scan. Postgres ships six index types and three or four index modifiers that matter. Knowing which one fits which query shape is most of the game.

B-tree: the default, and usually right

B-tree handles equality and range queries on ordered data — which is 90% of OLTP workloads. The details that actually matter: column order in composite indexes (leftmost prefix rule — an index on (org_id, created_at) serves WHERE org_id = ? and WHERE org_id = ? AND created_at > ?, but not WHERE created_at > ? alone), and the fact that an index on an expression only helps if the query uses the exact same expression.

-- This index...
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- ...serves this query
SELECT * FROM users WHERE lower(email) = lower($1);

-- ...but NOT this one (no expression match)
SELECT * FROM users WHERE email = $1;

GIN: anything with multiple values per row

GIN (Generalized Inverted Index) is the answer whenever one row contains many indexable values: jsonb columns, arrays, and full-text search vectors. The classic mistake is adding a B-tree to a jsonb column and wondering why containment queries (@>) still seq-scan. GIN is bigger and slower to update than B-tree — budget for that on write-heavy tables, and consider the fastupdate storage parameter if insert latency spikes.

BRIN: huge append-only tables for nearly free

BRIN (Block Range Index) stores min/max values per block range instead of per row, so it's absurdly small — megabytes where a B-tree would be gigabytes. It only works when the column's values correlate with physical row order, which in practice means created_at on append-only tables: events, logs, audit trails. If your time-series table has a B-tree on its timestamp column, try BRIN; the storage savings alone can pay for the migration.

Partial and covering indexes: the underused workhorses

-- Partial: index only the rows queries actually touch.
-- 95% of subscription lookups are for active ones:
CREATE INDEX idx_subs_active ON subscriptions (user_id)
  WHERE status = 'active';

-- Covering: satisfy the query entirely from the index
-- (no heap fetch) with INCLUDE:
CREATE INDEX idx_orders_user ON orders (user_id)
  INCLUDE (total, created_at);

Partial indexes are smaller, faster to update, and more likely to stay in cache. Covering indexes enable index-only scans — watch for 'Heap Fetches: 0' in EXPLAIN ANALYZE to confirm they're working (and vacuum regularly, because index-only scans depend on the visibility map).

Reading EXPLAIN without dread

• Always use EXPLAIN (ANALYZE, BUFFERS) - the estimates alone will lie to you when statistics are stale.
• A seq scan is not automatically bad. For queries touching >5-10% of a table, it's often faster than an index scan.
• 'Rows Removed by Filter' in the thousands means your index isn't selective enough - a partial index or an added column usually fixes it.
• Check pg_stat_user_indexes.idx_scan monthly and drop indexes with zero scans - every unused index is pure write amplification.

The workflow that keeps you honest

Enable pg_stat_statements, review the top ten queries by total time once a sprint, EXPLAIN the offenders, and fix the worst one. Indexing isn't a one-time setup task — it's ongoing maintenance driven by what production actually runs, not what you guessed at schema-design time.

Written by Appesto Engineering.