How Databases Organize Data: Storage Engines Explained
Database internals are built on fundamental trade-offs between speed and space. OLTP databases use row storage for fast transactional lookups, while OLAP databases use column storage for efficient analytics. Storage engines consist of transaction managers, lock managers, access layers, buffer managers, and recovery managers—each optimizing different aspects of reliability and performance.
Database Categories: OLTP, OLAP, and HTAP
OLTP: Online Transaction Processing
OLTP databases are optimized for many small, fast transactions with semi-random lookups. They power most internet applications (MySQL, PostgreSQL, SQLite) by efficiently handling user logins, profile loads, and account queries where you need to fetch multiple related fields from a single row.
OLAP: Online Analytical Processing
OLAP databases are designed for analyzing large datasets to answer business questions like 'what time of year do users sign up most' or 'which countries watch the most videos.' They handle queries that scan gigabytes or terabytes of data to produce summaries and trends, not individual row lookups.
HTAP: Hybrid Transactional/Analytical Processing
HTAP databases attempt to handle both OLTP and OLAP workloads in a single system (e.g., SingleStore, SAP HANA). However, at scale this approach often fails because compute resources compete, data duplication creates sync issues, and separate optimized databases usually outperform a single hybrid solution.
Row Storage vs. Column Storage: The Core Trade-off
Row Storage for OLTP
Row storage clusters all data from one row together on disk (ID, name, email, address, signup date all adjacent). This is efficient for OLTP because when you look up a user, you typically need multiple fields from that row, and disk reads happen in pages (4-16 KB), so nearby data comes 'for free' with a single page fetch.
Column Storage for OLAP
Column storage groups all values of one column together (all IDs, then all names, then all emails, then all dates). For analytics queries that only need one or two columns from billions of rows, this avoids reading irrelevant data. Additionally, repeated values in a column (e.g., signup dates) compress well, reducing I/O and enabling pre-aggregation.
Compression Advantage in Column Storage
When a column has low cardinality (few unique values), column storage enables compression. For example, if 1 billion users have only ~7,000 possible signup dates, you can store 'date_value: count' pairs instead of repeating the date 1 billion times, dramatically reducing disk I/O and enabling faster aggregations.
Storage Engine Components
Transaction Manager
Ensures that each database connection sees a consistent snapshot of data and that transactions leave the database in a valid state. It prevents one transaction from seeing partial writes from another and guarantees that committed data is never lost or corrupted.
Lock Manager
Prevents conflicts when multiple transactions try to read or modify the same data. If one transaction is inserting rows, the lock manager ensures another transaction doesn't claim the same disk slot or see inconsistent intermediate states. It uses locks and multi-version concurrency control to manage access.
Access Layer (CRUD Operations)
The interface between queries and physical data structures. It abstracts away how data is actually stored (B-tree, LSM tree, column format) so queries just say 'give me this row' or 'lookup by index' without knowing the underlying implementation details.
Buffer Manager
Manages which pages from disk stay in RAM for fast access. Since disk I/O is much slower than RAM, the buffer manager caches frequently accessed pages and decides which pages to evict when memory is full. Good eviction policies (e.g., LRU) prevent re-reading data that will be needed soon.
Recovery Manager
Ensures durability by maintaining write-ahead logs (MySQL binlog, PostgreSQL WAL). If the database crashes, the recovery manager replays the log to restore committed transactions and discard uncommitted changes, preventing data loss or corruption.
Indexes: Speed vs. Space Trade-off
Why Indexes Matter
Without an index on a column like username, a query must scan all rows to find matches. With an index (B-tree or hash-based), the database can narrow results in logarithmic time. The trade-off: indexes consume disk space and slow down inserts because every insert must also update all indexes on that table.
Write Amplification from Indexes
Every insert into a table with N indexes must write to the table plus N index structures. If a table has 10 indexes, one insert becomes 11 writes. This is called write amplification. Write-ahead logging helps by batching these writes to disk, but the fundamental cost remains.
Index Pointer Strategies
Indexes can point to data two ways: (1) direct physical address on disk—fast lookup but requires updating all indexes if the row moves; (2) primary key reference—slower lookup (requires second B-tree search) but no index maintenance if the row is rewritten elsewhere. This is another perpetual trade-off.
Key Insights and Trade-offs
Everything is a Trade-off
Database design is fundamentally about balancing competing goals: speed vs. space, reads vs. writes, consistency vs. availability. Choosing row storage makes reads fast but analytics slow. Adding indexes speeds queries but slows inserts. There is no universal 'best' design—only designs optimized for specific workloads.
Database Choice Depends on Workload
MySQL and PostgreSQL excel at OLTP (many small transactions). ClickHouse, DuckDB, and Redshift excel at OLAP (big analytical scans). Trying to use one for the other's job results in poor performance. At scale, companies typically run separate OLTP and OLAP systems with data pipelines between them.
Index Tuning is a Specialized Skill
Database performance specialists (DBAs) analyze query patterns and table schemas to decide which indexes to add (for speed) or remove (to save space and reduce write amplification). This is an ongoing optimization process that requires understanding both the application workload and database internals.
Notable quotes
The nice thing about this book is it's got better bite-sized chunks that we can go over on stream because it's 14 chapters and only 300 pages. — Ben Dicken
Everything in databases is a trade-off. You can make things faster if you use more space, or use less space if you're willing to do things more slowly. — Ben Dicken
At scale, the idea of putting everything in a single database is a nice idea in theory, but in practice you run into issues with compute competing or data duplication. — Ben Dicken