Postgres Foreign Data Wrapper

PostgreSQL FDW extension is cool. It allows us to query a table on a remote database server as if it was a local one. Many different database server software types are supported.

As an example (must be superuser on our PostgreSQL instance) 👇

create extension if not exists postgres_fdw;
-- Cleaning instructions if needed
--drop foreign table if exists my_remote_table;
--drop user mapping if exists for my_user server remote_server;
--drop server if exists remote_server;
create server if not exists remote_server
  foreign data wrapper postgres_fdw
  options (host 'remote_server.domain', port '5432', dbname 'remote_db');
create user mapping if not exists for my_user
  server remote_server
  options (user 'my_user', password 'my_password');
create foreign table if not exists my_remote_table (
  id uuid,
  name text
)
  server remote_server
  options (schema_name 'public', table_name 'my_table');

Given that:

• We specify the correct network parameters and database name to access the remote database
• my_user exists on both the local and remote database servers, and the password of the remote user is correct
• my_table exists on the remote server with the suggested structure

We should be able to query my_remote_table on the local server and fetch records from the remote one.

Some considerations

🔒Beware of security

• In this example setup, the remote user password is stored in the local database (select * from pg_user_mapping; will expose it)
• We could consider alternatives like a Connection Service File → https://www.postgresql.org/docs/current/libpq-pgservice.html

⚡Beware of performance issues on cross-server joins

• The local PostgreSQL instance might not be able to correctly optimize queries due to its inability to access query statistics of the remote server
• So we should observe how queries are executed on the remote server and take action if needed (like using CTEs to force the query planner to act in a certain way)
• This article is great on the topic → https://www.crunchydata.com/blog/performance-tips-for-postgres-fdw

🚨Beware of unavailability of the remote server

• Using Foreign Data Wrapper introduces coupling between the two databases
• We could use materialized views for data that doesn’t need real-time freshness as an option to be resilient during downtime