PostgreSQL Indexing Strategies That Actually Matter

This is part of the AI Automation Engineer Roadmap series.

TL;DR

Use B-tree for equality/range queries, GIN for full-text and JSONB, and partial indexes when you only query a subset of rows.

Why This Matters

You've added indexes to your PostgreSQL tables, but queries are still slow. The default B-tree index doesn't help with JSONB searches or full-text queries, and indexing every column wastes disk space and slows down writes. You need to match your index type to your actual query patterns.

Indexes are one of the highest-leverage performance tools in PostgreSQL, but only when they match how the application actually queries data.

The wrong indexing strategy creates two problems at once:

• ›reads stay slower than they should be
• ›writes become more expensive because every extra index must be maintained

That is why "add an index" is not enough. The real question is: which index, on which columns, for which query pattern?

Start with Query Patterns, Not Index Types

Before choosing an index, look at the actual workload:

• ›equality lookups
• ›range filters
• ›sort-heavy queries
• ›joins
• ›JSONB containment
• ›full-text search

The index should support the query shape you already have. If you choose the type first and the workload second, you usually end up with waste.

B-tree: The Default for a Reason

B-tree -- the default, ideal for equality and range queries:

-- Perfect for WHERE, ORDER BY, and JOIN conditions
CREATE INDEX idx_orders_created_at ON orders (created_at);
 
-- Composite index for multi-column queries (column order matters!)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

B-tree is usually the right choice for:

• ›WHERE email = ?
• ›WHERE created_at > ?
• ›ORDER BY created_at DESC
• ›join keys

It is also where composite index design matters most.

Composite Index Order Matters

PostgreSQL can only use a composite index efficiently when the leftmost columns align with the query pattern.

For example, this index:

CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

works well for queries like:

SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC;

But it is much less useful for queries that only filter by status without user_id.

GIN: Use It for Search-Like Access Patterns

GIN -- designed for composite values like JSONB, arrays, and full-text:

-- Index JSONB columns for @>, ?, and ?| operators
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Full-text search index
CREATE INDEX idx_articles_search ON articles USING GIN (
  to_tsvector('english', title || ' ' || body)
);

GIN is useful when one row contains many searchable terms or keys. Common examples:

• ›JSONB attribute filtering
• ›array containment
• ›full-text search

It is not a universal replacement for B-tree. Use it when your query pattern is "contains" or "matches tokens," not when you just need normal equality lookups.

Partial Indexes: Often the Highest-ROI Optimization

Partial indexes -- index only the rows you actually query:

-- Only index active orders (skip 90% of historical rows)
CREATE INDEX idx_active_orders ON orders (created_at)
  WHERE status = 'active';
 
-- Only index non-null values
CREATE INDEX idx_users_email_verified ON users (email)
  WHERE verified_at IS NOT NULL;

Partial indexes are excellent when most rows are irrelevant to the application path that needs speed.

Good examples:

• ›active vs archived records
• ›published vs draft content
• ›soft-deleted records
• ›non-null values only

These indexes are smaller, faster to scan, and cheaper to maintain than indexing the entire table.

Covering Indexes and Included Columns

For some read-heavy queries, an index can do more than locate rows. It can also supply the selected columns so PostgreSQL avoids extra heap lookups.

CREATE INDEX idx_orders_user_status_covering
  ON orders (user_id, status)
  INCLUDE (total_amount, created_at);

This can help when a query frequently selects a small set of non-filter columns along with its filter conditions.

Use EXPLAIN ANALYZE Relentlessly

Check which indexes are actually being used:

EXPLAIN ANALYZE SELECT * FROM orders
  WHERE user_id = 42 AND status = 'active';

This is non-negotiable. PostgreSQL's planner may ignore an index if:

• ›the table is small
• ›the query returns a large percentage of rows
• ›statistics are stale
• ›the index does not match the predicate shape

Never assume an index helps just because it exists.

Common Mistakes

Indexing Every Column Separately

This is a common overreaction to slow queries. It increases write cost and often still fails to support the actual query plan you need.

Ignoring Composite Query Shape

If the query filters by user_id, status, and sorts by created_at, a single well-ordered composite index is often better than several unrelated single-column indexes.

Using GIN for Problems It Does Not Solve

GIN is great for JSONB and search-like workloads. It is not the right default for ordinary relational filters.

Forgetting Write Costs

Every insert, update, and delete has to maintain indexes. A heavily indexed table can become slower on writes even if reads improve.

Practical Indexing Workflow

The safest performance workflow looks like this:

1. ›identify the slow query
2. ›inspect EXPLAIN ANALYZE
3. ›design an index for that exact query pattern
4. ›test again
5. ›measure write tradeoffs if the table is hot

That is much better than adding indexes based on intuition alone.

Production Recommendations

If you want a pragmatic default:

1. ›start with B-tree for relational filters and ordering
2. ›add GIN for JSONB, arrays, and full-text search
3. ›prefer partial indexes when only a subset of rows matters
4. ›use composite indexes for real multi-column query paths
5. ›verify with EXPLAIN ANALYZE before and after

Why This Works

B-tree indexes store sorted data in a balanced tree, making equality lookups and range scans O(log n). GIN (Generalized Inverted Index) builds an inverted index mapping each key or lexeme to the rows containing it, which is why it excels at "contains" operations on composite types. Partial indexes only store entries for rows matching the WHERE clause, so they're smaller, faster to scan, and cheaper to maintain on writes. Always use EXPLAIN ANALYZE to verify PostgreSQL is actually using your index -- the query planner may choose a sequential scan if the table is small or the query returns too many rows.

Final Takeaway

Good PostgreSQL indexing is not about collecting index types. It is about matching real query patterns with the cheapest structure that improves them. Start with the workload, not the feature list, and let EXPLAIN ANALYZE keep you honest.