HN
Today

Massively Parallel Postgres Backups

PlanetScale details their sophisticated, massively parallel system for backing up sharded PostgreSQL databases, ensuring consistent snapshots with minimal production impact. By orchestrating cloud infrastructure and advanced WAL replay, they achieve petabyte-scale backups in mere hours. This deep technical dive reveals the engineering prowess behind making database backups 'boring' for users.

11
Score
0
Comments
#10
Highest Rank
3h
on Front Page
First Seen
Aug 3, 6:00 PM
Last Seen
Aug 3, 8:00 PM
Rank Over Time
121010

The Lowdown

PlanetScale's article delves into the complex, often-overlooked world of database backups, specifically for large, sharded PostgreSQL instances within their 'Neki' system. It outlines their innovative approach to achieving consistent, petabyte-scale backups with minimal impact on production, emphasizing massive parallelism and careful orchestration of cloud infrastructure and DBMS tooling.

  • The Challenge: Backing up busy, sharded databases (e.g., 8 shards, hundreds of thousands of QPS) consistently and without impacting production is a critical, yet challenging task.
  • Core Method: They employ a combination of filesystem backups and Write-Ahead Log (WAL) replay, recognized as a highly effective PostgreSQL backup strategy.
  • Minimizing Production Impact: To avoid performance degradation, new, temporary EC2 instances are spun up for each shard to handle the intensive backup processes, leveraging cloud elasticity.
  • Steady-State Backup Flow: This involves restoring the previous backup from S3, replaying the WAL (primarily from S3 archives and the last few minutes from the primary), stopping replication for consistency, encrypting, and finally uploading the new backup to S3.
  • Initial Backup: For new databases, pg_basebackup seeds the temporary nodes, followed by wal-g processing to ensure consistency with the steady-state backup format.
  • Parallelism for Speed: Sharding allows independent, parallel backups across multiple nodes. This dramatically reduces backup times: a 32TB database could take 22 hours unsharded, but just 2.8 hours with 8 shards or 42 minutes with 32 shards.
  • Beyond Recovery: Backups are not just for disaster recovery; they are fundamental for routine operations like resizing 'Metal' databases and replacing failed nodes efficiently.
  • MySQL Parallels: A similar parallel strategy is applied to sharded MySQL databases using Vitess, with VTBackup and MySQL's binary logs.

Ultimately, PlanetScale aims to make these complex operations transparent and effortless for their users, despite the intricate engineering required beneath the surface.