Foreign Data Wrapper
- NOTE: If you are considering foreign partitioning due to scalability, then also consider a different database, like: CockroachDB
- Postgres lets you access data stored in other servers and systems
- postgres_fdw extention is available in "standard distribution" and can be installed with regular
CREATE EXTENSION command
- Use cases are enormous, specially in a microservice environment where you want to use data from other database, i.e. your authentication database is different from your application database
- Since the data flows through network layer, there is obvious latency and it is definitely slower than a table residing in the same database. However, you can utilize materialized views to overcome that.
CREATE EXTENSION postgres_fdw;
- Let’s assume you have another PostgreSQL server “box2” with a database called “box2db”. You can create a “foreign server” for this:
CREATE SERVER box2 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'box2', dbname 'box2db');
- map our user “alice” (the user you’re logged in as on current database) to box2 user “box2alice”. This allows “alice” to be “box2alice” when accessing remote tables
CREATE USER MAPPING FOR alice SERVER box2
OPTIONS (user 'box2alice');
- You can now access tables (also views, matviews etc) on box2. First, create a table on box2, and then a “foreign table” on your server. The foreign table does not hold any actual data, but serves as a proxy for accessing the table on box2.
-- on box2
CREATE TABLE foo (id int, someLegacyUUID varchar(30), name varchar(255));
-- on your server
IMPORT FOREIGN SCHEMA public LIMIT TO (foo)
FROM SERVER box2 INTO public;
-- Or add only specifc table with limited
CREATE FOREIGN TABLE IF NOT EXISTS <schema>.foo (
someLegacyUUID uuid no null, -- here casted from varchar(30) to uuid
name varchar(255)
)
SERVER box2;
-- If you want to use a different name for the table, in your imported fdt, you can do that as well
-- also you don't need to type schema name if it is public
CREATE FOREIGN TABLE IF NOT EXISTS my_fdt_foo (
...
)
SERVER box2
OPTIONS(table_name 'foo');
- Applications "DO NOT" have to know that tables are interacting with local or foreign. However large selects can be slower of course
- Joins and aggregations can be performed on such tables as well
- Foreign table works almost the same as local table, except latency of course!