Running Data Stores in Production
Transcript
Made automatically from the video's sound, so it may contain mistakes. Click a line to play from there.
Following our episode 5 breakdown of vector store trade-offs, let's dive straight into today's explainer. Up until now, we've spent a lot of time talking about how to choose a database engine.
But today, today we're shifting our mindset entirely. We're gonna focus on surviving the brutal, frankly unforgiving realities of day two production across your stateful tiers. This is all about what happens when your systems finally collide with the real world.
Picture this, and trust me, this happens way more often than any of us would like to admit. A tiny 50 millisecond schema change cues up behind a 10-minute analytics query. Because it's waiting, it instantly locks out all incoming checkouts.
Your application servers keep desperately trying to connect, which leads to total connection pool exhaustion. That chaos triggers an automated but completely blind failover script. It promotes a lagging replica whilst the old primary is somehow still accepting rights.
And just like that, congratulations, you've created a catastrophic dual primary split brain and permanently corrupted your data. So let's look at how we actually untangle that operational nightmare. Today we're covering the anatomy of a production outage, moving beyond the cap theorem, consensus and failover mechanics, high availability versus PITR backups, and finally, zero downtime migrations.
The anatomy of a production outage. Let's dissect exactly how that catastrophic failure we just talked about actually cascaded. We really have to accept a harsh truth here.
Your database won't die from bad SQL syntax. It's going to die from the sheer physics of distributed systems. Things like network partitions, replication lag, saturation, and untested restores. This perfectly illustrates the cascade.
Step one: an unguarded schema change, like an alter table, demands an access exclusive lock, but it's stuck waiting for a long-running read query to finish. Steps two and three, every single new select query cues up behind that waiting altar.
And step four, your application keeps retraining, completely starves the system of connections, and forces your infrastructure into making terrible, uninformed failover decisions. Getting beyond the cap theorem.
To truly understand the physics of distributed data, we've got to throw out that lazy pick two out of three cap theorem cliche. It's deeply misleading, because actual network partitions are quite rare, yet we're making latency trade-offs every single millisecond.
Daniel Abardi's Pac LC framework is vastly superior for this. It forces us to explicitly quantify our latency versus consistency trade-offs during normal operations. On one side, we have PCEC systems, think Google Spanner, or Cockroach DB using Raft and Paxos, which prioritise consistency.
On the other side, PAEL architectures like Dynamo or Cassandra will happily trade that consistency for lower latency and higher availability. These are concrete architectural choices, not just marketing fluff. Now, Japsen's hierarchy is brilliant at exposing the flaws in what we often assume is totally safe.
Take snapshot isolation. It gives each transaction a fixed snapshot of committed state. It feels perfectly safe, but it can silently corrupt your data via write skew anomalies.
Imagine the classic two doctors on call bug. A hospital requires at least one doctor on call. Both doctors check the system at the exact same time, they both see the other is on call, and so they both book time off.
Because their transactions modify disjoint sets of data concurrently, the system just allows it, completely violating the hospital's core rule. Unless you enforce strict serializability, your data integrity is absolutely at risk.
Consensus, failover, and fencing. Let's put some concrete numbers to this, using PostgresQL as our architectural example. We can map exact right latency budgets directly to your recovery point objective, or RPO.
If synchronous commit is set to just local, you get minimal latency, but you're risking real data loss. Move that up to remote right and you can survive a PostgreSQL crash, but maybe not an OS level crash, costing you a network round trip.
But if you demand strictly zero data loss, you must use remote apply. The catch? You suffer the highest right latency penalty because your primary literally has to wait for the standby to fully replay the transaction.
You just don't get something for nothing. So what on earth happens when a primary node gets hit with a massive garbage collection pause? Well, it freezes.
The rest of your cluster naturally assumes it's dead and elects a new leader. But then that old primary wakes up, completely unaware it was deposed, and it starts trying to accept rights. We call this a zombie primary.
To stop it from corrupting your storage, you absolutely need a monotonic fencing token. It's a strictly increasing counter, and the storage system simply rejects any rights carrying an older, lower token.
It is literally the only mathematical guarantee to block split-brain zombie rights. Distributed systems researcher Martin Kletman hits the nail on the head here. Standard asynchronous replication is utterly useless in this scenario. Without proper consensus algorithms, enforcing these monotonically increasing terms to protect your data's integrity, your highly available system is, well, it's just highly available at corrupting your data.
Let's dive into section four: high availability versus PITR backups. Right, time to address the single most dangerous assumption engineers make. You have to engrave this in your mind. Replication gives you high availability, but it is not a backup.
If a tired engineer accidentally runs drop table users, that destructive command will happily and perfectly replicate across all your highly available nodes in a matter of milliseconds. An untested backup is just wishful thinking when human error inevitably destroys your schema.
This matrix right here is your absolute survival guide. Notice that read replicas give you zero protection against human error. Logical dumps like PG underscore dump do protect you, but recovering massive datasets can take days, completely ruining your recovery time objective.
This proves that only physical base backups, combined with continuous while archiving, give you true point-in-time recovery. It's the only setup that lets you rewind the database to the exact second before a mistake was made, literally saving your data and probably your job.
Finally, section five, zero downtime migrations and pooling. Let's bring this back to the application layer and how we avoid that massive cold open outage we discussed at the very beginning. Honestly, it boils down to simple maths.
If you have 5,000 application containers in your fleet, and they are all fighting for just 50 Postgres backend processes, you are guaranteed to exhaust your memory, saturate the database, and crash the entire system. As we covered back in Web Architecture Season 2 episodes 6 and 7, layering transaction pooling, like PG Bouncer, is strictly non negotiable.
It multiplexes thousands of front-end connections into just a handful of back end connections. Pair that with aggressive load shedding and jittered retries, and you can actually survive massive traffic spikes without causing database saturation.
But wait, what about that 50 millisecond lock disaster? To prevent an innocent alter table from taking down the whole site, you have to ship schema changes using this four-phase expand and contract parallel migration playbook.
First, you expand by adding the new column, making the schema compatible with both old and new code. Second, the app dual writes to both. Third, you backfill the old data and switch reads over to the new column.
And finally, you contract and safely drop the old column. It requires discipline for sure, but it absolutely guarantees zero downtime. So this is your non-negotiable day two production SRE checklist. You must quantify your RPO and RTO in exact minutes.
You must automate and continuously drill your point in time restores. You absolutely have to enforce transaction pooling. You must guard all data definition language changes with a strict lock timeout. And you need to mandate expand and contract migrations across all your teams to keep your systems online and your data actually safe.
You now have the proper operational toolkit to keep a data store alive in the absolute harshest production environments. So take these guardrails back to your teams and start drilling those backups today. But before we wrap up, consider this.
Out of the hundreds of options out there, from document to graph, relational to column family, how do you actually choose the right one for your exact workload? We are going to answer exactly that question in our episode 7 series finale. See you next time on The Explainer.