Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.
Partitioning solves specific problems. It also creates new ones. Here's an honest look at range, list, and hash partitioning — including the mistakes that will haunt you.
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.
Primary key choice is one of those decisions that looks academic until you are migrating six months in. Here is the framework I use, and the cases where each one wins.
Choosing between `timestamp` and `timestamptz` looks like a stylistic preference until daylight saving moves the wrong customer's appointment by an hour.
Adding `deleted_at` to every table looks innocent and ages badly. Indexes get bigger, queries get more `WHERE deleted_at IS NULL`, and bugs hide in the rows you forgot to filter.
Multi-tenant Postgres design is not about elegance. It is choosing the failure, noisy-neighbor, backup, migration, and support story you can survive.
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.
ENUMs feel cleaner until product asks for a label, a translation, or a soft-delete. Here is the simple rule I use, and the migration I have run more times than I can count.
Normalization and denormalization are not opposing camps; they are two answers to two different questions. The trick is knowing which question you are actually asking.
Generated columns are useful when derived data is a database invariant, not an application convenience. The hard part is deciding what belongs in the schema.
Constraints are not pedantic. They are how the database protects you from the application bugs you have not written yet. Here is the set I always add and why.