3

Can anyone suggest me what are the pros & cons of using dblink in Postgres?

I use dblink to connect to multiple database in my function in Postgres.

Peter Susanto
  • 85
  • 1
  • 6

1 Answers1

0

dblink is a great tool and it works very well.

The main cons are:

  • If you run a query between 2 servers not on the same network you will have a lot of latency and the performance will be very degraded
  • If you use dblink in a JOIN, in order to process this JOIN a lot of rows will have to be transferred from the remote server which will use bandwidth and degrade performance

If you have the possibility to use a single database for each query and not use multiple databases with dblink it will always be a better option.

Read also this interesting thread: http://www.postgresql-archive.org/dblink-performance-td5056145.html

Gab
  • 3,404
  • 1
  • 11
  • 22
  • 1
    You're much better off using foreign data wrappers, where some degree of condition pushdown/pullup through joins is supported in newer Pg versions. – Craig Ringer Mar 13 '17 at 04:31
  • Yes, foreign data wrappers that i know is i don't need to initiate connection every time i want query data, but in my case, my client database can be more than 1 (depends on number of client). – Peter Susanto Mar 13 '17 at 07:32