Comparing InnoDB and Iceberg Internals for Stock Analysis Workloads

InnoDB's Core Design for OLTP

InnoDB, an Online Transaction Processing (OLTP) engine, stores all columns of a row together. This design is efficient for ingest operations where fetching a single record touches a single disk location. However, fetching specific columns for an indicator still retrieves all columns through the buffer pool.

Indexing and Write Performance

Indexes in InnoDB are implemented as B-trees, which accelerate read operations but incur a performance cost during writes. Every write operation must update all B-trees associated with the table. This characteristic makes InnoDB suitable for read-heavy selective queries but less optimal for write-heavy ingestion tasks.

Multi-Version Concurrency Control (MVCC)

InnoDB utilizes Multi-Version Concurrency Control (MVCC) to enable concurrent reads and writes without blocking. When a row is updated or deleted, its old value is stored in an undo log. This allows sessions reading the table during a write to access the older version of the data. Undo logs are purged after all relevant sessions have completed, preventing the history list from growing indefinitely in update/delete-heavy databases.

OLTP Design Challenges for Analytics

The author initially designed an InnoDB database with an OLAP fundamental, storing `datetime`, `open`, `high`, `low`, and `close` in a single `financialdata` table. This design required joins on the `financialdata` table for any technical indicator query, which proved to be an inefficient data design for analytical workloads.