Partial indexes are genuinely one of the most underused Postgres features. The classic example that saves teams real money: if you have a status column and 95% of rows are "completed" but you only ever query for "pending" or "failed" rows, a partial index with WHERE status != 'completed' gives you a tiny index that covers all your actual queries.

The INCLUDE clause for covering indexes is another one that more people should know about. Before Postgres 11, you'd either add columns to the index key (which affects ordering and bloats the tree) or accept an extra heap fetch. INCLUDE lets you store extra columns in the leaf pages without affecting the index structure. The performance difference on read heavy workloads can be dramatic because you avoid the heap lookup entirely.

One thing the article could have emphasized more: EXPLAIN ANALYZE is the only way to know if your index is actually being used. I've seen teams create elaborate multi column indexes that the planner completely ignores because the table is small enough for a sequential scan to be faster, or because the column selectivity doesn't justify an index lookup. Always verify with real data volumes, not with your dev database that has 50 rows.