Re: dblink versus schemas. What to use in this case?

Robert,

A couple of possible issues --

Running two different databases (on the same server) implies more use of system resources, but may be slightly more robust (i.e. one could go down but the other would still work). dblink is certainly slower than refering to a table in a schema, but it seems to work reasonably well, as least in talking between two databases on the same server (I've not really tested it between servers but it would obviously be slower depending on one's network).

If you want to enforce referential integrity then a schema is the way to go; schemas have permissions so it should be possible to lock out unwanted users almost as effectively as if there were two databases.

I have a database that uses schemas fairly heavily (in a postgres 7.4 installation) and I have had to edit my restore scripts -- partly because the script is confused by all of the ALTER statements I needed, and partly to get schema restored in the corect order. Version 8 may be better but might still need some manual editing of the restore script. I used schemas to simplify scripts and maitain references.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: KÖPFERL Robert [mailto:robert(dot)koepferl(at)sonorys(dot)at]
Sent: Wed 2/23/2005 6:33 AM
To: pgsql-sql(at)postgresql(dot)org
Cc:
Subject: [SQL] dblink versus schemas. What to use in this case?
Hi all,

I have got two database schemas. They're rather independend. Thus they are
in two databases. However there is one function that needs access to the
other database.

As I found out, I have two choices:
*Using schemas and put the schemas tighter together (via interdependencies).
Dumping distinct schemas is possible, however quistionable if a restore will
work with the dependencies.
*Using dblink. Dblink gives me a loose binding of the two databases. Some of
us care about the 'contrib' status of dblink. Speed (connect, query,
disconnect may sloww down) and it's deadlock resolv capabilities. However
the deadlock thingy is just a question of interest.

What should I do?
....to make one fcn of one DB access another DB's tables/fcns....

Thanks

---------------------------(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

!DSPAM:421c94cc83679760939685!