The Desi Architect
Notes / Docker Sandboxes / PostgreSQL Synchronous vs Asynchronous Replication Tradeoffs
Docker Sandboxes · Tier 2: Data & Scale · Lab 24

PostgreSQL Synchronous vs Asynchronous Replication Tradeoffs

Freeze a replica on purpose and see what a commit does. In async mode it finishes at once. In sync mode it waits, and Postgres tells you it is waiting. Then bring the replica back, let one transaction skip the wait, and measure what the wait costs.

Time40 minutes
StackPostgreSQL 16Ports 15434, 15435
MemoryAbout 120 MB
Before thisLab 23PostgreSQL Streaming Replication L
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Explain asynchronous and synchronous replication, and what each risks.
  • Read pg_stat_replication.sync_state and a SyncRep wait event.
  • Explain why a commit that is waiting is not visible to other sessions.
  • Use SET LOCAL synchronous_commit = local for a write that does not need the wait.
  • Place async and sync on PACELC (PA/EL and PC/EC).

Before you start

Finish Lab 23 (Replication Lag and Stale Reads) first. You should know what a primary and a replica are. Docker Desktop must be running.