Thread: Sharing static data among several databases

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

"Robert James" <srobertjames@gmail.com> writes:

> 1.) Is there a way of separating, isolating, and sharing the shared data that
> will still allow FKs to it?

The only approach I know of would be to make all your customers use
independent schemas in one database, with isolation via appropriate
permissions, and have your shared data in another schema that all the
users have read access to.  Each user could have a search_path that
contained their schema and the shared schema, which should minimize
the amount of query-changing you'd need to do.

Note that this is basically what you would do with Oracle--it doesn't
have a concept of "database" really, just an "instance" that contains
schemas (which map more-or-less onto database users).

It would certainly be a change in your architecture, but how much work
it would be I don't know...

-Doug

Thanks.  For legal requirements, we need to keep each customer in a fully isolated, separate db.  (I'm not very familiar with schema - perhaps they can do the same thing...).

What about just dropping the FKs? Can we do cross DB joins? Are there significant performance penalties?

"Robert James" <srobertjames@gmail.com> writes:
> Thanks.  For legal requirements, we need to keep each customer in a fully
> isolated, separate db.  (I'm not very familiar with schema - perhaps they
> can do the same thing...).

[ shrug... ] If your lawyers insist on that, wouldn't they also object
to all customers linking to the same copy of the shared data?  They
should, if they know what they're about.

            regards, tom lane

On Nov 19, 2007 11:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [ shrug... ] If your lawyers insist on that, wouldn't they also object
> to all customers linking to the same copy of the shared data?  They
> should, if they know what they're about.
You're implying that that lawyers understand what database, schema
and shared data are ... ?


>                         regards, tom lane
Cheers,
Andrej

On Nov 19, 2007 12:29 PM, Robert James <srobertjames@gmail.com> wrote:
> Comedy aside, this makes a lot of sense:
> The shared data has nothing private in it at all - it's chemical info.
> Sharing it is no worse than sharing the application code, or the OS's
> libraries.  It's the customer's data which needs to be isolated.
I appreciate that.  But realistically if you had locked information isolation
down via permissions and appropriate views the information for each
customer would be as safe as it would using separate databases or
even servers.


Cheers,
Andrej

P.S.: I assume this was meant to go to the list, not to me as an individual;
try reply-all for this list.
--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

Comedy aside, this makes a lot of sense:
The shared data has nothing private in it at all - it's chemical info. Sharing it is no worse than sharing the application code, or the OS's libraries. It's the customer's data which needs to be isolated.

On 11/18/07, Andrej Ricnik-Bay <andrej.groups@gmail.com> wrote:

I appreciate that.  But realistically if you had locked information isolation
down via permissions and appropriate views the information for each
customer would be as safe as it would using separate databases or
even servers.

True.  But, being human, we make mistakes.  The simpler things are, the less likely the chance of mistake.  Sep. DBs = simple, dumb.  "ocked information isolation down via permissions and views" = complicated, smart.  When it comes to reliability, dumb is good.