Reg - Postgresql cross database reference

We are currently using Postgres 14.5, where we are noticing some challenges with cross database reference.

Note: Earlier, we were using SQL Server where the cross database reference was very simple.

Everything was perfect until we handle the data of each customer separately but when we thought of consolidating the data(for EDW), we came to know that the cross database reference in Postgres is not as simple as SQL Server. We have to create fdw for each database(in the EDW database).

As the database count is less now, creating fdw for each database is not a big deal. But, when the customer count increase, the complexity will also increase(need to create fdw for each customer).

Moreover, if we want to make any schema modifications(such as increasing the length of the column), we have to make changes in all the fdw, which will make the setup even worse.

Now, we are thinking of rearchitecting the application to have a single database for all the client. But since, we are in Healthcare RCM business, data isolation is also a key point to be noted while handling data (shared database)

Can anyone suggest the best solutions for our requirement, out the below two.

1. Keep all the customer's data into single database and segregate them based on schema(one schema for each customer)
2. Our current setup - separate database for each customer

Additional question, why Postgres's cross database reference is so complex unlike SQL Server

CodePudding user response：

Put all the data in a single database, ideally in different schemas. You didn't have data isolation in SQL Server, because you can perform cross-database queries there, so you don't have to worry about it here.

Also, you can specify permissions on schemas in PostgreSQL, so you can control who gets to access the data in which schema, which should be enough to assuage your concerns about data separation.