Understanding conflict resolution and avoidance in PostgreSQL: a complete guide(pgedge.com)

  Don’t do what? Consider the primary cause of conflicts: simultaneous operations occurring on the same data on different nodes. That happens because data may not have distinct structural or regional boundaries, or because a single application instance is interacting with multiple nodes simultaneously without regard for transmission latency.

  Thus the simplest way to avoid conflicts is to control write targets

  Use “sticky” sessions. Applications should only interact with a single write target at a time, and never “roam” within the cluster.

  Assign app servers to specific   (regional) nodes. Nodes in Mumbai shouldn’t write to databases in Chicago, and vice versa. It’s faster to write locally anyway.

  interact with specific (regional) data. Again, an account in Mumbai may physically exist in a globally distributed database, but multiple accessors increase the potential for conflicts.

  Avoid unnecessary cross-node activity. Regional interaction also applies on a more local scale. If applications can silo or prefer certain data segments on specific database nodes, they should.

  To solve the issue of updates on different database nodes modifying the same rows, there’s a solution for that too: use a ledger instead