Architectural Tradeoffs in Modern Data Systems
Transcript
Made automatically from the audio, so it may contain mistakes. Click a line to play from there.
Imagine you were the technical lead for well, a burgeoning e-commerce startup. And when you first wrote the initial back end, you made this brilliant, incredibly pragmatic choice. You used SQLite as your application's primary data store.
Which, I mean, is exactly how you should start. It's the perfect tool for the job at that stage.
Right, because it's wildly fast. It requires zero background administration, and it basically competes directly with faux open for local disk access. You just deployed your app and it ran flawlessly.
The beauty of SQLite really is its simplicity. Because it runs directly within your application's process space, you completely avoid all that overhead. There are no network calls, no socket connections, no managing separate database servers. It's literally just a file on a disk and a highly optimized C library managing it.
Yeah. And the documentation itself notes that, you know, for a site getting under a hundred thousand hits a day, SQLite thrives. It's amazing. But here's the scenario for you, our listener today. Your startup just went viral.
Oh boy.
You didn't just cross that hundred K threshold, you absolutely obliterated it. You now have millions of active users, and suddenly that single SQLite file is the bottleneck choking your entire company to death.
And we know exactly why it's choking. It really comes down to file level locking. Right. Because SQL A is phenomenal for reads. It can handle basically unlimited simultaneous readers, but because it relies on standard file system locks, only one process can write to that database file at any given millisecond.
So when you scale up.
Yeah, when you scale up to dozens of application servers behind a load balancer and they are all trying to record checkout transactions over a network mount to that single file, the write queue just stalls.
It just backs up completely.
Exactly. The network latency compounds the lock contention and your application effectively grinds to a halt.
So your trusted tool has basically become a liability, and you are forced to migrate to a client server architecture. But looking out of the modern data landscape today is frankly downright intimidating. It really is. It's a dizzying array of specialized stores, streaming engines, and open formats. Which brings us to our mission today. Consider this the series overview, episode E00, of our brand new series on modern data stores.
I'm really excited for this one.
Me too. Over the coming weeks, we're going to dive into the specific systems, but our goal for this deep dive is to map the entire landscape. We've taken a massive stack of foundational architectural papers, system documentation from tools like Kafka, MongoDB, Clickhouse, Postgres, and distilled the critical insights.
We want to equip you to actually understand why these systems work under the hood, so you can choose and run the right one.
Because in distributed systems, there is no such thing as a perfect database.
No, definitely not. There are only trade-offs. You are constantly turning dials, you know, trading strict consistency for horizontal availability or write speed for read performance. The fundamental rule of thumb here is it depends on your specific constraint, and you have to explicitly define what that constraint is.
Okay, let's unpack this. We need to fix our crashing e-commerce app. And looking at the telemetry, I noticed something really interesting. The checkout process isn't just failing under its own weight. The absolute worst latency spikes happen precisely when our marketing team logs in and runs their massive end-of-year historical sales reports. They are hitting the exact same database that's trying to process live shopping carts.
And there it is. That right there is the catalyst for our first major architectural split. You are mixing operational workloads, which is often called OLTP or online transaction processing. Yeah. With analytical workloads or OLAP. And when you put them on the same physical hardware, their access patterns are so diametrically opposed that they actively destroy each other's performance.
So let's break down why that actually happens. On the operational side, when a user clicks checkout, we need absolute certainty. The payment must clear, the inventory must decrement, and the order must record.
Right, you can't have half a transaction.
Exactly. Yeah. We need relational databases for this, like Postgres, MySQL, or Azure SQL. I need the database to enforce ACID guarantees. You know, atomicity, consistency, isolation, and durability.
Precisely. Relational systems are built for strict multi-row consistency. If a user buys the last pair of boots, you have to update the inventory table and the orders table simultaneously.
And if it fails.
If the payment gateway times out halfway through, the whole transaction must roll back. Relational databases achieve this through write-ahead logging and strict schema on write. They validate the data structure before it ever hits the disk.
Okay. But the trade-off rule kicks in immediately here. Relational databases are highly optimized for strict correctness and complex state changes on individual rows. If I want to scale that horizontally, say spanning my Postgres database across 20 servers to handle Black Friday traffic.
It becomes incredibly painful.
It really does. I have to implement complex sharding logic.
And because relational data is highly normalized to prevent duplication, an order might be scattered across a user's table, an orders table, a line items table, and a products table.
So you're jumping all over the place.
Exactly. When you start across servers, a single SQL join might suddenly require pulling data back and forth across a network, which absolutely kills performance. So the trade-off is clear. It depends on your need for strict transactional safety versus rapid, frictionless horizontal scaling. For checkouts, you choose safety.
Right. And relational databases physically store data row by row on the disk. Which perfectly explains why the marketing team's query is crashing the system.
Yep.
I always think of this analogy. OLTP, the operational side, is like a really fast-paced restaurant kitchen. They are taking individual orders row by row, cooking them and getting them out as fast as possible.
I like that.
But OLAP, the analytical side, is like an auditor who walks into the kitchen and says, I need to count exactly how many tomatoes were used across all your restaurants for the entire year.
And the kitchen just stops.
Right. Marketing doesn't care about an individual user's cart. They want to run an aggregate. Sum up the total revenue from shoe sales in the last 12 months. To answer that, Postgres basically has to read every single row in the database, load it into memory, extract just the revenue column, and throw the rest of the data away. It is an astronomical waste of disk I.O.
Which forces you to pivot to the analytical side of the divide. Systems like Click House or DuckDB, we'll cover those heavily in episode E01. But they are engineered to scan billions of records in milliseconds.
Billions.
Literally billions. And they do this because they are true column-oriented databases.
I want to spend some time here because understanding the physical storage mechanics of Columnar databases was like a huge light bulb moment for me. In Postgres, a row might be, you know, Alice, 28. New York, 150 dollars. On disk, those bytes sit right next to each other.
Right. Contiguous bytes.
But in ClickHouse, all the names are grouped together. All the ages are grouped. All the purchase amounts are grouped.
Exactly. And the ClickHouse architecture documentation stresses something really critical here. In a true columnar DBMS, absolutely no extra metadata is stored with the values.
What do you mean by extra metadata?
Well, in a row store, every value might have a few bytes of padding or a length header, just to manage the row structure. In a true columnar store, if you have one billion eight-bit integers representing ages, they will consume exactly one gigabyte of uncompressed space on disk. No garbage, no overhead.
Wow. Which also means the compression ratios must be insane. Like if I have a column of country codes and it's just US, US, US, UK, US, repeated millions of times. I can use run length encoding.
Exactly. The database just stores US times three million.
So when the CPU asks the disk for that data, it's pulling in vastly smaller, highly compressed blocks.
And that brings us to the CPU execution layer itself. It's not just about saving disk I.O. Clickhouse uses a vectorized query execution engine.
Okay, let's define that because vector is this incredibly overloaded term that we are definitely going to hit again later with AI. What is vector execution mean in the context of analytics?
So traditional relational engines often use what's called a volcano processing model. They evaluate queries row by row, basically calling an execution function for every single tuple.
That sounds slow.
It's a massive amount of CPU overhead, just in function calls. Vectorized engines, however, process data in blocks or vectors, say arrays of a thousand column values at a time. This leverages SIMD instructions.
Single instruction, multiple data?
Right, which are built into modern CPUs. The CPU can take a single mathematical instruction and apply it to an entire array of values in one single hardware clock cycle. It maximizes the L1 and L2 CPU caches and just completely eliminates that row by row function overhead.
That makes total sense. But I do want to clarify a metal trap I actually fell into early on. I used to think analytical databases were basically just read-only archives. Like data goes in at night in a batch and you query it during the day. But that's not really true for modern systems like ClickHouse, is it?
No, it's actually a huge misconception. ClickHouse actively supports very high throughput real-time inserts.
But how? I mean, if I'm inserting a new row, a new order from Alice, and ClickHouse stores data in these massive dense columns, wouldn't it have to rewrite the entire names column and the entire ages column just to append one value? That would be incredibly slow.
Well, if it updated in place, yes, it would be awful. But ClickHouse solves this using a structure based on the log structured merge tree or LSM tree. In ClickHouse, they call it the merge tree engine family.
Okay.
When you insert a batch of new rows, ClickHouse doesn't even touch the existing historical columns. Instead, it writes the new data as a completely new, small sorted part on the disk. Because it's just writing a new file, it doesn't need to take any locks. Right. And then entirely in the background, a separate thread continually sweeps the disk, taking these small parts and merging them together into larger, heavily compressed parts. It's totally asynchronous.
So what's the catch?
The trade-off is that while it supports millions of inserts per second, it completely sacrifices full-fledged transactions. And it makes point updates or single row deletes incredibly expensive. You definitely wouldn't use it to update a user's password.
Got it. When I'm architecting a system, I like to map these trade-offs out in my head. I have this mental matrix for comparing operational and analytical systems across three dimensions purpose, data shape, and scale. Let's do a verbal table for the listener.
Sure, let's build it out.
For the first dimension primary purpose, it's straightforward. Operational is about consistent transactional operations. Analytical is about large-scale historical aggregation.
Exactly. For your second dimension, data shape, operational leans heavily into highly normalized relational tables or flexible documents. You want the whole entity together. Analytical relies on columnar or sparse tabular formats heavily compressed by data type.
And the third dimension is the scale dimension. If I need to scale my operational Postgres database, I'm usually looking at partition count, sharding customers A through M on one server, N through Z on another. But if I scale my analytical database, I'm utilizing massively parallel processing MPP across servers.
Which means when that heavy marketing query comes in, the ClickHouse coordinator node receives it, rewrites the query, and distributes it to 50 different servers simultaneously. Each server scans its local chunk of the columnar data, does a local sum, and returns just a tiny partial result to the coordinator, which then merges them. The query latency literally drops linearly as you add hardware.
Perfect. So applying this to our startup scenario, we route our live shopping carts to Postgres and our heavy marketing reports to ClickHouse. The immediate crisis is averted. Yeah, it stops crashing.
Mission accomplished.
Well, almost. Because now we have a temporal problem, a problem with time. The traditional way to get data from Postgres into a warehouse is a nightly batch job. We run an ETL script at 2am.
Right. The classic extract, transform, load.
But if we do that, the shiny new real-time inventory dashboard the business just requested is completely useless. It's always up to 24 hours out of date. We have to fundamentally rethink how data moves through time.
And this transitions us from the era of batch processing into the era of continuous event streaming, which we're gonna dissect in episode EO6. But to appreciate streaming, you really have to understand the batch paradigm that ruled the Earth for a decade.
Heavily popularized by Google's 2004 MapReduce paper, right?
Exactly. That paper is legendary.
But explain the mechanics of it. Why did the industry rely on batch processing for so long?
Well, before MapReduce, if you wanted to process terabytes of data across a cluster of commodity machines, you had to manually write code to handle parallelization, network partitions, data routing. It was an absolute nightmare.
I can imagine.
The map reduce abstraction hid all that complexity behind two simple functions borrowed from functional programming map and reduce. The framework takes a massive data set, splits it into chunks, and assigns them to worker nodes. The map function processes that chunk and emits intermediate key value pairs. Say you are counting page views by country. The map function outputs US, comma one, UK, comma one.
And then there is a massive network shuffle, right? Like a giant post office sorting everything.
Yes, exactly. The framework guarantees that every value with the same key is routed to the exact same reduce worker. The reduce function then takes all those ones for the US key and just sums them up.
And the reason everyone loved this was the fault tolerance. I mean, if you have a thousand servers, one of them is inevitably going to catch fire or lose power during a job.
Without a doubt. The framework achieved fault tolerance through simple re-execution. If a node running a map task died, the master node just noticed the heartbeat failed and reassigned that specific chunk of work to a healthy node. It's incredibly resilient.
But it's slow.
Very slow. The trade-off is speed. A map reduced job requires the entire map phase to completely finish, write its intermediate data to disk, and synchronize before the reduce phase can even begin. It operates on bounded, static data sets. It is inherently a batch process.
Which means it definitely doesn't solve our real-time dashboard problem. To fix that, we have to adopt a philosophy that honestly blew my mind when I first read it.
Oh, I think I know what you're gonna say.
Martin Klepman. He wrote a piece called Turning the Database Inside Out.
It's a classic.
He argues that since the dawn of SQL, we have treated databases as systems of global shared mutable state. When Alice changes her shipping address, Postgres finds the row on the disk and physically overwrites the old address with a new one.
And Kletman's point is really profound here. Software engineers learned decades ago that global mutable variables in application code lead to race conditions, bugs, concurrency nightmares. We abandon them in our code, yet we place them at the very center of our data architecture.
His analogy is brilliant. He says accountants don't use erasers.
Right.
If a bank makes a mistake and credits your account twice, they don't go into the ledger and erase the bad transaction. That would destroy the audit trail. Instead, they append a new transaction, a debit, that corrects the error. The ledger is an immutable, append only history effects.
And that is exactly what Apache Kafka brings to modern architecture. Kafka is not a database you query in the traditional sense. It is a distributed, durable commit log. It acts as the central nervous system.
So everything just flows into it.
Every action in your system. A user clicking a button, a microservice updating an inventory count, a sensor recording a temperature. It's all serialized as an immutable event and appended to a Kafka topic.
So the data's safely written to disk in Kafka, but Kafka is terrible for like looking up a specific user's profile. It's just a raw log.
That's where you build materialized views at the edges of your architecture using stream processing engines like Apache Flink. You turn the database inside out. Instead of writing to a monolithic database that tries to handle all read patterns, you write once to the Kafka log. And then Flink consumes that log in real time and constantly updates specialized data stores. If you need a real-time leaderboard, Flink updates a Redis cache. If you need complex profile lookups, it updates MongoDB. The database is totally deconstructed.
But wait, let me push back here. In MapReduce, fault tolerance was easy because it was batch. If a node crashed, you just restarted the map task from the static file.
Right.
But if we have a continuous, never-ending stream of millions of events per second flowing through Apache Flink, and a server dies, how do you recover without losing data or worse, double counting events? Doesn't taking a backup snapshot force you to basically stall the entire streaming system?
That was actually the fatal flaw of early streaming engines. Pausing a high throughput stream to take a global snapshot absolutely ruined latency. But the researchers behind Apache Flink solved this with an algorithm called asynchronous barrier snapshotting, or ABS.
I've seen the acronym, but how does ABS actually work without stalling the computation?
It's ingenious, really. The system periodically injects special control records, which they call barriers, directly into the data stream itself. Okay. These barriers flow downstream right alongside the actual user events. They don't overtake the data. When a processing operator, say a node counting page views, receives a barrier, it knows with absolute certainty that it has processed every single record that occurred before that barrier.
So it knows its exact state at that exact point in the stream.
Right. At that precise moment, the operator pauses its input for just a fraction of a millisecond, asynchronously flushes its current state, the current page view counts, to a durable storage layer like Amazon S3, and then forwards the barrier downstream to the next operator.
Oh, wow. So the snapshot isn't taken all at once globally. The snapshot ripples through the cluster, operator by operator, riding along with the data.
Exactly. Because it persists only the internal operator states along a cyclic execution topologies, meaning the data flows in one direction without looping back on itself. It doesn't have to eagerly save all the actual data records in transit.
It just saves the mathematical state of the barrier.
Yes. And this allows Flink to guarantee exactly once processing with subsecond latency without stalling the global pipeline.
That is heavy, but it is so elegant. When I compare batch and streaming architectures, I use another mental matrix. Let's do table number two.
Go for it.
Row one is the core abstraction. In batch systems, we think in terms of static data sets. In streaming, the abstraction is continuous event logs.
Row two is fault tolerance, as we just discussed. Batch systems rely on re-execution of map or reduce tasks. Streaming systems rely on asynchronous barrier snapshotting to resume exactly where they left off.
And row three is the system example. The classic batch paradigm is Hadoop paired with MapReduce. The modern streaming paradigm is Kafka, acting as the durable ledger paired with Flink, acting as the computation engine.
And by adopting that streaming mindset, your e-commerce architecture becomes fully reactive. The moment a checkout event hits Kafka, Flink processes it and updates the real-time inventory materialized view instantly.
But that brings us to a massive question, which we'll explore heavily in episodes E02 and E03. If Kafka is just a log, where do these materialized views actually live?
Good question.
If we have truly deconstructed the database, we need specific engines for specific reads. Let's walk through section three, the menagerie of operational non-relational stores, often called NoSQL. Let's start with document stores like MongoDB.
The defining characteristic here is schema on read.
Right. In Postgres, as we discussed, you have schema on write. If you want to start tracking a user's loyalty points, you have to run a heavy alter table migration.
Which nobody wants to do in production.
Exactly. In MongoDB, data is stored as Bison or binary JSON. You simply start writing documents with a loyalty points field. The database happily accepts it. It supports polymorphic data, meaning two documents in the exact same user's collection can have wildly different structures.
That flexibility just completely changes data modeling. In relational modeling, you normalize, you put users in one table, there are multiple addresses in another, and you join them.
But at MongoDB, you have to choose do I reference the addresses via an ID, or do I embed an array of addresses directly inside the user document itself. What's the architectural rule here?
Well, it depends on your access patterns. The rule of thumb for document stores is data that is accessed together should be stored together. If it is a one-to-one or one to few relationship where the child data is rarely accessed outside the context of the parent, like an order and its line items, embedding is massively superior.
Because of the read speed.
Yes. When you query the order, the disk head reads the entire document in one contiguous read. No joins required.
But there is a huge trap there. The danger of embedding is unbounded document growth.
Precisely. MongoDB enforces a strict 16 megabyte size limit per document. Let's say you build a product catalog and you decide to embed every user review inside the product document. As that product gets popular, the array of reviews grows. Every time you append a review, the document size increases.
And physically, on the disk, if the document outgrows its allocated page space, the database actually has to pick up the entire document and move it to a new location with more contiguous space, right?
Yes, and that causes severe memory fragmentation and huge IO overhead. So if the many side of a one-to-many relationship is unbounded, you must use referencing. You just store the reviews in a separate collection.
Okay, so that's the document model. But what if I just need raw speed? Say I'm building a session cache or a real-time leaderboard for our flash sales. That's where key value stores come in, like Redis.
Redis trades structural complexity for sheer blistering speed. By associating a unique key with a value and operating primarily in memory, it easily delivers sub-millisecond latencies.
But calling the values just opaque strings is a huge disservice to Redis. It's actually a full data structure server.
Right. I was amazed when I first learned it wasn't just SCT key value. You can store complex structures under a key. It supports bitmaps for incredibly dense Boolean arrays, geospatial indexes for location radius queries, and probabilistic data structures like hyperlog.
Okay, let's explain hyperlog because it sounds like literal magic. If I want to count the exact number of unique users visiting our site today, and I have 50 million visitors, storing every unique ID in a standard set would consume gigabytes of RAM.
But if you just need an estimate with, say, a 1% error margin, hyperlog can calculate the cardinality of those 50 million unique IDs using a maximum of 12 kilobytes of memory.
12 kilobytes. How?
It works by hashing the inputs and counting the maximum number of leading zeros in the binary hashes. It's mathematically brilliant and essential for high volume streaming analytics where exact precision just isn't necessary.
And alongside Redis in the key value family is Amazon's dynamo. The original dynamo paper is foundational to our industry. It was born out of the necessity to keep the Amazon shopping cart highly available.
Right. They realize that if a database note fails and rejects a write, a customer can't add an item to their cart.
No, that is directly lost revenue.
Exactly. So Dynamo famously sacrifices strict consistency for high availability. It basically popularized the concept of eventual consistency. And the most elegant part of the dynamo design is how it exposes tunable SLAs, service level agreements, to the developer through three integers, n, r, and w.
Walk me through the math on this because it's so important.
Okay. So N is the total number of physical nodes that will store a replica of a piece of data. W is the write quorum, meaning the number of nodes that must successfully acknowledge receiving a write before the database tells the client it was a success.
An R?
R is the read quorum, the number of nodes you must query when reading a value to determine the most recent state.
So let's say I set N to three. I have three copies of the shopping cart. How do I tune R and W based on my trade-offs?
Well, if you are building a system where read speed is everything, you might set W to three and R to one. A write has to be confirmed by all three replicas. It's slow and brittle because if one node is down, the write fails entirely. But a read only has to hit a single local node, making it lightning fast.
But conversely, if availability is paramount, like the shopping cart, you set W to one. As long as one single node receives the cart update, the write succeeds. But if you leave R at one, you risk querying a node that hasn't synchronized the latest cart update yet. You get a stale read.
Exactly. To guarantee strong consistency while tuning, you just follow a simple formula. R plus W must be greater than N. If N is three, and you set W to two and R to two, the read and write quorums overlap. The read is mathematically guaranteed to contact at least one node containing the latest write. It's a beautiful way to let architects dynamically negotiate the cap theorem.
It is fascinating how just three integers define your entire distributed architecture. Let's finish up the operational menagerie. We have wide column stores and graph databases. Wide column stores like Cassandra or Google's Big Table are often described as sparse, multidimensional sorted maps.
In a relational database, every row must have the exact same columns. In a wide column store, data is indexed by a row key, a column key, and a timestamp. But the schemas are totally dynamic within a column family. Row one might have three columns. Row two might have 3,000 entirely different columns.
And it's heavily optimized for write throughput, right?
Yes. It's the absolute standard for IoT telemetry where you have millions of smart meters constantly writing varied metrics at incredibly high frequencies.
And finally, graph databases like Neo4J. Instead of tables, they store data as nodes like user Alice or product shoe and edges, which are the relationships like purchased.
Graph stores shine when the relationship between data points is actually more valuable than the data itself. If you need to write a query to find, you know, all the friends of friends of people who live in London who bought this specific shoe.
Writing that in SQL requires deeply nested recursive joins that will bring a relational database to its knees.
Oh, it would be awful.
Yeah.
But a graph database traverses the physical pointers between nodes almost instantly.
But the trade-off is the overhead. If you're just looking up a user's email address by their user ID, the graph traversal engine is a massive unnecessary penalty.
Right. You have to match the model to the access pattern.
Let's summarize the NoSQL menagerie with table three. Row one, normalization. Document stores are denormalized into hierarchical structures. Key value stores are denormalized into opaque values. Wide column stores are denormalized into sparse dynamic columns.
Row two, access pattern. Document stores are for fetching complex aggregate entities all at once. Key value is for blazing fast point lookups by a single known key. Wide column is for appending and scanning wide time series rows.
And row three, scale dimension. Document stores generally scale by horizontal partition count. Key values scale seamlessly across a massive hashed key space. Wide column scales by partition and column family width.
That perfectly covers the operational edge. But that leaves a massive, glaring architectural gap for our startup.
Yes, it definitely does. We are streaming millions of events through Kafka. We've materialized the current state into Redis and Mongo, but Kafka topics eventually expire, and those operational stores are far too expensive to store 10 years of raw historical data. Where does the immutable log go to rest permanently?
Which brings us to section four. The analytical giants and the lake house, which we'll cover in E04 and E05. To understand the lake house, you really have to look at the history. Generation one was the data warehouse, systems like Teradata. They coupled proprietary storage directly to the compute engine. They required rigid schema on right. They were incredibly powerful for structured business intelligence, but wildly expensive and utterly incapable of storing unstructured data, like images, logs, or raw JSON.
So the industry pivoted to generation two, the data lake. We started dumping all our raw data into cheap cloud object storage like Amazon S3 or Hadoop HDFS. It was schema on read. Just store the files now, figure out the schema later.
But S3 isn't a database.
No, it's not. You can't just run a SQL query against a folder of CSV files and expect it to be fast.
Which led to a highly complex two-tier architecture. You had the data lake for cheap storage and machine learning, but you still had to run complex ETL pipelines to move a structured subset of that data into a data warehouse for the BI teams.
And maintaining two systems is a total nightmare. The data in the warehouse was always stale, and the data engineers were constantly fixing broken ETL scripts.
But the turning point, the real paradigm shift here was the widespread adoption of open columner file formats, specifically Apache Parquet.
Okay. Let's dive deep into Parquet. Because Parquet is not a database. It's a highly optimized file format, but it completely revolutionized the data industry. Here's where it gets really interesting. How is a parquet file structured internally?
It has a brilliant hierarchy. At the top level, a parquet file is divided into row groups, typically chunks of 50 to a thousand megabytes of rows. Row groups are the unit of parallelization. If you give a parquet file to a Spark cluster, it assigns different row groups to different worker nodes.
Makes sense.
Inside a row group, the data is separated into column chunks. All the names, all the ages. Column chunks are the unit of I.O. If your query only asks for names, the disk head only reads that specific chunk and entirely skips the rest.
And inside those.
Finally, inside the column chunks, you have pages. Pages are the unit of compression and encoding, often using dictionary encoding to shrink repetitive values.
But the absolute killer feature of Parquet is predicate pushdown, right? The metadata statistics.
Yes, absolutely. At the end of the file, Parquet stores a metadata footer. For every single column chunk, it records the minimum and maximum values. If your SQL query says where age greater than 50, the query engine first reads the tiny footer.
And if it sees that a specific row group has a maximum age of 40.
The engine completely skips reading that 100 megabyte chunk of data from disk. It doesn't even load it. It's an I.O. optimization miracle.
And because it's an open standard, any compute engine, Spark, Presto, DuckDB, Python Pandas, can read the exact same parquet files. This laid the foundation for the third generation, the Lake House architecture. Famously detailed in a 2021 CIDR paper by researchers from Databricks.
The Lake House takes the cheap, scalable object storage of a data lake, uses open formats like Parquet, and introduces a critical new component, a transactional metadata layer. Projects like Apache Iceberg or Delta Lake sit on top of the Parquet files and act as the brain.
But how does Iceberg actually provide database guarantees like acid transactions over a bunch of flat files sitting in dumb cloud storage? S3 doesn't have a transaction manager.
No, it doesn't. It uses multi version concurrency control, or MVCC, implemented via pointer files. Iceberg maintains a central hierarchical metadata catalog. At any given moment, there is one root metadata file that lists exactly which parquet files constitute the current state of the table.
Okay, so when a Spark job wants to append new data.
It writes new parquet files to S3. These files are totally invisible to the users. Once the write is completely finished, Spark attempts an atomic swap to update the central iceberg pointer to a new metadata file that includes those new parquet files.
And if the Spark job crashes halfway through writing the parquet files?
The atomic swap never happens. The partial files are just orphaned in S3 and eventually garbage collected. The transaction fails cleanly. Users querying the table never see the corrupted half-written data. This provides full ACID isolation on a data lake.
And because it's using MVC C, it gives you time travel out of the box.
Yes.
If I screw up a table today, Iceberg still has the metadata pointer from last Tuesday. I can just query the table as of last Tuesday, and it will read the parquet files that were active at that timestamp.
And this is totally revolutionary for machine learning. Previously, data scientists had to connect to a data warehouse through slow ODBC connections, pulling data row by row into Python.
Which takes forever.
Right. With a lake house, tools using declarative data frame APIs can talk to Iceberg, locate the exact parquet files they need, and pull them directly from S3 into memory, bypassing the query engine bottleneck entirely. It unifies analytics and AI workloads on a single storage layer.
I love mapping this transition. Let's do table four for the three generations data warehouse, data lake, and lake house.
Sounds good.
Row one, storage format. Proprietary for warehouse, open formats for lake, open formats plus transactional metadata for Lakehouse.
Row two, ML and data science support. Poor for warehouse, requires slow export, good for data lake, but lacks transactions. First class declarative data frame APIs for Lakehouse.
And row three data staleness. High for warehouse due to ETL delays, medium for lake, low for lake house due to direct operational ingest.
It really is the convergence of the best ideas in data engineering over the last 20 years. But as our startup matures, we are encountering new use cases that SQL and Parquet alone can't solve.
Right. Which brings us to our final technical frontier, section five. Vectors and consistency, teeing up episode E07. Our e-commerce product team wants to build an AI visual search. A user uploads a photo of a sneaker, and our system finds similar looking sneakers, even if the text descriptions don't match at all. Standard SQL keyword search is useless here. We need semantic trimilarity.
This is the domain of vector databases. When you pass an image or a text description through an AI embedding model, it converts the semantic meaning of that data into a high dimensional vector, essentially an array of hundreds or thousands of floating point numbers.
So finding similar sneakers translates mathematically to finding vectors that are physically close to each other in that high dimensional space?
Exactly. We're gonna focus on PG vector, an open source extension that is wildly popular because it turns standard Postgres into a vector store. You can query your relational business data and your AI embeddings in the same SQL statement.
But how does it actually calculate that distance?
Well, PG vector supports several mathematical distance functions. L2 distance calculates the straight line Euclidean distance between two points. Inner product multiplies the vectors. But for many AI embeddings, especially text, cosine distance is the standard.
Cosine distance.
Yeah, it measures the angle between two vectors, entirely ignoring their magnitude.
Why does the angle matter more than the length?
Imagine two documents. One is a 10-word tweet about sports, the other is a 10,000 word Wikipedia article about sports. They have the same semantic meaning, but wildly different lengths. In a vector space, their vectors will point in the exact same direction, but the Wikipedia vector will be much, much longer.
Okay, I see.
Cosine distance recognizes that the angle between them is zero, meaning they are highly similar, whereas Euclidean distance would penalize them heavily for the difference in length.
That makes perfect sense. But if I have a catalog of 10 million products, running complex cosine trigonometry between my search vector and 10 million row vectors for every single search would take minutes. We have to index them.
We do. And here we hit a massive architectural trade-off. Speed versus recall. Recall is the percentage of the true nearest neighbors that your search actually retrieves. If you want 100% perfect recall, you must perform an exact search, scanning every single vector.
Which doesn't scale.
No. For production AI, you use approximate nearest neighbor or ANN indexes. You trade a slight drop in accuracy, maybe achieving 95% recall, for a thousand times increase in query speed.
PG Vector provides two primary ANN indexes, HNSW and IVF flat. Let's break down the mechanics, starting with HNSW.
HNSW stands for hierarchical navigable small world. It is an incredibly clever graph-based index. It builds a multi-layered hierarchy of the vectors.
Kind of like zoom levels on a map.
Exactly like that. The top layers of the graph contain very few vectors with long distance connections. The bottom layers contain all the vectors densely connected to their local neighbors. When you search, it enters the top layer, takes a massive leap across the graph to get into the general neighborhood of your query, and then drops down a layer for more fine-grained routing.
It offers spectacular recall and speed. But the trade-off is the build cost.
Yes.
Building that graph requires complex memory management. It takes a really long time. It also consumes a massive amount of RAM. You have to tune parameters like M, which is the maximum number of connections per node, and EF construction, which dictates the size of the dynamic candidate list evaluated while building the graph.
So a higher EF construction means better recall, but vastly slower insert times.
Right. And the alternative index is IVF flat.
Inverted file flat.
Yeah.
It takes a completely different approach using K means clustering. It groups your vectors into a defined number of clusters, often visualized as Voronoi cells. When a search vector comes in, IVF flat calculates which cluster center it is closest to and only compares the query against the vectors inside that specific cell. It ignores 99% of the database instantly.
It's much faster to build and uses a fraction of the memory of HNSW. But the recall is generally lower, especially around the edges of the clusters. You tune it by setting the number of lists, which are the clusters, and probes, which is how many neighboring clusters to check during a search. Right. Now, here's a practical question that hits SaaS architects constantly. Multi tenancy. I have a thousand different vendors using my e commerce platform. If I throw all their vectors into one massive shared IVF flat or HNSW index, won't one vendor with five million products basically skew the clustering and ruin the recall and speed for a vendor with only 500 products?
Yes, they absolutely will. A global approximate index is influenced by the density of the data. The recommended architectural pattern in PG vector for strict multi-tenancy is to use list partitioning in Postgres.
How does that work?
You partition the underlying table by tenant ID. This forces Postgres to create physically separate underlying tables and crucially, completely isolated vector indexes for every single tenant. It guarantees perfect performance isolation.
That is the exact kind of architectural nugget we're looking for. Let's do table five, our last one for these vector indexes. Row one, recall versus speed trade-off, high recall, slower build for HNSW, lower recall, faster build for IVF flat.
Row two, memory footprint.
High for HNSW, low for IVF flat.
Row three, tuning parameters.
EF construction and M connections for HNSW. Number of lists and probes for IVF flat.
That perfectly frames the vector landscape. But before we conclude this overview, we have to talk about the invisible gravity that holds every single distributed system we've discussed together, from dynamo to iceberg to Postgres.
And that is distributed consistency.
The hardest problem in computer science.
It's terrifying. Jepson formalizes consistency by defining a consistency model as a set of histories that a system is legally allowed to execute. The absolute gold standard is serializability.
Serializability guarantees that even if hundreds of clients are executing transactions concurrently, the database will guarantee a final state that is exactly identical to as if those transactions had been executed one by one sequentially in some order.
But a serializability requires massive coordination overhead, which kills performance. So databases usually default to weaker consistency models. Jeffson defines these weaker models by the specific anomalies or prescribed phenomena they are supposed to prevent.
Right. Let's look at a critical anomaly. G1A, known as the aborted read. This happens when a database allows a transaction to read uncommitted data from another transaction that eventually rolls back.
Oh, that sounds bad.
It is. Imagine transaction A debits 100 from an account. Before it commits, transaction B reads that new balance and approves a withdrawal. Then transaction A hits a validation error and aborts, reverting the initial debit.
So transaction B just approved a withdrawal based on phantom money that mathematically never existed. That is catastrophic for an e-commerce platform.
To prevent this, databases track operations using dependency graphs. If transaction B reads a value written by transaction A, B has a write read dependency on A. The database engine monitors this graph of dependencies in real time. By enforcing mathematical rules that prevent specific cycles from forming in that graph, the database guarantees that phenomena like aborted reads cannot happen.
The real takeaway here for an architect is that you cannot just read a marketing page that says highly consistent. You have to fundamentally understand the formal consistency model of your data store because the edge case failures will destroy your business logic.
Absolutely.
So what does this all mean? We have mapped the entire landscape. We've gone from embedded SQLite to massive map reduce batches, unbundled the database with Kafka and Flink, explored the NoSQL menagerie, built an iceberg lake house, and implemented AI vector search. As a technical lead, how do you synthesize all of this? Here are three concrete architectural takeaways from today's deep dive. Takeaway one, network proximity dictates architecture.
Explain that one.
Well, if your application code and your database are separated by a network, you must use a client server database like Postgres to act as a compute filter. But if your application and your data reside on the exact same physical device, an IoT sensor, a mobile app, or a single node script, do not incur network overhead. Use an embedded store like SQLite.
Perfect. Takeaway two, turn your architecture inside out. Stop trying to force a monolithic relational database to handle text search, caching, analytics, and transactions. Embrace polyglot persistence. Treat your data as an immutable stream of events, use a durable log like Kafka for writes, and use stream processing like Flink to continuously update tailored materialized views. You know, Redis for speed, Mongo for documents, ClickHouse for analytics.
And takeaway three, collapse your analytics. If you are building a new data platform today, do not build a complex two-tier data lake plus data warehouse pipeline. Implement a lake house. Standardize on parquet formats, and use Apache Iceberg to get asset transactions and time travel directly on cheap object storage. It eliminates ETL pipelines and gives your AI workloads direct high performance access to the truth.
Those principles really are the compass you need to navigate this landscape.
This has been an incredibly dense overview, and it perfectly sets the stage for the rest of the series. In our next deep dive, episode E01, we are going to double down on the Great Divide. OLTP versus OLAP. We will look specifically at the internal storage engines, B trees, and buffer pools that make Postgres tick and contrast them with ClickHouse.
To close out today, though, I want to leave you with a fascinating paradox that emerges from the systems we just discussed. Martin Kletman built a brilliant philosophy around the immutable event log. Accountants don't use erasers. Kafka and Iceberg are fundamentally designed around the idea that data is never overwritten. You simply append new facts to an immutable history.
Which provides perfect auditing, time travel, and rollback capabilities.
Exactly. However, in the official ClickHouse documentation regarding their merge tree engine, they explicitly note that they had to engineer expensive, heavyweight mutation operations specifically to comply with modern legal frameworks like GDPR, the European law that includes the right to be forgotten.
So if a user legally demands you delete their account, you cannot just append a new event to Kafka saying user deleted and have your application ignore the old data.
Exactly. The law requires the actual physical destruction of the underlying bits on the disk. The old data cannot exist anywhere in your historical snapshots or logs. So here is the thought for you to mull over. As our architectural best practices move entirely toward append-only immutable ledgers like Kafka and Iceberg, how will software engineering reconcile with real-world legal systems that mandate data destruction? Can a mathematically pure immutable system actually exist in a GDPR world?
That is a profound tension between computer science and the law, and a reality every architect has to face. Thank you for joining us on this exploration. Keep diving deep.