Skip to main content
PreparationZone Logo
PrepZone

Database Production Checklist

The expert reference card — indexing, pooling, caching, backups, and interview talking points.

Pass checkpoint below

Why this matters

  • Senior engineers are judged on whether they have shipped databases safely — this card distills the VaultCommerce playbook across Postgres, Redis, Elasticsearch, and DynamoDB.
  • Interview closers ask "how would you run this in production?" — each section below is a structured talking point, not a script.
App writesINSERT / UPDATE
LeaderPrimary node
Replica 1Read traffic
Replica 2Read traffic
Writes go to the leader; replicas serve read traffic asynchronously.

Schema and indexing

Before every migration ships

  • Access patterns first — every table has documented read/write queries; no schema without a query that needs it.
  • Primary key — UUID or BIGINT with a sequence; never auto-increment across shards.
  • Foreign keys — enforced in Postgres for core domains (orders → users); deferred or omitted only in high-write sharded paths with app-level enforcement.
  • Indexes — one per WHERE/JOIN column in hot queries; verify with EXPLAIN (ANALYZE, BUFFERS) on production-like data volume.
  • Partial indexes — for status-filtered queries (WHERE status = 'pending'); cap at 6 indexes per table unless justified.
  • Migrations — Flyway/Liquibase in CI; CREATE INDEX CONCURRENTLY for Postgres 16; never lock a 100M-row table in peak hours.

Connection pooling and transactions

Application layer

  • Pool size — (cores × 2) + 1 per instance; total across pods < Postgres max_connections minus admin reserve.
  • Timeouts — connection-timeout 5–10 s; statement_timeout 30 s for API queries, longer only for batch jobs.
  • Read replicas — route read-only analytics to replica; never write on replica connection.
  • Isolation — READ COMMITTED default; SERIALIZABLE only for inventory deduction with retry logic.
  • Transaction scope — keep short; no external HTTP calls inside a DB transaction.
  • Leak detection — HikariCP leak-detection-threshold: 60000 in staging and production.

The rest of this guide is part of Pro

Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.

Still to come · 5 sections

  • Caching (Redis 7)
  • Search (Elasticsearch 8)
  • Backup and disaster recovery
  • Security
  • Monitoring and alerting

Test yourself

Answer these before moving on — recall is what makes it stick.