Menu
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?
351
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.
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.
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.
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.
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
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
