On a single Postgres server, primary keys stay unique because only one sequence hands out the numbers: a bigint identity column asks it for the next one, and no two rows ever get the same. Now put writers in several regions, each of them accepting inserts while the links between them are slow or down. Two regions that each ask their own sequence for “the next number” will hand out the same one, and the first time those two rows meet on a subscriber, one of them is rejected.
So which primary key should we give a table that several regions write at the same time? That’s the question of this article. It continues the logical replication series: Part 1 listed the ways to make ids that can’t collide, and Part 4 built the system that uses them, with a group of nodes in each region and every region subscribed to the others. Here we measure what each choice costs on the same workload: in size, in WAL, in joins, during failovers, and when clocks disagree.
We take the basics for granted. A primary key is a unique and non-null constraint. A generated id is a surrogate key, and it doesn’t replace the natural key, because it can’t stop two rows that describe the same thing. Database Normalization and Primary Keys covers both, and this article is about where the generated id comes from.
TL;DR We compared five primary keys for a table that several regions write at once. A sequence per region is the trap: when a region’s standby is promoted, the sequence can hand out numbers that were already used. uuidv7() is cheap and needs no coordination. A composite (region, id) key lets a publication filter on the region without a second index. A bigint made of a timestamp, the region and a counter does the same with a single column, as long as the clocks agree. And whatever the key, 32 bits is a wall.

