Re: dblink performance

On Wed, Dec 7, 2011 at 2:47 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
>> 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
>
> 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