DEV Community: Leonardo Bagno

I Stopped My PostgreSQL Primary 10 Times to Measure Real RPO

Leonardo Bagno — Sun, 19 Jul 2026 12:10:22 +0000

In my previous article, I used real measurements to show that lowering the DNS TTL does not necessarily make Route 53 failover faster. The lesson was simple: test the system instead of assuming how it behaves.

This time, I wanted to measure RPO.

RPO, or Recovery Point Objective, describes how much data may be lost when a system fails. Disaster recovery projects often claim an RPO of "near zero," but that number is not always tested.

My dissertation project, leomulticloud, originally had an RPO of zero because the application was stateless. In version 5.0, I am adding PostgreSQL logical replication between AWS and Azure. This raised a practical question:

If the primary stops while receiving writes, how many confirmed rows will be missing from the standby?

To begin answering it, I stopped my local PostgreSQL primary ten times.

The experiment

I built the first version of the lab locally using Docker Compose inside WSL2.

It used two PostgreSQL 18 containers:

pg-primary, representing the AWS database
pg-standby, representing the Azure database

They were connected through native PostgreSQL logical replication using a publication and subscription.

I also wrote a Python script called measure_rpo.py using psycopg. The script continuously inserted timestamped rows into the primary. It used a synchronous, single-threaded writer, so it waited for each insert to be confirmed before sending the next one.

During each run, I stopped the primary from another terminal:

docker stop pg-primary

After the primary became unavailable, the script compared the last ID confirmed by the primary with the highest ID present on the standby. The difference was the measured RPO.

I tested nominal rates of 10, 100, and 1,000 writes per second. Including an earlier baseline run, I performed ten runs.

The results

Battery	Nominal rate	Runs	RPO in rows
Baseline	10/s	1	0
A	10/s	3	0, 0, 0
B	100/s	3	0, 0, 0
C	1,000/s	3	0, 0, 0

All ten runs produced a measured RPO of zero. Every row confirmed by the primary was present on the standby when I checked it.

However, this does not mean that PostgreSQL logical replication guarantees zero data loss. It means that the standby kept up with this particular workload in my local environment.

The writer reached its limit first

The most useful finding came from the tests configured for 1,000 writes per second.

The actual rate was only around 330 to 345 writes per second, based on the timestamps stored in the database. My estimate while watching the test was much higher, but the recorded data corrected it.

The bottleneck was the test script, not the replication.

Because the writer waited for each insert to be confirmed, every cycle took approximately three milliseconds. Reaching 1,000 writes per second would require each cycle to finish in approximately one millisecond.

I therefore reached the limit of my writer before reaching the limit of the replication system.

What this experiment does not prove

This was a local baseline, not a test of full cross-cloud conditions. Replication used Docker networking, and the workload came from one synchronous writer that peaked at approximately 345 writes per second. The results therefore apply only to this environment and workload.

Conclusion

Across ten local runs, the measured RPO was zero. Under this specific workload, the subscriber contained every row confirmed by the primary before it stopped.

The next step is to repeat the experiment across AWS and Azure, with a real network between the publisher and subscriber. This should provide a more realistic measurement of cross-cloud replication RPO. The findings should come out soon.

The code, Docker Compose configuration, and CSV files from all ten runs are available in the leomulticloud repository.

Lowering your DNS TTL will not speed up your failover, and here is the data showing why

Leonardo Bagno — Sun, 12 Jul 2026 10:18:28 +0000

I spent three months measuring multi-cloud DNS failover for my final-year dissertation. The most useful thing I learned contradicts common advice, so I'm sharing the numbers.

The assumption

When the AWS US-East-1 outage hit in October 2025, half the internet went down with it. The standard answer to "how do I survive a cloud outage?" is DNS-based failover: run a standby in another cloud, point a Route 53 health check at your primary, and let DNS redirect traffic when it fails.

And the standard tuning advice that comes with it: lower your TTL to fail over faster. Set it to 60 seconds instead of 300 and you'll recover five times quicker. It sounds obvious. It's also wrong. Or at least, it's only a small part of the story.

The experiment

I built a deliberately minimal setup so the measurements would be clean:

A stateless Flask weather app deployed identically on AWS EC2 (eu-west-1) and an Azure VM (northeurope), via Terraform
Route 53 with failover routing and a health check on the primary
No Docker, no orchestration. I removed anything that added boot-time noise to the measurements

Then I killed the primary. Nine times, across three TTL configurations (60s, 120s, 300s), measuring from multiple DNS resolvers, plus three failback runs in the other direction. Every run logged to CSV.

The finding

Failover time (RTO) has two separate components, and most advice mixes them up:

1. Detection time: how long Route 53 takes to notice your primary is dead. In my runs this came out at ~48 seconds, and here's the key part: it was constant across every TTL configuration. TTL=60 and TTL=300 detected the failure in the same time, because detection is governed by Route 53's internal polling and quorum logic, not by the TTL advertised to resolvers. To reduce it, you need to adjust the health check interval and the failure threshold.

2. Propagation time: how long resolvers take to pick up the new record. This is where TTL matters, but it turned out to be resolver-dependent, and sometimes dramatically so. The clearest example: at TTL=300s, Cloudflare's resolver kept oscillating for over 325 seconds after the failover, while Google DNS stabilised much faster. Same record, same TTL, very different behaviour depending on who resolves your users' queries.

The full numbers:

Metric	Result
Mean failover RTO (all TTL configs, n=9)	48.0 s
RTO at TTL=60s (mean ± SD)	46.3 s ± 0.6 s
RTO at TTL=120s (mean ± SD)	45.3 s ± 2.9 s
RTO at TTL=300s (mean ± SD)	52.3 s ± 7.6 s
Failback RTO at TTL=60s (mean ± SD)	35.3 s ± 3.1 s
Route 53 detection time	~48 s, TTL-independent
RPO	0 s (stateless workload)
Total infrastructure cost	< €5 across all three iterations

Notice the pattern: TTL=60 and TTL=120 land within noise of each other, because detection dominates both. Only at TTL=300 does the RTO climb, and its standard deviation more than doubles, because now you're at the mercy of resolver caching. RPO was zero, but only because the app was stateless. A real application with a database would not get the same result.

Why this matters

If your failover is taking 60+ seconds and you respond by dropping the TTL from 300 to 60, you'll be disappointed. You're optimising the small, variable component while the large, constant one (health check detection) stays exactly where it was.

The practical order of operations is the opposite of the common advice:

Tune the health check first. Interval and failure threshold set the floor for your RTO. This is the big lever.
Then lower the TTL. It helps with the tail, not the floor.
And don't trust the TTL blindly, because resolvers between you and your users have opinions of their own.

Reproduce it for under €5

Everything is public and runs on free-tier-sized instances: the Terraform code, the measurement scripts, and the raw CSVs from every run.

Repo: https://github.com/leobagno-labs/leomulticloud
Archived release with DOI: https://doi.org/10.5281/zenodo.20189820

Total cloud spend for the entire experimental campaign was under €5. If you want to verify my numbers or run the tests against a different DNS provider, terraform apply gets you there.

New findings coming soon

The stateless app made RPO trivially zero, which avoids the hardest part of real disaster recovery: data. So this project is not finished. I'm already working on the next iteration, and the numbers will land here and in the repo.

Stay tuned and follow the repo for more to come.