BRIN indexes are 100x smaller than B-tree on the right data. They are also useless on the wrong data. The deciding factor is whether the table is naturally sorted.
Database Topic Archive
Indexes Articles
Index design, index debt, reindexing, constraint indexes, and evidence-driven DDL.
Foreign key constraints check the parent side automatically. The child side is your problem. Forgetting this index is one of the most common silent performance bugs.
Expression indexes are powerful but exacting. If the query expression and index expression do not match, the index you trusted may not be used.
REINDEX is a repair tool, not an indexing strategy. Use it when bloat, corruption, or access pattern churn makes rebuilding cheaper than carrying the old index forward.
Both enforce uniqueness. They look identical in `\d`. The differences only show up when you try to alter the table or hit them with a partial uniqueness rule.
Index work is not about adding more indexes. It is about finding which indexes earn their write cost, which ones duplicate each other, and which missing one is hurting users.
JSONB columns are great. JSONB indexing has a steeper learning curve than the docs admit. Here is what works in production.