The first version of my stock analysis repo ran analytics on an OLTP engine, and the second runs the same workload on an OLAP stack. I chose the OLAP stack for a single optimization, but had to understand the entire lakehouse to take advantage of it.
InnoDB's design has three guarantees that shaped my early choices. It stores all columns of a row together, so fetching one bar for one symbol touches a single spot on disk, which suits ingestion. However, an indicator needing only two columns still fetches every column through the buffer pool. Indexes speed up reads but slow writes, since every write must update each B-tree in the table. That is great for selective reads and awful for write-heavy ingestion, which is why my Raw* tables have only a primary key.
InnoDB also uses an undo log, which allows concurrent reads and writes. When a row is updated or deleted, the old value is stored so other sessions can still read it. Inserts get a smaller undo log that is purged on commit. Update and delete undo logs join a global history list, which InnoDB purges once every session that started before the commit has finished. This mechanism is Multi-Version Concurrency Control, or MVCC, which lets inserts and updates happen without blocking reads.
Without realizing it, I had designed my InnoDB database using an OLAP fundamental. My financialdata table held the datetime along with OHLC prices, and every technical indicator required a join on it to find the date. It was one of my worst data designs, and I only realized it once the database held over 100GB of data. Fixing it was brutal.
Source: Hacker News · Summarized by HeadlinesBriefing