1

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?

4
  • Probably by replication it meant multi-master replication. The question has no [postgresql] tag and the linked answer does not even mention PostgreSQL. Jun 12, 2018 at 19:40
  • Yes no tags and no mention, which means it could apply to any db. So I asked about PG. Do you see potential issues with multi-master replication in PG?
    – ARX
    Jun 12, 2018 at 21:33
  • 1
    Maybe "replication" was more related to moving data between different environments. Regarding performance: I did some simple performance tests and while DBMS that use clustered indexes (MySQL, SQL Server) indeed are substantially slower for high volume inserts, this is not so much the case for Postgres. The inserts are slower but only by something like 5-10% in my tests That is still a lot if you need to sustain thousands (or more) of inserts per second constantly. For our use-case the advantages of UUIDs outweighed the performance disadvantages though.
    – user1822
    Jun 13, 2018 at 9:04
  • @a_horse_with_no_name Thank you for your useful input. The across environments scenarios makes sense. I see the point of inserts. And regarding joins, have you noticed any performance impact?
    – ARX
    Jun 13, 2018 at 15:36

1 Answer 1

2

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.

2
  • Using the increment step to separate server counts is a nice tip. Though, I think it fixes the number of masters you can use, right?.
    – ARX
    Jun 13, 2018 at 15:43
  • 1
    it's not a hard limit. in the examle above when you need eleven servers change one of the steps to 20, and use 20 on the new server too. start the new sequence in the gaps left buy this change.
    – Jasen
    Jun 14, 2018 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.