Database Governance

Non-Negotiables

1. Schema changes are production changes. They require review.
2. Backwards compatible by default. Deployments must be safe under rolling upgrades.
3. Data integrity beats convenience. Constraints are not optional.
4. Performance regressions are incidents. Measure before/after.

Minimum Requirements (Industry Standard Baseline)

Change Management

• Every change uses migrations (no manual production edits).
• Every migration is:
  • reversible where feasible
  • idempotent
  • tested in staging
• Use the expand/contract pattern for breaking changes.

Schema Design

• Primary key on every table.
• Use foreign keys (or documented alternative) to protect integrity.
• Use NOT NULL where applicable.
• Use consistent naming conventions (tables, columns, indexes).

Performance

• Add indexes intentionally; verify with query plans.
• No unbounded queries: pagination is mandatory for list endpoints.
• Avoid N+1 query patterns.

Safety & Operations

• Backups are mandatory; restoration must be tested.
• Define retention/TTL for large tables and logs.
• No PII in non-production datasets unless anonymized.

References

• PostgreSQL: Schema design & constraints: https://www.postgresql.org/docs/current/ddl.html
• Expand/Contract migrations: https://martinfowler.com/bliki/ParallelChange.html
• Indexing (PostgreSQL): https://www.postgresql.org/docs/current/indexes.html