Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DBLINK vs Postgres_FDW, which one may provide better performance?

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?

like image 351
Mark Giaconia Avatar asked Jan 16 '18 18:01

Mark Giaconia


People also ask

What is postgres_fdw?

The postgres_fdw module provides the foreign-data wrapper postgres_fdw , which can be used to access data stored in external PostgreSQL servers. The functionality provided by this module overlaps substantially with the functionality of the older dblink module.

What is dblink in Postgres?

dblink is a module that supports connections to other PostgreSQL databases from within a database session. See also postgres_fdw, which provides roughly the same functionality using a more modern and standards-compliant infrastructure. Prev.

How does foreign data wrapper work?

A foreign data wrapper is a library that can communicate with an external data source, hiding the details of connecting to the data source and obtaining data from it. There are some foreign data wrappers available as contrib modules; see Appendix F.

What is FDW database?

There are now a variety of Foreign Data Wrappers (FDW) available which enable PostgreSQL Server to different remote data stores, ranging from other SQL databases through to flat file. This page list some of the wrappers currently available. Another fdw list can be found at the PGXN website.


1 Answers

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

like image 158
Dan Avatar answered Sep 28 '22 06:09

Dan