Give each row one home region before you replicate across regions

How to do it

Two regions that accept writes to the same row will one day accept two different values within the replication delay. Then one write is lost or replication stops. The simplest design keeps one writing primary and read copies elsewhere, as Test what a failover loses describes. The next simplest routes each tenant's writes to its home region, as Pin each tenant's data to its region does. Only shared data with a merge rule, such as counters or sets, should take writes everywhere. Last writer wins by clock silently drops one update, so use it only where losing it is acceptable, and name the fields in writing. In PostgreSQL logical replication, a unique key conflict stops replication until someone fixes it. Some other conflicts are logged and skipped. Watch the conflict counters in pg_stat_subscription_stats and alert above 0.

Examples

SQL example

-- PostgreSQL 18: conflicts per subscription, expect zeros
SELECT subname, confl_insert_exists, confl_update_exists, confl_update_missing, confl_delete_missing
FROM pg_stat_subscription_stats;

JavaScript example

export function writerFor(tenant) {
  const database = primariesByRegion[tenant.homeRegion];
  if (!database) throw new Error(`no writing primary in ${tenant.homeRegion}`);
  return database;
}

Python example

def writer_for(tenant):
    database = primaries_by_region.get(tenant.home_region)
    if database is None:
        raise LookupError(f"no writing primary in {tenant.home_region}")
    return database

How to check

A design record lists each table with its writing region or its conflict rule. A test writes the same row in two regions within the replication delay, and the result matches the written rule. The conflict counter query below returns 0 in production, or an alert fired for each conflict.

Sources

• PostgreSQL logical replication conflicts

Related rules

• DAT-29 Test what a failover loses in Data and databases
• DAT-75 Pin each tenant's data to its region in Data and databases