dblink performance

On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible <Alex.Bible@ctg.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

On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible <Alex.Bible@ctg.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 ;-)

reagrds,

Marc Mamin

Le 2011-12-07 11:14, Alex Bible a �crit :

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!

Alexander E. Bible

Hello,

one problem is when you join local data with remote data. The optimizer
falls short on this when finding good plans and executing. If you
frequently need to join with non-volatile remote data, it's generally
better to make a snapshot of remote tables. It all depends on volume and
usage patterns of databases.

Interesting link:
http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html

hope that's help
Bruno Lavoie
bl@brunol.com
bruno.lavoie@gmail.com

On Wed, Dec 7, 2011 at 2:47 PM, Marc Mamin <M.Mamin@intershop.de> wrote:

On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible <Alex.Bible@ctg.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

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

Thanks,
... so I don't really understand where all the time get lost in the
example I posted a few weeks ago:

http://archives.postgresql.org/pgsql-general/2011-09/msg00436.php

Marc Mamin

On Wed, Dec 7, 2011 at 3:37 PM, Marc Mamin <M.Mamin@intershop.de> wrote:

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

Thanks,
... so I don't really understand where all the time get lost in the
example I posted a few weeks ago:

http://archives.postgresql.org/pgsql-general/2011-09/msg00436.php

you wrote:
"select count(*) from
(
select * from dblink('a','select * from test_cluster')as t1(a int)
union all
select * from dblink('a','select * from test_cluster')as t1(a int)
union all
select * from dblink('a','select * from test_cluster')as t1(a int)
)foo

is about 5 times slower than an equivalent query run locally.

working with asynchron. queries (dblink_send_query) does not bring much
benefit
so that much time seems to be spent for transfer and merge"

it's not exactly fair to compare dblink to local query -- dblink
method requires having to marshal all the data over the protoocl and
un-marshal on the other end. I was seeing 3-5x times difference vs
local query but this is to be expected. note the 'union all' had
nothing to so with your performance problems. Also the querying
server can do a very special trick for count(*) since it only needs to
check tuple visibility that can't be done when doing select count(*)
from (<dblink_queries>).

My point up thread is that dblink is going to be comparable to other
methods that involve querying the data off the server and doing the
processing on the client side.

merlin