PostgreSQL Indexing Strategies: Choosing the Right Index for Every Query

Most production performance problems in database-backed applications trace back to the same root cause: missing or misused indexes. Not slow queries per se — slow queries are the symptom. The cause is that the database is scanning millions of rows when it could be skimming a handful.

PostgreSQL gives you an unusually rich indexing toolkit. Beyond the default B-tree, there are GIN indexes for full-text search and arrays, BRIN for time-series tables, GiST for geometric and nearest-neighbor queries, and covering indexes that eliminate heap lookups entirely. The challenge isn’t a lack of options — it’s knowing which tool fits the job and how to verify that it’s actually working.

Let’s walk through the indexing patterns that matter most in production, with working SQL you can adapt to your own schema.

Start With EXPLAIN, Not With Indexes

Before creating a single index, look at what the query planner is actually doing. The EXPLAIN (ANALYZE, BUFFERS) command shows you the real execution plan with actual row counts and I/O statistics. This is your ground truth — if the planner is already doing an index scan, adding another index won’t help.

EXPLAIN (ANALYZE, BUFFERS)
SELECT email, created_at
FROM users
WHERE status = 'active' AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

If you see Seq Scan on a large table, that’s a red flag. If you see Index Scan or Index Only Scan, the planner is already using an index. The BUFFERS option shows how many shared buffer hits versus disk reads occurred, telling you whether your working set fits in memory.

B-Tree: The Workhorse, Used Correctly

The default B-tree index handles equality (=), range (<, >, BETWEEN), and IS NULL checks. It also supports sorted output, so the planner can use a B-tree to satisfy ORDER BY without a separate sort step.

The most common mistake is getting column order wrong in composite indexes. The rule is simple: equality columns first, then the range or sort column. The index is only usable if the leading column is constrained by the query.

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY created_at
-- Correct: equality column first, then range/sort column
CREATE INDEX idx_users_status_created
ON users (status, created_at DESC);

-- Wrong: created_at in the lead position means the planner can't
-- efficiently use this for the status = 'active' predicate
CREATE INDEX idx_users_bad
ON users (created_at DESC, status);

The second index isn’t useless — it can serve queries filtering on created_at alone — but it won’t help the specific query above as efficiently. Getting column order right from the start matters.

Covering Indexes: Skip the Heap Visit

In a standard index scan, PostgreSQL finds matching entries in the index, then makes a separate heap lookup for each row to fetch column values. That heap access is random I/O — the most expensive kind on traditional storage.

Introduced in PostgreSQL 11, the INCLUDE clause lets you add non-key columns to an index. These columns are stored as payload — they’re not part of the search structure, but they’re available for retrieval. When all columns needed by a query are in the index, PostgreSQL can perform an index-only scan, skipping the heap entirely.

-- Query: SELECT email FROM users WHERE user_id = 42;
-- Without INCLUDE: index lookup + heap visit to get 'email'
-- With INCLUDE: index-only scan, no heap access

CREATE UNIQUE INDEX idx_users_id_email
ON users (user_id)
INCLUDE (email);

There’s a caveat: index-only scans require that the heap pages being referenced have their visibility map bits set. This means the pages must be “all-visible” — all transactions can see all rows on that page. Recently updated pages won’t qualify until VACUUM runs and sets the bits. For tables with frequent updates, index-only scans may degrade to regular index scans more often than you’d expect.

Partial Indexes: Don’t Index What You Won’t Query

Many applications have tables where queries almost always filter on a specific subset. Orders where status = 'pending'. Support tickets where resolved = false. Tasks where completed_at IS NULL. A full index includes millions of rows that no query will ever look up through that index.

A partial index restricts itself to rows matching a WHERE predicate. This shrinks the index, speeds up writes, and focuses the index on exactly the rows your queries target.

-- Only index pending orders — skip the vast majority that are completed
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- Unique email constraint that only applies to active users
-- (allows deleted users to share a previously-used email)
CREATE UNIQUE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

The critical requirement: your query’s WHERE clause must imply the index’s predicate. PostgreSQL doesn’t have a theorem prover — it uses straightforward pattern matching. WHERE billed IS NOT true in the query will match WHERE billed IS NOT true in the index. WHERE NOT billed may not, even though they’re logically equivalent. Keep the predicate expressions identical.

GIN: Indexing the Un-indexable

Generalized Inverted Indexes (GIN) are designed for composite values — where each row contains multiple component values that need to be searched individually. The most common use cases are full-text search using tsvector, JSONB column queries, and array containment checks.

-- Full-text search on product descriptions
CREATE INDEX idx_products_search
ON products
USING GIN (to_tsvector('english', name || ' ' || description));

-- JSONB containment queries on a metadata column
CREATE INDEX idx_events_metadata
ON events
USING GIN (metadata jsonb_path_ops);

-- Array membership: WHERE tags && ARRAY['go', 'postgres']
CREATE INDEX idx_articles_tags
ON articles
USING GIN (tags);

The jsonb_path_ops operator class deserves attention — it produces significantly smaller GIN indexes than the default for JSONB columns, at the cost of supporting fewer operators (containment @>, path existence @?, and path match @@). If you’re doing JSONB lookups on large tables, this can cut index size substantially.

BRIN: Massive Tables, Tiny Indexes

Block Range Indexes (BRIN) are the most underused index type in PostgreSQL. Instead of indexing individual rows, BRIN stores summary information (min and max values) for ranges of physical blocks. For a table with billions of rows, a BRIN index can be a few megabytes instead of gigabytes.

The trade-off: BRIN only works well when column values are naturally correlated with physical row order — which is true for append-only tables like logs, metrics, and time-series data. If the values are randomly distributed, BRIN is useless because every block range will contain all possible values.

-- Time-series table: events are appended chronologically
-- A B-tree on timestamp would be huge; BRIN is nearly free
CREATE INDEX idx_events_ts_brin
ON events
USING BRIN (timestamp_at)
WITH (pages_per_range = 32);

-- Range queries like WHERE timestamp_at > '2026-07-01'
-- can skip entire block ranges where the max value is older

For a billion-row event table, a BRIN index on the timestamp column might be 2 MB while a B-tree equivalent would be 20+ GB. The BRIN won’t pinpoint individual rows like a B-tree, but it lets the planner skip 90%+ of the table for range queries, which is often all you need.

Index Maintenance: When Good Indexes Go Bad

Indexes aren’t fire-and-forget. Over time, they accumulate bloat from updates and deletes, and unused indexes silently tax every write. Two maintenance tasks should be part of your regular database health checks.

Detect unused indexes. PostgreSQL tracks index usage statistics in pg_stat_user_indexes. Any index with zero idx_scan counts over a representative period is a candidate for removal. Every index adds overhead to every INSERT, UPDATE, and DELETE.

SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND indisunique IS FALSE
ORDER BY pg_relation_size(indexrelid) DESC;

Rebuild bloated indexes. The REINDEX INDEX CONCURRENTLY command rebuilds an index without blocking writes. For production systems, always use CONCURRENTLY — a regular REINDEX takes an exclusive lock.

-- Safe for production: no exclusive lock, but takes longer
REINDEX INDEX CONCURRENTLY idx_users_status_created;

Putting It Together

Effective indexing comes down to a handful of principles. Match the index type to the query pattern — B-tree for equality and range, GIN for arrays and JSONB, BRIN for append-only time-series, GiST for geometric and nearest-neighbor. Order composite index columns by constraint type: equality first, range and sort after. Use covering indexes to enable index-only scans for your hottest read paths. Use partial indexes to focus storage and write overhead on the rows that actually matter.

And always validate with EXPLAIN (ANALYZE, BUFFERS) before and after creating an index. If the plan doesn’t change, the index isn’t helping — it’s just consuming space and write bandwidth. The best index is the one the planner actually uses.