Обсуждение: Sharing data between databases
What is the preferred way to share tables between databases? I read about dblink but it doesn't seem optimal because it needs to pull in the entire query every time the view is referenced so it might be highly inefficient if I am trying to join a dblinked table with a "local" table. Cheers.
On 05/12/2011 08:48 AM, Tim Uckun wrote: > What is the preferred way to share tables between databases? > > I read about dblink but it doesn't seem optimal because it needs to > pull in the entire query every time the view is referenced so it might > be highly inefficient if I am trying to join a dblinked table with a > "local" table. There isn't a good one for PostgreSQL, really. Most people use replication tools like slony, londiste or bucardo to replicate shared tables between databases, so each has a copy. SQL/MED may improve the situation - at some point, possibly. -- Craig Ringer
That's disappointing. I guess I'll have to look towards a replication solution.
On 05/11/11 8:10 PM, Tim Uckun wrote: > That's disappointing. I guess I'll have to look towards a replication solution. > or carefully structure your dblink joins so they can perform efficiently, possibly using temp tables as a sort of materialized view. thats really all that the fancier database engines do behind the scenes... and even then, distributed joins can be painful.
> 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 05/11/11 9: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. > well, the idea is, you only query the remote server for the data you know you'll need. yeah, you can't arbitrarily do complex joins between large tables, you want to filter as much as you can with the remote query so the dblink only pulls across data you need. procedures might help too.
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
> > 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. > That's an interesting idea. Since I am building this app from scratch I suppose I could create different schemas for different applications instead of using different databases. I wonder how rails and active record can deal with that. I'll take a look and see. I am presuming of course that one can query across schemas.
On 05/12/11 8:38 PM, Tim Uckun wrote: >> 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. >> > > That's an interesting idea. Since I am building this app from scratch > I suppose I could create different schemas for different applications > instead of using different databases. I wonder how rails and active > record can deal with that. I'll take a look and see. > > I am presuming of course that one can query across schemas. most certainly. just prefix any objects or fields with schemaname.objectname. if you don't specify the schemaname it looks in the SEARCH_PATH, which defaults to "$user",public
On 05/13/2011 11:38 AM, Tim Uckun wrote: > That's an interesting idea. Since I am building this app from scratch > I suppose I could create different schemas for different applications > instead of using different databases. I wonder how rails and active > record can deal with that. I'll take a look and see. > > I am presuming of course that one can query across schemas. Yep, no problem at all with that, schema just let you categorize tables/functions/etc into namespaces. -- Craig Ringer