My company currently has about 250 tenants/dbs (postgres). They are small in size, averaging 100mb each.
For ease of scale we want to merge them into a single database.
Problems:
- we have no foreign keys therefore no relations between tables built in the database schema.
- managing primary keys. We already have a column in each table with the uuid of the tenant, but it is not currently part of the key.
What is the best option? Recalculate each primary key during the merge process so that we have no conflict and each row is identified solely by the proper id or add the tenant uuid as part of the key?
And what could be a solution to merge around 250 dbs?