Using PostgreSQL FDW and Materialized Views for Efficient Data Synchronization

The author, a PostgreSQL DBA at Qunar, introduces the challenge of synchronizing data between PostgreSQL databases or clusters, especially when data volumes are large and manual methods cause slow queries or replication lag.

To address these issues, the article explores PostgreSQL's support for the SQL/MED standard through Foreign Data Wrappers (FDW), which allow remote data to be accessed via ordinary SQL queries. It details the evolution of FDW support from read‑only in PostgreSQL 9.1 to full write capabilities and advanced features such as remote join, aggregation, and parallel execution in later versions.

Example FDW commands are provided to create a foreign server, user mapping, and foreign table, followed by a simple SELECT that retrieves data from the remote table as if it were local:

school_info_backup=# create server school foreign data wrapper postgres_fdw options(host 'remotehost',port '5432',dbname 'school_info');
school_info_backup=# create user mapping for local_dba server school options(user 'remote_dba',password 'xxxxxx');
school_info_backup=# create foreign table instructor_fdw(id int, name varchar(20), dept_name varchar(20), salary int) server school options(table_name 'instructor');
school_info_backup=# select * from instructor_fdw;

Materialized Views (MV) are then introduced as persisted query results that can be refreshed on demand. The article explains the need for view maintenance and shows how to refresh an MV, both normally and with the CONCURRENTLY option to avoid locking readers.

REFRESH MATERIALIZED VIEW department_mv;
REFRESH MATERIALIZED VIEW CONCURRENTLY department_mv;

A concrete MV example creates a materialized view on a department table, inserts new rows, and demonstrates how the view reflects changes only after a refresh.

CREATE MATERIALIZED VIEW department_mv AS SELECT * FROM department;
INSERT INTO department VALUES ('history','painter',50000);
REFRESH MATERIALIZED VIEW department_mv;

The combination of FDW and MV (FDW+MV) is presented as a powerful pattern: remote data is accessed via FDW, materialized locally for fast queries, and refreshed periodically. An architecture diagram (described in text) shows a remote source table accessed through postgres_fdw and materialized locally.

A real‑world case from Qunar demonstrates syncing a 20 GB order_detail table using FDW+MV, creating a foreign table, a materialized view for yearly sales, and refreshing it concurrently after adding a unique index.

# Define foreign server and mappings
\dew+
\des+
\deu+
\det+ order_detail
# Create materialized view
CREATE MATERIALIZED VIEW order_product_report AS SELECT order_detail.product_id, count(1) AS count FROM order_detail WHERE order_detail.create_time >= '2021-01-01 00:00:00+08'::timestamp with time zone GROUP BY order_detail.product_id;
# Refresh view
REFRESH MATERIALIZED VIEW order_product_report;
# Create unique index for concurrent refresh
CREATE INDEX CONCURRENTLY ON order_product_report(product_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_product_report;

The article concludes that FDW+MV provides a flexible, high‑performance alternative to traditional dump‑and‑reload or dblink approaches, especially for scenarios requiring real‑time access or periodic batch updates, and lists several reference resources.