We have an application in which every customer has their own database, all running from our Postgres server.
There is a large, mostly static, database of information (chemical information), which each customer needs read access to. Lots of customer data points to this static db, with foreign keys.
Being that Postgres doesn't support FKs spanning databases, we currently have a copy of the static data in each customer's database. However, we'd much rather keep all the static data in one, separate, shared database, with each customer having read only access. (This should improve performance, make maintenance easier, and simplify the customer's database layout.) But we don't want to give up on Foreign Keys.
So, my questions are:
1.) Is there a way of separating, isolating, and sharing the shared data that will still allow FKs to it?
2.) If so, can this be done in a way which won't rely on non-standard PG extensions? That is, although we are using Postgres now, we don't want to structure our system in a way which will marry us to it - we'd like to retain the flexibility to migrate DBMS without having to rearchitecture the entire data.
Thanks
