Back to System Design Course

System Design Course

Databases

At its most fundamental, a database is responsible for the storage and retrieval of data for an application. A database can also be called a Database Management System (DBMS) because it's an application that manages access to a physical data store. The core functions of a database are to:

  • Store data
  • Update and delete data
  • Return data according to a query
  • Administer the database

And when providing these data services, a database needs to be reliable, efficient, and correct.

Transactions

A transaction is a series of database operations that are considered to be a "single unit of work". The operations in a transaction either all succeed, or they all fail. In this way, the notion of a transaction supports data integrity when part of a system fails. This is formalized in the ACID properties:

  • Atomicity: All operations in the transaction succeed or fail together. A transaction is an atomic unit — there is no partial completion. If any step fails, the entire transaction is rolled back.
  • Consistency: A successful transaction leaves the database in a valid state — no schema violations, referential integrity constraints are satisfied. The database transitions from one valid state to another.
  • Isolation: Concurrent transactions execute as if they were run serially. One transaction's intermediate state is not visible to other transactions. The degree of isolation is configurable (read uncommitted, read committed, repeatable read, serializable).
  • Durability: Once a transaction is committed, it is permanently persisted to non-volatile, durable storage (disk) — not memory. A committed transaction survives system crashes, power failures, and restarts. Storing to memory would not satisfy durability because RAM is volatile and loses data when power is removed. This is why databases use write-ahead logs (WAL) and flush to disk before acknowledging a commit.

Not all databases choose to support full ACID transactions, usually because they are prioritizing scalability or availability. Generally, relational databases support ACID transactions; many NoSQL databases offer weaker guarantees (eventual consistency) in exchange for horizontal scalability.

Schemas

The role of a schema is to define the shape of a data structure and specify what kinds of data can go where. A schema can specify database-level structures like tables and indexes, and data-level constraints like field types, nullability, and foreign key references.

Schemas can be strictly enforced (relational databases), loosely enforced (document databases that optionally validate JSON schema), or absent entirely (key-value stores). The advantage of strict schema enforcement is safety — any data returned by a query is guaranteed to conform to the schema. The disadvantages: computationally expensive to enforce on every write, and difficult to evolve — adding a column to a large table can be a costly, locking operation.

Scaling

Two kinds of scaling:

  • Vertical Scaling: Add more CPU, RAM, or faster disk to a single server. Simple but has hard hardware limits and is a single point of failure.
  • Horizontal Scaling: Add more servers to a cluster and distribute data across them (sharding/partitioning). Much higher theoretical capacity, can be sized without downtime, but introduces complexity: relational databases struggle to scale horizontally because maintaining foreign key constraints and joins across nodes requires expensive network coordination.

Types of Databases

Correction: The original content listed Cassandra and HBase under "Document DB" — this is incorrect. Cassandra and HBase are column-family (wide-column) databases, not document databases. Google Spanner is a distributed relational database, not a document store. The corrected categorization is below.

  • Relational (SQL) DB: MySQL, PostgreSQL, Oracle, MS SQL Server, SQLite, IBM DB2
  • Document DB: MongoDB, Couchbase, Firebase Firestore, CouchDB, RethinkDB, Amazon DocumentDB
  • Column-Family (Wide-Column) DB: Apache Cassandra, Apache HBase, Google Bigtable, ScyllaDB
  • Graph DB: Neo4j, Amazon Neptune, TigerGraph, ArangoDB (multi-model)
  • Key-Value Store: Redis, Amazon DynamoDB, Memcached, Hazelcast, Aerospike
  • Search Engine DB: Elasticsearch, Apache Solr, OpenSearch, Typesense
  • Time Series DB: InfluxDB, Prometheus, TimescaleDB, Kdb+
  • Distributed Relational DB: Google Spanner, CockroachDB, PlanetScale, Amazon Aurora

Relational Databases

A relational database uses a relational data model — data is organized in tables (relations) with rows of entries and columns of typed fields. Relationships between tables are represented using foreign key columns that reference the primary key columns of other tables.

Relational databases enforce schema constraints and almost always implement ACID transactions to ensure schema conformance and data correctness. They use SQL (Structured Query Language) — a declarative language where you specify what you want and the query planner decides how to retrieve it. Query planner tuning and index design are the primary optimization levers.

Indexes: When data is frequently queried by a specific column, adding an index on that column speeds up lookups from O(n) (full table scan) to O(log n) (B-tree index) or O(1) (hash index). The trade-off is slower writes — every write must update both the primary table and all indexes on it.

Relational databases are almost always CP databases (per the CAP theorem) — they prioritize consistency over availability during network partitions.

When to Use

  • Many-to-many relationships between entities that need to be joined
  • Data integrity and referential integrity are critical (financial transactions, medical records)
  • Complex queries spanning multiple related tables
  • Consistent transactions are required (e.g., transferring money between accounts)
  • The data model is well-understood and unlikely to change frequently

Top Industry Options: PostgreSQL (strongly recommended for new projects — feature-rich, open source, excellent performance), MySQL, Oracle, MS SQL Server

Disadvantages

  • Hard to scale horizontally because cross-node joins and foreign key enforcement require network communication, making distributed transactions slow and complex
  • Schema changes (ALTER TABLE on large tables) can require downtime or careful migration strategies
  • Not a good fit if data doesn't have consistent structure or changes shape frequently

Non-Relational (NoSQL) Databases

NoSQL databases optimize for specific use cases that need horizontal scalability, schema flexibility, or specialized query support. They sacrifice some ACID guarantees in exchange. Most NoSQL databases are either AP (available and partition-tolerant) using eventual consistency, or CP (consistent and partition-tolerant) without full availability during partitions.

Document Store

Stores documents — typically JSON or BSON objects — each identified by a key. Documents within the same collection can have different schemas, making it easy to add fields without a migration. All data for one entity is usually in a single document, avoiding joins.

Use when: data is self-contained per entity, schema evolves frequently, horizontal scaling is a priority.

Top options: MongoDB, Couchbase, Firebase Firestore, Amazon DocumentDB

Key-Value Store

The simplest model: a massive hash table. The database has no knowledge of the value's structure — it only provides get, set, and delete by key. Minimal overhead means very high throughput and easy horizontal scaling.

When values need to be large binary objects (videos, images, logs), this is called an Object Store / Blob Store (e.g., Amazon S3, Azure Blob Storage, Google Cloud Storage).

Use when: caching, session storage, simple counters, feature flags, shopping carts.

Top options: Redis (also supports richer data types: lists, sets, sorted sets, streams), Amazon DynamoDB (also supports document model), Memcached, Hazelcast

Column-Family (Wide-Column) Database

Stores data in tables like a relational database but groups columns that are typically accessed together into column families, stored together on disk. Does not enforce relational constraints. Excellent for high write throughput and time-series or event data with billions of rows.

Cassandra and HBase are the canonical examples. Cassandra is AP (highly available, eventually consistent by default, tunable consistency). HBase is CP (strongly consistent, built on top of HDFS).

Use when: write-heavy workloads, time-series data, event logs, IoT data, large scale analytics where queries always access the same column families.

Top options: Apache Cassandra, Apache HBase, Google Bigtable, ScyllaDB

Graph Database

Models data as nodes (entities) and edges (relationships). Optimized for traversing many relationships — queries that would require many expensive JOIN operations in a relational database are navigated efficiently along edges.

Use when: social networks, fraud detection (finding relationship patterns), recommendation engines, knowledge graphs, network topology.

Top options: Neo4j, Amazon Neptune, TigerGraph

Search Engine Database

Provides full-text search with relevance ranking, fuzzy matching, and faceted filtering over large volumes of unstructured or semi-structured text. Built on inverted indexes rather than B-trees.

Use when: search functionality, log analytics (ELK stack), product search with ranking.

Top options: Elasticsearch / OpenSearch, Apache Solr, Typesense

Time Series Database

Optimized for data that arrives in time order — metrics, monitoring data, IoT sensor readings, financial tick data. Data is append-mostly, queries are typically aggregations over time ranges, and data is often automatically compressed or rolled up after a retention period.

Top options: InfluxDB, Prometheus (metrics-focused, pull-based), TimescaleDB (PostgreSQL extension), Kdb+

Distributed Relational Database

A newer category: databases that give you relational SQL semantics (ACID transactions, foreign keys, joins) while scaling horizontally like a NoSQL database. They achieve this through distributed consensus protocols (Paxos, Raft).

Top options: Google Spanner, CockroachDB, PlanetScale, Amazon Aurora (distributed storage with MySQL/PostgreSQL compatibility)

Summary: Choosing the Right Database

Need                                             | Choose
-------------------------------------------------|---------------------------------------------
Complex relationships, strict consistency        | Relational (PostgreSQL, MySQL)
Flexible schema, self-contained documents         | Document (MongoDB)
Ultra-high write throughput, time-series, events | Column-family (Cassandra, HBase)
Complex traversals across many relationships     | Graph (Neo4j)
Caching, sessions, simple O(1) lookups           | Key-value (Redis, Memcached)
Full-text search, log analytics                  | Search (Elasticsearch)
Metrics, monitoring, IoT data                    | Time-series (InfluxDB, Prometheus)
Horizontal scale with SQL semantics              | Distributed relational (CockroachDB, Spanner)

In large systems, it is common and correct to use multiple database types for different parts of the system — a relational database for transactional data, Redis for caching, Elasticsearch for search, and Kafka (a streaming log) for event sourcing.