Re: Cross-DB linking?

You might want to consider using schemas to accomplish some of this.

You can backup individual schemas as of 7.4 (maybe 7.3, but I've not used
it in production, waiting for 7.4 to upgrade from 7.2)

performance will almost certainly suffer if you are doing cross db work,
so schemas help there.

I've never had any stability issues with Postgresql, and certainly not
from having everything in one database.

Other than the ability to spread your load across multiple machines,
7.3/7.4 and schemas should address all your concerns.

And no, you can't fk across databases.  You can get some primitive (but
quite functional) cross database action with the contrib/dblink package.

On Thu, 11 Sep 2003, Andrew Biagioni wrote:

> I am thinking of separating my data into various DBs (maybe on the same server,
> probably not) -- mostly for performance/stability/backup reasons -- but I have
> a considerable amount of foreign keys, views, and queries that would need to
> work across DBs if I were to split things the way I want to.
>
> Is it possible to have foreign keys / views / queries work across database
> boundaries?  On the same server / on separate servers?  If so, how?
>
> For example, I have:
>  - a table, A, with > 200 K rows which never changes;
>  - another table, B with < 10 K rows which changes frequently;
>  - and a third table, C, which joins A and B, i.e. has foreign keys into A and
> B, and changes rarely
>
> I would like to have A in one DB, dbA (possibly its own server);  B in another
> DB, dbB (possibly its own server);  and C either with A or with B (this one is
> not an issue per se).
>
> What I'm looking to gain is:
>  - dbA would be backed up/replicated religiously, and possibly on a server
> optimized for frequent writes
>  - dbB would NEVER be backed up, possibly on a server optimized for cacheing
>  - each database's schema would be simpler and easier to manage
>  - as the number of records and users grow, be able to distribute the
> computing/storage/memory load among various machines rather than have to
> upgrade the hardware
>
> Thanks in advance!
>
>         Andrew
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>