DBLINK vs Postgres_FDW, which one may provide better performance?

postgresql dblink postgres-fdw
markgiaconia picture markgiaconia · Jan 16, 2018 · Viewed 7.5k times · Source

I have a use case to distribute data across many databases on many servers, all in postgres tables. From any given server/db, I may need to query another server/db. The queries are quite basic, standard selects with where clauses on standard fields.

I have currently implemented postgres_FDW, (I'm, using postgres 9.5), but I think the queries are not using indexes on the remote db. For this use case (a random node may query N other nodes), which is likely my best performance choice based on how each underlying engine actually executes?

Answer

Dan picture Dan · Jan 16, 2018

The Postgres foreign data wrapper (postgres_FDW) is newer to PostgreSQL so it tends to be the recommended method. While the functionality in the dblink extension is similar to that in the foreign data wrapper, the Postgres foreign data wrapper is more SQL standard compliant and can provide improved performance over dblink connections.

Read this article for more detailed info: Cross Database queryng

Related questions

postgresql: INSERT INTO ... (SELECT * ...)

I'm not sure if its standard SQL: INSERT INTO tblA (SELECT id, time FROM tblB WHERE time > 1000) What I'm looking for is: what if tblA and tblB are in different DB Servers. Does PostgreSql gives any utility or has …

Read More
How to use (install) dblink in PostgreSQL?

I am used to Oracle and to create a dblink in my schema and then access to a remote database like this: mytable@myremotedb, is there anyway do to the same with PostgreSQL? Right now I am using dblink like …

Read More
Postgres Error: More than one row returned by a subquery used as an expression

I have two separate databases. I am trying to update a column in one database to the values of a column from the other database: UPDATE customer SET customer_id= (SELECT t1 FROM dblink('port=5432, dbname=SERVER1 user=postgres password=309245…

Read More