Start here: Modern Data Stores
Podcast · Start here: Modern Data Stores

The Physics of Modern Data Stores

by ZumiLabs · 56m
0:00 56m

Transcript

Made automatically from the audio, so it may contain mistakes. Click a line to play from there.

Host 1

Picture this. It is three o'clock on a Friday afternoon.

Host 2

Oh, I know where this is going.

Host 1

Right. Your engineering team just deployed a massive new feature. Morale is super high. Everyone is basically getting ready to log off for the weekend.

Host 2

The dangerous hours.

Host 1

Exactly. And behind the scenes, your architecture looks, you know, beautifully simple. You've got a single massive production Postgres primary database handling everything.

Host 2

And by everything you mean literally everything.

Host 1

Literally. I mean the team is running analytical group by revenue reports for the finance department. They're running full text search for the user-facing app. Oh boy. And background job cues, right? Pulling thousands of tasks a minute, all hitting that exact same primary instance.

Host 2

It's a classic scenario. I mean, everything feels incredibly robust at first. The system's humming along, the CPU utilization looks totally fine on the datadog dashboards, the business is processing orders until, you know, mathematically and physically the inevitable happens.

Host 1

The breaking point.

Host 2

Right. A massive buffer cache eviction.

Host 1

Yeah, that's the killer.

Host 2

Those heavy analytical queries from finance, they require scanning millions of rows. And to do that, Postgres has to pull that data from the physical disk into memory.

Host 1

But memory is finite.

Host 2

Exactly. So it starts evicting the hottest, most critical transactional data, like the active user sessions, the current shopping carts. It pushes them right out of RAM to make room for the revenue report.

Host 1

Which means suddenly a simple user checkout transaction that usually takes a snappy like eight milliseconds now requires a trip to the physical disk.

Host 2

And because of those background job cues you mentioned, the ones constantly updating task statuses, you get massive lock contention.

Host 1

The whole system just freezes.

Host 2

Yeah. You watch your checkout P99 latency violently spike from eight milliseconds to a catastrophic four seconds.

Host 1

Which feels like an eternity.

Host 2

The application times out, users drop off, revenue flatlines.

Host 1

And the Slack alerts start screaming in red.

Host 2

Yeah.

Host 1

It is the absolute nightmare scenario for any tech lead.

Host 2

It really is. You're staring at the monitoring tools, realizing you treated a highly tuned operational relational database like a like a bottomless magic bucket.

Host 1

You assumed that because it speaks SQL, it can handle any workload shape you throw at it.

Host 2

Right. And that Friday afternoon panic is exactly what we are here to cure today.

Host 1

Welcome to this foundational deep dive. This is the series overview of modern data stores. Our mission here is to map the entire modern data landscape for you. The whole thing. Yeah, because if you are a software engineer, an architect, or a tech lead, you are navigating an absolute minefield right now.

Host 2

Operational, analytical, noSQL, streaming, vector store families, it's overwhelming.

Host 1

It is. And over the series, we are going to unpack every single one of them. By the time we finish, you won't just know which tool to reach for. You will know the exact physical thresholds of when to leave a shiny new tool on the shelf.

Host 2

But we need to set some strict ground rules for this series first before we dive into the internal physics of these storage engines.

Host 1

Let's hear them.

Host 2

Throughout our discussions, we will absolutely name real products. We'll talk about Postgres QL, MongoDB, Click House, Apache Kafka, Snowflake.

Host 1

But to be clear, we use these purely as concrete mechanical examples of architectural patterns.

Host 2

Right. They are never endorsements.

Host 1

Right.

Host 2

Furthermore, we are going to ruthlessly push back on hollow vendor marketing claims.

Host 1

Oh, definitely.

Host 2

When a sales page tells you their database is schemaless or infinitely scalable, or a one-size-fits-all operational and analytical hybrid, we are going to break down the physical impossibilities of those claims.

Host 1

We're basically going to banish the phrase it depends from our vocabulary. We want to spell out the exact quantified workload thresholds that determine those dependencies.

Host 2

We also want to respect your time, which means establishing our prerequisites for this deep dive.

Host 1

Very important. We are operating under the assumption that you, the listener, already write production SQL. You understand the fundamental mechanics of primary and foreign keys.

Host 2

And you know network client server basics.

Host 1

Right. We will not waste a single second of this series defining what a database table is or explaining the syntax of a JSON object.

Host 2

We are bypassing the introductory tutorials completely. We're diving straight into the bare metal of storage engine physics.

Host 1

So let's start at the very foundation, right? How data actually comes to rest on a physical disk.

Host 2

The great divide in databases often comes down to internal mechanics. And the most common starting point is the operational OLTP row store.

Host 1

The classics.

Host 2

Exactly. When we talk about the physics of a row store, we are talking about an architecture fiercely optimized for point lookups and transactional integrity.

Host 1

Let's unpack why that is physically true.

Host 2

So in a row-oriented database, all the data associated with a single record, let's say a user profile containing an ID, an email, a hashed password, and a creation date. It's all stored contiguously on the disk media.

Host 1

They are physically placed right next to each other.

Host 2

Right. So when your application needs to authenticate a user, the SSD controller fetches that entire row in one single I/O. operation.

Host 1

It's incredibly efficient for single record retrieval.

Host 2

It is. But to handle this at a massive scale with thousands of concurrent users, systems like Postgres lean heavily on a concept called MVCC.

Host 1

Multiversion concurrency control. This is basically the secret sauce that prevents the lock contention nightmare we talked about in the cold open.

Host 2

Well, at least under normal operating conditions.

Host 1

Fair point.

Host 2

The core philosophy of MVCC is that readers should never block writers, and writers should never block readers.

Host 1

Because in a naive database system, if I am updating your user profile, I have to place a lock on that row, right?

Host 2

Exactly. And if someone else tries to read your profile at that exact microsecond, they have to wait for my update to finish. MVCC changes the physics entirely.

Host 1

How so?

Host 2

When you run an update statement in Postgres, it does not overwrite the data in place.

Host 1

Okay.

Host 2

Instead, it writes a completely new version of the row to the disk and marks the old version as logically deleted, but physically still present.

Host 1

So it's essentially appending a new state rather than destroying the old state.

Host 2

Yes. And it manages all of this using transaction IDs. Every transaction gets a sequentially increasing ID.

Host 1

Right.

Host 2

When a query searches for data, the engine checks these IDs to ensure the query only sees the versions of the rows that were committed before the query started.

Host 1

Which provides a beautifully consistent snapshot of the database at a specific point in time. It completely isolates concurrent operations from each other.

Host 2

But the trade-off, of course, is bloat.

Host 1

Because Postgres is keeping all those dead versions of rows around.

Host 2

Exactly. You eventually have to clean them up, which is why Postgres relies on the vacuum process to sweep through and reclaim that physical disk space.

Host 1

But that's a whole separate deep dive for another day. I want to pivot to another ROS store that occupies a completely different, almost paradoxical domain in the engineering world.

Host 2

You're talking about SQLite.

Host 1

I am. SQLite is a fascinating piece of engineering because it is ubiquitous. I mean, it's in every smartphone, every web browser, yet it fundamentally breaks the rules of traditional database architecture.

Host 2

It really does. It is an OLTP ROS store, but its stated design philosophy is that it does not compete with client server databases like Postgres.

Host 1

Right.

Host 2

It actually competes with the OpenFile command in the C programming language.

Host 1

Which is wild to think about. It's designed to replace custom ad hoc application files. It entirely bypasses the network layer.

Host 2

It writes directly to a single monolithic file on the local file system. And the theoretical limits of that single file are staggering.

Host 1

Like how staggering.

Host 2

Mathematically, SQLite database can scale up to 281 terabytes.

Host 1

That is absurd for a single file.

Host 2

It is. But the practical real-world thresholds are what engineers actually need to understand. The SQLite documentation itself is very transparent about this.

Host 1

Right. It thrives in environments like embedded IoT devices, mobile apps, or low to medium traffic websites dealing with, say, under a hundred thousand hits per day.

Host 2

But if the math allows for a 281 terabyte database, what physically breaks down when I try to put a high traffic web application on top of a, let's say, 50 gigabyte SQLite file.

Host 1

File system locking latency.

Host 2

Exactly. Because SQLite doesn't have a background server process managing connections. It relies entirely on the operating system's file system locks to handle concurrency.

Host 1

And SQLite allows theoretically unlimited concurrent readers, but it only allows one writer at any given microsecond.

Host 2

Yes. So if you have dozens of concurrent network requests trying to write data simultaneously, they all have to queue up and wait for the file system lock to release.

Host 1

And what if you try to get clever, like hosting that SQLite file on a network file system like NFS, so multiple application servers can access it.

Host 2

Oh, that is a recipe for catastrophic failure.

Host 1

Really? Why?

Host 2

The latency associated with remote file locking across a network makes performance plummet exponentially. Worse, the locking implementations on many network file systems are notoriously buggy. Oh. Yeah. If the lock state gets corrupted across the network, your SQLite database file gets corrupted.

Host 1

And the data is just gone.

Host 2

Gone. So the physical boundary is very clear. SQLite is a masterpiece for isolated local data storage. But it fundamentally disintegrates under high concurrency write workloads distributed across a network.

Host 1

To put row stores into perspective, I always like to use a physical analogy. Working with a row store is like retrieving a single highly detailed receipt from a massive shoebox of receipts.

Host 2

I like that.

Host 1

It is the perfect mechanism when you need to know exactly what happened in one highly specific transaction. You reach in, you pull out that one receipt, and you immediately know what you bought, the timestamp, the cashier's name, the last four digits of the credit card.

Host 2

It's precise.

Host 1

Exactly. But imagine you want to calculate the total tax you paid across all receipts for the entire year.

Host 2

A row store forces you to perform an incredible amount of wasted labor to do that.

Host 1

You have to pull out every single receipt in the box, read the entire piece of paper, visually scan down to the tax line, extract that one number, and add it to your running total.

Host 2

You are physically pulling and reading a massive amount of data you do not care about. The item descriptions, the timestamps, the stored locations, just to find one single number per receipt.

Host 1

And that wasted I.O. is exactly what kills Postgres in an analytical workload.

Host 2

Which leads us directly into the physics of analytical OLAP column stores and the concept of vectorized execution.

Host 1

Right. Because if you need to sum up a billion tax columns, you must abandon the row-oriented architecture entirely. You turn to systems like ClickHouse or DuckDB.

Host 2

Because they physically lay out the data on the disk in a completely different dimension. They take the concept of a row and they shred it.

Host 1

That's a great way to put it.

Host 2

When you insert a record into a column store, it doesn't write the whole record contiguously. It takes the tax value and stores it physically next to the tax values of a million other records.

Host 1

And it takes the timestamp and stores it next to all the other timestamps.

Host 2

Exactly. So if your query is select some tax from receipts, the disk controller only fetches the tax data. It completely ignores the rest of the columns, reducing disk IO by orders of magnitude.

Host 1

But the true physics magic of a modern column store isn't just on the disk. It happens inside the CPU cache, right?

Host 2

Yes. You're talking about columnar vectorized query execution.

Host 1

It sounds like intense marketing jargon, but it's actually one of the most important low level hardware optimizations in modern computing.

Host 2

Let's compare how a traditional row engine and a vectorized engine actually talk to a CPU.

Host 1

Okay, let's do it.

Host 2

In a traditional database like Postgres, the execution engine interprets a query row by row. It loads a row into the CPU, evaluates the where clause condition, processes the data, and then fetches the next row.

Host 1

For every single value, there is a massive amount of CPU instruction overhead, right? Function calls, type checking, branching logic.

Host 2

It's essentially treating every single piece of data as a unique artisanal object.

Host 1

Right.

Host 2

Vectorized execution changes the granularity completely. Instead of passing a single value to the CPU, the database engine passes a large, contiguous batch of values, a vector in a single operation.

Host 1

And because the data is strictly typed and stored contiguously, the CPU can leverage SIMD instructions. Single instruction, multiple data.

Host 2

Exactly. The CPU executes one single computational instruction that applies simultaneously to the entire array of data sitting in its L1 or L2 cache.

Host 1

By processing data in these tight, highly optimized loops, you severely reduce the CPU cycles expended per individual value.

Host 2

And systems like Click House push this optimization even further with their indexing strategies. Because they don't use traditional B trees that point to specific individual rows.

Host 1

Right. ClickHouse relies heavily on sparse indexes, which are often referred to as data skipping indexes.

Host 2

A traditional B tree index is incredibly precise. It tells the execution engine the exact row you are looking for lives at this specific memory address on this specific page.

Host 1

A sparse index takes a completely different philosophical approach. It doesn't tell the engine where the data is, it tells the engine where the data is not.

Host 2

It's like navigating a massive library. Instead of a catalog telling you exactly which shelf a book is on, the catalog just tells you, I guarantee the book you want is definitely not on floors two, three, or four.

Host 1

So you completely skip reading those floors. Exactly.

Host 2

Clickhouse groups data into blocks, often containing thousands of rows. The sparse index simply records the minimum and maximum values for that block.

Host 1

So if your query is looking for a transaction from October and the index sees that a particular block only contains data from January through March, the engine skips reading that entire massive chunk of physical disk.

Host 2

But this mechanical design introduces severe trade-offs.

Host 1

It always does.

Host 2

Because ClickHouse physically sorts massive blocks of data on disk by primary key and relies on these macro level block indexes. It is catastrophically bad. It's single row point updates.

Host 1

Right. You cannot just write an update statement targeting row number four million and expect it to execute in a millisecond.

Host 2

No way. To update a single value in the middle of a compressed column or block, the engine often has to rewrite the entire block.

Host 1

Wow.

Host 2

In fact, ClickHouse lacks full-fledged OLTP transactions entirely. It is designed for appending massive volumes of immutable analytical data, telemetry, logs, clickstreams.

Host 1

You never use a column store to manage stateful transactional updates like changing a user's password or updating an account balance.

Host 2

Exactly. So we have a clear dichotomy. We have row stores for transactional precision, and we have column stores for analytical aggregations.

Host 1

But if you walk into any modern enterprise, the reality is far messier. We don't just have neat rows and columns. Not at all. We have mountains of unstructured data. We have petabytes of log files, raw JSON dumps, CSVs sitting in cloud storage. The historical approach was the data lake. Just dumping everything into AWS S3 and hoping you could make sense of it later.

Host 2

But data lakes turn into data swamps pretty quickly.

Host 1

Right. How do we bridge the gap between this cheap, infinitely scalable, messy storage, and the rigid, high performance world of data warehouses?

Host 2

That tension gave birth to the lake house architecture.

Host 1

Okay, let's talk about lake houses.

Host 2

When we talk about lake houses, we are talking about open source projects like Apache Iceberg, Delta Lake, or Apache Hootie.

Host 1

And the core innovation of a lake house is that you keep your data sitting on dumb, highly durable, incredibly cheap object storage like S3 or Azure Blob.

Host 2

Exactly. You keep the data in open, non-proprietary formats like Apache Parquet. But then you wrap that dumb storage in an intelligent transactional metadata layer.

Host 1

To really grasp how a lake house achieves warehouse level performance on object storage, we have to look closely at the physics of Apache Parquet itself. Because parquet isn't just a flat text file.

Host 2

It is a wildly complex, internally structured data format.

Host 1

How does it work under the hood?

Host 2

A parquet file has a highly specific internal hierarchy designed for distributed computing. The data inside the file is first divided horizontally into massive chunks called row groups.

Host 1

Okay, row groups.

Host 2

Within each row group, the data is then divided vertically into column chunks. All the tax data for that row group is in one column chunk, all the timestamps in another.

Host 1

And finally, those column chunks are divided into pages.

Host 2

Yes. Conceptually, the page is the indivisible unit for compression and encoding.

Host 1

So it's the atomic unit of the format.

Host 2

Right. When a distributed computing engine reads a parquet file, it parallelizes the workload across this hierarchy. A framework might distribute different row groups to different worker nodes across a cluster.

Host 1

The I.O. subsystem reads the specific column chunks it needs.

Host 2

But the actual decompression of the data, unpacking dictionary encodings or run-length encodings happens at the page level inside the CPU.

Host 1

So we have these highly optimized parquet files sitting in an S3 bucket. But an S3 bucket doesn't understand SQL. It doesn't understand transactions. Where does a project like Apache Iceberg come in?

Host 2

Iceberg acts as the transactional metadata layer. It maintains a meticulously detailed, append-only log of exactly which parquet files, down to their specific S3 URIs, belong to a specific version of a table at a specific point in time.

Host 1

So when you run an update or delete statement. Because the metadata layer tracks this atomically, you suddenly get acid transactions over flat files.

Host 2

It's incredible. You can even do time travel.

Host 1

Oh, time travel's the best.

Host 2

If a data engineer accidentally drops critical records on Thursday, you can run a query against the database as it looked last Tuesday. Because last Tuesday's metadata simply points to an older preserved set of parquet files. It also unlocks zero copy cloning.

Host 1

Right. You can spin up an entirely new isolated testing environment for your data science team by simply copying the metadata pointers. You don't have to duplicate or move a single byte of the underlying petabytes of physical parquet data. I have to put on my skeptical engineer hat here, though.

Host 2

Go for it.

Host 1

I hear the architectural elegance of this, but physically, if we are just slapping a transaction log on top of flat files living in object storage, aren't we just reinventing the relational database but making it infinitely slower?

Host 2

That's a fair question.

Host 1

I mean, every time we want to read data, we have to endure network latency over HTTP to access an S3 bucket. You can't beat the speed of a local NVMe drive.

Host 2

That is the fundamental physics tension of the lakehouse, and you are absolutely right to call it out. You are trading the pure speed of local disk access for infinite scalability and radically cheaper storage costs.

Host 1

Right.

Host 2

I can see the latency reality. You are never ever going to get a sub-millisecond point lookup for a user profile out of a lake house on S3. That is simply not its job. But however, lake houses compete directly with proprietary cloud data warehouses on massive, complex analytical benchmarks like TPC-DS. They achieve this through aggressive layer-by-passing optimizations.

Host 1

How do you optimize away the speed of light over a network?

Host 2

You avoid the network entirely whenever possible. First, Lake House compute engines aggressively cache hot data. The nodes executing the queries have local NVMe drives, and they pull down frequently accessed parquet files so they don't have to hit S3 repeatedly.

Host 1

Okay, that makes sense.

Host 2

Second, they use background data layout optimization. They constantly rewrite small files into larger files and cluster co-access data together, ensuring that a single HTTP get request to S3 pulls down a massive, perfectly organized payload of data.

Host 1

And they leverage the metadata to skip data before even talking to the network, right?

Host 2

Precisely. They use zone maps, which are essentially the exact same sparse data skipping indexes we talked about with ClickHouse.

Host 1

Right.

Host 2

Iceberg's metadata tracks the minimum and maximum values of every single parquet file. When you query for a specific date range, the query planner looks at the metadata, realizes that 90% of the parquet files don't contain data in that range, and simply never sends an HTTP request to S3 for those files.

Host 1

So the IO is minimized before the network is even engaged. Exactly. It's an incredible architectural compromise. So whether we are talking about the precision of Postgres rows, the brute force of clickhouse columns, or the scalable complexity of iceberg parquet files, we have essentially been discussing data at rest.

Host 2

The data has arrived, it has been written to physical storage, and we are querying it after the fact. And it was incredibly fault tolerant.

Host 1

Because it assumed hardware would fail. If a machine died halfway through a computation, the central coordinator didn't panic. It just restarted that specific task on a different node.

Host 2

Right. It achieved this safety by aggressively writing intermediate results to the local disk. It traded speed for guaranteed completion.

Host 1

But the fundamental limitation of a batch architecture is the batch window. You always have to wait for the batch to finish.

Host 2

Which is the problem now.

Host 1

Right. In a world defined by continuous events, credit card authorization swipes, IoT telemetry sensors on factory floors, millions of real-time website clicks, waiting for a nightly batch job to run at 2 a.m. is a massive competitive disadvantage.

Host 2

The business needs to react to data the millisecond it is generated, which is why we've seen a massive architectural shift toward unbounded event time streaming.

Host 1

And to truly grasp how this works, we need to look at Martin Klepman's philosophy of turning the database inside out.

Host 2

The concept completely changed how we think about this.

Host 1

It completely blew my mind the first time I encountered it. Because if you think about traditional databases like the Postgres database from our cold open, they operate by mutating global state in place. Yes. If I deposit $50, the database locates my row, overwrites the old balance of $100 with the new balance of $150. The old state is destroyed.

Host 2

And that in-place mutation is exactly what causes race conditions, the need for complex locking mechanisms, and the performance bottlenecks.

Host 1

Clettman argues that mutating state is fundamentally the wrong approach for high-scale systems. Instead of overriding data, we should rely on an immutable, distributed, append-only commit log.

Host 2

Distributed streaming platforms like Apache Kafka are the pure embodiment of this philosophy. Right. An event, say user making a payment, is written to the very end of the log. It is an immutable fact. It happened. You cannot change it, you cannot update it, you can only append new facts after it.

Host 1

But here's the obvious problem with that. How do you query a log? A log is just a massive sequential fire hose of events. If a user loads their profile page, you can't run a SQL join on a raw stream of a billion payment events fast enough to render a web page in a hundred milliseconds.

Host 2

You never query the log directly for user facing synchronous reads.

Host 1

Okay, so what do you do?

Host 2

Instead, you introduce stream processing frameworks. Systems like Apache Samsung or Apache Flink. These frameworks constantly consume the Kafka log in real time, process the logic, and use the event data to continuously pre-compute materialized views.

Host 1

So the database your application queries is simply a materialized view of the log.

Host 2

Exactly.

Host 1

Let's dig into that because a materialized view built from a stream is fundamentally architecturally different from a traditional read-through application cache, like putting memcatch or Redis in front of a slow database.

Host 2

A read-through cache is a nightmare of complexity in edge cases. In a traditional architecture, your application logic carries the burden of handling cache misses.

Host 1

Right. The app asks the cache, do you have user 42's profile? If the cache says no, the app has to pause, query the heavy relational database, get the beta, return it to the user, and then remember to write that data back into the cache for the next time.

Host 2

And this pattern is deeply prone to invalidation errors. If the database updates, but the cache doesn't get the memo, your user sees stale data. The famous quote There are only two hard things in computer science cache invalidation and naming things.

Host 1

Exactly.

Host 2

But with a stream process materialized view, you completely eliminate the cache invalidation problem.

Host 1

Because there are no cache misses.

Host 2

Right. The view is comprehensively and deterministically built from the immutable log.

Host 1

If the data isn't in the materialized view, it doesn't exist in the system. The application simply queries the view and trusts it unconditionally.

Host 2

Stream processing solves a lot, but it introduces its own terrifying demons. Specifically around state and failure recovery.

Host 1

Oh right. Let's say my Apache Flink job is consuming a million events a second, maintaining complex rolling averages of user behavior. Suddenly, a server node dies. The process crashes.

Host 2

A very real scenario.

Host 1

How does Flink recover its state without having to rewind the Kafka log and reprocess everything from the beginning of time? Because that would completely stall data ingestion.

Host 2

That specific challenge required a massive leap forward in distributed systems design, leading to mechanics like asynchronous barrier snapshotting or ABS.

Host 1

Which Flink pioneered based on the Chandy Lamport algorithm, right?

Host 2

Yes. Older streaming systems used to handle state by periodically stopping the world. They would pause ingestion, let all the data flow through the pipeline, take a global snapshot of everything in memory, save it to disk, and then resume.

Host 1

It was brutal for throughput. It's like stopping traffic on the highway every five minutes just to count the cars.

Host 2

That's exactly what it felt like.

Host 1

So how does ABS fix it without stopping traffic?

Host 2

ABS injects special lightweight barrier markers directly into the data stream itself.

Host 1

Okay.

Host 2

Imagine pouring a drop of bright red dye into a flowing water pipe. The barrier flows through the distributed topology alongside the regular data records.

Host 1

I see.

Host 2

When an operator in the Flink pipeline receives one of these barriers, it knows with absolute mathematical certainty that all records before this barrier belong to the current snapshot, and all records after belong to the next.

Host 1

So the operator asynchronously writes its small local state to durable storage, like S3, and forwards the barrier down the line.

Host 2

Exactly. So it persists only the internal state of the mathematical operations on acyclic topologies rather than trying to eagerly persist millions of massive in-transit data records.

Host 1

It takes a globally consistent snapshot of the entire distributed pipeline's memory at a specific logical point in time without ever stopping the flow of water through the pipes.

Host 2

Which is brilliant. This mechanism allows modern streaming engines to maintain linear scalability, process millions of events, and still guarantee exactly one's processing semantics, even in the face of hardware failure.

Host 1

I have to offer a huge pushback here on behalf of the engineering teams who actually have to build this stuff.

Host 2

I'm ready.

Host 1

By adopting this database inside out architecture where Kafka is our immutable log, Flink is our processor, and some other specialized data store is holding the materialized view. Aren't we just forcing application developers to become database engine builders?

Host 2

It certainly feels that way sometimes.

Host 1

We are manually wiring up transaction logs, commit protocols, and view updates in our application layer. We're rebuilding Postgres from scratch, but distributed across ten different services.

Host 2

The industry often romanticizes streaming architectures without acknowledging the gravity of what you just described.

Host 1

Right.

Host 2

Unbundling the database heavily increases your architectural complexity. You're taking on the immense operational burden of wiring these disparate systems together, managing complex schema evolution across the entire pipeline, and handling complex deployment orchestration. It is not for the faint of heart.

Host 1

You essentially take on the polyglot tax before you even write a single line of business logic.

Host 2

You do. The trade-off is that this architecture achieves a level of decoupling, fault tolerance, and pure throughput scale that a monolithic database simply cannot reach physically. But the golden rule applies. You only pay this complexity tax when your scale mathematically demands it.

Host 1

Let's assume our scale does demand it. We've unbundled the database. We have Flink processing our streams. Now we have to decide where to actually store those materialized views so the application can query them.

Host 2

And that brings us to the wild, highly specialized world of NoSQL data stores.

Host 1

Yes.

Host 2

The NoSQL landscape is vast. So let's categorize them by their physical access shapes. The simplest end of the spectrum is the key value and in-memory stores like Redis or Memcached.

Host 1

The physics here are delightfully straightforward. You have an opaque value, it could be a string, a blob of JSON, a serialized object, accessed by one single unique key.

Host 2

Because it's just a key, partitioning the data across a cluster is mathematically trivial. You just hash the key and map it to a server. It gives you atomic operations and linear scalability.

Host 1

But the downside is absolutely zero query expressiveness. You cannot ask Redis, give me all the users who live in New York and logged in yesterday.

Host 2

Right. The database doesn't know what's inside the value. You can only ask for a specific user ID. If you want query expressiveness without the rigidity of a relational schema, you move up in complexity to document stores. Most notably MongoDB.

Host 1

Okay, here we go. The marketing teams absolutely love to sell document stores with the word schemaless. It's the pitch that seduces every startup trying to move fast.

Host 2

And it is the most dangerous pitch in the industry.

Host 1

Yes, it is.

Host 2

And we need to push back on that term with everything we have. There is no such thing as schemaless data.

Host 1

Right.

Host 2

The application reading the data inherently expects a certain structure. If it expects a Boolean and gets an array, it throws an exception and crashes. MongoDB is not schemaless, it is schema on read.

Host 1

Meaning the database engine doesn't enforce the shape of the data when you write it to the physical disk. The application code is responsible for enforcing the schema when it pulls the data out.

Host 2

Exactly. MongoDB optimizes for flexible polymorphic data structures. But the physical rule of document modeling is incredibly strict, and if you violate it, your performance collapses.

Host 1

And what is that rule?

Host 2

Data accessed together must be stored together.

Host 1

Let's walk through a concrete scenario. If I am building a blogging platform and I have a blog post with comments, how do I physically model that in a document store?

Host 2

You face a critical architectural choice between embedding and referencing.

Host 1

Okay.

Host 2

If the comments are always displayed immediately when the blog post is loaded, and you know there will only ever be a dozen comments, you embed them. You store the comments as a nested array inside the single main blog post JSON document.

Host 1

This makes the disk read phenomenally efficient, right? The SSD does one single seek, pulls the document, and you have all the data you need to render the page.

Host 2

Exactly. But what if it's not a small blog? What if it's Taylor Swift's Instagram posts and it gets three million comments in an hour?

Host 1

Then an embedding becomes a physical disaster.

Host 2

A total disaster. In MongoDB, data is stored as Bison, binary JSON, and the document has a maximum size limit, typically 16 megabytes.

Host 1

Right.

Host 2

If you keep appending comments, the document outgrows its allocated space on the disk. The database literally has to pick up the entire massive document, find a new larger empty space on the disk, and rewrite the whole thing.

Host 1

Every time someone comments, you rewrite megabytes of data. The I.O. penalty is catastrophic.

Host 2

So to avoid that, you have to use referencing. You store the comments in an entirely separate collection, and you put the blog post's ID on the comment.

Host 1

Which looks exactly like traditional relational foreign keys.

Host 2

Yes, it does. You sacrifice the speed of a single read operation to avoid the massive overwrite penalties.

Host 1

The irony is that by trying to avoid relational databases, you end up manually rebuilding relational joints in your application layer.

Host 2

Happens all the time.

Host 1

Schema on read doesn't mean you don't design your schema. It means your specific access patterns dictate your physical schema entirely.

Host 2

Absolutely. Let's move to a system built for a completely different scale. Wide column stores. Systems like Apache Cassandra, Google Big Table, Amazon Dynamo DB.

Host 1

This lineage traces back to the original Google Big Table paper. Architecturally, these are sparse multi-dimensional sorted maps.

Host 2

Okay, but to understand why they are so incredibly fast at writing data, we have to look at their underlying physics, which rely on log structured merge trees or LSM trees. We talked about traditional B trees in Postgres earlier. Let's look at how an LSM tree fundamentally changes the physics of disk I.O.

Host 1

A B tree is designed to update data in place. When you modify a row, the disk head has to seek to that specific location and overwrite it.

Host 2

Right, which results in random I/O. And even on modern SSDs, random I.O. is slower than sequential writing.

Host 1

So how does an LSM tree fix that?

Host 2

An LSM tree completely avoids random I/O. When a write comes into Cassandra, it doesn't go to the disk immediately. It buffers the incoming write in memory in a highly optimized structure called a mem table.

Host 1

Because it's operating purely in RAM, it can sort the incoming data by key instantaneously.

Host 2

Yes. But RAM is volatile. If the server loses power, you lose everything in the mem table.

Host 1

Which is why it simultaneously appends the raw operation to a sequential commit log on disk for durability.

Host 2

Exactly. But the magic happens when the mem table fills up. Cassandra flushes that entire sorted block of data sequentially to the disk is a brand new immutable file called an SS table or sorted string table.

Host 1

It never updates an existing file.

Host 2

It just keeps writing new sorted files next to each other. Because it only ever performs sequential appends, wide column stores achieve unbelievably high write throughput. They are the perfect architecture for time series style, tall data, like massive IoT telemetry, server metrics, or financial tick data.

Host 1

Where you are constantly blasting millions of new data points into the system every second.

Host 2

Right. But the cost, however, has to be paid on the read side.

Host 1

If the data is scattered across dozens of these immutable SS table files, finding the most recent version of a specific row sounds like a nightmare.

Host 2

It can be. The database might have to look in the mem table and then check multiple files on disk, merging the results on the fly just to answer a simple query.

Host 1

The read penalty is very real. To mitigate it, LSM-based systems rely on background compaction processes to merge the files over time. And they use Bloom filters.

Host 2

Bloom filters. A bloom filter is a probabilistic data structure that can mathematically tell the database I am absolutely certain this key does not exist in this specific SS table.

Host 1

So it allows the engine to completely skip reading files that don't contain the requested data.

Host 2

Yes. Let's briefly touch on another specialized shape before we move on. Index-free graph databases like Neo4J.

Host 1

Graph databases are entirely optimized for deep relationship traversal. In a traditional relational database, if you want to find friends of friends of friends, you have to compute incredibly expensive join operations at runtime. Right. An index-free adjacency graph changes the physical layout. It stores pointers from a node directly to its connected relationships on disk.

Host 2

So traversing the graph is essentially just chasing memory pointers from one object to the next rather than scanning massive indexes.

Host 1

It makes complex multi-hop recommendation queries incredibly fast, but at the cost of being highly inefficient for simple, wide aggregate queries.

Host 2

Exactly.

Host 1

Okay, we've covered the classics. Now we have to talk about the most explosive topic in the data landscape right now: vector search.

Host 2

Oh yes.

Host 1

With the rise of generative AI, everyone is bolting vector capabilities onto their databases like the PG vector extension for Postgres. But what are the actual physics of storing and searching vectors?

Host 2

To demystify it, a vector is simply an array of floating point numbers. It might have hundreds or thousands of dimensions.

Host 1

Right.

Host 2

These numbers represent the deep semantic meaning of text, images, or audio generated by a machine learning embedding model. Two pieces of text with similar meanings will be mathematically closer together in this multidimensional space.

Host 1

And systems like PG vector can natively store these massive arrays up to 2,000 dimensions.

Host 2

Storing an array of numbers isn't revolutionary, though. The monumental engineering challenge is querying them.

Host 1

Right. How do you find the nearest neighbor to a user's query vector when you have a billion rows of vectors in a table?

Host 2

You have to choose your architectural compromise.

Host 1

Well, let's start with exact search.

Host 2

Exact search guarantees you will find the absolute closest vectors by calculating the mathematical distance like cosine distance or Euclidean L2 distance against every single row in the database. So the industry standard is approximate nearest neighbor or ANM algorithms. You trade perfect accuracy for blazing speed.

Host 1

The two heavyweight algorithms in PG vector are HNSW and IVF flat. Let's break down the mechanics starting with H and SW.

Host 2

HNSW stands for hierarchical navigable small world. It physically builds a multi-layer graph of your vectors in memory.

Host 1

Okay, how does that work?

Host 2

Imagine you are navigating a massive highway system. The top layer of the graph is like looking at a map from space. You find the general region.

Host 1

Right.

Host 2

The next layer down gets you to the state highways. And the bottom layer connects you to the specific local roads. It provides a phenomenal trade-off between search speed and recall accuracy.

Host 1

But the operational catch is massive.

Host 2

Oh, it is. Building that hierarchical graph is incredibly slow and it devours memory. The PG vector documentation issues severe warnings about this.

Host 1

If the HNSW graph outgrows your configured maintenance work mem setting during the index creation, the database falls back to disk, and the build time will skyrocket from minutes to hours or even fail entirely.

Host 2

So if you can't afford the memory overhead of HNSW, what is the alternative?

Host 1

IVF flat, which stands for inverted file with flat compression. It uses K-means clustering to divide the multidimensional vector space into distinct zones or lists.

Host 2

When you execute a query, it doesn't search the whole space. It figures out which zone your query vector falls into, and only searches a small subset of the nearby zones. These are called probes.

Host 1

It's much faster to build, and it uses significantly less memory than the complex graph structure of HNSW.

Host 2

But the trade-off is recall. The accuracy of the results is noticeably lower. You might miss highly relevant vectors that happen to sit just across the boundary line of a zone you didn't probe.

Host 1

Here's where vector search gets truly complicated in a production database. Metadata filtering.

Host 2

This is the trap.

Host 1

Let's say I want to find the nearest vectors to the phrase machine learning tutorials, but I only want documents where the author ID is 42.

Host 2

This is the classic trap that catches almost every engineering team implementing vector search for the first time. Approximate indexes like H and SW are highly optimized to quickly find nearest neighbors, purely based on geometric distance.

Host 1

Right.

Host 2

If you add a strict relational metadata filter to the query, PG vector applies that filter after the approximate index is scaled.

Host 1

This is critical to understand.

Host 2

It's post-filtering. Let's run the scenario. The vector index does its job perfectly and retrieves the 100 geometrically closest neighbors to your query.

Host 1

Okay.

Host 2

But then the database applies the where author ID equals 42 filter. If author 42 didn't write any of those specific 100 documents, the database filters them all out and you get zero result back.

Host 1

Even if author 42 has a brilliant machine learning tutorial sitting at position 105 in the vector space.

Host 2

The database just stopped looking too early.

Host 1

So what's the solution?

Host 2

The mechanical solution to this in PG vector is iterative index scans. You configure the execution engine to keep scanning further out into the H and SW graph, relaxing the strict distance boundaries, until it finds enough vectors that also pass the relational metadata filter.

Host 1

You can do this with strict ordering, which guarantees perfect distance sorting but requires heavy computation, or relaxed ordering, which is much faster but might return results slightly out of order by semantic distance.

Host 2

We have covered incredible ground here. We've mapped the internal mechanics of rows for transactions, columns for analytics, lake houses for unstructured scale, streams for real-time views, documents for polymorphism, wide columns for massive rights, graphs for traversal, and vectors for semantic search.

Host 1

From a pure engineering perspective, having five or six different specialized databases running in your architecture sounds like an optimized utopia.

Host 2

It looks beautiful on an architecture diagram. Right up until the moment a backhoe cuts a fiber optic network cable outside your AWS data center.

Host 1

And that brings us to the harsh realities of distributed systems. Trade-offs, failure modes, and the polyglot tax. Let's start with the foundational law of distributed databases. The Packulk theorem, heavily popularized by the original Amazon Dynamo architecture paper.

Host 2

Most engineers are familiar with the CAP theorem, the idea that you cannot simultaneously guarantee consistency, availability, and partition tolerance.

Host 1

Right.

Host 2

But Daniel Avadi's PACOLC theorem provides a much more nuanced, realistic physical model. It states that in the case of a network partition, P, the system must choose between availability, A, and consistency, C.

Host 1

Okay.

Host 2

Else, E, meaning during normal operations when the network is perfectly fine, the system must still choose between latency, L and consistency, C.

Host 1

The Dynamo paper is famous because Amazon explicitly chose availability and latency, willingly sacrificing strict consistency.

Host 2

The business logic dictated the architecture. If the Amazon shopping cart service goes down, or if a user tries to add an item to their cart and the database blocks the right because a node is down, Amazon loses millions of dollars a minute.

Host 1

They designed Dynamo to be an always writable data store. It must accept the user's right no matter how broken the underlying infrastructure is at that exact moment.

Host 2

To understand how a database can physically guarantee it is always writable, we need to look at the mechanical innovations Dynamo introduced. Consistent hashing, sloppy quorums, hinted handoff, vector clocks, and Merkle trees.

Host 1

Let's run through them. How does Dynamo handle partitioning data across servers?

Host 2

First, consistent hashing. In an older system, you might map data to servers using a simple mathematical modulo operation. If you have 10 servers, key number 15 goes to server five.

Host 1

But if you add a new server, the math changes and you have to reshovel almost all the data across the entire network.

Host 2

Right. Consistent hashing maps both the data keys and the server nodes to a conceptual circular ring. A piece of data is stored on the first server node it encounters moving clockwise around the ring.

Host 1

This allows for massive incremental scaling. Adding or removing a node only affects the data on its immediate neighbors on the ring, minimizing network chatter. Okay, so that handles the distribution. What about availability? What happens when a server dies?

Host 2

That brings in sloppy quorums and hinted handoff. In a strictly consistent database, if the configuration requires rights to be replicated to three specific nodes and one of those nodes is down, the write fails. It returns an error to the user.

Host 1

Dynamo uses a sloppy quorum. Right. If node A is down, Dynamo will look at the ring and temporarily write the data to node D instead.

Host 2

So it accepts the right and keeps the shopping cart alive even if it has to store the data in the wrong place.

Host 1

Yes. And it wraps that data in a hint that says, I am node D, but this data actually belongs to node A. When the network partition heals and node A comes back online, node D automatically hands the data over to its rightful owner.

Host 2

This is where the consistency nightmare begins, though. What if a network partition splits the data center in half? A user tries to update their cart from their phone, which hits one side of the partition, and updates it from their laptop, which hits the other. Both sides accept the right because of the sloppy quorum.

Host 1

When the partition heals, the database is suddenly faced with two divergent conflicting versions of the exact same data.

Host 2

Right. Dynamo tracks these conflicts using vector clocks. A vector clock is essentially a list of nodes and countervalues attached to every single piece of data.

Host 1

By comparing the clocks, the database can definitively tell you if one version of a record is a direct sequential ancestor of another, or if they were edited concurrently and are truly in conflict.

Host 2

It's exactly like collaborating on a shared code repository offline. You and I both clone the repo. I edit a file, you edit the same file. When we reconnect and try to merge, we have diverging versions. The system has to decide what to do.

Host 1

Then Dynamo gives the application developers two distinct choices. Either the database can blindly apply a last write wins policy based on a physical timestamp, which runs a massive risk of silently deleting user data, or it pushes the semantic reconciliation back up to the application logic.

Host 2

The database returns both conflicting shopping carts to the application and forces the application code to merge them together.

Host 1

And finally, how do these nodes constantly ensure they aren't drifting apart over time?

Host 2

They use Merkle trees for background anti-entropy synchronization. Merkle tree is a cryptographic data structure. The nodes constantly exchange the root hashes of their Merkle trees.

Host 1

If the hashes match perfectly, the nodes know their data sets are completely in sync.

Host 2

If the hashes differ, they traverse down the branches of the tree comparing smaller and smaller hashes until they quickly pinpoint the exact specific keys that are out of sync. They can repair the divergence without sending the entire massive dataset over the network.

Host 1

It is brilliant foundational engineering. But these distributed systems are incredibly complex, and vendors routinely overpromise their safety guarantees. Which brings us to the ultimate reality check, Jepsen testing.

Host 2

Jepsen. Created by Kyle Kingsbury. It's a rigorous open source framework designed to evaluate the actual safety properties of distributed databases under extreme duress.

Host 1

When a vendor claims their new distributed database is strongly consistent, Jepsen puts it to the test by aggressively injecting network partitions, physically cutting connections, dropping packets, pausing CPU processes, and skewing server clocks.

Host 2

And Jepsen frequently exposes that under pressure, these databases allow deeply prescribed phenomena like G1A, the aborted read.

Host 1

An aborted read is a terrifying failure of transactional isolation. It occurs when a transaction observes a write performed by a completely different transaction that eventually aborted and rolled back.

Host 2

Let's contextualize that for a financial application. Transaction A updates a user's account balance from zero to a million dollars, but then encounters an error and rolls back.

Host 1

But in that split second before it rolls back, transaction B reads that million dollar balance, assumes it's real, and triggers a massive wire transfer out of the bank.

Host 2

If your database allows an aborted read, it is violating basic isolation guarantees. The business logic executes on phantom data that mathematically never existed. Jepson realities prove that you must deeply understand exactly what anomalies your distributed database allows when the network starts lying to it.

Host 1

Which perfectly leads us to the ultimate architectural debate in modern engineering Martin Fowler's concept of polyglot persistence.

Host 2

Fowler argued that a modern, complex enterprise application handles fundamentally different shapes of data, and we should wrap specific storage technologies behind component boundaries.

Host 1

Right. Use a highly tuned relational database for financial transactions, a document store for the polymorphic product catalogs, and an index-free graph for the social recommendation engine.

Host 2

I am going to play devil's advocate here.

Host 1

Oh, please do.

Host 2

If I use Postgres for billing, Mongo for user profiles, Kafka for telemetry streams, and Neo4J for my recommendation engine.

Host 1

From a pure code execution and query latency standpoint, you might be right. But Fowler himself acknowledges, and modern site reliability engineers will scream from the rooftops that there is a massive crushing polyglot persistence tax.

Host 2

The operational tax.

Host 1

Exactly. Every single additional data store technology you adopt multiplies your operational burden exponentially. You have to write, maintain, and test completely different backup and restore protocols. You have to conduct separate disaster recovery drills.

Host 2

You require brittle change data capture or CDC pipelines to keep the databases synchronized, trying to seamlessly stream transaction changes from Postgres into the Neo4J graph without losing a single event.

Host 1

And what happens when a CDC pipeline breaks at 3 a.m. Your graph database is suddenly four hours out of sync with your billing database, the pager goes off.

Host 2

But the real nightmare is the partial failure modes across your entire application footprint.

Host 1

What does your application do if the MongoDB user profile service is healthy, but the Postgres billing database is unreachable? Handling distributed transactions or writing compensating sagas to manage state across heterogeneous databases is quite literally one of the hardest problems in computer science.

Host 2

The polyglot tax means you should never ever adopt a new database just because it is mathematically 10% faster for one specific query. The operational pain will completely dwarf the performance game.

Host 1

So if we are facing the polyglot tax on one side and the physical limits of a single database on the other, how do we make the call? If I am an engineering lead staring at a massive new feature requirement, how do I classify the storage problem?

Host 2

You need to ruthlessly answer three strict quantified workload questions. Question one, what is the exact physical access shape of the data? Are you doing single key point lookups for user sessions? That points to key value.

Host 1

Are you doing time window aggregations over rolling metrics? Time series. Wide, sparse aggregations across billions of rows, column family, or deep relationship to versals, graph. You must define the geometry of the access pattern first.

Host 2

Question two.

Host 1

Or is the goal massive batch throughput, where seconds or even minutes of latency are perfectly acceptable for asynchronous analytical workloads? That pushes you toward column stores and lakehouses.

Host 2

And question three, which we just hammered on, but it is the most important check of all. What is your actual team operations capacity?

Host 1

Can your operations team realistically manage the polyglot persistence tax? Do you have the engineering headcount to have a dedicated on-call expert for Kafka and another for MongoDB and another for Cassandra?

Host 2

If the answer is no, do not adopt a new paradigm. Shoehorn the data into Postgres. Denormalize your tables, add more RAM, rely on aggressive indexing, and push the relational model until the vertical scaling cost absolutely forces your hand.

Host 1

And that is exactly what we are gonna dive into next. Now that we've mapped this massive landscape, our next deep dive will zoom in entirely on the granddaddy of them all: OLTP versus OLAP.

Host 2

We are going microscopically deep into row versus column internals and definitively answering why Postgres remains the absolute default starting point for 90% of engineering workloads.

Host 1

We'll look at the specific physical mechanisms that make B trees work under heavy concurrency, how right-ahead logging guarantees durability when the power goes out, and where the breaking points actually lie before you have to unbundle the database. But before we go, I want to leave you with a final provocative thought to mull over. If we take Martin Klepman's database inside out philosophy to its logical, extreme conclusion, a database is nothing more than a materialized view of an immutable log.

Host 2

Therefore, polyglot persistence isn't actually about choosing different isolated competing databases.

Host 1

Right. It is simply about choosing different lenses to view the exact same immutable reality. As a software engineer, your job is no longer just storing data. Your job is deciding which mathematical lens your users need to look through at any given microsecond.

Host 2

It fundamentally rewrites how you design distributed systems.

Host 1

It really does. Because when you finally realize that the log is the absolute truth and the database is just a temporary perspective, suddenly that murky, terrifying diagnostic landscape we talked about in the Friday afternoon cold open becomes a whole lot clearer.

More Podcasts from Start here: Modern Data Stores
Architectural Tradeoffs in Modern Data Systems
47m

Save to playlist

Manage playlists →

Share