Understanding conflict resolution and avoidance in PostgreSQL: a complete guide

> An ounce of prevention is worth a pound of cure, after all.

  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

Best points are this summary near the end. IMO it's better to also allow for slower writes doing something simpler than trying to complex distributed stuff just so writes are quick. Users seem to have pretty long tolerance for something they understand as a write taking even many seconds.

Aside: Please for the love of God do not force emails to be unique and also require separate accounts per person (especially if you previously did not). This causes no end of irritation for elderly couples who only use a family email, and they WILL switch to your competitors (and I will help them do so) if yours gets in the way of them sharing like they always used to.

See also https://beesbuzz.biz/code/439-Falsehoods-programmers-believe...