CODEWITHSUNDEEP
databasepostgresqlamazon-rdsdistributed-system
ct101
ct101

Reputation: 1

Table primary key uniqueness across different / multi-region Amazon RDS postgres

I have an application that uses postgres database on one region (US West) containing several tables, one of which contains several hundred thousand records (let's call it "events" table with "eventId" as the primary key/Integer/auto-increment) I also recently cloned this database into another region (EU central) for users using the application on that region.

What I'd like to achieve is uniqueness of the primary key across the two databases such that if a new "events" record is created at US West, the primary key must NOT overlap with another "events" record that already exist at EU central and vice-versa. Since the database was cloned, any previous overlap doesn't matter in this case. I also need to do this for other tables such as Users, etc.

I looked into Postgres partitioning and sharding (via foreign data wrappers) but from my research, I cannot partition the original table (at US West) without copying it into another "partitioned" table which would require downtime (if my understanding is correct) In addition, I also needed to have to rename the tables (e.g. events_eu_central vs. events_us_west) which would require modifications to the application backend which may be extra work for the devs.

I'm about to look into something like a "dedicated ID-lookup" table on a database sitting on one region (e.g. US West) that can be used by other regions (via Foreign data wrappers). Then, during INSERT to "events" table, a database trigger runs and checks if the ID already exists.

I don't know if this is going to work or will cause a massive performance hit but would like to hear any ideas.

Upvotes: 0

Views: 50

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247865

There are two approaches:

  • use an uuid as primary key

  • create the sequences differently on both systems:

    id bigint GENERATED ALWAYS AS IDENTITY (START 1 INCREMENT 2)

    versus

    id bigint GENERATED ALWAYS AS IDENTITY (START 2 INCREMENT 2)

Upvotes: 0

Related Questions