14.1 Memory-Optimized Databases: Execution, Layout, and Recovery
Metadata Card
- Prerequisites: Chapter 4, Storage and Recovery; Chapter 13, Concurrency Control
- Keywords: memory-optimized, CPU cache, partitioned execution, logging, snapshot, recovery
- Case Studies: H-Store/VoltDB, SAP HANA, DuckDB
"The data is in memory" might simply indicate a cache hit, or it might mean that the entire system, from data layout and concurrency control to recovery mechanisms, is designed with DRAM residency as a foundational assumption. These two interpretations cannot be conflated.
Distinguish Among Three System Types
| Type | Where the authoritative state is stored | How to handle memory shortages | Key concerns |
|---|---|---|---|
| Disk-optimized database | Persistent pages or segments; memory acts as cache | Evict pages and reload from storage | Buffer Pool, random I/O, WAL |
| Memory-optimized database | Main working set resides in directly executable memory layout | Capacity planning, partitioning, cold data tiering, or write rejection | Cache/NUMA, log throughput, recovery time |
| Analytics engine with spilling | Memory serves as operator workspace; data can persist in files or external object storage | Operator spilling, re-scanning, or chunking | Vectorization, memory budgeting, temporary I/O |
DuckDB belongs to the category of embedded analytics databases, supporting buffer management and queries larger than available memory. It should not be labeled as a "pure in-memory database that replaces the buffer pool with operating system mmap." Redis is closer to a memory-based data structure service; systems like HANA and VoltDB explore memory-optimized data management from different architectural angles.
Removing Disk Page Faults Does Not Mean Zero Storage Cost
Treating a memory access as a simple ratio (“disk: 10 ms, memory: 100 ns, therefore 100,000x faster”) misleads system design. Real-world latency is influenced by hardware, queue depth, page cache behavior, network latency, access patterns, and hit rates. Once data resides in memory, the bottleneck shifts to:
- CPU cache misses and pointer chasing;
- NUMA remote memory access;
- Memory bandwidth and branch prediction;
- Capacity amplification from indexing, versioning, object headers, and fragmentation;
- Log flushing, replication acknowledgments, and network round-trips;
- Serialization and queuing on hot keys.
Thus, in-memory databases still require query planning, compact layout, and concurrency control. A poorly chosen query will not automatically improve simply because disk page faults have been eliminated.
Memory Layout Can Be Designed Around CPU Architecture
Disk-based paging systems tend to use stable page numbers and slots to locate records. In contrast, memory-optimized systems can adopt more compact arrays, offsets, or pointers, but direct pointers come with costs: addresses become invalid after restart, object compression and relocation become more difficult, and snapshot copying requires repositioning.
When designing, consider the trade-offs between:
- Row layout is efficient for point queries and updates, while column layout excels at projections, compression, and scans;
- Fixed-width access is simpler, whereas variable-length fields require additional space or indirect references;
- Node-based indexing enables easy updates but suffers from poor cache locality; compact arrays perform better in batch operations;
- Per-row versioning and lock metadata reduce the actual data capacity available.
"No Buffer Pool, Direct Pointers, Compiled Execution" are not universal laws shared by all memory systems; they represent optional design choices within a broader design space.
Partitioned Serial Execution: Eliminating Local Coordination via Data Locality
H-Store/VoltDB routes data to execution sites based on partition ownership. Transactions within a single partition execute in a deterministic order within their owning execution context, avoiding the high overhead of extensive locking within that partition.
This benefit hinges on a key assumption: most of a transaction’s read and write operations occur within the same partition. If partition keys are chosen poorly:
- Hot partitions limit overall throughput;
- Cross-partition transactions require coordination across multiple execution sites;
- Partition migration and rebalancing become costly;
- Long-running transactions block subsequent work within the same partition.
"Single-threaded" does not mean the entire cluster operates with a single thread; transactions on a specific partition are serialized. Network, logging, replication, and other partitions can still run in parallel. Do not conclude that the system lacks any form of concurrency control.
Persistence Must Simultaneously Address RPO and RTO
Memory represents the current state of operation, not a justification for omitting persistence. Common strategies include:
- Logging transactional changes or deterministic commands required for recovery;
- Taking periodic snapshots to reduce the replay window of logs;
- Replicating data to lower the risk of single-point failures;
- Maintaining backups to protect against accidental deletions, logical corruption, and complete failure of a failure domain.
Two key objectives must be clearly defined:
- RPO: the amount of data that can be lost during a failure, how much received data is acceptable to be missing at recovery;
- RTO: the time required to restore service from a snapshot, logs, and replicas.
Stating only "we have snapshots" without specifying the data window since the last snapshot is insufficient. Similarly, saying "we have replicas" does not clarify whether the replicas are synchronized, whether they have been written to persistent storage, or whether a failover would result in loss of already-confirmed writes.
Recovery Paths Should Be Regularly Drilled
The larger the in-memory dataset, the more likely a restart and reload operation becomes a bottleneck for availability. A recovery budget must include:
Detect failure
+ Election or instance reconstruction
+ Read base snapshot
+ Replay incremental logs
+ Rebuild non-persistent indexes or caches
+ Catch up with replication or message streams
+ Validate and then reopen trafficAssume a 128 GiB snapshot loads at an effective 1 GiB/s, then 8 million log records replay at 200,000 records/s. If these stages do not overlap, loading and replay alone take about 128 + 40 = 168 seconds, before election, validation, and reopening traffic. Fitting the original data in RAM does not skip those 168 seconds on restart.
Fast log writing does not imply fast recovery. Recovery time, validation methods, and failover scripts should be tested regularly using backups that closely mirror production data volumes.
How to Understand DuckDB and HANA Placement
DuckDB is an embedded OLAP engine that uses vectorized execution and its own buffer manager, allowing some intermediate states to spill to disk within a memory budget. Some aggregate states and operator combinations can still exceed that budget or hit spill limitations; this is not an OOM-proof guarantee for arbitrary queries. It demonstrates that "optimizing for memory efficiency" is not the same as requiring that all data must reside in memory.
HANA retains logs, savepoints, and a persistence layer: restart can load a completed savepoint and replay subsequent redo. See SAP HANA data and log volumes. The in-memory primary layout service enables low-latency access during active work, while the persistence layer handles recovery; this is not simply about using disk for occasional backups.
Selection Checklist
- What is the amplification of working set and peak memory usage, rather than the original data volume alone?
- Can hotspots be partitioned by stable business keys? What is the proportion of cross-partition transactions?
- Where are commit acknowledgments required, on local logs, synchronized replicas, or across multiple fault domains?
- Does cold start or full recovery meet the required RTO?
- When memory budget is exceeded, does the system spill data, evict entries, reject writes, or crash?
- Is there a genuine need for full SQL statements, constraints, and multi-row transactions?
References
The next section focuses on Redis: what atomic commands, MULTI/EXEC, RDB, and AOF collectively guarantee.