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.
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.
The multi-tenant schema choice is rarely about elegance. It is about which tenant problem you want to be solvable at 3 AM. Here is how I think about the three options.
Index cleanup starts with evidence: which indexes serve real queries, which duplicate each other, and which slow writes without earning their keep.
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 let you keep derived data in the schema instead of in scattered application code. Here is when they pay off and the corner where they bite.
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.
Major version upgrades are intimidating until you have done one cleanly. Here is the playbook I use, the failure modes I have seen, and what to test before you commit.
Most schema changes can ship without downtime if you split them into safe steps. Here are the three patterns that handle 90% of what you will need.
VACUUM is routine maintenance until it is not. Understand dead tuples, autovacuum pressure, bloat, and transaction age before they become production risk.
A connection that holds a transaction open for hours blocks vacuum, holds locks, and pollutes your pool. They are easy to find and easy to fix once you know where to look.
Read Committed, Repeatable Read, Serializable. The textbook treatment puts most readers to sleep. Here is what each one actually means for your application.