Skip to content

PostgreSQL — Checklist

OWNER: boris, yoanna ALSO_USED_BY: urszula, maxim, piotr, ashley, floris LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: PostgreSQL 16.x

NEW PROJECT CHECKLIST (database setup)

  • [ ] CHECK: PostgreSQL 16.x is the database IF_SKIPPED: Wrong version causes extension incompatibilities
  • [ ] CHECK: SSL enabled with sslmode=require in connection string IF_SKIPPED: Unencrypted connections violate ISO 27001
  • [ ] CHECK: pg_stat_statements extension enabled IF_SKIPPED: No query performance monitoring capability
  • [ ] CHECK: pgcrypto extension enabled IF_SKIPPED: UUID generation and encryption unavailable
  • [ ] CHECK: Database timezone set to UTC IF_SKIPPED: Timestamp interpretation varies by server locale
  • [ ] CHECK: All timestamps use timestamptz (with time zone) IF_SKIPPED: Timezone data silently dropped
  • [ ] CHECK: Application connects as app_role, not superuser IF_SKIPPED: Principle of least privilege violated
  • [ ] CHECK: Credentials stored in k8s secrets or Vault IF_SKIPPED: Credentials in source code — security incident
  • [ ] CHECK: idle_in_transaction_session_timeout configured IF_SKIPPED: Idle transactions block autovacuum indefinitely
  • [ ] CHECK: Database hosted in EU (UpCloud Frankfurt) IF_SKIPPED: GDPR and EU data sovereignty violation

MULTI-TENANT CHECKLIST (per tenant-scoped table)

  • [ ] CHECK: Table has tenant_id column with foreign key to tenants IF_SKIPPED: No tenant isolation possible
  • [ ] CHECK: RLS enabled AND forced on the table IF_SKIPPED: Table owner bypasses all tenant isolation
  • [ ] CHECK: RLS policies exist for SELECT, INSERT, UPDATE, DELETE IF_SKIPPED: Some operations bypass tenant isolation
  • [ ] CHECK: tenant_id is the first column in composite indexes IF_SKIPPED: Queries scan ALL tenants before filtering — O(N) per tenant
  • [ ] CHECK: Application sets SET LOCAL app.current_tenant in transaction IF_SKIPPED: RLS returns empty results or throws ADDED_FROM: admin-ui-2026-02, multi-tenant implementation

SCHEMA CHANGE CHECKLIST (before any migration)

  • [ ] CHECK: New columns are nullable or have defaults IF_SKIPPED: Table rewrite locks all rows — downtime
  • [ ] CHECK: New indexes use CREATE INDEX CONCURRENTLY IF_SKIPPED: Table locked for duration of index build
  • [ ] CHECK: Enum changes are additive only (no removes, no renames) IF_SKIPPED: Running application instances break during rolling deploy
  • [ ] CHECK: Column renames use add-backfill-drop pattern IF_SKIPPED: Running instances query old column name — errors
  • [ ] CHECK: Rollback SQL file exists for destructive changes IF_SKIPPED: No recovery path if migration causes issues
  • [ ] CHECK: Boris has reviewed the migration IF_SKIPPED: Schema changes without DBA review violate GE process

SECURITY CHECKLIST (per database)

  • [ ] CHECK: pg_hba.conf restricts connections to known CIDR ranges IF_SKIPPED: Database accessible from unauthorized networks
  • [ ] CHECK: No trust authentication entries in pg_hba.conf IF_SKIPPED: Authentication bypassed entirely
  • [ ] CHECK: hostnossl lines reject non-SSL connections IF_SKIPPED: Unencrypted connections possible
  • [ ] CHECK: Audit triggers exist on tables with PII or financial data IF_SKIPPED: No tamper trail — ISO 27001 non-compliance
  • [ ] CHECK: audit_log is append-only (RLS enforced) IF_SKIPPED: Audit trail can be tampered with
  • [ ] CHECK: PII columns evaluated for column-level encryption IF_SKIPPED: Data breach exposes plaintext PII
  • [ ] CHECK: Password length >= 24 characters, randomly generated IF_SKIPPED: Weak credentials enable brute force

PERFORMANCE CHECKLIST (quarterly review)

  • [ ] CHECK: Cache hit ratio > 99% IF_SKIPPED: Excessive disk I/O degrades all queries
  • [ ] CHECK: No tables with > 20% dead row percentage IF_SKIPPED: Table bloat wastes storage and slows sequential scans
  • [ ] CHECK: No queries > 1s mean execution time in pg_stat_statements IF_SKIPPED: Slow queries degrade user experience
  • [ ] CHECK: No idle-in-transaction connections > 30s IF_SKIPPED: Lock contention and autovacuum blocking
  • [ ] CHECK: Unused indexes identified and dropped IF_SKIPPED: Unused indexes slow writes and waste storage
  • [ ] CHECK: PgBouncer in transaction mode for production IF_SKIPPED: Connection exhaustion under load ADDED_FROM: admin-ui-2026-02, connection pooling audit

Cross-References

READ_ALSO: wiki/docs/stack/postgresql/index.md READ_ALSO: wiki/docs/stack/postgresql/security.md READ_ALSO: wiki/docs/stack/postgresql/performance.md READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md READ_ALSO: wiki/docs/stack/postgresql/pitfalls.md READ_ALSO: wiki/docs/stack/drizzle/checklist.md