Efficiently backing up petabyte-scale sharded databases requires decoupling the backup process from production traffic. This architecture ensures data consistency and high performance (50 GB/s) while continuously validating that backups are actually restorable.
Every 12 hours, a backup system must turn the entire state of a busy database into a consistent, encrypted snapshot, with no impact to production queries.
Such backups are crucially important and simultaneously something that most engineers would rather never have to think about.
Just make the backup work.
Our goal at PlanetScale is to make taking, scheduling, managing, and restoring Postgres and MySQL backups effortless.
Though this is what our customers experience from the outside, achieving this internally requires careful orchestration of cloud infrastructure and DBMS tooling.
It's especially interesting to look at backups for sharded databases, which requires spinning up backup-specific nodes, pulling data from object storage, and WAL replay, all with massive parallelism. These techniques allow for petabyte-scale databases to be backed up in hours, at rates over 50 GB/s.
Here we take a behind-the-curtain look at how to effectively back up a sharded database with massive parallelism.
Here is an example of a Neki (sharded Postgres) database with 8 shards, happily handling hundreds of thousands of queries per second of production traffic.
If sharding is a new concept to you, check out our recent post Making 768 servers look like 1 on how it all works. The first step in taking a backup depends on whether this is the very first backup or if we've taken one previously. We'll start with the steady-state case, which assumes a prior healthy backup already captured and stored in Amazon S3 (or similar object storage in other clouds).
Since a sharded Postgres database is many individual primary Postgres servers working together, we use regular Postgres backups as the building block for large-scale backups with Neki.
There are three ways to take backups in Postgres, which we've written about in detail previously. The best of the three, and the one that Neki uses, is combining filesystem backups with the replay of archived Write-Ahead Log (WAL). To summarize, the steps for this are:
T1T2; between T1 and T2, rows on disk may have been mutatedT1 and T2 to correct mutated dataWe could complete these steps directly on the primary, or perhaps one of the traffic-serving replicas. The problem is that this task utilizes a significant amount of IOPS and compute, especially for a large database. Our goal should be to minimize the impact a backup will have on production query serving.
Because of this, we take a different approach. We spin up a brand new set of EC2 instances, one per shard, to manage the backups on.
These new instances will be responsible for the majority of the work in the backup. Because we operate these sharded databases in clouds like AWS and GCP, dynamically spinning up tens or hundreds of instances for short time slices to complete backups is achievable. It adds a small amount of cost, but is worth it to minimize negative production impact.
The next step is restoring the most recent backup to each shard. Prior backups are stored in object storage. Here we will use Amazon S3 as the example, but the same applies to other clouds (like GCS in Google Cloud). These are streamed directly from here.
This approach requires temporary compute and transfers each shard's data out of and back into object storage. We accept that cost for two important reasons:
Once all the copying completes, these 8 servers have the exact state of each of the 8 shards from the previous backup, 12 hours ago.
We now must catch up each shard's old backup to match the present state of the database. This requires replaying all changes from the Postgres Write-Ahead Log between 12 hours ago and now.
A naïve approach would be to pull the WAL directly from the primary. This is problematic for several reasons:
On PlanetScale, all Postgres databases use wal-g to continuously archive their write-ahead logs. A sharded Neki database is similar, except each shard has a distinct WAL archive stream.
What if we streamed from there instead?
This almost solves the problem. The remaining problem is that Postgres archives WAL only after a segment is complete. If write traffic doesn't fill a segment sooner, our five-minute archive_timeout setting forces a segment switch so it can be archived. This means the newest changes may not have reached S3 yet. Thus, we take a hybrid approach. The system uses S3 for the majority of the replay, then streams the last ~few minutes of changes directly from the primary. Ideally, this last step takes on the order of seconds, not minutes or hours.
Putting this step together, back in our sharded database:
When every node has caught up replication to time T, where T is the timestamp we will log for the backup time, we stop WAL replication, freezing the point-in-time of the backup. Time T is saved to ensure we know the precise time, down to the second, included in this backup. The full backup is now consistent, with no smeared data. The final step is to encrypt and send these backups off to a new S3 bucket for safe keeping.
When complete, the backup nodes have served their purpose, and are decommissioned.
The cycle just described assumed we had a good backup from 12 hours prior to start with. In the steady-state this is true, but not for a brand new database.
Within 12 hours of a new database creation, pg_basebackup is used to seed a backup node for each shard.
pg_basebackup is a built-in PostgreSQL client utility that takes a physical backup of the entire database cluster: the data directory, tablespaces, and configuration needed for recovery.
Why not upload the pg_basebackup output directly? We use it only to seed the temporary backup nodes. The durable backup is created with wal-g, keeping the format and restore process identical for initial and steady-state backups.
The steps for this first one are similar, but slightly different than the steady-state:
pg_basebackup on each instance to copy data from its primary.T.wal-g to format, encrypt, and upload each backup to S3.Once this exists, all future backups happen with the restore -> catch up -> save flow.
Part of the reason we do so much parallelism is due to the nature of sharding. Whether it's 4 shards or 400, since each shard contains its own Postgres primary, we may as well take a rock-solid system (regular Postgres backups) and do it over and over on each shard.
A side-effect of this is that backups are VERY fast, even for large databases.
For every backup, the data transfer steps are:
Consider what it would take to complete this backup cycle on an unsharded 32 terabyte database. Backups on PlanetScale are compressed and stored with encryption at rest in S3. We'll assume that backups for this 32 terabyte database compress down to 20 terabytes. Therefore, we have
This equates to a total transfer volume of ~40,030 GB. If our various nodes and interconnects can sustain a 500 MBps transfer rate, this means the backup would take ~22 hours. Crucially, this means that if we want twice-daily backups, multiple backups would overlap. This would prevent us from meeting our recovery point objective (RPO).
This same database in Neki, spread over 8 shards storing ~4 TB each, performs quite differently. In total, we need to transfer, catch up, and store the same 40,030 GB. But if we can do so across 8 distinct backup nodes in parallel, each capable of 500 MBps, we reduce the total time to ~2.8 hours.
This gets better the more shards you add. The same data spread across 32 shards would back up in 42 minutes. Backup speed scales well. 100 terabytes on 100 shards backs up at ~the same speed as 1 terabyte on a single shard.
One reason to take backups is data safety. In the case of accidental data deletion or a one-in-a-million database disaster, backups (and the WAL) are an essential fallback.
But at PlanetScale, backups are also a core part of everyday database operations. For Metal databases specifically (ones with Local NVMe), backups are also used every time the database is resized.
Resizing a Metal database requires:
8 x 3 = 24 i8g.xlarge nodes. While running, we initialize 24 additional i8g.2xlarges to double the compute capacity.i8g.xlarge primary to a selected new i8g.2xlarge primary.We've now completed a full resize across all shards. Backups are used to facilitate the creation / catch-up of the new nodes. This process can take anywhere from minutes to hours, depending on how large the backups are and how much WAL there is to replay.
Backups are also used when a node needs to be replaced due to unexpected failure. If your average server lifetime is 5 years, you'll rarely notice a failure on a small database with a single primary. For a database with hundreds of shards, each with multiple replicas, the statistical likelihood for node failure in a given week or month increases significantly. When we see an individual node fail (say, a single replica in a shard), the process for replacement looks like:
Everything here has been about how we complete sharded backups and resizes on Postgres databases, powered by Neki. PlanetScale also operates large-scale sharded MySQL databases, powered by Vitess as our query routing / sharding layer.
The process for backing up and resizing these databases is quite similar! We have a whole separate blog on how this works, but the main difference is that we use VTBackup instead of the Postgres backup builtins, we use the MySQL binary log replication instead of WAL, and replication catchup is done from the primary instead of a hybrid between S3 and primary.
Ultimately, we want all this to be transparent to you. Taking a backup should be as easy as an automated schedule, or clicking a button. Resizing a database should be a single click or API call.
However, we also have a deep appreciation for the finer details of database operations. By taking a journey through the lifecycle of backups, we hope you have a new-found appreciation for how incredible database orchestration is.
If you find this fascinating, we're always looking for talented database engineers.
Continue reading on the original blog to support the author
Read full articleNeki solves the scaling limits of single-instance Postgres without sacrificing compatibility or forcing application-level sharding. It provides a managed path to horizontal scaling with online operations, making it easier to handle massive workloads while keeping standard Postgres tools.
Understanding sharded query lifecycles is essential for engineers scaling relational databases. It reveals the trade-offs in query routing and aggregation necessary for high-performance distributed systems.
Neki enables Postgres to scale horizontally by decoupling connection management and query routing from the core engine. It allows developers to use standard drivers while transparently handling sharding, complex distributed queries, and high connection counts.
Large tables in Postgres create systemic risks, from vacuum bloat to transaction ID wraparound. Understanding how to mitigate these through partitioning or sharding is critical for maintaining high-availability systems as data scales beyond the limits of vertical hardware.