Massively parallel Postgres backups

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.

The backup lifecycle

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:

1. Begin a full backup of the on-disk Postgres database files at time T1
2. The backup completes at time T2; between T1 and T2, rows on disk may have been mutated
3. Replay the write-ahead log modifications between T1 and T2 to correct mutated data
4. Store the final result in a distinct storage location like Amazon S3

We 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.

Reusing old backups

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:

• Only recent WAL comes from the primary, minimizing production impact
• Every cycle proves the previous backup can be restored and replayed

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.

Replaying the WAL

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:

1. This would have nontrivial production impact. Replaying 12 hours of WAL on a high-churn database could take tens of minutes, or even an hour+.
2. Because we don't want too much server storage consumed by WAL, we continuously archive it to S3. Thus, we likely don't even have the full past 12 hours of WAL resident on the primary node.

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:

Completing the cycle

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 initial backup

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:

1. Spin up one new EC2 instance per shard.
2. Run pg_basebackup on each instance to copy data from its primary.
3. Configure each instance to replicate from its primary.
4. Replay WAL until every instance catches up.
5. Stop replication at time T.
6. Use 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.

Need for speed

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:

1. Restore old backup from S3
2. Catch up the backup using a hybrid of S3 + the primary
3. Send the new file back to S3

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

1. 20,000 GB transfer from S3
2. Catch up (Ex: 20 GB, compressed to 10 GB)
3. 20,010 GB back to S3

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.

What are backups used for?

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:

1. For every existing node in the sharded database, spin up a brand-new EC2 instance at the new size. In a database with 8 shards each with a primary and 2 replicas, we may currently be running it on 8 x 3 = 24 i8g.xlarge nodes. While running, we initialize 24 additional i8g.2xlarges to double the compute capacity.
2. Each of the 24 new nodes pulls down a copy of the most recent backup from S3, and begins catching up to the present state of the database via the archived WAL.
3. All nodes are initialized as standbys of the original primary, and complete final replication catch-up.
4. When all nodes are synchronized with the primary, a switchover is made from the old i8g.xlarge primary to a selected new i8g.2xlarge primary.
5. The smaller nodes are decommissioned, leaving only the larger primary and replicas.

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:

1. Initialize a new cloud instance of the same size as the previously failed one.
2. Use the process described earlier to restore a recent back-up to it, catch up the WAL.
3. Initialize as a follower of the original primary, synchronize data.
4. This new node can now actively serve read queries and/or serve as a new primary during a switchover or failover operation.

What about MySQL?

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.

Make it boring

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.