Postgres replica conflicts: Part 2

Photo by Erik Mclean on Unsplash
  • A transaction t1 started at 10:01:00 on replica.
    At 10:01:30, a wal arrives at the replica (dead rows deletion change).
    At 10:01:35, t1 completes.
    As t1 which is in conflict with wal completed, wal gets applied now.
  • A transaction t1 started at 10:01:00 on replica.
    At 10:01:30, a wal arrives at the replica (dead rows deletion change).
    At 10:01:30, t1 is still running and it is now in conflict state with the wal.
    At 10:02:00, t1 is still running.Wal changes were delayed by 30 seconds from 10:01:30 to 10:02:00 but still t1 did not complete, hence t1 is cancelled and the wal is applied.
  • A transaction t1 started at 10:01:00 on replica.
    At 10:01:30, a wal arrives at the replica (dead rows deletion change).
    At 10:01:30, t1 is still running and it is now in conflict state with the wal which just arrived.
    At 10:01:56, t2 started and it is also in conflict(assume) with the wal.
    At 10:01:57, t1 completed.
    At 10:02:00, t2 is still running.As the wal which arrived at 10:01:30 has been delayed by 30 seconds, now its applied and t2 will be cancelled.
    The wal changes were delayed by 30 seconds for t1(10:02:00–10:01:30) and 4 seconds for t2.
  • In a standby server that exists primarily for high availability, it’s best to set the delay parameters relatively short, so that the server cannot fall far behind the primary due to delays caused by standby queries.
  • The one caveat about long-running queries and setting these values higher is that other queries running on the slave in parallel with the long-running one which is causing the WAL action to be delayed will see old data until the long query has completed.
  • If you increase the param to a too higher value, the value of the follower as a standby / backup diminishes as any other queries running on the follower may return stale data.

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Create and Run your very first app in Flutter from Terminal without any code

You’ve Got Kubernetes. Now You Need App-Focused Security Using Istio

Restricting Permissions in Strapi Admin Panel 🔐

My Journey as a Front-End Developer Intern

Raspberry Pi Pico — (Review)

A Seagulls! button

Deploying a Laravel app to Google App Engine with Gitlab CI

How iOS Application Development is Beneficial For Your Business?

How iOS Application Development is Beneficial For Your Business?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
none

none

More from Medium

How to: List GitHub default Branch protection rules

“One Button” Network Validation for Your IoT Device Installations

BLUMYCELIUM: Let the Elves Do It For You

Auto Tagging and using Semantic Versioning with GitHub Actions