All writing
postgresql database-optimization indexing query-performance

Database Indexing Strategies That Work in Production

Ayush Kaushik 7 min read
Database Indexing Strategies That Work in Production

I’ve spent years debugging slow queries that trace back to poor database indexing strategies. The fix is rarely adding more indexes. It’s understanding which index type matches your access pattern, ordering composite columns for the queries you actually run, and knowing when a partial index saves you gigabytes of write amplification. This post covers the decisions I make on production PostgreSQL clusters running FastAPI services with heavy read and write loads.

How Do I Choose Between B-Tree, Hash, and GIN Indexes?

Start with B-Tree. It is the default for a reason. It handles equality and range queries, sorts naturally, and supports ORDER BY optimization. Use Hash indexes only for pure equality checks on high-cardinality columns where you never need ranges or sorting. They are smaller than B-Trees for that specific case but PostgreSQL does not write them to WAL by default in older versions, so check your replication setup.

GIN indexes are for composite values: jsonb, arrays, tsvector, or full-text search. I use them heavily in RAG pipelines where metadata filtering happens inside a jsonb column. If you are building a retrieval system, the chunking strategy determines your filter cardinality. I covered that in Practical RAG chunking strategies for reliable RAG pipelines.

-- Good: B-Tree for range and sort
CREATE INDEX idx_events_created_at ON events (created_at DESC);
-- Good: GIN for jsonb containment queries
CREATE INDEX idx_events_metadata ON events USING GIN (metadata jsonb_path_ops);
-- Rarely good: Hash for pure equality on UUIDs
CREATE INDEX idx_users_external_id ON users USING HASH (external_id);

Don’t create a GIN index on a scalar column. Don’t use Hash if you might need ORDER BY or >. The planner will ignore the index and you will have wasted write throughput.

What Is the Right Column Order for Composite Indexes?

Put equality columns first, then range columns, then sort columns. The planner can only use a prefix of the index. If your query filters on status = 'active' and created_at > '2024-01-01', the index must be (status, created_at). Reversing the order makes the index useless for the status filter because created_at is a range.

-- Query: WHERE tenant_id = 5 AND status = 'open' ORDER BY priority DESC, created_at
-- Index:
CREATE INDEX idx_tickets_tenant_status_priority ON tickets (tenant_id, status, priority DESC, created_at);

I learned this the hard way on a multi-tenant API. We had (created_at, tenant_id) because “time series first.” Every query filtered by tenant first. The planner scanned the entire time range for that tenant. Flipping the order dropped p99 latency from 800ms to 12ms.

If you have OR conditions across columns, a composite index usually won’t help. Consider separate indexes and a bitmap scan, or restructure the query.

When Do Partial and Expression Indexes Pay Off?

Partial indexes shrink the index size by only indexing rows that match a WHERE clause. Expression indexes let you index the result of a function. Both are surgical tools for sparse data patterns.

We run a webhook ingestion endpoint. 95% of rows have processed = true. We never query processed rows by ID. We only look for processed = false to retry.

-- Tiny index, only ~5% of the table
CREATE INDEX idx_webhooks_pending ON webhooks (id) WHERE processed = false;

That index is a fraction of the size. Inserts for processed rows don’t touch it. Write amplification drops.

Expression indexes handle computed access patterns. If you query lower(email) for case-insensitive login, index the expression:

CREATE INDEX idx_users_lower_email ON users (lower(email));

Without it, the planner cannot use a standard index on email for that predicate. I’ve seen teams add citext columns instead. That works but doubles storage for the column. An expression index is cheaper if the column stays as text.

Can Covering Indexes Eliminate Heap Fetches?

Yes. INCLUDE columns let you satisfy a query entirely from the index without visiting the heap. This is an index-only scan. It requires the visibility map to be set for the pages, so autovacuum must keep up.

We have a high-throughput endpoint that returns id, status, external_id for a tenant dashboard. The table has 50 columns including large jsonb payloads.

CREATE INDEX idx_orders_tenant_covering ON orders (tenant_id, status) INCLUDE (external_id, created_at);

The query SELECT id, status, external_id, created_at FROM orders WHERE tenant_id = $1 AND status = $2 never hits the heap. Throughput doubled because we stopped pulling 2KB rows into shared buffers for a 200-byte response.

Trade-off: every INCLUDE column widens the index tuple. Updates to included columns still write to the index. Don’t include columns that change frequently.

What Is the Write Amplification Cost of Each Index?

Every index is a tax on writes. INSERT writes to the table and every index. UPDATE touches every index where the indexed column changes. DELETE marks tuples dead in every index. On a write-heavy service, five indexes can mean 6x the I/O of a bare table.

I benchmarked this on a Bolt.new generated FastAPI service. The baseline was 12k writes/sec with zero indexes. Adding three B-Trees dropped it to 4k writes/sec. Adding a GIN index on jsonb dropped it to 1.8k writes/sec. The GIN index was necessary for the product, but we had to provision 3x the disk IOPS. If you are prototyping with Bolt.new Python Backend: FastAPI from Zero to Production, add indexes after you measure the query shape, not before.

Monitor pg_stat_user_indexes. Look for idx_scan = 0 on large indexes. That is wasted write throughput.

SELECT
relname AS table,
indexrelname AS index,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

If idx_scan is zero and the index is large, drop it. If idx_tup_fetch is high relative to idx_tup_read, you are hitting the heap often. Consider a covering index.

How Do I Monitor Index Usage in Production?

pg_stat_user_indexes is the source of truth. But it resets on stats reset or restart. I snapshot it hourly into a timeseries table.

CREATE TABLE index_usage_log AS
SELECT now() AS ts, * FROM pg_stat_user_indexes;

Alert on indexes with zero scans over 7 days. Alert on idx_tup_fetch / nullif(idx_tup_read, 0) > 0.1 for indexes without INCLUDE columns. That ratio means 10% of index entries required a heap fetch. The visibility map is likely stale. Check autovacuum settings.

Also watch pg_stat_user_tables.n_tup_upd vs n_tup_hot_upd. Low HOT update ratio means updates are hitting indexed columns. That forces index writes. If you update a status column frequently and it is indexed, every update writes the index. Consider removing the index if the cardinality is low (e.g., only 3 statuses) and a sequential scan is fast enough.

FAQ

Does PostgreSQL use multiple indexes in a single query?

Yes. The planner can combine them with a BitmapAnd or BitmapOr node. This works well for low-cardinality filters. It fails when the combined selectivity is high because the bitmap becomes too large and degrades to a sequential scan.

Should I index foreign keys?

Usually yes. It speeds up joins and prevents full table scans when deleting from the parent table. If the child table is write-heavy and the parent is rarely queried by that key, measure first. The write cost is real.

What is the difference between REINDEX and pg_repack?

REINDEX locks the table. pg_repack builds a new table and swaps it with minimal locking. Use pg_repack for production databases. Run REINDEX CONCURRENTLY in Postgres 12+ if you cannot install extensions.

How do I index a jsonb column for key existence?

Use jsonb_path_ops with a GIN index. It is smaller and faster for @> containment checks than the default jsonb_ops. It does not support ? key existence operator efficiently. If you need key existence, stick with default jsonb_ops or create a generated column for that key and index it with B-Tree.

Key Takeaways

  • Default to B-Tree. Use GIN for jsonb, arrays, full-text. Use Hash only for pure equality on high-cardinality columns.
  • Composite index order: equality columns first, then range, then sort. The planner uses a leftmost prefix.
  • Partial indexes (WHERE clause) and expression indexes (ON (lower(email))) reduce size and write amplification for sparse patterns.
  • Covering indexes (INCLUDE) enable index-only scans. They widen the index tuple. Use them for high-read, low-write columns.
  • Every index costs write throughput. Monitor pg_stat_user_indexes for zero-scan indexes and high heap fetch ratios.
  • autovacuum must keep the visibility map current for index-only scans to work.

Working on something similar?

If you're building backend or AI systems and want a second set of senior eyes, let's talk.

Keep reading

Related articles