On Wed, Dec 7, 2011 at 2:47 PM, Marc Mamin <M.Mamin@xxxxxxxxxxxx> wrote: >> On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible <Alex.Bible@xxxxxxx> > wrote: >> > >> > Hello All, >> > >> > I'm currently on a development team utilizing PostgreSQL and we are >> looking into the possibility of using dblink to reference an external >> database (read only). Our system administrator and DBA were concerned >> about the performance impact that cross-database queries would have on >> a production application. Are there any known performance issues or >> anything of the like that I would need to know before pushing this >> issue further? I have been using PostgreSQL for the past couple months >> but this is my first time using dblink. I really just need an opinion >> from someone who has used this technology before. Thanks! >> >> dblink is a very thin wrapper for libpq. From the querying database, >> the overhead is pretty light -- basically the query is fired and the >> results are interpreted from text into whatever the database has in >> the receiving result via the various typein functions. For all >> intents and purposes, this is pretty similar to sending in queries >> over the regular sql interface. One gotcha of course is that libpq >> buffers the entire result in memory which can be dangerous, so be >> advised. >> >> To the receiving database, dblink queries are no different from any >> other query, except that they are not parameterized. Lack of >> parameterization and access to the binary protocol are the major >> downsides when using dblink. IMNSHO, dblink needs a variable argument >> call that uses the paramterized interface. Also support for binary >> transfer of data would be nice. >> >> merlin > > I find dblink being a nice tool as long as the data volume to transfer > remains low. > I've evaluated it to implement a clustered Postgres environment, but > gave it up due to the poor performances. > Still waiting for the binary transfer before the next try ;-) Binary transfer is not a super big deal in terms of performance actually in the general case. It's only substantially faster in a few cases like timestamp, geo types, and of course bytea. Lack of parameterization I find to be a bigger deal actually -- it's more of a usability headache than a performance thing. Also FYI binary dblink between databases is going to be problematic for any non built in type unless the type oids are synchronized across databases. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
