Same Job, Two Engines: InnoDB and Iceberg Internals

The first version of my stock analysis repo ran analytics on an OLTP engine and my second version runs the same thing on an OLAP stack. I chose the OLAP stack because of a single optimization, but had to understand everything about the lakehouse to take advantage of it. OLTP InnoDB’s Three Guarantees Rows store all columns together Fetching one bar for one symbol touches a single spot on disk, which is exactly what ingest wants. An indicator that needs two columns still fetches every column through the buffer pool. Indexes speed up reads and slow writes Every index is its own B-tree and every write must update all B-trees in the table. B-trees are extremely quick to read. The goal of any index is to speed up reads at the cost of slower writes. Great for selective reads, awful for write heavy ingestion work. Which was my reasoning why my Raw* tables only have a primary key, and why there’s heavy indexing and partitioning on the tables that receive the Raw*’s work. Undo log allows concurrent reads and writes When a row is updated or deleted, the old value is stored in something called an undo log. If a session reads the table during the write, the old value comes from the undo log. Inserts get a smaller undo log too, the table and the primary key being inserted are stored, so InnoDB knows what to revert if the insert doesn’t commit. On commit an insert’s undo log is purged immediately. An update or delete’s undo log joins a global list called the history list, which is what lets sessions read older versions of changed data. For update and delete heavy databases the history list grows quickly, which would be a nightmare to deal with. Luckily someone smarter than me built the solution and you guessed right, it’s built right into InnoDB. InnoDB flags undo logs to be purged once every session that started before or during the commit has finished. This system is called Multi-Version Concurrency Control, MVCC for short. After all the technical jargon, the English translation is the MVCC allows inserts and updates to happen without blocking reads. Without knowing, I designed my InnoDB database using an OLAP design fundamental. financialdata held the datetime, open, high, low, and close. It was originally the only table that held datetime. Any technical indicator I wanted to query required a join on the financialdata table to know the date the indicator’s row was calculated for. It was one of my worst data designs. A design I realized wouldn’t work only after the database had over 100GBs of data. Fixing this was brutal. CREATE TABLE `FinancialData` ( `FinancialDataID` int NOT NULL AUTO_INCREMENT, `StockID` int NOT NULL, `StockDate` datetime DEFAULT NULL, `Open` decimal(20,5) DEFAULT NULL, `Low` decimal(20,5) DEFAULT NULL, `High` decimal(20,5) DEFAULT NULL, `Close` decimal(20,5) DEFAULT NULL, `Volume` decimal(20,5) DEFAULT NULL, `EMA` decimal(20,5) DEFAULT NULL, `VWAP` decimal(20,5) DEFAULT NULL, `RateOfChange` decimal(20,5) DEFAULT NULL, `OnBalanceVolume` decimal(20,5) DEFAULT NULL, PRIMARY KEY (`FinancialDataID`,`StockID`), UNIQUE KEY `StockID_Date_Idx` (`StockID`,`StockDate`), KEY `idx3` (`StockID`,`FinancialDataID`,`StockDate`,`Close`) ) ENGINE=InnoDB AUTO_INCREMENT=320075156 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`StockID`) PARTITIONS 100 */; Here’s a picture of the files responsible for my financialdata table partition. Each .ibd is an InnoDB tablespace file with roughly 1/100th of the table’s data. The files range: FinancialData#p#p0.ibd - FinancialData#p#p99.ibd. To be fair, the idea behind the design was sound. financialdata has datetime, open, close, low, high, and StockID, along with quite a few other columns, as you can see from the schema above. When I started this database, I thought I would need these columns for every query. When I started this database, I didn’t even know the most important price is the closing price. Let’s just say I have come a long way, I even read the Wall Street Journal now, and I think we all know what that means. Anyhow, this design meant all of my indicator tables had FinancialDataID and StockID in them, which served as efficient columns to join on. The idea of using financialdata as my base table and “picking” which columns I wanted based on the indicators I was working with sounds pretty darn similar to querying only the columns I want and ignoring the other columns in a table. When I finally bit the bullet and added StockDate to my indicator tables, I used ole reliable, a stored procedure ran by several different scheduled events. Each scheduled event ran the stored procedure on a separate table, so locking wasn’t an issue, waiting was. CREATE TEMPORARY TABLE IF NOT EXISTS t (StockDate DATETIME, FinancialDataID INT PRIMARY KEY); INSERT INTO t(StockDate, FinancialDataID) SELECT StockDate, FinancialDataID FROM FinancialData WHERE StockID = iStockID; UPDATE sar sr INNER JOIN t on sr.FinancialDataID = t.FinancialDataID SET sr.StockDate = t.StockDate; UPDATE macd md INNER JOIN t on md.FinancialDataID = t.FinancialDataID SET md.StockDate = t.StockDate; UPDATE directionalmovement dm INNER JOIN t on dm.FinancialDataID = t.FinancialDataID SET dm.StockDate = t.StockDate; UPDATE boilerband bb INNER JOIN t on bb.FinancialDataID = t.FinancialDataID SET bb.StockDate = t.StockDate; UPDATE chaikinoscillator co INNER JOIN t on co.FinancialDataID = t.FinancialDataID SET co.StockDate = t.StockDate; DROP TEMPORARY TABLE IF EXISTS t; Each scheduled event ran a single one of these updates, with the other updates commented out. 5 years into v1, I realized my analytical work used 2 or 3 columns per table, while I was forced to read every column. Since optimizing for the analytical work could theoretically give me an edge in the market, I decided to optimize towards it. After doing some research, I came to the conclusion the engine choice was wrong. OLAP If you thought the InnoDB section was technical, oh buddy, are you in for a rude awakening. Version 2’s analytical tables are Iceberg on MinIO, Parquet files behind a Polaris catalog, with Spark handling the reads. The entire partitioning story is one clause of DDL, pulled from here: CREATE TABLE IF NOT EXISTS raw_bars (`symbol` string, `time_stamp` timestamp, `open` double, `high` double, `low` double, `close` double, `volume` bigint, `vwap` double, `trade_count` bigint, `is_valid` boolean) USING iceberg PARTITIONED BY (bucket(16,`symbol`)); One of the luxuries of working with Iceberg is it computes and stores any partition in the metadata. Which means if I want to add a column to the partition I pay the price during reads. Data is grouped by partition version, pruned independently per version, and the prunes are merged afterwards. This step is called split planning. It took several days to a week to partition a table in v1, I had plenty of coffee breaks working with v1, and if I wanted to change a partition, I’d have to re-partition the entire table again, which is why I chose a larger than normal hash of 100. ~13,000 StockIDs split into groups of 130 StockIDs meant it would take YEARS before I had to repartition. The Read Path My lakehouse’s query optimizer took me about a month to understand, because it’s a relatively complex topic, plus, it was one of the few ideas I couldn’t relate to my OLTP background. I’m spoiling some of the technical magic, but I struggled to understand the difference between a manifest and a data file. In the scheme of things, these two definitions are on the simpler side, but I couldn’t grasp that grouping of data happened on multiple different layers. Understanding manifests and data files was my ah ha moment. Getting back to the regularly scheduled program, the lakehouse is composed of three pieces, Apache Polaris, MinIO, and Apache Iceberg. Polaris serves as my catalog, which for me, is a Postgres database and an API to read and write to it. In terms of query optimization, the Postgres database’s main responsibility is to store a pointer to a metadata.json file per Iceberg table. Every query starts by asking for that pointer, and every commit ends by updating it. Polaris only accepts one update at a time, which is what keeps table updates atomic, and what happens when two writers show up at once is covered in the commit path. Obviously this is my favorite piece of the lake house since it has an OLTP database. MinIO is the physical storage of my lakehouse. The only reason I use MinIO is because I’m hosting my lakehouse in my homelab. The main purpose of MinIO is to store the parquet data files, the manifests and manifest lists, and the metadata.json files. Using MinIO means the file structure is identical to an S3 Bucket. That’s all MinIO is responsible for, replacing the real thing with a homemade one.