Overview of Database System Design

Overview of Database System Design

There are only two kinds of developers in the world: those who use database systems and those who develop database systems.

Data is the most important information in a system. Most systems manage data, using data models to represent the real world and algorithms to manipulate objects or data structures, thereby changing the state of the data model. Data is stored in operating‑system files, and we use database systems to organize, query, search, and process that data.

This article explores the development, classification, common architectures, concepts and technologies of databases, and interleaves implementation principles of various databases to help readers understand concrete implementations.

1. Exploring the Origins

Early computers used punched cards for input, output and storage. The first computer programs in the early 1950s focused on coding languages and algorithms; data was considered a residual. As computers became commercial, the importance of data grew.

In the 1960s two popular data models emerged: the network model CODASYL and the hierarchical model IMS. In 1970 E.F. Codd published the seminal paper "A Relational Model of Data for Large Shared Databases", introducing the relational model that decoupled logical organization from physical storage.

Subsequent systems such as Ingres, System R, Oracle, DB2, MySQL, PostgreSQL, MongoDB, HBase, Neo4j, Elasticsearch, etc., illustrate the rapid evolution of database technology.

2. The Many Voices (Classification)

DB‑Engines ranks hundreds of databases. They can be broadly classified by relational vs. non‑relational (NoSQL) and by workload: operational (OLTP) vs. analytical (OLAP).

Non‑relational databases can be further divided into document stores (MongoDB, Elasticsearch), key‑value stores (Redis, DynamoDB), graph databases (Neo4j), time‑series databases (InfluxDB, Timescale), and wide‑column stores (Cassandra, HBase, Bigtable).

Relational Model

Relational databases use two‑dimensional tables and are suitable when the schema is known and relatively stable. They dominate transaction processing (ERP, banking, e‑commerce) and batch processing.

Analytical World (OLAP)

OLAP (Online Analytical Processing) supports complex analytical queries on large data sets, while OLTP (Online Transaction Processing) handles high‑frequency transactional workloads. The two worlds differ in data volume, update frequency, and query patterns.

Column‑Oriented Storage

Traditional OLTP stores data row‑wise; column‑oriented storage stores each column contiguously, enabling efficient column‑wise scans, automatic indexing, and better compression for analytical workloads.

Search Made Simple

Elasticsearch, built on Apache Lucene, provides distributed real‑time full‑text search. It originated from a personal project to manage recipes and grew into a widely adopted search engine.

Key‑Value Cache Dominance

Redis is a high‑performance key‑value store supporting strings, lists, sets, sorted sets, hashes, expiration, transactions, clustering, and advanced features such as distributed locks, Pub/Sub, Bloom filters, and HyperLogLog.

Embedded Databases (Small and Precise)

Lightweight embedded databases include SQLite, H2, Berkeley DB, LevelDB, and RocksDB, often used in mobile devices or as storage engines for custom database systems.

3. Bridging the Gap (Choosing a Storage Solution)

Selection principles: understand requirements, investigate characteristics of candidate databases, and consider factors such as transactionality, analytical needs, document or graph models, time‑series, key‑value, read/write patterns, data volume, reliability, scalability, and maintainability.

4. Knowledge in Action (Technical Details)

System Architecture

Master‑Slave

Master‑slave is a common architecture where the master handles writes and slaves replicate data for high availability and read scalability. Master‑master adds a standby master for failover.

Data Consistency

Consistency can be eventual or strong. Strong consistency sacrifices availability; eventual consistency guarantees convergence after updates cease.

CAP Theorem

A distributed system can satisfy at most two of consistency, availability, and partition tolerance. The three possible trade‑offs are CA, CP, and AP.

Sharding (Partitioning)

Data is split into independent partitions to improve performance and availability. Common strategies include range‑based partitioning and hash‑based partitioning.

Dynamo (Decentralized)

Amazon Dynamo uses consistent hashing, vector clocks, quorum‑like replication, and a gossip protocol for membership and failure detection, providing high scalability and availability with eventual consistency.

Bigtable (Master‑Worker)

Bigtable consists of a master server, tablet servers, and a client library. The master manages metadata, tablet assignment, load balancing, and schema changes, while tablet servers handle reads/writes directly.

Indexing

Hash Index

Hash indexes map keys to disk offsets, offering O(1) lookups but requiring the entire hash table in memory and supporting only exact matches.

B‑Tree Index

B‑trees (and B+‑trees) provide balanced tree structures with logarithmic search, range queries, and efficient disk‑page access.

Log‑Structured Merge Tree (LSM)

LSM stores data in an in‑memory Memtable (often a skip list) and immutable memtables flushed as sorted SSTable files. Reads check memtables, immutable memtables, and SSTables in order, optionally using Bloom filters to skip non‑existent keys.

Compression

Compression reduces storage, bandwidth, and improves throughput. Lossless algorithms (e.g., Snappy, LZ4) are used for text and numeric data, while lossy methods apply to media. Columnar stores often use delta‑of‑delta encoding for numeric columns.

Read/Write Optimizations

Techniques include asynchronous I/O, buffering and batch writes, page‑aligned disk access, pre‑fetching, and memory‑mapped files. MySQL InnoDB uses native AIO; Kafka and Elasticsearch clients employ buffered batch writes.

The article only scratches the surface of each technology; readers are encouraged to explore the referenced books and articles for deeper understanding.

Please give the article a "like", "share", and "watch" – that is the greatest encouragement.