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
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 CONCURRENTLYfor Postgres 16; never lock a 100M-row table in peak hours.
Connection pooling and transactions
Application layer
- Pool size —
(cores × 2) + 1per instance; total across pods < Postgresmax_connectionsminus admin reserve. - Timeouts —
connection-timeout5–10 s;statement_timeout30 s for API queries, longer only for batch jobs. - Read replicas — route read-only analytics to replica; never write on replica connection.
- Isolation —
READ COMMITTEDdefault;SERIALIZABLEonly for inventory deduction with retry logic. - Transaction scope — keep short; no external HTTP calls inside a DB transaction.
- Leak detection — HikariCP
leak-detection-threshold: 60000in 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.