Point-in-Time Recovery (PITR) as a Product Promise: Backup Strategies at Scale

In the world of databases, there’s a significant difference between a “backup” and “recoverability.” For a DBaaS provider, a daily snapshot of data is not enough. If a customer accidentally deletes an important table at 2:05 PM, a backup from 2:00 AM is only partially helpful—they would lose an entire morning’s work.

The true product promise of a modern database platform is Point-in-Time Recovery (PITR). It allows restoration to any second within the retention period.

1. The Mechanics: Base Backups and WAL Streaming

To technically implement PITR, we use a combined approach of two components:

1. Full Base Backups: At regular intervals (e.g., daily), a complete image of the database is created and stored in Object Storage (Ceph RGW).
2. WAL Archiving (Write-Ahead Logs): Every change to the database is logged in so-called WAL files. These files are continuously—almost in real-time—streamed to S3 storage.

When a restore is requested, the system first applies the last base backup before the target time and then “fast-forwards” the WAL files to the exact second. The result: a consistent data state with minimal data loss (RPO near zero).

2. Scaling: Hundreds of Instances, One Backup Logic

The challenge for our customer was the volume: How do you manage this logic for hundreds of databases simultaneously without exploding storage or losing oversight?

• Central Orchestration: The database operator handles the automatic rotation of backups and the cleanup of old WAL files (Retention Management).
• Georedundancy as Standard: Every backup is immediately replicated to a second, physically separate region after creation. This way, the platform is prepared for the failure of an entire data center.
• Cost Control through Object Storage: Since WAL files can grow massively, storing them on cost-effective S3 storage (Ceph) is key to economical scaling.

3. The Restore Guarantee: Trust is Good, Automated Tests are Better

A backup is only as good as the successful restore. In practice, many DR (Disaster Recovery) concepts fail because the restoration was never seriously tested.

We have established automated restore tests for the platform. The system regularly creates test instances from the existing backups and verifies their integrity. Only then can the provider confidently offer an availability and security guarantee to their customers.

Conclusion: Data Security as a Core Value

For a DBaaS provider, PITR is not a “feature” but the lifeline for their customers’ business. By automating restoration to the second level and securing it georedundantly, we create the necessary trust to maintain business-critical workloads on the platform.

FAQ: Backup & Recovery

How far back can a customer go in time? This depends on the defined “Retention Policy.” Periods between 7 and 30 days are common. Since WAL files occupy storage space, this is often a differentiating factor between different pricing models of the provider.

Does WAL streaming affect database performance? By using asynchronous archiving and dedicated Object Storage, we keep the overhead extremely low. The database writes its logs locally anyway; the copying process to S3 storage happens in the background.

What happens in the event of a “corruption” error in the database? This is where PITR shines. If the data has been corrupted (e.g., due to a software bug), the customer can choose the point in time just before the corrupting event and restore a clean instance.

Can customers trigger restores themselves? Yes, that’s the goal of self-service. Through the API or the customer portal, the user selects the time and target instance. The platform takes care of provisioning the resources and applying the data in the background.