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 | RPO in seconds |
|---|---|---|---|---|
| Baseline | 10/s | 1 | 0 | 0.0 |
| A | 10/s | 3 | 0, 0, 0 | 0.0 |
| B | 100/s | 3 | 0, 0, 0 | 0.0 |
| C | 1,000/s | 3 | 0, 0, 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.
Top comments (1)
Good experimental discipline, especially correcting the claimed rate with stored timestamps. One subtlety for the next round:
docker stopis a graceful SIGTERM, so PostgreSQL can shut down cleanly, and the subscriber may continue applying WAL it already received before you compare results. That can measure eventual catch-up rather than the rows available at the actual failover decision. I would add abruptkill -9and network-partition cases, snapshot/promote the standby at a defined deadline, and compare acknowledged transaction IDs or commit LSNs rather than onlymax(id). A pipelined or concurrent writer will also let you test replication lag beyond the single-writer ceiling.