6

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?

Mark Giaconia
  • 3,844
  • 5
  • 20
  • 42
  • 4
    upgrade to 9.6 or better to 10. The ability to push predicates and work to the remote server has been improved substantially since 9.5 –  Jan 16 '18 at 21:07
  • 2
    *I think the queries are not using indexes on the remote db*: find out the remote query with `EXPLAIN` and use `EXPLAIN` on the remote database to get the execution plan. – Laurenz Albe Jan 17 '18 at 08:27

2 Answers2

11

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

Dan
  • 1,771
  • 1
  • 11
  • 19
4

My solution was simple: I upgraded to Postgres 10, and it appears to push where clauses down to the remote server.

Mark Giaconia
  • 3,844
  • 5
  • 20
  • 42
  • 3
    To anyone who cares, if you are using datatypes from postgis (geometry etc) you will have to declare that extension in the foreign server definition, or the conditions in teh where clause will NOT be pushed to the remote server – Mark Giaconia Jan 08 '19 at 14:52
  • 1
    Did you have to use Postgres_fdw or blink to access the remote server? – dataconsumer Mar 12 '21 at 09:55