How Distributed Databases Fail in Production
Transcript
Made automatically from the audio, so it may contain mistakes. Click a line to play from there.
Picture this, right? It is Tuesday afternoon, two o'clock.
Oh, the absolute worst time for something to go wrong.
Exactly. You're right in the middle of peak traffic. And the dashboards, you know, they're looking beautiful. The system is just humming along.
Right.
Then a well-meaning engineer on your team decides to run an unguarded alter table command on the primary production database.
Oh no.
I can already see where this is going.
Yep. I can already see where this is going.
Yeah. They just want to add a single column. It seems, you know, incredibly simple. But what they don't realize is that their simple little schema change is cueing directly behind a massive long-running ORM reporting transaction.
The classic hidden monster.
Right. The kind of transaction that is doing a sequential scan across like a hundred million rows because somebody forgot an index. So instantly, that alter table triggers an access exclusive lock pileup.
Yep.
Every single incoming checkout request from your customers is now blocked. The traffic just completely flatlined.
Which is terrifying because from the outside, I mean the database hasn't actually crashed. No. The CPU might even look totally fine, but it's a silent chokehold.
Right. And then the dominoes just start falling. You have an automated failover script running in the background, right? It sees the checkout timeouts, assumes the primary database is completely dead, panics, and promotes a lagging asynchronous replica to view the new primary.
Because it's just doing what it was programmed to do.
Exactly. But here is the absolute kicker. So a minute later the lock clears.
Oh wow.
Yeah. Now you have the old primary happily accepting rights from half your application servers that haven't updated their connection strings yet. And the new primary accepting rights from the other half. The result? Total split brain data loss. Welcome to the nightmare. Okay. Let's let's unpack this.
I mean, it is the ultimate operational horror story. You have cascading failures, automation behaving badly, and just fundamental misunderstandings of database locking, all converging to destroy your data integrity in what, a matter of seconds?
Exactly why we are here today. Welcome to Deep Dive Episode 6 of Modern Data Stores. And if you are an experienced software engineer or a tech lead, you are in exactly the right place.
Absolutely.
Just to set the ground rules for today's session, you already know right ahead log mechanics and MVCC from episode one. We covered quorum rings in episode three, CDC replication slots in episode four, and vector RAM sizing in episode five.
Right. So we are not going backwards. We are skipping the basics entirely today.
Yep. Our mission today is all about running data stores in actual brutal production environments.
And to do that, we are synthesizing a massive stack of operational research today. We're pulling from Google Site Reliability Engineering Protocols, AWS Builder Center architecture guides, Martin Kletman's distributed systems research, the infamous Jepsen consistency analyses.
Oh, Jepson's so good.
So good. And Martin Fowler's evolutionary database models. We are bringing all of this together into a master class on distributed failure modes and operational engineering.
I love it. I want to start by throwing out a question that I see like constantly in tech interviews, and it drives me absolutely crazy.
Oh, I bet I know which one.
People always look at a database architecture and ask so is this system CP or AP?
Yep, there it is.
Referring to the classic cap theorem, right? Choosing between consistency and availability in the face of a partition. But characterizing a distributed database like that, I mean, it feels to me like asking if an airplane is gravity oriented or thrust oriented.
That's a great way to put it.
It completely ignores the actual physics of flight. It's just a false dichotomy.
It's a completely outdated framing. And to be fair to Eric Brewer, you know, the original author of the CAP Theorem, he has openly published retrospectives, admitting that the choose two out of three framing was deeply misleading for the industry. Oh yeah.
He actually walked it back.
Oh yeah, because fundamentally you cannot choose P. A network partition is not a toggle switch in a configuration file.
Right.
You are communicating over an asynchronous network, BGP routes flap, optical cables get accidentally severed by backhoes, which happens more often than you'd think.
The dreaded backhoe.
Exactly. And switch buffers overflow and drop packets. The network partition is just a fundamental reality of the physical world. You are always operating in a P environment.
Right. You don't get to opt out of the speed of light or faulty hardware. So when the network breaks, yes, you have to choose. Do I return an error to the user to preserve absolute truth across the cluster, or do I serve them potentially stale out of date data just to keep the application alive?
Exactly.
But what about when the network is perfectly fine? The cap theorem just goes completely silent on that.
Which is exactly why distributed systems researchers moved on. Daniel Abadi introduced the PAC ELSC theorem. That's P-A-C-E-L-C. And it is a much more useful mental model for engineers.
Break that down for us.
So it formalizes what happens when things are normal. It states that, yes, in the case of a partition, you choose between availability and consistency, but the E stands for else.
Okay, else.
Else, when the system is running normally without a partition, you still have to make a brutal choice. You must choose between latency and consistency.
Let's dig into the mechanics of that else state, because this is where I think a lot of developers get tripped up. Even with a perfect network, infinite consistency isn't free.
No, not at all.
If I want a database cluster spanning like three continents to be perfectly in sync, meaning any read anywhere it gets the absolute latest right, I have to coordinate.
Right.
My database has to lock a record, send a message across the Atlantic Ocean, wait for the European node to write it to disk, wait for the acknowledgement to travel all the way back, and only then return success to the user.
Precisely. And that physical round trip takes time. So a system might offer absolute ironclad consistency, but the mechanical cost of that coordination makes the system incredibly slow. Right. Alternatively, the system can be blazing fast, but the only way to achieve that speed is to return local reads immediately, knowing full well that those nodes might be slightly out of sync with the rest of the cluster for you know a few milliseconds.
You're constantly trading time for truth.
You hit the nail on the head. Time for truth.
Which brings us to a massive semantic problem in our industry. Developers use the word consistency as this blanket term, but it actually means about five different things depending on the context.
No, easily.
I want to look at the Jepson analyses here, because Kyle Kingsbury does a brilliant job of separating these formal consistency models by essentially torturing databases to see where they break.
It's fun to read.
It really is. Let's start at the very top of the mountain. What is the absolute gold standard that everyone thinks they want?
The absolute peak is linearizability. Sometimes you'll hear it called strong consistency or atomic consistency. It is a single object guarantee, and it is entirely based on real-time ordering.
Okay. So real time.
Yes. The rule is simple. If operation A completes before operation B begins, operation B absolutely must see the result of operation A, regardless of which nodes they are talking to in the cluster.
I mean, it sounds like how the physical universe should naturally work, but mechanically in a distributed system, enforcing this is a nightmare, right? How do you even prove that operation A finished before operation B if they have it on opposite sides of the planet?
You've hit on the core problem, the speed of light. Let's say a user updates their profile picture on a server in New York, and the database acknowledges that write as complete.
Okay.
One millisecond later, their friend in Tokyo opens the app. If your system promises linearizability, that Tokyo user must see the new picture.
Right, because the write in New York finished before the read in Tokyo started.
Exactly. To mechanically guarantee that the Tokyo database node cannot just serve its local cache. It must proactively pause the read request, reach out to the global cluster, verify there are no newly committed rights floating around the network, wait for all state to converge and then serve the image.
Oh wow.
The latency penalty is immense because every read requires global coordination.
Okay, so linear ability is heavily bound to wall clock time and real-time sequencing. But then we have serializability, which I think is where the most confusion lies.
Oh, absolutely.
A lot of engineers conflate the two. If I look at the definition of serializability, it says it ensures that the outcome of a batch of concurrent transactions is equivalent to if those transactions were executed sequentially, one after the other. That sounds a lot like linearability.
It sounds similar, but the mechanical difference is profound. Serializability is a multi-object guarantee, meaning it covers transactions touching many rows or tables.
Okay.
But here is the critical distinction. Serializability has zero real-time constraints. It does not care about the physical wall clock time that requests arrive at the server.
Wait, I want to make sure I am fully grasping the mechanics here. You're saying the database can process a bunch of overlapping transactions. And as long as it can reorganize them into some valid logical sequence, it is considered serializable.
Exactly.
Even if that sequence doesn't match the order the users actually click the buttons.
Exactly. The database gets to dictate the timeline. A database could technically satisfy strict serializability by freezing all incoming read requests, processing a massive batch of writes, and then executing those reads against a snapshot of the database from like five minutes ago.
That is wild.
As long as the execution order doesn't violate internal transactional integrity, like foreign key constraints or check constraints, it is serializable.
So under serializability, if I run a script that writes a value and then immediately runs a new transaction to read that value, there is no strict real-time guarantee that my second transaction will actually see my first write.
Correct.
Because the database might logically order my retransaction before my write transaction in its internal manufacturer timeline.
That is entirely possible. Unless the database also implements linearizability on top of serializability, which is incredibly rare and slow. This perfectly illustrates why relying on, you know, ACID marketing copy on a database vendor's website is so dangerous.
Yeah, you have to understand the specific isolation level being enforced.
Exactly.
Let's go one step further down that isolation ladder to snapshot isolation. Because realistically, when a modern database claims to be highly consistent and performant, this is what they are actually using under the hood.
Almost always, yeah.
It utilizes multiversion concurrency control, which you, the listener, already know the disk level mechanics of from episode one. But I want to talk about the specific anomaly that slips through the cracks here. What is the blind spot of snapshot isolation?
The defining anomaly of snapshot isolation is called write skew. The mechanics of snapshot isolation dictate that when a transaction begins, the database gives it a frozen, consistent snapshot of the data. Okay. It can read freely without blocking rights. It can modify data in its own private memory space, and when it tries to commit, the database just checks one thing. Has any other concurrent transaction modified the exact same rows that this transaction modified?
So just a row level check.
Right. If not, it commits successfully.
Okay. That sounds pretty robust. It obviously prevents dirty reads and it prevents non-repeatable reads because your snapshot doesn't change. So how does write SKU actually break the application logic? Let me try to put a concrete architecture example to that. Imagine we are building a scheduling application for a hospital.
Okay, I like it.
The core business invariant, the rule we absolutely cannot break, is that there must always be at least one doctor on call in the emergency room, never zero.
Okay, I see where this is going. So Dr. Alice and Dr. Bob are both currently on call. The database shows on call equals true for both of them.
Right. Now Alice and Bob both get food poisoning at the exact same time. Tragic. Very. They both open the hospital app on their phones and hit the book off shift button at the exact same millisecond.
Mechanically, the database opens transaction A for Alice and transaction B for Bob. Under snapshot isolation, they both get an identical frozen view of the database. Right. Transaction A runs a select count where on call equals true. The snapshot returns two. Transaction B runs the exact same query in its isolated space. Its snapshot also returns two.
Now the application logic takes over. Transaction A checks the business rule. If I change Alice to off-call, will there still be at least one doctor left? The snapshot shows Bob is still on call, so the math is two minus one equals one. The rule is satisfied.
Right.
Transaction A updates Alice's record to on call equals false and issues a commit.
Simultaneously, transaction B evaluates the same rule for Bob. Its snapshot says Alice is still there. The math checks out. Transaction B updates Bob's record to on call equals false and issues a commit.
And here is the failure mode. The database's conflict detection only looks at the modified rows. Transaction A only modified Alice's row. Transaction B only modified Bob's row. There is no row level conflict.
None at all.
The database happily commits both transactions. Neither throws a concurrency error, but the end result is that both doctors are off call, the is empty, and the business invariant is destroyed.
That is right skew in a nutshell. They made decisions based on stale reads of each other's data, and because they didn't write to the same rows, snapshot isolation couldn't catch it.
Wow.
True serializability would have caught it by forcing one transaction to execute before the other, but again, that comes with a massive performance cost.
And solving these anomalies, whether you're trying to achieve linearizability across the globe or just trying to prevent right skew in a local cluster, always seems to come back to one fundamental inescapable physical problem.
The clock problem. All of these consistency models secretly rely on the concept of time. To order events, you need to know when they happened. But servers are notoriously comically terrible at telling time.
This is something I feel like we don't talk about enough in software engineering. A server's local clock is essentially a highly opinionated guess.
It really is.
You have the physical quartz crystal on the motherboard that vibrates to keep time, but that crystal's vibration frequency changes based on the ambient temperature of the server rack. So a server running a heavy CPU load literally experiences time differently than an idle server.
Exactly. And to fix that drift, we use network time protocol or NTP to periodically sync the server's clock with an external time surfer.
But NTP is communicating over the network.
Right, which means the time sync packet itself is subject to variable network latency. Plus you have leap seconds and garbage collection plazas where the CPU completely stops updating the clock for seconds at a time. Relying on a server's wall clock time for data consistency is a recipe for silent data corruption.
Which is where Google decided to just flip the table and solve the software problem with hardware. I want to talk about Google Spanner's true time architecture, because it completely revolutionized how we think about distributed consensus.
Oh, true time is beautiful.
They didn't try to write a better time sync algorithm. They fundamentally changed what a clock returns.
Right. TrueTime is a highly available distributed clock infrastructure provided to applications on all Google servers. But the genius of TrueTime is that when you ask it for the current time, it doesn't give you a timestamp, it gives you a time interval.
A window of uncertainty. Like I am 100% sure the actual time of the universe is somewhere between 2.000.001 and 2.000.01.
Precisely. It returns an array earliest and latest. It guarantees that the absolute true time falls somewhere within that mathematical window.
And how do they do that?
True time achieves this by outfitting every single Google data center with a redundant combination of GPS receivers and dedicated atomic clocks.
Wait, hold on. Atomic clocks? Like the ones in underground scientific bunkers.
Yep.
How does strapping a cesium clock to a server rack actually solve the distributed consistency issue? You still have network latency between the data centers.
That is the perfect question to ask because TrueTime doesn't eliminate latency or network physics. It exposes the exact boundary of the uncertainty to the database layer.
Okay, I'm tracking.
The GPS systems provide a global time reference, but GPS antennas can fail or experience interference. When that happens, the atomic clocks take over. Over time, even atomic clocks drift slightly, so the uncertainty window, the gap between earliest and latest, gradually widens.
And then it shrinks when it sinks back up.
Exactly. When the server successfully syncs with the time masters again, the window shrinks back down. Typically, this uncertainty window is incredibly small, usually between one and seven milliseconds.
Okay, so the database knows exactly how wrong its clock might be. How does Spanner use that interval to provide external consistency or linearizability without the massive latency penalties we talked about earlier?
It uses a mechanism called commit weight. Let's say transaction one finishes its work and spanner assigns it a globally meaningful commit timestamp.
Okay.
But because of the uncertainty window, spanner cannot be absolutely certain that this timestamp is definitively in the past from the perspective of another node in a different data center.
Because the other node's clock might be skewed slightly behind.
Exactly. So Spanner does something incredibly simple but powerful. It waits.
It waits.
It literally pauses the commit process and holds the lock until the latest time in its true time interval has physically passed.
So if the uncertainty window is four milliseconds, it just sleeps for four milliseconds.
Yes.
It guarantees that the timestamp it assigned to transaction one is now strictly globally in the past, beyond any shadow of a doubt, even accounting for the worst case clock skew of any other machine on Earth.
Yes. So when transaction two starts on a node in Europe, its earliest possible start time is guaranteed to be mathematically greater than transaction one's commit time.
That enforces the real-time causal ordering required for linear linearizability.
Exactly. The nodes don't have to constantly chat with each other across the Atlantic Ocean to figure out who wrote what first. They just look at their local true time intervals and wait out the uncertainty.
That is an incredibly elegant mechanism. It turns a distributed software concurrency problem into a localized hardware delay.
It really does.
But bringing this back to reality for the listener, most of us are not building our infrastructure on top of proprietary atomic clock networks. We are renting virtual machines on AWS or Azure.
Very true.
So how do typical open source databases manage consistency and leadership over completely unreliable networks without atomic clocks?
If you don't have hardware enforced true time, you are forced to rely on replication and software consensus protocols. This brings us to the operational reality of managing state. Let's look at replication tradeoffs first, because this is where the PAC elk latency versus consistency choice hits you right in the face. The core choice is between asynchronous and synchronous replication.
Asynchronous write ahead log replication is what most people are intimately familiar with. The primary node receives an insert statement, writes the transaction to its local disk, and immediately tells the application client, success, I've got it.
Yep, fire and forget.
Then, completely in the background, it asynchronously streams that write-ahead log over the network to the replicas. It is incredibly fast because the primary never waits for the network.
But the operational risk is significant. During an unexpected hardware failure, if the primary server dies before that background WAL transmission finishes, you have permanent data loss. Exactly. On the other hand, you have synchronous replication. In Postgres, for example, this would be setting synchronous commit to remote apply.
Right. In this mode, the mechanical flow changes entirely. The primary node receives the insert, writes it locally, but it will not acknowledge the commit to the client. It pauses the client connection.
Right.
It sends the wall over the network to the replica. The replica must receive it, write it to its own local disk, apply the changes to its memory structures, and send a network acknowledgement back to the primary. Only then does the primary tell the client success.
Which guarantees you will not lose a single byte of data during a failover.
But the performance cost is absolutely brutal. I want to quantify this for the listener because it's just math. Let's talk about the round trip time or RTT.
The killer of latency.
If you are running synchronous replication across two availability zones, say US East 1A to US East 1B, you might have a five-millisecond network RTT. That means every single write transaction you execute pays a mandatory five millisecond latency penalty.
And that is a best case scenario. If you try to run synchronous replication across regions, say New York to London, your RTT is governed by the speed of light through submarine fiber optic cables. That is roughly 70 to 90 milliseconds.
Wow.
If your application needs to do a thousand writes a second sequentially, and each write takes 90 milliseconds just waiting on the network, your application will just grind to a halt. You have fundamentally bound your database's right throughput to the physical speed of light.
Which is exactly why almost every production system in the world defaults to asynchronous replication. We collectively accept a small window of potential data loss in exchange for low latency and high throughput.
Yeah, we choose latency over consistency.
We call that window the recovery point objective or RPO. We will talk more about RPO when we get to backups, but it's crucial to understand that we are actively choosing data vulnerability to make the app feel fast.
So if we are mostly relying on asynchronous replication, and we know our nodes can get slightly out of sync, we need a rock solid mathematical way to manage who is the actual leader of the cluster, especially when network partitions happen.
We talked about quorum rings in episode three, like in Cassandra or DynamoDB. But when you need strong single leader election for coordination, like in systems such as etcd, console, or Kafka, the industry standard is the Raft consensus algorithm.
Yep, Raft is everywhere.
How does Raft actually solve this without true time?
Raft solves the consensus problem by dividing time into logical terms, and each term begins with an election. Mechanically, every follower node in a raft cluster expects a continuous stream of heartbeat RPCs from the leader.
Like a pulse.
Exactly. These heartbeats essentially say, I am still here, I am still the leader, do not start an election.
But if a switch fails or the leader's CPU spikes and it stops sending heartbeats, the follower's internal timer runs out.
Exactly. If a follower doesn't hear a heartbeat within a specific time frame, it assumes the leader is dead. It increments the term number, transitions its internal state to a candidate, votes for itself, and blasts out request vote RPCs to all the other nodes in the cluster.
But here is the vulnerability I always see when looking at these protocols. What happens if the network partition isolates the leader and multiple followers stop receiving heartbeats at the exact same millisecond?
The split vote problem.
Right. Their timers expire simultaneously, they all become candidates, they all vote for themselves, and no one can get a majority. Does the cluster just deadlock?
What's fascinating here is how elegantly Wrapped solves this exact deadlock, and it does it with something remarkably simple. It uses randomized election timeouts.
Wait, it just uses a random number generator.
Yes. Instead of every node timing out at exactly 150 milliseconds, each node randomly assigns its own timeout value, typically somewhere between 150 and 300 milliseconds.
It's just adding a tiny bit of jitter to the clock.
Precisely. Because the timeouts are randomized at the millisecond level, it is statistically highly improbable that two nodes will ever time out at the exact same moment.
That makes total sense.
One node will inevitably time out a few milliseconds before the rest. It becomes a candidate and blasts out its vote request before any other node even realizes the leader is fully gone.
And the other nodes receive the vote request, grant their vote, and reset their own timers.
The fast node wins the election, becomes the new leader, and immediately starts sending heartbeats to suppress any further elections.
It's beautifully simple. You solve a complex distributed synchronization problem by intentionally desynchronizing the nodes. But leadership in these systems often relies on granting leases. The cluster votes and says, node A, you have a lease to be the leader for the next 10 seconds.
Right.
Node A has to periodically renew that lease to keep the crown. But this brings us right back to the physical clock problem we discussed earlier.
Yes, and this is where things get dangerous. Leases are incredibly fragile if they rely on local node wall clock time for validation. If a node's clock gets forcibly adjusted forward by an aggressive NTP sync, it might suddenly think its 10-second lease has expired when it actually hasn't.
Or worse, the clock drifts backward and the node thinks it still holds the lease when the rest of the cluster has already moved on.
Exactly.
Let me throw a really provocative scenario at you, because this is something that burns teams all the time. What happens if a leader node experiences a massive garbage collection pause just as it checks the validity of its lease?
Oh, this is a classic distributed systems trap. And it was perfectly detailed by Martin Klepman in his famous critique of the red lock algorithm. Let's walk through the mechanics of the failure.
Okay, lay it out.
We have a cluster with node A and node B. Node A successfully requests a lock or a lease from the consensus cluster to write to a shared storage drive. The lease is valid for 10 seconds.
Okay.
Node A checks its local CPU timer. The logic says if current time is less than lease expiration time. The evaluation returns true. Node A sees it has 10 seconds left.
So as far as node A is concerned, it is the undisputed leader and it is safe to proceed.
Right. But at that exact microsecond, right after the if statement evaluates, but before the write command is sent, the Java virtual machine running on node A triggers a massive stop the world garbage collection pause.
Oh no.
The entire application process is frozen by the OS. The CPU is completely consumed by the garbage collector reclaiming memory. 10 seconds go by. Node A is completely silently frozen.
But the rest of the cluster is still ticking. They see that 10 seconds have elapsed. Node A's lease has officially expired.
Exactly. Node B requests the lease, and the cluster, seeing the lease is free, grants it to node B. Node B is now the leader. It connects to the shared storage, writes its critical data, and finishes its task cleanly.
And then finally, node A's garbage collection finishes. The JVM unpauses the threads. The process wakes up.
And here is the fatal mechanical flaw. Node A has absolutely no idea it was asleep for 10 seconds. The CPU doesn't tell the thread, hey, you missed a lot while you were out.
Right.
As far as the execution thread on node A is concerned, it just checked the lease, verified it was valid, and now it simply executes the very next line of code, which is sending the write command to the shared storage.
Oh man. So node A blindly reaches out and overwrites node B's data, completely corrupting the system state. It thought it was the leader, but it was actually a zombie operating on data from 10 seconds ago.
Yep. A total zombie node.
How do you even fix that? Because you can't just tell the server to check the clock again. The pods could happen between the second check and the write.
You can't fix it at the compute layer. The compute node is fundamentally compromised. It cannot trust its own perception of time. This is why relying on time-based leases for absolute correctness is a recipe for disaster. The architectural solution to this specific zombie problem is called monotonic fencing tokens.
Okay, break down the mechanics of fencing tokens for us. How does it stop the zombie?
A fencing token is incredibly simple. It is a strictly increasing integer. Every single time the consensus cluster grants a lock or release to any node, it issues a new higher token number.
Okay.
So let's replay the scenario. Node A requests the lock. The cluster grants it and assigns it token number 33.
Node A takes token 33 and then goes into its 10 second GC sleep. The lock expires.
Node B requests the lock.
The cluster grants it, increments the counter, and hands node B, token number 34. So the token number is always strictly going up. It's a monotonically increasing sequence.
Yes. Now here is the crucial architectural shift. The underlying storage system, the database or the object store, must be configured to actively validate these tokens. It cannot be dumb storage. Right. Node B connects to the storage, writes its data, and includes its token, 34 in the metadata of the write request. The storage engine processes the write and records internally. The highest token I have ever seen is 34.
Then Zombie Node A finally wakes up from its garbage collection app. It connects to the storage and tries to write its stale data, proudly presenting token number 33.
And the storage engine immediately rejects the right at the protocol level. It says, I have already processed a write with token 34. Your token 33 is mathematically older. You have been superseded. Access denied. Exactly.
Which actually transitions us perfectly to trade-offs and production failure modes part one. Because we have established how systems replicate data and how they elect leaders using raft and tokens. But here is the brutal irony. Replication is highly efficient at instantly propagating human errors.
Oh, yeah. It doesn't judge.
Which brings us right back to our cold open catastrophe.
And this is a mental hurdle that many infrastructure teams struggle with. We need to fundamentally separate the concepts of system availability and data durability. You must drill this into your mind. High availability is not a backup.
Yes. I want to emphasize this because I have seen so many startup teams rely entirely on their read replicas for data safety. They look at their AWS console and think we have a primary Postgres node and three synchronous replicas spread across three different availability zones. If a meteor hits the US.
But they are forgetting that database replication is completely agnostic to intent. It does exactly what you tell it to do, flawlessly and immediately. If a tired engineer accidentally runs drop table users on the primary node instead of their local dev environment, that command is replicated with breathtaking efficiency to all three availability zones in a matter of milliseconds. Your highly available architecture just executed a highly available, redundant deletion of your entire user base. It really is. True data safety requires temporal isolation, meaning isolation across time, not just across physical space.
Right.
You need backups and you need archives, but even those two terms get constantly confused by developers. A traditional utility like PG dump or MySQL dump creates an archive. It is a logical snapshot of the database state at a very specific moment in time, written out as a massive text file of SQL commands.
Archives are fantastic if you are migrating to a new major database version, or if you need to load a dataset into a data warehouse for offline analytics.
Sure.
But they are notoriously paralyzingly slow to restore. If you have a two-terabyte database, restoring a logical PG dump involves the database executing millions of insert statements one by one, rebuilding every single index from scratch. That restore could easily take 12 to 24 hours.
And if your production system is down, your business cannot survive that level of downtime.
Exactly.
Which is why serious production systems do not rely on logical dumps for disaster recovery. They use physical base backups combined with continuous WAL archiving. This is the foundation of point-in-time recovery or PITR.
Okay, let's get into the mechanics of PITR, because this is how the big players actually survive disasters. How does a physical base backup differ from a dump?
With a physical base backup, you are not extracting SQL data. You are taking a literal byte-for-byte binary copy of the underlying database files on the disk. Okay. You execute a command like pgstart-backup, which tells the database to flush its buffers and put it checkpoint marker in the write-ahead log. Then you use standard file copy tools to stream the raw data directory to a secure object store like Amazon S3. You might do this heavy operation once a week, say on Sunday at 2 a.m. That is your base backup.
But if I only do that on Sunday and the database crashes on Thursday, I've lost four days of data.
That is where the second half of PITR comes in. Continuous wall archiving. Every time the database finishes writing a segment of the write-ahead log, usually a 16-megabyte file containing all the raw binary transaction changes, a background process immediately uploads that tiny file to S3 alongside your base backup. This is happening continuously, 24-7.
So mechanically, if a disaster strikes on Thursday at 2 15 p.m., how do I get the database back? I don't just restore Wednesday night's state.
No. You spin up a new server, you download Sunday's physical base backup. Because it's just raw files, not SQL statements, restoring the base backup is only limited by your network bandwidth. It's incredibly fast.
Makes sense.
Then you configure the database to start in recovery mode. You point it out your S3 bucket full of wall archives, the database boots up, sees Sunday's data, and then sequentially replays every single archived 16 megabyte wall file in order, rapidly applying the binary changes right up until Thursday at 214 PM and 59 seconds.
It's like fast-forwarding a VHS tape from Sunday to Thursday. So but running a system like this requires strict metrics. You can't just set up a cron job for PITR and hope for the best. You have to actively quantify your RPO and RTO. So if you are a tech lead listening to this, these are the two acronyms you need to memorize.
Let's define them clearly.
RPO is the recovery point objective. It is a business metric and not a technical one.
Okay.
It defines exactly how much data your business can financially afford to lose. If your wall archiving script is configured to upload segments to S3 every five minutes, your technical RPO is five minutes. If the primary data center turns into a smoking crater, you will lose at absolute most the last five minutes of committed transactions that hadn't been uploaded yet. And you know, you have to ask the business is five minutes of lost orders acceptable? And RTO is the recovery time objective. How fast can you actually get the system restored, validated, and back online serving traffic to customers?
Exactly. If your two terabyte based backup takes one hour to download from S3, and replaying four days of wall files takes another 45 minutes, your total RTO is one hour and 45 minutes. That is how long the CEO will be pacing the floor.
Right.
But here is the absolute golden rule of site reliability engineering, and it is a hard lesson learned. An untested backup has an RTO of infinity.
I am going to repeat that because I want it etched into the listener's brain. An untested backup has an RTO of infinity. If you are just uploading files to S3 and assuming you are safe, you are in for a brutal awakening.
If you are not continuously automatically restoring your backups into an isolated staging environment and verifying their integrity, you do not actually have backups. You have a very expensive folder full of wishful thinking. When the crisis finally hits on a Friday night, you will invariably discover that the wall archive script silently failed due to a permissions error three months ago, or the AWS KMS encryption keys required to decrypt the backup were accidentally deleted, or the base backup is corrupted. You only know a backup works when you successfully restore it.
It is terrifying how many companies operate with Schrdinger's backup. But let's pivot and look at a disaster that doesn't involve the data center literally burning down, but still causes a complete outage. Let's revisit the alter table catastrophe from our cold open.
Oh yeah. The silent killer.
Because this is the most common way engineers accidentally nuke their own uptime. How does a simple metadata change take down a highly available cluster?
It comes down to the mechanics of lock cues and the dreaded access exclusive lock. In databases like Postgres QL, running alter table to perform certain operations, like adding a column with a default value or changing a column's data type requires an access exclusive lock on the entire table.
Entire table.
Yes. The database has to ensure no one is reading or writing to the table while it rewrites the underlying file structure. Crucially, the specific lock conflicts with absolutely every other type of lock, including simple select statements.
So let's trace the physical pileup that causes the outage. We have that long-running ORM query doing a massive monthly report. Because it's reading the table, it holds an access share lock on the orders table. It's doing a huge sequential scan, so it's going to take 45 seconds to finish reading.
The database execution engine says, okay, I need an access exclusive lock. I can't get it right now because the reporting query is currently holding an access share lock. I will wait politely in the lock queue.
Now, a customer on the website clicks checkout. The back end application tries to run a simple insert into the orders table. It requests a row exclusive lock.
The database lock manager looks at the queue and says, I see your insert request, but there is an alter table request waiting for an exclusive lock ahead of you in line. To prevent lock starvation, you must wait your turn behind the alter table.
And then another customer clicks checkout, and another and another. You were doing hundreds of requests per second. Every single incoming query to the orders table, even simple reads, now cues up behind the alter table, which is permanently blocked, waiting for the 45 second reporting query to finish.
Exactly.
Within a matter of seconds, your database connection pool is completely exhausted. The back end memory spikes as it holds all these open connections. The application threads all block, waiting for the database, and the entire web application grinds to a complete catastrophic halt.
This is exactly why you must adopt the expand and contract pattern for zero downtime schema migrations. Martin Fowler extensively documented this as part of evolutionary database design. The core philosophy is that you never make a destructive or table locking schema change in a single atomic step. You must break the migration into parallel phases deployed over time.
Okay, let's walk through the mechanics of expand and contract to solve a real problem. Let's say we have a massive users table with a full name column. The product team wants to split that into first name and last name. If we just run alter table, we cause the lock pile up. How do we do it safely?
Step one is the expand phase. You write a database migration to add the new first name and last name columns to the table. But the critical mechanical detail is that you add them as nullable columns, strictly without default values.
Why does that matter?
In most modern databases, adding a nullable column without a default is a pure metadata operation. It simply updates the system catalogs. It takes a fraction of a millisecond and does not require rewriting the table data. So the access exclusive lock is acquired and released almost instantly. Traffic flows normally.
Okay. So the columns exist, but they are empty. What is step two?
Step two is the migrate phase. You deploy a new version of your application code to production. This new code is configured to write to both the old full name column and the new first name and last name columns for any new signups.
Okay, dual rights.
Simultaneously, you run a background script that slowly, methodically iterates through all the historical rows in the table. It reads the old full name, splits it in code, and runs an update to populate the new columns for existing data.
And you do that backfill script in small chunks, like a hundred rows at a time, specifically to avoid locking huge swathes of the table or bloating the wall.
Exactly. Once the backfill script finishes, every row in the database has the data in both places. This brings us to step three, transition. You deploy another version of your application. This version exclusively reads from and writes to the new first name and last name columns. The application code completely ignores the existence of the old full name column.
And finally, step four is the contract phase. You wait a few weeks, you let the system bake. Once you are absolutely mathematically certain that the transition is stable and you will not need to roll back to the old application version, you run a final database migration to drop the old full name column.
The entire evolution is done in parallel, distributed across multiple application deployments and background scripts without ever holding a long table lock or causing a split second of downtime.
It absolutely requires operational discipline, and it turns a five minute task into a three week process, but it is the only way to evolve a schema at a massive scale without taking the business offline. But as we know, schema migrations are the only way to crush a healthy data store. This brings us to trade-offs and production failure modes part two. Cascading failures and traffic management.
Because even if your schema architecture is flawless and your queries are beautifully optimized with perfect indexes, the sheer volume of network connections and application retries can trigger a catastrophic cascading failure.
I want to directly address you, the listener, for a moment. If you listen to Web Architecture Season 2 Episode 7, you know exactly what I am about to say regarding connection exhaustion. Let's look at the mechanics of Postgres. Unlike thread-based databases, Postgres operates on a process per connection model.
Right.
Every single time your application opens a network connection to Postgres, the database server forks a brand new operating system process to handle it. Each one of those processes consumes a non-trivial chunk of baseline memory just to exist.
If you let your horizontally scaled application servers open connections unboundedly, say 50 auto-scaled app servers each opening 100 connections, you suddenly have 5,000 active Postgres processes.
That's a lot of overhead.
Massive. PG Bouncer acts as a highly efficient proxy layer. The thousands of application threads connect to PG Bouncer, which happily accepts thousands of lightweight virtual connections. But PG Bouncer maintains a very small, fixed pool of actual physical connections to the underlying Postgres database, say 100 or 200 connections, tuned specifically to the number of CPU cores on the database server.
Okay, so a bottleneck but a good one.
Exactly. When an application thread issues a begin statement, PG Bouncer temporarily assigns one of the 100 physical connections to that thread. As soon as the thread issues a commit, PG bouncer instantly snatches the physical connection back and hands it to the next waiting application thread.
So it multiplexes thousands of logical sessions onto a small, highly utilized set of physical database processes, keeping the database's memory footprint tiny and stable.
Yes. And you must combine that connection architecture with proactive query tuning using PG stat statements. This extension tracks execution statistics for all your queries. You use it to look at the shared blocks hit versus shared blocks red metrics. Right. You want to ensure that the queries multiplexing through PG bouncer are mostly hitting data that is already cached in RAM, that's shared blocks hit, rather than forcing the limited physical connections to wait on slow disk IO, which is shared blocks red. If your connections are stalled waiting on disk, the PG bouncer pool cue backs up and your app latency spikes.
But even with a perfectly tuned PG bouncer setup and 100% cash hit rates, you are still vulnerable to the most insidious operational failure in distributed systems, the retrystorm Oh the retry storm.
It is a classic cascading failure. It is a situation where the system's automated attempt to recover from a minor error actually amplifies the load, driving the system further and deeper into failure. Let's trace the mechanics of a real world request to see the math.
Okay.
Imagine a standard modern architecture: a front-end React application running in the user's browser, talking to an API gateway, which talks to a back-end microservice which queries the database.
Perfectly normal, three-layer setup.
Now, suppose the database experiences a momentary two-second network blip. A query from the back-end microservice times out and fails.
The developer who wrote the microservice added some naive resilience logic. If the database query throws a timeout exception, retry the query up to three times.
But the developer managing the API gateway also wrote retry logic. If the back end microservice returns a 500 error, retry the HTTP request three times. And the front-end engineer writing the React client also implemented retry logic. If the fetch API fails, retry three times to ensure a good user experience.
Oh no. I see where the multiplication is going.
The math is absolutely brutal. The user taps a button, the client sends one request, it fails because of the blip. The client retries three times. That is four total attempts hitting the API gateway.
Right.
The gateway receives those four attempts. Each one fails, so it retries three times for each. That is 16 concurrent attempts hitting the back-end microservice.
The microservice receives 16 attempts. Each one fails, so it retries three times. That is 64 attempts hitting the database for a single user tapping a button once.
Yep.
And if the database is struggling because it's slightly overloaded, dumping 64 times the normal traffic volume onto it will mathematically guarantee that it never recovers. It will just spiral into connection exhaustion and die.
And it scales exponentially.
Exactly.
If you have a slightly deeper microservice architecture with five layers, and everyone implements a three retry limit, you end up with 243 identical requests hitting the database for every single drop packet. You are essentially executing a highly coordinated, distributed denial of service attack against your own infrastructure.
So how do we stop the retry storm mechanically? We touched on this in Web Architecture, Season 2, Episode 6, but it is absolutely critical to reiterate here in the context of stateful data stores.
First, you must implement a server-wide retry budget. You don't just limit retries on a per request basis. You limit them globally across the service. The back end microservice should track a rolling window ratio of retries to successful requests.
Okay, ratio.
If it detects that more than 10% of its current outbound requests are retries, the system should actively trip a circuit breaker, stop retrying entirely, and just fail fast.
The logic being that if the failure rate is that high, the database isn't experiencing a blip. It is genuinely overloaded. Retrying is just adding fuel to the fire. You have to back off and let the database breathe.
Exactly.
Second, when you do retry, you must use randomized exponential back-off with jitter.
Jitter is the magic ingredient here.
I want to explain the mechanics of why. If a database goes down for five seconds and you have 10,000 client applications that all implement a strict mathematical one second back off.
Then exactly one second later, 10,000 clients will hit the database at the exact same microsecond. The database, which just came back online and has cold caches, will be instantly overwhelmed.
It will chale again.
The clients will receive the error, mathematically calculate a two-second backoff, wait exactly two seconds, and slam the database again with a synchronized wave of traffic. You create an oscillating wave of destruction.
By adding jitter, which mechanically just means injecting math.random into your back-off calculation, you desynchronize the clients. One client waits 1.2 seconds, another waits 1.7 seconds, another waits 0.9 seconds. You smear the retry traffic out over time. You flatten the curve, allowing the database to process the queue sequentially rather than concurrently.
And finally, to protect the database from these storms, we have to talk about load shedding at the database level itself. You must proactively quantify and configure your lock timeouts.
This goes directly back to our alter table nightmare. If a query is stuck waiting in a lock queue, it isn't doing nothing. It is holding a physical connection open in PG Bouncer, it is consuming RAM, and it is blocking other operations from proceeding.
You must set strict lock timeout and statement timeout values in your database configuration. If a transaction sits in a queue waiting for a lock for more than, say, two seconds, the database should aggressively kill the transaction and return an error to the application immediately.
It's better to kill it.
It is operationally far better to shed the load and fail one specific request quickly than to let the lock queue back up silently and take down the entire system. Doomed requests must fail fast to protect the whole.
So what does this all mean for you, the listener? We have covered a massive amount of technical ground today. We move from the theoretical physics of true time atomic clocks, to the mechanical reality of Jepson anomalies, and down to the practical horror of an access exclusive lock cue taking down your web servers.
We covered a lot.
I want to distill this entire deep dive into four non-negotiable guardrails for your production environments. If you are a tech lead building a modern architecture, write these down.
Guardrail number one.
Automated, regularly tested PITR restores. I will say it one last time. A backup isn't a backup until you have successfully restored it. If your restore process isn't fully scripted, automated and tested weekly in an isolated staging environment, you're operating without a safety net. You're trusting your company's survival to hope.
Guardrail number two.
Automated, regularly tested PITR restores. I will say it one last time. A backup isn't a backup until you have successfully restored it. If your restore process isn't fully scripted, automated and tested weekly in an isolated staging environment, you are operating without a safety net. You are trusting your company's survival to hope.
Guardrail number three, architectural traffic control. You must mechanically protect the database from the application layer. Use a transaction pooler like PG bouncer to prevent connection exhaustion. Implement strict lock timeout values at the database level to shed stalled load, and enforce global retry budgets with randomized jitter in your application code to prevent cascading retry storms.
And guardrail number four, strict adherence to expand and contract DDL for zero downtime schema migrations. No more rogue alter table commands run from a developer's laptop. Every single schema change must be phased, non-locking, and entirely decoupled from the application deployment lifecycle.
This raises an important final question, though. We spend all this time engineering for extreme high availability. We set up synchronous quorum commits, we pay for cross-region replication spanning multiple continents, all to ensure we never lose a single bite of data and survive a data center fire.
Right.
But if your data store handles failure perfectly, yet the cloud provider charges you exponentially for the cross-region network egress required to achieve that high availability. Are you actually designing for resilience? Or are you just systematically setting your infrastructure budget on fire for a disaster that might never happen?
That is a brutal, sobering question. And it is the absolute perfect transition for our next episode. Because architectural perfection means absolutely nothing if the unit economics destroy the business model. Next time, we are diving into deep dive episode seven, choosing a data store. It is the capstone decision framework, our series finale, where we take everything we've learned over the last six episodes and build a matrix for deciding exactly which database technology to use, when to use it, and how much it'll actually cost you at scale. You won't want to miss it. Until then, keep an eye on your lock cues.