Re: Sharing data between databases

On 05/12/2011 12:04 PM, Tim Uckun wrote:
>> or carefully structure your dblink joins so they can perform efficiently,
>> possibly using temp tables as a sort of materialized view.
>
> According to the documents unless you are writing procedural code with
> cursors when you touch the dblink view it will pull the entire
> table/recordset over.
>
>>
>> thats really all that the fancier database engines do behind the scenes...
>> and even then, distributed joins can be painful.
>
> I am not sure what they do but I have done this kind of thing in SQL
> server without any problems and with almost no performance penalty if
> the two databases were on the same instance.

On the same instance? Yes, that's a bit different. Many database engines
manage multiple "databases" that're really just namespaces within a
single storage engine. I don't know if that's how SQL Server does
things, but it's certainly how MySQL does for example, and people are
often confused by the way they can't SELECT from tables on another
database in Pg.

Unfortunately, Pg's design doesn't make it easy for a single backend to
have multiple databases open at once. Inter-database communication even
within a single Pg instance (cluster) requires multiple backends.

I sometimes think it'd be nice if Pg offered the ability to translate
schema to "databases", so it runs with a single database and multiple
schema, and you "connect" to a schema, MySQL style. It'd help people who
want to use multiple databases on a machine and query between them,
though of course it'd do nothing for people who want to do inter-machine
or inter-instance queries.

--
Craig Ringer