I am rearchitecting a single global web application (1 PostgreSQL database) to be multi-region (multiple PostgreSQL databases) to comply with data sovereignty requirements.

In doing so I will be separating data in multiple databases that will run completely isolated and separately. This means that the primary keys for each table will now have conflicts if the data is migrated between regions.

Example: A last user in the current system has an ID of 1000. Once the data is separated the the multiple database will continue to increase the ID based on their defined sequence. Therefore, in the new architecture, the Users in Region A would have IDs of 1001, 1002, 1003, etc. The users in Region B would also increment their IDs in a similar manner.

This wouldn't be an issue if the data remains separated but there are edge cases where a user will move between regions and their data needs to be portable. One solution is to prefix each Region database with a region code.

Example: Region A would have a region code of 300. So the newly incremented IDs for Region A would be 3001001, 3001002, 3001003, etc. Region B would have a region code of 400 and would increment IDs like 4001001, 4001002, 4001003, etc.

With the region prefix we should be able migrate users between regions without conflicting IDs.

I've read a few articles about this and some recommend changing the next sequence value and others recommend using database triggers to add the prefix to the serial ID as it is generated.

Any guidance as to best practices or pros or cons to this strategy are welcome.

  • 2
    you are putting logic into the PK value itself. That is usually a bad idea. I would simply use UUIDs for the primary keys then it's easy to move them between systems Jan 5 at 14:21
  • There is a lot of existing data with SERIAL IDs. It would be difficult to migrate them to UUIDs. If this were a new system, I think UUIDs would be a good solution.
    – Nate Bird
    Jan 5 at 14:24
  • 1
    Not ideal as has been mentioned, but BIGINT does give you (~) 19 digits to play with, or if you want to use Peter Eisentraut's UNSIGNED INT extension, 20 (1844674407 3709551616). If your serial is a 4 byte int, then you're golden - just add them onto new PK, reserving the right-most 10 digits for the purpose - you'll have a direct connection to the old system. Always messy mixing systems like this - be sure and test well! Jan 5 at 15:50

Instead of combining the RegionCode value into the primary key's value itself, rather make the primary key a composite key on the RegionCode field and the existing primary key field. E.g. the Users table's primary key would be a composite key defined on (RegionCode, UserId) where RegionCode is an integer based field, and UserId is an auto incremental field and retains it's original data type.

This is a better design implementation then trying to stuff them into the same value, and guarantees you won't ever have collisions between primary keys while also not necessarily having to change to using a GUID, and could even be potentially more performant than a GUID.

  • 1
    So each table would have an additional column with the RegionCode and then a composite key would be created using the primary ID and the RegionCode. If the data moves to a new region then the RegionCode would ensure the primary key is unique. Any new data created would use the new RegionCode and it would remain portable. I will have to look into composite keys some more. Thanks!
    – Nate Bird
    Jan 6 at 16:32
  • @NateBird No problem! It's actually an industry standard design pattern I've seen used in enterprise software such as ERP systems, CRM systems, etc.
    – J.D.
    Jan 6 at 17:42

Use UUID keys, with region_id uuid DEFAULT uuid_generate_v4 () as your primary key.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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