Postrgres FDW vs multiple clients

Question

Say I have two db1 and db2, with schema

  - db1.public.orders
  - db1.public.accounts
  - db2.public.orders
  - db2.public.accounts

If I wish to have access to two databases, would it be recommended to import the whole schema using fdw into db1, so that one client is used:

  - db1.public.orders
  - db1.public.accounts
  - db1.shard02.orders   (fdw linked to db2.public.orders)
  - db1.shard02.accounts (fdw linked to db2.public.accounts)

Or should I simply maintain two clients dbClient1 and dbClient2?

I wish to know which method is better in terms of connection robustness and performance when the two dbs reside in different data centres, clients using connection pools considered.

With fdw, each connection in the pool will also establish a tcp connection to the remote db, and all the queries will need to be pushed down and therefore a performance cost will incur. Why bother at all when two separate clients can be used?

score 0 · Answer 1 · answered Jun 15 '22 at 09:33

If you need to access two databases independently from each other, use two database connections. If you need to coordinate transactions across two databases, use two database connections with the two-phase commit protocol. If you need to join tables from different databases, use a solution with postgres_fdw for performance reasons.

Postrgres FDW vs multiple clients

1 Answers1