Index strategy in Postgres hasn't fundamentally changed, but the workloads it handles have. Multi-tenant SaaS tables with hundreds of millions of rows, time-series data arriving at millions of events per second, JSON columns storing semi-structured data alongside relational fields — these patterns require index strategies that weren't common even five years ago.
Partial indexes are underused
One of the most consistently underused tools in Postgres indexing is the partial index. A partial index includes only rows that satisfy a WHERE clause. If 90% of your queries filter on status = 'active' and only 5% of your rows are active, a partial index on active rows is one-twentieth the size of a full index and just as effective for the queries that matter.
The case for partial indexes is strongest on soft-delete patterns, status fields with skewed distributions, and multi-tenant tables where most queries are scoped to a single tenant's rows.
BRIN for append-only time-series data
BRIN (Block Range Indexes) are vastly smaller than B-tree indexes for naturally ordered data. On an event log table where timestamps increase monotonically with insert order, a BRIN index on the timestamp column can be 200x smaller than a B-tree while providing equivalent performance for time-range queries. The caveat: BRIN only works well when data is physically ordered on disk in correlation with the indexed column. Random updates break the correlation and degrade performance significantly.
The index bloat problem
Every UPDATE in Postgres is implemented as a delete-plus-insert at the storage level. This means your indexes accumulate dead tuples that need to be vacuumed. High-update tables can have indexes that are 3-5x larger than the live data they cover. Regular REINDEX operations during low-traffic windows are part of good database maintenance practice that most teams skip until they notice query performance degrading unexpectedly.