Massively Parallel Postgres Backups

The Mission: Every 12 hours, a system must transform the entire state of a high-traffic database into a consistent, encrypted snapshot—all without slowing down production queries.

While most engineers prefer to treat backups as a "set it and forget it" utility, the internal orchestration required to make this effortless for customers is complex. At PlanetScale, we've developed a method for sharded databases that leverages massive parallelism, allowing us to back up petabyte-scale data in a matter of hours at speeds exceeding $50\text{ GB/s}$.

---

🏗️ The Backup Lifecycle

To understand the process, consider a Neki (PlanetScale's sharded Postgres) database consisting of 8 shards, managing hundreds of thousands of queries per second.

(If you are unfamiliar with sharding, refer to our guide: "Making 768 servers look like 1".)

The Strategy: Filesystem + WAL

Postgres offers several backup methods, but Neki utilizes the most robust combination: filesystem backups paired with Write-Ahead Log (WAL) replay.

The conceptual flow is as follows:

1. Start a full disk backup at time $T_1$.
2. Finish the backup at time $T_2$.
3. Because data changed between $T_1$ and $T_2$, replay the WAL modifications to resolve inconsistencies.
4. Save the final state to object storage (e.g., Amazon S3).

Avoiding Production Friction

Performing this on a primary node or a production replica would consume excessive IOPS and CPU, potentially lagging user queries. To prevent this, we use a dynamic infrastructure approach:

• Dynamic Provisioning: We spin up a fresh set of EC2 instances (one per shard).
• Isolation: These temporary nodes handle the heavy lifting.
• Cloud Agility: In AWS or GCP, launching hundreds of instances for a short window is cost-effective and ensures production stability.

---

🔄 Reusing Previous Snapshots

Rather than starting from scratch, we restore the most recent backup from S3 to our new temporary nodes.

Why move data out of and back into S3?

• Reduced Load: Only the most recent WAL segments are pulled from the primary.
• Continuous Validation: Every 12-hour cycle serves as a "fire drill," proving that the previous backup is actually recoverable.

Once the transfer is complete, our 8 temporary servers mirror the state of the 8 shards as they existed 12 hours ago.

---

⏩ Replaying the WAL

To bring the 12-hour-old data up to the present, we must replay the WAL.

The "Wrong" Way vs. The "PlanetScale" Way

Technical Implementation

We use wal-g for continuous archiving. However, Postgres only archives WAL segments once they are full. To ensure we don't have huge gaps in our archives, we use a specific configuration:

archive_timeout = '300s' (5 minutes)

This forces a segment switch every five minutes, ensuring data is pushed to S3 regularly. The final "catch-up" phase works like this:

1. Bulk Replay: Pull the majority of the 12-hour window from S3.
2. Final Stream: Stream the last few minutes of changes directly from the primary node.
3. Duration: This final step typically takes seconds, not hours.

---

✅ Completing the Cycle

Once every node has reached the synchronization point $T$ (the official backup timestamp), we freeze the replication.

Final Checklist:

• All shards synchronized to time $T$.
• Precise timestamp $T$ logged for point-in-time recovery.
• WAL replication halted.
• Data encrypted and stored.

By treating backups as a massively parallel compute problem rather than a database maintenance task, we ensure that even the largest databases remain safe without ever compromising the performance of the live application.