Databases in System Design

When to Use It

Use relational databases when transactions, referential integrity, and complex joins are core requirements. Billing, inventory, and identity systems benefit from strong ACID guarantees.

Use document or key-value stores when access patterns are dominated by single-key lookups with flexible schemas. Session stores, user preferences, and content catalogs are common fits.

Use specialized engines (time-series, graph, search) only when a measurable query pattern cannot be served efficiently by a general-purpose store. Each additional engine adds operational cost.

Why Databases Matters

Product features become reliable only when their data model is reliable. Order systems, messaging timelines, billing workflows, and identity services all depend on predictable write semantics and query patterns. A mismatch between data model and workload creates chronic latency and incident risk.

Scaling pressure usually appears first in storage. Traffic can be distributed across stateless services, but write coordination, index maintenance, and transaction boundaries converge in the database layer. Early schema and indexing decisions strongly influence how gracefully the system grows.

Database architecture also determines recovery posture. Backup cadence, replication topology, and failover procedures define real-world RTO and RPO outcomes. Teams that treat these as secondary concerns often discover during incidents that theoretical durability was never operationalized.

Data quality also drives downstream analytics and machine learning outcomes. If operational schemas lack clear ownership, retention rules, and consistency guarantees, downstream reporting drifts from reality. Good database discipline in transactional paths improves decision quality across the entire organization.

Core Concepts and Mental Models

Model data around access paths, not abstract entities alone. If critical queries need low latency, ensure indexes and partition keys align with those exact filters and sort orders. Query-driven modeling reduces expensive rewrites later when traffic and feature complexity increase.

Separate transactional boundaries from analytical workloads. OLTP systems need tight latency and correctness, while analytics can tolerate batch latency and denormalization. Isolating these patterns protects user-facing paths and gives data teams freedom to optimize heavy reporting separately.

Replication and partitioning solve different problems. Replication improves read scale and availability, while partitioning addresses write throughput and dataset size. Systems that mix these concerns without clear intent often end up with expensive topologies that solve neither bottleneck well.

Key Tradeoffs

Common Mistakes

• Over-normalization on hot read paths: excessive joins increase latency. Denormalize read models where access frequency justifies the trade.
• Treating eventual consistency as free: it reduces coordination cost but introduces stale reads and conflict resolution complexity. Reserve it for paths where business rules tolerate temporal lag.
• Database sprawl without justification: each new engine adds backup complexity, operational cost, and incident coordination burden. Add engines only for measurable bottlenecks.

Implementation Playbook

Begin with a schema that captures invariants explicitly. Use constraints, unique indexes, and transaction boundaries to encode business rules near the data layer, not only in application code. This prevents drift across services and reduces silent corruption during edge-case failures.

Instrument query performance continuously. Track slow queries, lock wait time, replication lag, and index hit ratio. Visibility into these signals lets teams catch regressions from feature launches or migration scripts before they become user-facing incidents.

Treat migrations as deployable software. Prefer additive changes, dual-write or backfill phases when needed, and rollback-safe plans. Data migration mistakes are harder to reverse than stateless code rollbacks, so cautious sequencing is a core engineering discipline.

Formalize backup and restore drills. A backup policy is only credible when restore time and data integrity are tested regularly. Teams that rehearse restores by service domain recover faster during real incidents and avoid discover-on-fire surprises in tooling, credentials, or data lineage.

Practice Path for Databases

Related Articles

Frequently Asked Questions