PostgreSQL Foreign Data Wrapper (FDW)

Recently i've been exposed to the idea of foreign data wrappers in Postgres, and thought i'd share what i've learned so far.

What is it?

The idea behind it is simple: being able to access and interact with data stored in remote (external) databases (i.e databases that live on different machines) as if they were regular database tables.
PostgreSQL provides this feature through an extension called postgres_fdw.

Setup

The general steps to setup and use the extension are as follows:

1. Install FDW extension, postgres_fdw comes pre-installed with Postgres and is officially supported. But you can also use other extensions.
2. Create server, use the CREATE SERVER statement to define a connection to the remote data source.
3. Create user mapping, create a user mapping to associate a local PostgreSQL user with a remote user for authentication purposes.
4. Create foreign table, use the CREATE FOREIGN TABLE statement to define the structure/schema of the foreign table (column names, data types, ...etc). There is also a command to automatically import the schema.
5. Querying foreign tables, you can finally interact with foreign tables as if they were regular local tables. Using normal SQL (Select, insert, ...etc). A good tip here would be to optimize performance, you can do so by pushing down operations to the remote server, i.e asking the foreign server to perform the cost estimate (EXPLAIN command) by setting the use_remote_estimate option to true on the server or table level

• You can follow a step-by-step tutorial through a helpful guide i found, though one thing to note if you don't have another machine to test fdw is that you can actually use the same machine and 2 seperate databases.

References

1. https://www.crunchydata.com/blog/understanding-postgres_fdw
2. https://dbsguru.com/steps-to-setup-a-foreign-data-wrapperpostgres_fdw-in-postgresql/#