Database Production Checklist

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

Expert12 min read

Read these first

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.

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.

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.

Cache layer

Pattern — cache-aside for product catalog; write-through for inventory counts with short TTL.
TTL — every key has one; sessions 24 h, product cache 5 min, rate-limit counters 60 s.
Stampede — probabilistic early refresh or mutex lock on hot keys (Black Friday SKUs).
Eviction — maxmemory-policy allkeys-lru; alert at 85% memory before eviction starts.
Persistence — AOF everysec for sessions; accept RPO trade-off documented in DR plan.
ACLs — per-service users; default user off; no FLUSHALL for app roles.
Hot keys — monitor with redis-cli --hotkeys; split across hash tags in Cluster if needed.

Search index

Mapping — explicit mapping before first index; keyword for filters, text for full-text.
Shards — target 20–50 GB per shard; VaultCommerce products index: 3 primary shards, 1 replica.
ILM — hot → warm → delete for time-series logs; forcemerge warm tier only.
Reindex — zero-downtime via alias swap (products_v2 → alias products).
Backups — daily S3 snapshot; test restore quarterly.

RPO / RTO reference

Postgres — RPO 5 min (WAL archiving), RTO 30 min (cross-region replica promotion).
Redis — RPO 1 min (AOF), RTO 10 min (restore RDB from S3).
Elasticsearch — RPO 15 min (snapshot), RTO 45 min (restore or reindex from Postgres).
DynamoDB — RPO 0 (PITR on), RTO 60 min; on-demand billing; GSI only when access pattern demands it.
Drills — quarterly restore into isolated VPC; verify row counts and checksums.
Runbook — DNS failover steps documented; no hardcoded writer hostnames.

Defence in depth

TLS — all connections encrypted; sslmode=verify-full for Postgres.
Encryption at rest — KMS on RDS, ElastiCache, EBS, S3 backups.
RBAC — one DB role per microservice; table-level grants; no runtime superuser.
Secrets — AWS Secrets Manager via External Secrets Operator; 90-day rotation.
Network — private subnets only; security groups scoped to app SG.
SQL injection — parameterized queries everywhere; WAF as secondary layer.
Audit — pgaudit for DDL; CloudTrail for RDS API; break-glass accounts MFA-gated.

On-call essentials

pg_stat_statements — top queries by total_exec_time; reset after index deploys.
Performance Insights — RDS wait events; alarm on IO:DataFileRead spikes.
Redis INFO — used_memory, evicted_keys, hit ratio, rejected_connections.
ES health — _cluster/health; page on red; investigate yellow within 1 h.
CloudWatch — CPU, connections, replica lag, memory; every alarm has a runbook link.
Locks and probes — pg_blocking_pids in runbook for checkout 5xx; synthetic checkout every 60 s from three regions.

Interview prompt	VaultCommerce answer
How do you handle a slow query?	pg_stat_statements → EXPLAIN ANALYZE → index or rewrite → reset stats
Cache invalidation?	TTL + event-driven eviction on product update via Kafka consumer
Zero-downtime migration?	Expand-contract: add column → dual-write → backfill → switch reads → drop old
Regional failover?	Promote async replica after lag check; update DNS; warm Redis; verify payment queue

How do you handle a slow query?
VaultCommerce answerpg_stat_statements → EXPLAIN ANALYZE → index or rewrite → reset stats
Cache invalidation?
VaultCommerce answerTTL + event-driven eviction on product update via Kafka consumer
Zero-downtime migration?
VaultCommerce answerExpand-contract: add column → dual-write → backfill → switch reads → drop old
Regional failover?
VaultCommerce answerPromote async replica after lag check; update DNS; warm Redis; verify payment queue

Use these as structured talking points — not memorised scripts.

Everything you need if you only revisit this box.

Schema: access patterns → keys → indexes verified with EXPLAIN → concurrent migrations.
Pooling: size per instance, total under max_connections, short transactions, leak detection.
Caching: TTL on every key, stampede protection, ACLs, memory alerts at 85%.
DR: know RPO/RTO per engine; test restores quarterly; rehearse failover.
Security: TLS + KMS + least-privilege roles + secrets manager + private network.
Monitoring: pg_stat_statements, Redis INFO, ES cluster health, CloudWatch with runbooks.

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