Foreign data wrappers are great to bring different kinds of data from many different sources, not limited to same database type, together.
However, since access to your foreign data table is usually remote, they can be slow.
To remedy this, combining materialized views with your fdts are a great way to get speedy data access. Not to mention, you can utilize foreign keys, indexes, some level of constraints and join other tables, with on-demand consistency model. Which btw, you cannot do with foreign data tables alone.
imagine very commonly occuring scenario where you have two applications, authentication platform and core application platform, both with their separate databases. In your core application, more often than not, you would want something in your core app from auth app.
Usually what you would end up doing is calling auth app through rest api or sync data through DML.
However, with fdw and materialized views, you can very easily remedy this, and in your application code, you would call this materialized view just like any other table. So, from developer’s perspective, they are using the same database.
Authentication Database
-- Authentication Database
-- hostname "authdb"
-- dbname authenticator
CREATE table if not exists public.user_entity (
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
email varchar(255),
email_verified bool NULL
company_id int4 NULL,
created_timestamp int8 NULL,
first_name varchar(255) NULL,
last_name varchar(255) NULL,
-- ...some other columns
CONSTRAINT constraint_pk_ue PRIMARY KEY (id)
);
CREATE TABLE public.user_role_mapping (
role_id int4 NOT NULL,
user_id int4 NOT NULL,
CONSTRAINT constraint_c PRIMARY KEY (role_id, user_id),
CONSTRAINT fk_user_rmp_to_user_entity FOREIGN KEY (user_id) REFERENCES public.user_entity(id)
);
CREATE TABLE public.application_roles (
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NULL,
company_id int4,
... constraints etc
);
-- Create index etc whatever
Application Database
-- Application Database
CREATE TABLE public.user (
id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
external_id uuid NOT NULL -- only piece that you need to keep in sync
);
-- Create Foreign data wrapper
CREATE EXTENSION postgres_fdw;
CREATE SERVER auth_fdw
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '<your-foreign-server-hostname', dbname 'authenticator');
CREATE USER MAPPING FOR <your-local-user>
SERVER auth_fdw
OPTIONS (user 'your-local-user', password 'some-password');
-- Now I can either import full schema of other database
-- or selectively add tables that I need
-- I will go with later approach, since you usually won't find that in tutorials
CREATE FOREIGN TABLE IF NOT EXISTS public.fdt_user_entity (
id uuid NOT NULL,
email varchar NULL,
first_name varchar NULL,
last_name varchar NULL
)
SERVER auth_fdw
options(table_name 'user_entity');
CREATE FOREIGN TABLE IF NOT EXISTS fdt_user_role_mapping (
user_id uuid not null ,
role_id uuid not null
)
server auth_fdw
options(table_name 'user_role_mapping');
CREATE FOREIGN TABLE IF NOT EXISTS fdt_application_roles (
id uuid not null,
company_id int4 null,
name varchar(255)
)
server auth_fdw
options(table_name 'application_roles');
-- Now you can query above created foreign tables.
-- You can even join them together or with other local tables
-- I will go with creating materialized view first, so my access is faster
CREATE MATERIALZIED VIEW IF NOT EXISTS user_mat_view
WITH (autovacuum_enabled = false) as
select
ue.email,
ue.first_name,
ue.last_name,
u.external_id,
u.id,
krd.name as role -- I can rename columns
from users u
join fdt_user_entity ue on ue.id = u.external_id
left join (
select kr.name, urm.user_id from fdt_application_roles kr
join fdt_user_role_mapping urm on kr.id = urm.role_id
where kr.name in ('admin', 'company_admin')
) as krd on krd.user_id = ue.id
where ue.email is not null;
-- I can now create indexes, since it is a materalized view on local database!
CREATE UNIQUE INDEX user_mat_view_id_idx ON public.user_mat_view USING btree (id);
-- Its good to mention that if you run EXPLAIN ANALYZE you won't have proper info, that is because we created table without auto vaccum
-- It is generally a good idea to refresh it right after creating it, anyways
-- Also, when refreshing we use "concurrently" to make sure no locks are created, otherwise refresh hard locks the table
-- concurrently also requires us to create unique index
refresh materialized view concurrently user_mat_view ;
// Now you can query on materialized view itself!
SELECT * from user_mat_view where id = 3;