A Practical Guide to PostgreSQL Indexing

Every slow query story eventually gets the same first prescription: "add an index." And it fixes maybe one in three cases — the rest are indexes that were never used, indexes doing the wrong kind of work, or indexes that look right but kill write throughput. Indexes aren't free. Each one costs disk, memory, and a write-amplification tax on every INSERT.

This article is the mental model I wish someone handed me before I indexed my way through a production schema: what B-trees are for, when GIN genuinely saves you, why partial and composite indexes beat "just add one," and a five-question checklist that filters out bad index requests.

The B-tree, and what it actually guarantees

The default PostgreSQL index is a B-tree. It gives you sortedness, which means the planner can use it for three very different things: equality lookups (WHERE user_id = $1), range scans (WHERE created_at > $1), and sorting / merge joins ($avoiding an explicit ORDER BY sort). If a query needs none of those properties, a B-tree on it is usually theater.

The common beginner error is indexing a column the query filters on but wrapping in a function:

-- Suppose status is stored as text: 'ACTIVE', 'INACTIVE', …
CREATE INDEX idx_status_lower ON users (lower(status));
 
-- Now this query can use it:
SELECT * FROM users WHERE lower(status) = 'active';
 
-- But this plain one CANNOT use idx_status_lower … it matches a
-- different index / none at all:
SELECT * FROM users WHERE status = 'ACTIVE';

B-tree indexes work on the expression you actually put in them. If your WHERE clause applies lower(), date_trunc(), or a cast, match the index to the expression — otherwise PostgreSQL quietly seq-scans and your "index" is a monument to good intentions.

GIN: the inverted index for arrays and full-text

GIN (Generalized Inverted Index) is PostgreSQL's answer to containment and token matching. It's the right tool when a value contains many entries and you want to query whether a composite match exists — think array membership, JSONB containment, or full-text search tsvectors.

-- Array membership
CREATE INDEX idx_projects_tags ON projects USING GIN (tags);
 
SELECT * FROM projects WHERE tags @> ARRAY['postgresql']::text[];
 
-- JSONB containment
CREATE INDEX idx_users_meta ON users USING GIN (metadata);
 
SELECT * FROM users WHERE metadata @> '{"plan": "scale"}'::jsonb;
 
-- Full-text search
ALTER TABLE posts ADD COLUMN search_doc tsvector;
CREATE INDEX idx_posts_search ON posts USING GIN (search_doc);
 
SELECT * FROM posts
WHERE search_doc @@ to_tsquery('english', 'multi & tenant');

There's a second, sneaky GIN use-case: partial-match on rows-per-key. If a column has low cardinality (like a status with 5 values), a plain B-tree isn't selective enough — a query for status = 'active' matching 70% of the table will ignore the index and seq-scan anyway. Pre-warming a GIN on the small key-list doesn't help either. What you actually want is a partial index — the under-appreciated workhorse index.

Partial indexes: index only the rows you care about

Most tables have a few "hot rows" and a pile of history. A partial index stores a fraction of the table, so it's tiny, fast to scan, and cheap to maintain:

-- Only ACTIVE users are ever queried for campaigns:
CREATE INDEX idx_campaign_user ON campaign_users (user_id)
WHERE status = 'active';
 
-- Queries that filter on that predicate can use it:
SELECT * FROM campaign_users
WHERE user_id = $1 AND status = 'active';

The planner will only use a partial index when the query's predicates imply the index's predicate. Keep the WHERE clause in the query (PostgreSQL checks the implied predicate, so writing the same status filter is both semantically correct and index-friendly).

A partial index is also the classic fix for a different symptom: the "index that makes inserts slow." If an archive table keeps growing forever but you only query the last 90 days, index on WHERE created_at > now() - interval '90 days' — writes to old rows stop paying index maintenance, and the index stays permanently small.

Composite indexes: column order is the contract

When two or more columns appear together in filters, a single composite index usually beats two separate single-column indexes. But order matters enormously, and PostgreSQL's rule is simple:

Put the equality columns first, the range column last. The index can lead with equalities, but as soon as it hits a range condition, everything after it is useless for that query.

CREATE INDEX idx_tenancy_time ON events (tenant_id, created_at);
 
-- Excellent: equality leads, range finishes.
SELECT * FROM events
WHERE tenant_id = $1 AND created_at > now() - interval '7 days';
 
-- Also excellent: the range can be BETWEEN because it's last.

Flip the order and the range-constrained query becomes a tuple-scan of every tenant — same disk, no win. And note: (tenant_id, created_at) makes a standalone tenant_id single-column index redundant, because the leading column of the composite can serve the equality lookup alone. That's one fewer write-tax index.

How to actually verify an index is being used

EXPLAIN ANALYZE is the only honest UI:

EXPLAIN (ANALYZE, BUFFERS) SELECT … WHERE …;

Read the plan for Index Scan (good), Bitmap Index Scan (fine, planning to recheck), and Seq Scan (your index wasn't used — ask why). Then check twice: once with a real query and once with a node-local variable that matches production cardinality. Indexes that get used under a 1,000-row local test but not under a 50M-row production table are a classic "why is it still slow in prod" mystery.

The five-question index veto

Before you add an index, answer these in order:

1. Is this query actually slow in production? If nobody's page is slow, you're adding write-tax for a hypothetical.
2. Is the query WHERE-able as written? Unwrapped function calls, LIKE '%…' without a trigram operator, or OR across un-unioned columns usually match no index.
3. Which index property is paying off? Equality vs. range vs. sort — pick the index shape (plain, partial, composite, expression, GIN) that maps to one of them.
4. Is a partial/composite version cheaper than a naive one? A 5% partial index on your hot predicate beats a full-table B-tree on a rare column.
5. Will this index starve writes? On a write-heavy table, one big composite index replacing two singles is the difference between a smooth deploy and an INSERT spike falling over.

The compounding payoff

Indexing is where database discipline compounds: the first correct index eliminates a seq-scan your server was doing 40x/second; pruning the fake ones removes write latency you haven't measured yet. Do it with EXPLAIN ANALYZE as your referee, treat every index as a maintained artifact with a documented justification, and your schema will still be fast when the table has ten times more rows than it does today.