Re: dblink performance

On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible <Alex(dot)Bible(at)ctg(dot)com> 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