Massively Parallel Postgres Backups

a day ago

PlanetScale automates backups for sharded Postgres and MySQL databases to minimize production impact by spinning up temporary EC2 instances per shard.
The steady-state backup process involves restoring the previous backup from object storage (e.g., S3), replaying WAL (mostly from S3, with recent segments directly from primary), and saving the consistent, encrypted snapshot.
Massive parallelism across shards enables high-speed backups (e.g., 32 TB spread over 8 shards backs up in ~2.8 hours; over 32 shards in 42 minutes), achieving rates over 50 GB/s and supporting petabyte-scale databases.
Initial backups use pg_basebackup to seed nodes and wal-g for durable storage, ensuring consistency with the steady-state format.
Backups are used not only for disaster recovery but also for database resizing (e.g., Metal databases with Local NVMe) and node replacement due to failures, leveraging restore-and-catch-up workflows.
For MySQL (with Vitess), the process is similar but uses VTBackup and binary log replication instead of Postgres WAL.
The ultimate goal is to make backups and resizes effortless, automated, and transparent to users.

Backup is often underestimated; common pitfalls include flawed techniques, untested backups, and confusion with RAID.
Cloud providers operate on a shared responsibility model; users are ultimately responsible for backing up their data.
A proper backup philosophy: data must be restorable quickly, in an open format, and consistent.

Canva uses encrypted browser cookies to store user session details, enabling fast authentication without per-request database lookups.
Session revocations (e.g., after logout or permission changes) are stored in an in-memory cache for near real-time updates and reliability.
At scale, seeding the in-memory cache during deployments caused a stampede on MySQL, requiring a more scalable solution.

FreeBSD is the preferred OS for backup servers due to its ability to partition services via jails and VMs, and ZFS provides flexibility and data integrity.
Backup servers should be hardened, encrypted (using GELI or ZFS native encryption), and ideally accessible only via VPN or LAN.
Two main backup strategies: zfs-autobackup for ZFS-based systems (pull-type) and BorgBackup for non-ZFS systems (push-type), with per-server jails for isolation.

PostgreSQL backups in Docker can be done using pg_dumpall with gzip, storing locally or to S3, with cron scheduling and pruning.
Traditional daily full SQL dumps are inefficient because even small changes require transferring the entire database each day, leading to high storage and transfer costs.
Plakar offers deduplication by storing chunks across snapshots, drastically reducing transfer and storage (e.g., ~64 GB/year vs ~12.5 TB for a growing 10 GB DB).

SQLite can be used in production for low-latency app servers by eliminating network overhead through local storage.
Enable Write-Ahead Logging (WAL) mode with PRAGMA journal_mode=WAL to allow concurrent reads and writes.
Manage checkpointing explicitly (e.g., PASSIVE or RESTART) to prevent WAL file growth and latency spikes.

Backup is often underestimated; common pitfalls include flawed techniques, untested backups, and confusion with RAID.
Cloud providers operate on a shared responsibility model; users are ultimately responsible for backing up their data.
A proper backup philosophy: data must be restorable quickly, in an open format, and consistent.

Canva uses encrypted browser cookies to store user session details, enabling fast authentication without per-request database lookups.
Session revocations (e.g., after logout or permission changes) are stored in an in-memory cache for near real-time updates and reliability.
At scale, seeding the in-memory cache during deployments caused a stampede on MySQL, requiring a more scalable solution.

FreeBSD is the preferred OS for backup servers due to its ability to partition services via jails and VMs, and ZFS provides flexibility and data integrity.
Backup servers should be hardened, encrypted (using GELI or ZFS native encryption), and ideally accessible only via VPN or LAN.
Two main backup strategies: zfs-autobackup for ZFS-based systems (pull-type) and BorgBackup for non-ZFS systems (push-type), with per-server jails for isolation.

PostgreSQL backups in Docker can be done using pg_dumpall with gzip, storing locally or to S3, with cron scheduling and pruning.
Traditional daily full SQL dumps are inefficient because even small changes require transferring the entire database each day, leading to high storage and transfer costs.
Plakar offers deduplication by storing chunks across snapshots, drastically reducing transfer and storage (e.g., ~64 GB/year vs ~12.5 TB for a growing 10 GB DB).

SQLite can be used in production for low-latency app servers by eliminating network overhead through local storage.
Enable Write-Ahead Logging (WAL) mode with PRAGMA journal_mode=WAL to allow concurrent reads and writes.
Manage checkpointing explicitly (e.g., PASSIVE or RESTART) to prevent WAL file growth and latency spikes.

Hasty Briefsbeta