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.