PostgreSQL Practice

• · PG1 — Foreign key constraints enforce referential integrity at the database level, not solely in application code.
• · PG2 — Columns get NOT NULL and CHECK constraints wherever the domain genuinely disallows null/invalid values.
• · PG3 — Indexes are added for columns used in WHERE/JOIN/ORDER BY on frequently-run queries, verified via EXPLAIN ANALYZE, not added speculatively.
• · PG4 — Migrations are reviewed for locking behavior on large tables (e.g. adding a NOT NULL column without a default locks/rewrites the table) before running against production.
• · PG5 — Normalization avoids duplicate/derivable data by default; denormalization is a deliberate, documented performance tradeoff, not a default.

• · A slow query? Run EXPLAIN ANALYZE before guessing — look for sequential scans on large tables where an index scan should occur.
• · A column referencing another table's ID with no foreign key constraint → add one, unless there's a specific documented reason (e.g. cross-database sharding).
• · A migration adding a column with a computed default on a large table → check if it requires a table rewrite/lock; consider a nullable column + backfill + constraint-add pattern instead.
• · Repeated identical WHERE-clause shapes across the codebase not backed by a matching index → add the index.
• · Data duplicated across tables "for query convenience" → confirm it's a deliberate denormalization decision, not an oversight.

• · Application-only referential integrity — relying on app code to never insert an orphaned foreign key, instead of a DB constraint; eventually violated by a bug, a script, or a manual fix.
• · Index-everything — adding an index to every column "just in case," bloating writes and storage with no corresponding read benefit.
• · Blocking migration on a hot table — running ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ... on a large live table during peak traffic, causing a multi-minute lock and an outage.
• · SELECT \* in application queries — fetching all columns including large/unneeded ones (blobs, rarely-used JSON), wasting bandwidth and cache.
• · N+1 from the ORM — lazy-loaded relations triggering one query per row in a loop, invisible in the code but visible in query logs.

• [ ] Foreign keys declared as real constraints. (PG-E1)
• [ ] Migrations on large tables use safe patterns (no blocking rewrites). (PG-E4)
• [ ] PII/sensitive tables have documented access controls. (PG-E6)
• [ ] NOT NULL/CHECK constraints match domain invariants. (PG-E2)
• [ ] Indexes match real query patterns, verified via EXPLAIN. (PG-E3)
• [ ] Migrations tested against production-like data volume. (PG-E5)

• PostgreSQL Documentation

Este pack cita las siguientes fuentes registradas en el índice bibliográfico:

• webPostgreSQL DocumentationPostgreSQL Global Development Group
• webUse The Index, LukeMarkus Winand