A common database design practice is to use autoincrementing (unsigned) integers as the primary key for a table. While there are other approaches, such as UUIDs (GUIDs), integers have several advantages:
- reduces memory footprint per item
- smaller database indices
- easy to query (compare to encoded UUIDs)
However, they do have a significant disadvantage compare to other solutions if you have multiple masters. The autoincrementing integer must be unique, and if you have multiple masters, you need to ensure that both masters don’t create the same value - that is, the database must synchronize on every insert. This synchornization defeats part of the purpose of allowing multiple masters.
It is possible to have non-overlapping autoincrementing keys without synchronization on every insert?
Yes - use custom sequences to increment by more than 1 and then set each master with a different starting point:
|DB A||DB B|
CREATE TABLE currentvalues ( idcurrent bigint primary key CREATE SEQUENCE currentvalues_seq INCREMENT 10 OWNED BY currentvalues.idcurrent USING local; ALTER TABLE currentvalues ALTER COLUMN idcurrent SET DEFAULT nextval('currentvalues_seq'); SELECT setval('currentvalues_seq', 1);
The second master is similar, but has a different starting point
SELECT setval('currentvalues_seq', 2)
Disadvantages? You will exhaust the possible values sooner and you have to maintain this stateful information, particularly through restoring from backups.