When to Scale Beyond Postgres
Transcript
Made automatically from the audio, so it may contain mistakes. Click a line to play from there.
You know that very specific flavor of terror that sets in at like three AM when you're on call.
Oh, absolutely. The pager goes off, you stumble to your desk and your data dog dashboards are just well, a sea of red.
Exactly. But the panic doesn't actually come from a simple hardware failure. I mean, a dead server's easy, right?
Right. You just replace it.
Yeah. The real panic comes from looking at the tracing graphs and realizing your team has built this this monster that no single human actually understands anymore.
It's the realization that the architecture itself has become the primary failure mode.
Spot on. So I want to paint a picture for you of like a hypothetical product team. They are building a solid, fairly standard web application, you know?
Okay, pretty common scenario.
Yeah. And if you check their metrics, they haven't even crossed five hundred queries per second in production yet.
Which is practically nothing in the grand scheme of things.
Right. But if you look at their architecture diagram, it looks like someone took a handful of tech conference lineages and just threw them at a whiteboard.
I can already picture it. Let me guess. They've got everything.
Oh, they have everything. They are running Postgres QL as their primary relational store. They've got MongoDB managing user profiles. Then they spun up a Cassandra cluster just because, you know, a tech lead read a blog post about web scale horizontal partitioning.
Naturally.
Right. And they have Kafka acting as a nervous system trying to stream events between all these disjointed islands. Redis is sitting in front of everything just caching data in a sheer panic.
And let me guess, some AI feature too.
You guessed it. They recently deployed a standalone quadrant vector database just because the product manager wanted to experiment with a new search feature.
Wow. I have audited this exact company more times than I can count, honestly. We call this resume-driven polyglot persistence.
Resume driven. I love that.
Yeah. And the human toll is just staggering. I mean, before we even talk about latency or compute costs, you just have to look at their sprint velocity.
It's probably non-existent.
Pretty much. They are likely spending like 80% of their engineering cycles just debugging change data capture lag.
Oh, that's brutal.
And their staging environments are permanently broken. Keeping six wildly different data stores synchronized outside of a production network is it's a distributed systems nightmare.
Yeah, and their disaster recovery plan usually consists of a few untested bash scripts and blind optimism, right?
Exactly. Just hoping nothing actually breaks.
Well, that crushing operational toil is exactly why we are here today. So welcome to the capstone finale of our modern data stores series. This is deep dive number seven.
It's been a long journey to get here.
It really has. Now, if you have been on this journey with us through the previous six deep dives, you know exactly why that hypothetical team's Kafka queue is backing up. We are assuming a shared baseline with you today.
Right. So we assume you already know your OLTP from your OLAP. You understand the structural differences between an iceberg lakehouse and a Delta Lake.
Yep. We've covered the NoSQL family trees, the mechanics of Kafka append-only logs, and how H DNSW indexes actually perverse vector space.
And of course the fundamental pack else trade-offs when dealing with network partitions. In our last deep dive, we went extensively into point-in-time recovery and the danger of non-concurrent data definition language operations.
Which means, you know, we are not going back to basics today.
No, definitely not. We are not reteaching the internal structure of a B tree or how a raft consensus algorithm establishes a quorum. We are taking those internal mechanics and applying them directly to real architectural trade-offs.
Exactly. So our mission for this finale is to take the massive stack of sources we've curated over the series. We're talking insights from AWS architecture centers, Martin Clettman's philosophies.
Jepson's consistency analyses, which are always terrifying.
So terrifying. Plus real world engineering playbooks from Figma, Notion, and Discord, and Dan McKinley's famous essays.
We are going to synthesize all of that into a concrete, quantified architecture decision framework. We need to put a real numerical price tag on polyglot persistence.
I love that. Real numbers.
Yeah, by the end of this discussion, we are going to map out clear thresholds across the different database families. And we will close out the series with five durable rules of thumb for your next architecture design document.
Because we have to stop guessing based on hacker news trends, right? We need to start measuring when a workload actually forces you into a new data store.
Absolutely.
But before we can even look at a master decision table or debate the merits of ScyllaDB over Cassandra, we have to address a fundamental ideological battle in modern system design.
Yeah, this is the philosophical split that pushes teams into that 3 AM polyglot nightmare in the first place.
So let's start with the push for simplicity. Dan McKinley wrote a phenomenal, widely circulated piece called Choose Boring Technology. And he introduces this concept of innovation tokens.
I love this concept.
It's one of the most useful mental models in engineering management. Hands down.
How does he break it down?
So McKinley argues that a company, and he means the whole company, not just a single team, only gets about three of these innovation tokens to spend in its early life.
Just three.
That's tight.
It is. And if you spend one of your precious tokens adopting a bleeding edge, highly complex database technology, instead of spending it on solving your actual core business problem, you are severely misallocating your risk budget.
He ties this directly to the concept of unknown unknowns, right?
He does, yeah.
Because if I choose a boring technology, let's say Postgres or SQLite, I still have problems. I have known unknowns.
Exactly. You know, what might go wrong?
Right. Like I might not know exactly what happens to my specific app when the database CPU hits 100%, but the global engineering community does. The failure modes are incredibly well documented.
But if you spend a token on something brand new, the magnitude of unknown unknowns is just terrifying.
Let's trace the mechanics of that. What does an unknown unknown actually look like in production?
Okay. So say you adopt a new highly specialized data store written in Java. You deploy it, and for three months it's fine. Everything looks great.
Sounds like a success story so far.
Until suddenly your specific type of bursty telemetry write load triggers a catastrophic garbage collection pause. The entire node just stops responding to network requests for like three full seconds.
Oh wow. Three seconds is an eternity in database time.
It's game over. The cluster assumes the node is dead, so it triggers a leader election, it shifts the data partitions around, and suddenly your whole network is saturated with rebalancing traffic.
Which takes down your entire application.
Exactly. You didn't just fail to plan for that scenario. You didn't even know that sequence of events was physically possible in that specific system.
That's the trap. Boring technology means the blast radius of failure is actually predictable.
Precisely.
I understand the appeal of predictability, I really do, but I have to push back here just to play devil's advocate.
Bring it on.
Because the counterargument is basically the bedrock of modern software engineering, right? Use the best tool for the job.
Right. The classic defense.
Yeah. Let's say I'm building a highly connected recursive social graph, like friends of friends of friends. If I force that into a relational model, I'm going to be writing recursive common table expressions that take hundreds of milliseconds to execute.
And they will completely chew through your memory.
Exactly. So isn't forcing that square peg into a boring round hole a massive local optimization. Doesn't the operational pain of maintaining those horrific SQL queries cost me an innovation token anyway?
That is a very valid challenge. And honestly, it brings us perfectly to the other side of this philosophical divide, which is championed by Werner Vogels, the CTO of Amazon.
Okay, what's his take on this?
Vogel's core architectural thesis for AWS is that a one-size-fits-all database doesn't fit anyone. And he doesn't just say that to sell managed services. He has the historical battle scars from Amazon.com to back it up. It sure is. Back in the day, Amazon pushed a monolithic commercial relational database to its absolute physical limits. They bought the biggest hardware money could buy, they optimized every query, and they still hit a wall.
But the critical part of the sources here is why they hit the wall, right?
Yeah, that's the fascinating part. When Amazon engineers actually analyze their database operations, they discovered this incredible metric. Roughly 70% of their database operations were just simple key value lookups.
Wait, 70%? Yep. Just grabbing a single row.
Yep. They were using a primary key to fetch a single isolated row. They didn't need referential integrity for those queries. They weren't joining the shopping cart table to the user profile table or anything complex.
So wait, if 70% of the queries were that simple, why was the massive relational database choking? I mean, shouldn't a simple primary key lookup be trivial for an engine like that?
Well, it is trivial in isolation.
Mm-hmm.
But you have to look at the mechanical overhead of ACID compliance.
Break that down for me.
In a traditional relational database, even a simple read requires the system to traverse a B tree index. Then it has to acquire latches on memory pages in the buffer pool to prevent concurrent modifications while reading.
Right, to keep things isolated.
Exactly. Then it has to check the multiversion concurrency control visibility rules to ensure the transaction is actually allowed to see that specific version of the row, and then it returns the data.
And when you do that tens of millions of times a second. So they were basically paying this massive architectural tax for guarantees they didn't even need for the vast majority of their traffic.
Precisely.
Which is a purpose-built database designed entirely around predictable performance at any scale, explicitly abandoning those complex relational features to achieve it.
Exactly. So Vogel's point is that if you try to make one database do everything, it will eventually do everything poorly.
So how do we synthesize this for the listener? We have McKinley saying, stay boring, hoard your tokens, and Vogel saying break your monolith apart and use purpose-built engines.
If you look closely at the timeline, they aren't actually disagreeing. They're just describing different phases of the company life cycle.
Oh, that makes sense.
Yeah, the synthesis is this. Boring is your default posture. You stay entirely boring until your measured workload explicitly forces you into a purpose-built paradigm.
So Amazon didn't start with DynamoDB in a garage.
Exactly. They started with a relational database, they scaled it, they measured their traffic, they identified that 70% key value bottleneck through rigorous profiling, and then they built a purpose-built solution.
So you don't get to claim you need a purpose-built graph database or a wide column store until you have the telemetry proving the boring technology is fundamentally failing you.
That's the trap right there.
Which brings us to the actual decision framework. If we agree to start boring and only move when forced, how do we know exactly when we are forced? Like what are the hard numbers?
To map those thresholds, we have to rely on a quantified architectural intake framework. Before a team is allowed to even propose a new data store, they must answer seven specific, quantified questions about their workload.
Let's run through those. First, what are your expected queries per second or QPS at peak?
Right. Second, what is your working set size in terabytes? And I mean the hot data actively being read and written, not your cold archives sitting in an S3 bucket.
Makes sense. Third, what is your query predictability? Will your application only ever execute 20 predefined prepared statements? Or will business analysts be running ad hoc, unpredictable analytical scans?
It changes everything. Fourth, you need strict numbers for your P99 latency requirements.
Are you rendering a background report where 500 milliseconds is fine? Or are you serving a real-time bidding ad where if you miss a 10 millisecond window, you lose actual money?
Exactly. Fifth, you must define your invariance based on Jepson testing and the Pacelsk theorem. In the event of a network partition, does this specific data require strict linearizability, or can you tolerate eventual consistency to maintain availability?
That's a huge one. Sixth, what are your recovery point objective and recovery time objective, RPO and RTO? Like if the database storage volume corrupts, how many minutes of data can the business legally lose? And how many minutes do you have to restore service?
And the seventh question, which is honestly the one that always trips up small teams, what is your dedicated database operations head count?
Oh, yeah.
Because if you have zero dedicated DBAs, you have absolutely no business running a 50 node Cassandra cluster yourself. You will destroy your company.
Period. So let's take those seven intake questions and map them to a master decision table. Let's start at the absolute edge of the spectrum, the ultimate boring technology. SQ Lite.
SQLite.
Now a lot of developers dismiss SQLite as a toy. They think it's just for iOS apps or local testing, but the sources show it is an absolute powerhouse.
It really is.
We are talking about an engine that routinely handles up to 100,000 hits per day on dynamic websites, supporting database files up to 281 terabytes.
The operational threshold for SQLite has almost nothing to do with data size, though. It's entirely about architectural boundaries.
What do you mean?
The official SQLite documentation states this perfectly, and it's a profound shift in perspective. SQLite does not compete with client server databases like Postgres or MySQL. SQLite competes with OpenFile.
Oh wow. So it's a replacement for writing ad hoc disk files.
Exactly. The boundary is the network. You use SQLite when the data is not separated from the application by a network layer. It runs in the exact same process space as your application code.
So when do you have to move off it?
The absolute moment your architecture requires the database to sit on a separate physical server from the application compute, or the moment you have multiple concurrent application servers trying to write to the database over a network, that's when you must abandon SQLite.
I want to make sure I understand the mechanics of that concurrent writer limitation. Why does it fail over a network?
Because SQLite, even in its modern write-ahead log or wall mode, fundamentally relies on file system locks. It only allows one writer at any given microsecond.
Okay, so it cues them up.
Yeah, it handles write concurrency by having incoming writers queue up and take turns. If your application is local, taking a file lock takes nanoseconds. The queue clears instantly.
But over a network.
Right. If you have multiple servers pushing heavy write volumes over a network mounted file system, the latency of acquiring and releasing those file locks skyrockets. The queue backs up, writers hit their timeout thresholds, and you get those dreaded databases locked errors cascading through your system.
So think of SQLite as a single lane bridge with a very fast, sophisticated traffic hop. It's incredibly efficient for local traffic, but you cannot route an interstate highway through it.
That's a perfect analogy.
Which means the moment we need an interstate highway, concurrent writers over a network, we graduate to the default powerhouse of the industry, Postgres.
The big one.
This is the heavy, complex city intersection with multi-directional traffic lights, which in database terms is multiversion concurrency control. Postgres is the default boring technology.
It is. But what's fascinating in our sources is how much purpose-built capability Postgres has absorbed recently, specifically regarding AI and vector search with PG vector.
This is such a critical threshold to define because right now every team building a retrieval augmented generation pipeline wants to immediately spin up Qdrin or Pinecone or Milvis.
They want the shiny new toy.
Exactly. But the reality is PG Vector running inside your existing Postgres instance is shockingly capable. It handles both exact nearest neighbor and approximate nearest neighbor or ANN search.
It natively calculates L2 distance, inner product, and cosine distance too.
Out of the box, it supports up to 2,000 dimensions. If you use half precision floating point numbers or half vec, you can double that to 4,000. And if you implement binary quantization, you can push it to 64,000 dimensions.
So the architectural rule here is do not run a standalone vector database prematurely. Keep your vectors right next to your relational data in Postgres.
But the sources detail exactly where the breaking point for PG vector actually is. It comes down to the mechanics of the indexes, doesn't it?
It does. You basically have two choices for your ANN index in PG vector H and SW or IVF flat. Let's drill into how those actually work because the mechanics dictate the threshold.
Okay, let's start with HNSW.
HSW stands for Hierarchical Navigable Small World. Think of it as a multi-layered graph. The bottom layer contains all your vectors.
So when you query, it enters the top layer, finds the closest node, drops down a layer, and navigates closer and closer.
Exactly. It is incredibly fast for querying and offers phenomenal recall accuracy. But building that multi-layered graph requires an enormous amount of memory, and the build times are extremely slow.
And what about IVF Flat?
IVF Flat, conversely, uses an inverted file index. It uses K-means clustering to divide the vector space into four noise cells or lists. When you query, it figures out which cell your query vector falls into and only searches the vectors in that specific cell.
That sounds more efficient to build.
It builds much faster and uses far less memory than HNSW, but the query performance and recall aren't quite as good. Crucially, IVF Flat requires you to have a representative sample of training data in the table before you build the index so it can calculate those cluster centers.
Whereas HSW can be built on an empty table.
Right. And this is where the operational threshold for Postgres is a vector store reveals itself. It's not necessarily about hitting 10 million vectors. It's about your memory configuration during the index build.
Okay, so when you build an HNSW index, Postgres has to hold a significant portion of that graph structure in memory, right?
Yes. The size of the memory buffer it uses for this is controlled by a setting called maintenance work mem.
Okay, I've seen that setting.
If your HNSW graph exceeds your configured maintenance work mem during a build or a massive re-indexing operation, the build times absolutely explode because Postgres has to start spilling to disk and thrashing.
Oh, that's bad.
Postgres will actually throw a system notice warning you that the graph no longer fits.
So that notice is your explicit metric. If you have tuned your maintenance work mem, if you have utilized binary quantization to compress the index footprint, and you have vertically scaled your RDS instance to the maximum RAM available, but your vector index builds are still stalling your deployments or causing memory exhaustion alerts.
Then you have the data to justify spending an innovation token on a dedicated standalone vector database like QDrant.
Until you hit that memory wall, stay boring.
Exactly. And this same philosophy applies to scaling the relational data itself. Before you abandon Postgres for a No SQL store, you can explore distributed SQL systems like FITES or Cockroach DB.
Right. They allow you to horizontally scale a relational workload while maintaining SQL semantics.
But again, they introduced immense operational complexity regarding query routing and distributed transaction coordination. They are a half step between a single node Postgres and a full No SQL rewrite.
Okay, let's shift gears to the analytical side of the house. We've got our transactional Postgres database humming, but the business analysts need to run massive aggregations over three years of historical data.
Typical Tuesday.
Yeah. We know from our previous deep dives on OLTP versus OLAP that running a complex analytical scan on a transactional database will destroy the buffer pool and cause latency spikes for our live users.
You definitely need to move the data. The traditional move was to buy a massive enterprise data warehouse, but the sources highlight the rise of lake houses, technologies like DuckDB, ClickHouse, and Apache Iceberg.
The Databricks CIDR paper outlines this threshold perfectly. The boundary for moving to a lakehouse architecture is when you need serious analytical processing, but you want to avoid what they call the data warehouse tax.
Right. And when they say tax, it's not just the massive vendor licensing fees. What else is it? Mechanically, it's about double storage and proprietary lock-in. Traditional data warehouses force you to ingest your data into their closed proprietary storage formats.
So you pay to store your raw event data in an S3 data lake, and then you pay computing costs to run ETL pipelines to transform and load it.
And then you pay premium storage costs to hold the exact same data inside the warehouse's proprietary format.
That's essentially paying three times for the same data.
Exactly. The lake house threshold is defined by the absolute need for open direct access formats. You want to store your data once in a standard open source columnar format like Apache Parquet or ORC, sitting in cheap object storage like S3.
Let me make sure I'm mapping this right based on our iceberg deep dive. Parquet handles the columnar storage, making analytical scans incredibly fast because it only reads the specific columns queried. Yes. But parquet files are immutable. You can't easily update a single row in an S3 parquet file without rewriting the whole file.
Exactly. And that's where the lake house table format comes in. You overlay a metadata and transaction layer, like Apache Iceberg or Delta Lake directly on top of those parquet files.
How does that solve the immutability issue?
Iceberg maintains manifest files that track which parquet files belong to which version of the table. It gives you acid transactions, time travel, and schema evolution, all while the data remains in an open format.
So the architectural boundary is decoupled compute and open storage. You can have a local Python script using DuckDB to query the iceberg tables for a small data set, while simultaneously having a massive click house cluster querying the exact same S3 parquet files for real-time user-facing analytics.
And you never had a copy of the data into either system. Precisely. You move to a lake house when your analytical scale and cost efficiency requirements demand that you stop paying to duplicate data into closed ecosystems.
Makes total sense. Exactly. But why can't a distributed relational system do that?
It comes down to the physics of distributed transactions. If you want strict acid guarantees across a distributed cluster, you have to use protocols like two phase commit.
Right, they have to agree.
Yeah, if a transaction updates a row on node A and a row on node B, they have to coordinate over the network to ensure they both commit simultaneously. That network coordination adds dozens or hundreds of milliseconds of latency, and it severely limits write throughput.
Got it. So systems like Dynamo DB or CELADB just abandon acid joins and complex transactions entirely.
They do. They use a distributed architecture based on a consistent hashing ring. When a query comes in, the system hashes the partition key. And that hash mathematically dictates exactly which node in the cluster holds that data.
So there is no central routing bottleneck.
Not at all.
I see. If Postgres is a complex city intersection, a NoSQL store like Dynamo DB is a massive high-speed rail network where your ticket, your partition key, determines exactly which track you are on before you even arrive at the station.
That's a great way to visualize it.
There are no traffic lights, just pre-calculated parallel tracks.
But the trade-off is that if you want to query across tracks, if you want to join data, you are completely out of luck.
Exactly.
You trade query flexibility for absolute unyielding performance at scale.
Okay. So we have our boundaries. We know when to use SQLite, Postgres, Lakehouses, and NoSQL. But here is the reality for most engineering teams. What happens when your Postgres database, your boring technology, is completely full?
That's the terrifying moment.
You follow the rules, you started boring, you didn't overengineer, but your application is wildly successful, and the database is melting down.
The natural instinct at this point is panic. Engineering leadership reads a blog post about microservices and decides they need to immediately shatter the monolith, extract every domain and do its own service and adopt polyglot persistence.
But the sources we reviewed are adamant. You must resist that urge. Before you shatter your architecture into microservices, you must stretch the monolith to its absolute physical breaking point.
Yes. And we have two incredible, highly detailed case studies on exactly how to do this from Figma and Notion.
Let's look at Figma first.
Figma's Crisis is a textbook example of pushing Postgres to the absolute limit. They were operating a massive R5.24X large RDS instance on AWS.
For context, that is a machine with 96 V CPUs and 768 gigabytes of RAM. It does not get much bigger without custom hardware.
And their CPU utilization was routinely hitting 65% during peak traffic.
Now I can hear a junior engineer saying, 65%, that means they have 35% headroom. What's the problem?
Yeah, that's a common misconception. In database terms, 65% on a highly concurrent transactional system is the danger zone. It comes down to queuing theory, specifically the Erlang C formula.
Right. How the wait times compound.
Exactly. When a system is handling thousands of concurrent requests, resource utilization doesn't degrade linearly. Once you cross a certain utilization threshold, often around 70%, the wait times in the queue begin to increase exponentially.
So a tiny microscopic spike in traffic or a single poorly optimized query that holds a lock for slightly too long. Wow. So their first tactical fixes were classic vertical scaling. They played cookie clicker with the AWS resize button until they maxed out the instance size.
They added read replicas to offload read heavy traffic, routing queries that didn't need absolute real-time consistency to the replicas.
And crucially, they inserted PG Bouncer between their application and the database. Let's explain why that specific piece of infrastructure is so vital.
PGBouncer is a lightweight connection pooler, and it solves a fundamental mechanical flaw in how Postgres handles connections.
How does Postgres handle them normally?
Well, every time a client application opens a direct connection to Postgres, Postgres has to fork a new operating system process to handle it. Forking a process consumes a significant amount of memory, often around 10 megabytes per connection, and creates CPU overhead for context switching.
So if you have a fleet of application servers opening 10,000 direct connections to the database.
Postgres will consume 100 gigabytes of RAM, just managing the idle connections, leaving very little memory for the actual buffer pool.
So PG Bouncer acts as a multiplexer.
Exactly. PG Bouncer sits in the middle. The client applications open their 10,000 connections to PG Bouncer. PG Bouncer accepts them easily because it's highly asynchronous.
And what does it do with Postgres?
Meanwhile, PG Bouncer maintains a very small, fixed pool of actual back end connections to Postgres, maybe just 200 connections. When a client wants to run a transaction, PG Bouncer borrows one of those 200 backend connections, executes the query, and instantly returns the connection to the pool.
It acts as a massive shock absorber, shielding Postgres from connection churn and allowing the CPU to focus on executing queries, not managing OS processes.
Exactly. But even with the biggest instance, read replicas and pgbouncer, Figma realized they were still going to hit a wall.
Because writes were dominating their CPU, and you cannot send rights to a read replica.
Right. They had to vertically partition their database. Meaning they had to surgically extract specific tables out of the main database and move them to a new dedicated Postgres instance.
And vertical partitioning is incredibly risky. If you extract tables that are constantly joined together in your application queries, you suddenly break your application because you can no longer do cross-database asset joins.
You lose foreign key constraints.
You lose atomic transactions across those domains. So Figma needed a rigorous mathematically sound way to identify which tables were safe to move and which tables would yield the most CPU relief if moved.
And their diagnostic method was brilliant. They didn't just guess based on table size, they wrote runtime validators in their Ruby application to send query telemetry to a Snowflake data warehouse for analysis.
But more importantly, they analyzed a system view within Postgres called PGstat activity.
PGstat activity is a real-time window into the soul of a Postgres database. It shows exactly what every single active connection is doing at that exact microsecond. What query it is running, what state it is in, and crucially, if it is waiting, what type of lock or resource it is waiting for.
Known as the wait event type.
Right.
But just looking at it once doesn't tell you much, so Figma sampled this view every 10 milliseconds.
Right. They were on a high frequency polling script that scraped PG stat activity 100 times the second. By aggregating this micro snapshots over time, they could calculate a metric called AAS or average active sessions.
What does that tell you?
AAS tells you on average how many CPU cores are actively engaged in executing queries for a specific table at any given time. By slicing the AAS data by table name and query type, they could pinpoint exactly which tables were responsible for the majority of the CPU wait times.
Crucially, they looked for tables that had high AAS but were functionally isolated, meaning they weren't strongly bound by foreign keys or frequent joints to the core user tables.
That high frequency sampling gave them the confidence to say, if we move these three specific logging and telemetry tables, we will reclaim 30% of our CPU capacity without breaking any relational integrity.
Once they identified the tables, they had to actually execute the migration. And for a collaborative design tool like Figma, taking a four-hour maintenance downtime window was completely unacceptable.
Oh yeah. This switchover was a master class in zero downtime engineering. To achieve a hot swap, they couldn't use standard streaming replication.
Because streaming replication creates an exact byte-for-byte physical replica of the entire database cluster, right? You can't filter it.
Exactly. Instead, they used logical replication.
Let's make sure the mechanics of that are clear. How does logical replication differ from streaming?
Postgres writes every single change to a right-ahead log or wall. Streaming replication ships those raw wall files to the replica, which blindly applies the exact physical disk changes. Logical replication, however, decodes the wall into a stream of logical SQL events like insert row into table X.
Because it's logical, you can apply a filter.
Yes. Figma set up a publisher-subscriber model to replicate only the specific subset of tables they wanted to vertically partition over to the new empty database.
So they spun up the new database, initiated the logical replication for those specific tables, and waited days for the historical data to sync up. But how do you actually flip the switch without dropping client requests?
This is where their PG bouncer layer proved its worth a second time. It wasn't just a shock absorber. They used it as a routing layer. They created separate PG bouncer service endpoints to virtually split the traffic.
So when the time came for the cutover, they didn't restart the application.
No. They simply paused activity on the original database for a few seconds to let the final bytes of logical replication drain and ensure both databases were perfectly in sync. Then they reconfigured the routing rules in PG Bouncer so that queries destined for the partitioned tables were instantly forwarded to the new database and resumed activity.
The total availability impact to the end user was less than one minute. It was a phased, highly measured approach using connection pooling to achieve a hot swap under massive load.
Figma stretched the monolith vertically, buying themselves years of runway without adopting microservices.
But vertical partitioning only works if you have distinct tables you can slice off. What happens when the problem is your primary core table?
That brings us to Notion.
Notion faced a completely different existential threat, and their solution was horizontal sharding.
Notion's crisis is the stuff of nightmares for a database administrator. Their vacuum process was stalling.
I know from our previous deep dives that vacuum is basically the Postgres garbage collector, but if a garbage collector stalls in a Java application, you get a latency spike. Why is a stalled vacuum in Postgres considered an existential threat to the business?
To understand the tear, we have to look deeply into the mechanics of multiversion concurrency control, or MVC C. When you update a row in Postgres, it does not overwrite the data on disk.
Right, it creates a new version.
Exactly. It marks the old version of the row as a dead tuple and writes a completely new version of the road to a new location on disk. This is what allows concurrent reads and writes without massive locking.
Readers look at the old version while writers create the new version.
Yes. The vacuum process runs in the background to sweep through the disk, find those dead tuples that are no longer visible to any active transactions, and mark that disk space as available for future writes.
So if vacuum stalls, your disk usage grows exponentially until you run out of storage.
But that's not the existential threat. The real danger is the transaction ID or TXID wraparound. Every single transaction in Postgres gets a unique incrementally assigned transaction ID. Postgres uses the IDs to determine visibility.
Okay, so if your transaction ID is 100 and you see a row created by transaction ID 105, you know that row is in the future relative to you, so you must ignore it and look for an older version.
Exactly. It builds a worldview based on sequence numbers, but those transaction IDs are stored as 32-bit integers, which means there are only about two billion available IDs.
Two billion sounds like an enormous number. Oh wow. So as the IDs increment, they eventually hit the 32 bit limit and wrap around back to zero.
And if that happens, a transaction ID of zero suddenly looks like it occurred in the distant past compared to a transaction ID of 1.9 billion. Data from yesterday suddenly looks like it's from the future.
The entire visibility logic of the database implodes, and all your data is effectively corrupted.
To prevent this, Vacuum performs a secondary vital task called freezing. It scans old rows and replaces their specific transaction ID with a special flag that says this row is infinitely old and visible to everyone. This allows the ID counter to safely wrap around. Exactly. And Postgres has a hard-coded safety mechanism. If vacuum falls too far behind and the system gets dangerously close to the wraparound point, Postgres will completely panic and halt all write operations to the database to prevent data corruption.
The database becomes strictly read-only.
And for a product like Notion, where the entire value proposition is writing and editing documents, a read-only database means the company is offline.
And Notion's vacuum was stalling because their core blocks table, the table that stores every single paragraph, checklist, and header in every Notion page, had grown so massive that the background workers simply could not scan the table fast enough to keep up with the incoming write volume.
They couldn't vertically partition it because it's a single table. You can't slice a column off to a new database. No, they had to horizontally shard it. They had to split the rows of that massive table across multiple physical databases.
Now they could have used native Postgres declarative partitioning or a managed extension like CITUS or Vitesse, but Notion made a very deliberate architectural choice. Application level sharding.
They decided to maintain absolute control over the data distribution and routing logic directly within their application code rather than trusting a magical middleware layer.
Which means their application code has to know exactly which physical database holds the data it needs before it executes a query. They architected a system with 480 logical shards distributed across 32 physical Postgres databases.
Let's unpack those numbers because they are very specific. The sources note they set strict limits to preserve replication guarantees and keep the vacuum process healthy. No physical database could exceed 10 terabytes, and no single table could exceed 500 gigabytes.
But why 480 logical shards across 32 physical servers?
It's an elegant mathematical design for future proofing. You never want to hard code your physical infrastructure into your data model. By creating logical shards, the application only knows how to route to a number between one and 480.
And 480 is a highly composite number. It is cleanly divisible by 32.
That means each of the 32 physical databases holds exactly 15 logical shards. If Notion continues to grow and 32 servers aren't enough, they can seamlessly scale to 48 or 64 or 96 physical databases.
They simply migrate a few logical shards from the old servers to the new servers and update a simple routing table in the application. They don't have to rehash or mathematically repartition the underlying data records.
That's brilliant engineering. But the absolute most critical decision in any horizontal sharding architecture is the partition key. How do you decide which logical shard gets which data? If you get this wrong, you destroy your performance.
This is where most teams fail. They often partition by user ID. But think about how Notion works. A Notion workspace is collaborative. You have 50 users interacting with the same pages, comments, and blocks.
Right. So if you partitioned by user ID, Alice's blocks might be on database one, Bob's blocks on database 17, and Charlie's comments on database 32.
So if a user loads a page, the application has to send parallel queries to 32 different databases, wait for all the results to come back over the network, and then stitch them together in memory before sending the HTML to the browser.
That is a distributed join, and it is the death of latency. The network overhead would be catastrophic. So Notion chose to partition by workspace ID.
That is the key takeaway. You must choose a partition key that preserves data locality.
Exactly. In Notion, almost all collaborative activity happens within the boundaries of a single workspace. By using Workspace ID, they guaranteed that all the pages, blocks, and comments for a specific workspace live on the exact same physical database shard.
When a user queries a page, the application hashes the workspace ID, routes the request to that specific physical database, and the database can perform blazing fast local SQL joints across the tables. They isolated the workload perfectly.
But the actual migration process was a nightmare, right? They couldn't use the logical replication trick that Figma used.
No, they couldn't. Logical replication is great, but it has throughput limits. The write volume on Notion's block table was so astronomically high that logical replication couldn't keep up during the initial snapshot phase. The replication lag would just grow infinitely.
So they had to build a custom migration pipeline. They used a dual write and backfill strategy. First, they updated their application code to write all incoming changes to both the old monolithic database and the newly created sharded databases simultaneously.
This is the audit log.
Once the dual rights were live, they spun up a massive 96-core AWS compute instance, running a custom backfill script. This script chugged through the historical data in the monolith, slowly migrating it to the shards over three days.
Because the live traffic was already being double written, the backfill script eventually caught up to the present moment. They ran extensive data validation scripts to ensure the shards matched the monolith exactly, and then they flipped the read traffic to the shards.
It was a monumental engineering effort, but it saved them from the TXID wraparound and gave them a horizontally scalable architecture.
So what does this all mean for you, the listener? Notion and Figma successfully stretched Postgres to incredible limits. They stayed boring as long as humanly possible, utilizing vertical partitioning and application level sharding to avoid adopting a new database engine.
But what if you do decide to split your monolith? What if you decide to embrace the microservices architecture? Give every service its own purpose-built database and add layers of caching to solve your performance problems.
It's time to price out the true hidden operational pain of polyglot persistence.
Let's start with the microservices data trap. The textbook microservices pattern mandates a database per service architecture. The order service has its own Postgres database. The customer service has its own MongoDB cluster. The inventory service has its own Cassandra cluster.
This creates strong domain encapsulation, meaning one team can deploy schema changes without breaking another team's code. It sounds like a utopia in theory.
But in practice, you immediately lose ACID joins across domains. If a product manager asks for a simple internal dashboard that shows customers in New York and their recent orders in a monolith that is a single five-line SQL join query that executes in two milliseconds.
In a microservices architecture, how do you even answer that question?
You are now fully responsible for distributed data management. You either have to make the front-end application send separate queries to both services and join the data in the client's memory, which is terribly inefficient, or you have to build complex asynchronous event streams.
But the real pain isn't querying its mutating state. Let's say a customer places an order. You need to insert a record in the order database, deduct the item count in the inventory database, and charge the credit card in the billing database.
Because these are three separate databases running on different servers, you cannot wrap them in a single ACID transaction. You cannot just commit or roll back everything at once.
So if the order writes successfully and the inventory deducts successfully, but the billing services database is offline and throws a timeout error, you are now in a distributed inconsistent state. The order exists, the inventory is gone, but no money was collected.
Exactly. To solve this, you are forced to build saga compensations. A saga is a sequence of local transactions. If one step fails, you have to write custom application code to issue compensating transactions to undo the previous steps.
So your application catches the billing failure and it sends a request to the inventory service to add the item back, and it sends a request to the order service to mark the order as cancelled.
Wait, but what if the compensating transaction itself fails? What if the network partition that took down the billing database suddenly takes down the inventory database while you were trying to compensate?
Then you are in a waking nightmare. You have to implement persistent retry cues, dead letter cues, and forward recovery mechanisms. And to make sure your services even know when to update their local views, teams usually implement the outbox pattern with change data capture or CDC tools like Debesium.
Let me see if I can map out that plumbing. Instead of directly updating the other services, the order service writes the order to its database. And in the same local acid transaction, it writes an event payload to a local outbox table.
Yes. Then Debezium, running as a separate infrastructure component, tails the Postgres transaction log, detects the insert in the outbox table, and streams that event over to an Apache Kafka cluster. That is an astronomical amount of infrastructure plumbing just to replace a simple SQL join and a commit statement. You are maintaining Debezium connectors, a zookeeper-backed Kafka cluster, and countless consumer microservices.
And we haven't even talked about the disaster recovery toil. Every new data store you add multiplies your point-in-time recovery drills.
Think about the restore rule. If your system crashes at 12.05 PM and you need to restore from backups, can you guarantee you can restore the Postgres orders database, the Cassandra Inventories database, and the Kafka event log to the exact same millisecond, so they are semantically consistent?
That is the hidden cost the teams rarely calculate. If your Postgres backup restores to 120500, but your Cassandra backup resources to 1205.01, you have a one-second window of data corruption, where orders exist but inventory wasn't deducted.
Furthermore, by adopting all these different databases, you multiply the Jepson anomalies you have to worry about. Every distributed database has different consistency quirks under network partitions.
Cassandra might drop rights due to clock skew, while MongoDB might serve stale reads during a leader election. You have to understand all of them simultaneously. This is the true price of polyglot persistence. It's not just the AWS infrastructure bill, it's the crushing cognitive load on your engineering team.
Which brings us back to Discord. Because Discord actually had to pay this price. Their scale absolutely mandated polyglot persistence and purpose-built engines. They executed a legendary migration of trillions of messages from MongoDB to Apache Cassandra and finally to ScyllaDB.
Let's analyze why Cassandra, which is explicitly designed for massive scale, was failing them. They had a cluster of 177 Cassandra nodes. Cassandra is written in Java, which means it relies on a garbage collector to manage memory.
Under Discord's massive read and write workload, they were generating objects in memory faster than the Java virtual machine could clean them up.
This led to severe stop the world garbage collection pauses. When a Java node pauses to collect garbage, it literally stops executing code and stops serving network requests.
So a user tries to load a channel, the request hits a node that happens to be garbage collecting, and the request just hangs.
Exactly. This led to violently unpredictable latency spikes, with their P99 latencies bouncing erratically between 40 and 125 milliseconds. The maintenance burden was huge. Operators frequently had to manually babysit nodes, carefully triggering compactions of SS tables to avoid taking down the cluster.
So they moved to ScyllaDB, which is functionally a drop-in replacement for Cassandra, but it is written in C. There is no garbage collector. They reduced their hardware footprint from 177 nodes down to just 72 nodes. And their P99 latency dropped to a rock solid 1.5 milliseconds.
But according to the engineering blogs, the unsung hero of this migration wasn't just swapping out the database engine. It was building a custom Rust data service to sit in front of the database.
This is a crucial architectural pattern for survival at scale. Discord was suffering from a phenomenon known as hot partitions. Imagine a massive Discord server with a million users. An admin types an at everyone ping.
Every single one of those million users receives a push notification simultaneously. Hundreds of thousands of users immediately open the app on their phones to read the message. Suddenly a massive surge of concurrent read requests hits the exact same partition in the database to fetch that specific channel's history.
And in a system like Cassandra or Scylla, unbound concurrency on a single partition causes cascading latency because the node holding that partition gets completely overwhelmed while the rest of the cluster sits idle.
Exactly. So Discord built an intermediary service in Rust, leveraging its incredibly safe and efficient concurrency model to perform a technique called request coalescing.
I want to make sure I fully grasp the mechanics of coalescing. How does it actually shield the database?
Think of it like this. When that at everyone ping happens, 50,000 requests hit the Rust service in the span of a few milliseconds, all asking for the history of channel ID 123. The Rust service intercepts the first request and spawns a worker thread to query the ScyllaDB database for that channel.
But when the second, third, and 50,000th requests arrive milliseconds later for the exact same channel.
The Rust service checks an internal hash map, sees that a query for channel 123 is already in flight, and it does not send another query to the database.
It just holds those requests in memory.
Yes. All those concurrent requests simply subscribe to the original worker task using channels or futures. The database only ever sees one single query. When the database returns the row to the worker task, the Rust service broadcasts the result to all 50,000 waiting subscribers simultaneously.
That is beautiful. It turns a catastrophic spike of 50,000 database queries into a single query. It completely flattens the load curve. That is how you survive at trillion message scale.
Speaking of thundering herds and catastrophic spikes, we need to address the dangers of cache addiction. We covered system resilience extensively in Web Architecture Season 2 episodes 6 and 7, but it's absolutely vital to bring those concepts into this data store framework. Teams often throw Redis in front of a slow relational database to fix latency problems.
And it usually works immediately. The latency drops from 200 milliseconds to two milliseconds, the database CPU load drops to zero, and the engineering team goes out for celebratory drinks.
But the AWS Builder Center insights highlight a terrifying failure mode. The service becomes addicted to its cache.
The modal behavior of the entire system changes entirely. Because the cache is handling 99% of the read traffic, the database is essentially idle. The team might even scale down the database hardware to save money, assuming the new normal.
But what happens when the Redis cache fleet requires a rolling reboot for security patching or simply crashes due to an out-of-memory error? You experience a cold start. The cache comes back online, but it is completely empty.
The thundering herd arrives.
The database, which was scaled down and hasn't seen real traffic in months, immediately saturates its CPU, rounds out, and crashes. Your caching layer didn't just fail. Its failure caused a catastrophic outage of your primary system of record. Or you use the soft TTL versus hard TTL pattern, which we touched on in the web architecture series.
How does that work again?
When you write a record to the cache, you set two expiration times. You set a soft time to live, say five minutes. When that expires, the client will attempt a background request to the primary database to refresh the data. But you also set a hard TTL, say 24 hours.
So if the cache tries to refresh the data at the five-minute mark, but it detects that that the downstream primary database is experiencing high latency or is completely unresponsive, what happens?
The cache receives a back pressure signal. Instead of failing the user request or hammering the database further, the cache catches the error and simply serves the stale data, which is still valid under the 24 hour hard TTL until the primary database recovers. You trade temporary data staleness for absolute systemic availability.
This discussion of caches and databases leads to a fascinating architectural evolution proposed by Martin Kletman. Throughout the series, we've talked about databases, Redis caches, and QDON search indexes as separate entities that we have to manually keep in sync. Clettman argues we are looking at this entire paradigm backwards.
He proposes the database inside out architecture. In traditional systems, we rely on fragile dual rights. Our application code receives a request, so it writes to the Postgres database and then it tries to update the Redis cache. And maybe it fires an API call to update the QDrant vector index.
If the network blips between step two and step three, our data is instantly out of sync. We have drift.
Klepman says that relational databases, caches and vector indexes, are all just derived data. They aren't the source of truth. They are just different highly optimized lenses for looking at the same underlying facts.
Every single change in your system, every click, every order, every update is appended as an immutable fact to this log. That is all the application writes to.
Then instead of application code doing dual writes, your Redis cache, your QDrant vector database, and even your primary Postgres relational database simply act as materialized views. They independently consume the event log stream, process the facts sequentially, and update their own internal state.
So if the Redis cache crashes and burns, or if you realize your QDRant vector index was built with the wrong dimensions, you don't panic and try to write complex migration scripts.
You just spin up a brand new empty Redis cluster or a new QDRant instance, point it at the beginning of the Kafka log and have it replay every event since the dawn of time to organically rebuild its state. The database is no longer the center of the universe. The immutable log is.
It is a profound paradigm shift.
It radically simplifies distributed data management because you eliminate dual rights. However, we must be intellectually honest, it introduces massive complexity in managing eventual consistency.
Your application has to be designed to handle the fact that it might write an event to the log, but the read view in Postgres might take 50 milliseconds to update.
But it's a theoretical model for handling polyglot persistence safely. It is incredibly powerful.
Okay. We have covered massive ground today. And across this entire series, we've seen the rigorous math of the intake framework, the master decision table spanning from SQLite to CelaDB, the monolith stretching playbooks from Figma and Notion, the hidden failure modes of microservices and caching, and the philosophy of immutable logs.
It is time to distill everything we've learned across season five into actionable, undeniable rules.
These are the five durable rules of thumb. I highly recommend writing these into your team's architectural guidelines.
Rule number one, start boring. Your default starting point should always be one well-indexed, vertically scaled PostgreSQL instance. If you are operating completely at the edge without network boundaries, use embedded SQL Lite or DuckDB. Do not spend an innovation token on day one. Accept the known unknowns.
Rule number two, stretch before you break. Before you resort to microservice sharding or adopting polyglot persistence, you must stretch your monolith to its absolute limit. Scale vertically, add read replicas, implement PG bouncer for connection multiplexing, and rigorously use telemetry like PG stat activity to perform application-level vertical partitioning or sharding, just like Notion and Figma did. Squeeze every ounce of performance out of the boring technology first.
Rule number three, prove it with metrics. Only add derived data stores like a dedicated OLAP Lake House, a standalone quadrant vector database over PG vector, or a Redis cache behind CDC logs when your measured P99 latency, your terabyte scale working set, or your QPS explicitly force your hand. Do not adopt a purpose-built database without the hard telemetry to justify the operational tax.
Rule number four. The restore rule. Never ever run a data store in production that you haven't successfully restore-tested in tandem with the rest of your architecture. If you have five databases, you must be able to restore all five to a semantically consistent point in time.
If your microservices are out of sync by 500 milliseconds after a restore, you will corrupt user data. If you can't restore it, you don't have an architecture, you have a ticking time bomb.
And rule number five, beware modal behavior. If you add a cache to protect a database, you must engineer the system so it survives when the cache inevitably goes cold. Implement request coalescing or soft TTL back pressure. Never allow your system to become so addicted to its cache that a cache failure causes a primary database outage.
Those are the rules.
Pin them to your Slack channel.
But before we sign off, I want to leave you with a final provocative thought. Building on Kleppman's idea of the database inside out. Think about how we traditionally view databases. We tweet them as global, shared, mutable state. We update rows, we overwrite old data. We literally delete history in the name of storage efficiency.
It's the exact kind of mutable global variable design that any senior engineer would reject instantly in a pull request for application code.
So if Klepman is right. Something to mull over the next time you are staring at a 65% CPU utilization chart at 3 a.m. It forces you to completely reevaluate what state actually means in a distributed system.
It really does. And with that, season five of Modern Data Stores is officially complete. Thank you for diving deep with us. Keep your systems boring and your metrics sharp.