Postgresql – In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all

postgresqlprimary-keyuuid

A popular answer on SO states that using UUIDs as primary keys has the following benefit:

Makes replication trivial (as opposed to int's, which makes it REALLY
hard)

I have been unable to find any evidence in PG's documentation (or anywhere else) to substantiate this claim. The PG docs mention nothing about this having any bearing on replication.

For me, if a replica is an exact copy of the master, the type of key used is irrelevant. So, is there something that escapes me? How would integer primary keys, compared to UUIDs, make harder to replicate your postgres db?

A related question is whether using UUIDs as primary keys slow down join operations (some people claim they do). Are there any benchmarks on this? Can anybody share experiences?

Best Answer

that claim seems spurious.

I do integer IDs with multi-master replication, it's not hard.

I set the step of the sequences to such that two servers cannot use the same id number.

alter sequence foo_id_seq set increment_by=10;
select setval('foo_id_seq',2);  -- or 3 or 4 etc...

but if you can do it natural keys are even easier to replicate.

Related Question