如何创建带筛选条件的PostgreSQL foreign table并避免每次查询源表数据

前置依赖 #

本地PostgreSQL实例和源库网络连通，已具备源库的读取权限。 #

完整实现脚本 #

步骤1：安装fdw扩展 #

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

步骤2：创建指向源库的外部服务器 #

CREATE SERVER source_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
    host '源库IP',
    port '源库端口，默认5432',
    dbname '源库数据库名'
);

步骤3：创建用户映射 #

将本地操作用户映射到源库的可读用户：

CREATE USER MAPPING FOR 本地操作用户名
SERVER source_db_server
OPTIONS (
    user '源库用户名',
    password '源库用户密码'
);

步骤4：创建带过滤条件的外部表 #

以仅拉取源表public.source_table中status=1且create_time >= '2023-01-01'的数据为例：

CREATE FOREIGN TABLE filtered_foreign_table (
    -- 字段类型和源表对应字段保持一致即可，不需要的字段可以不写
    id INT8 PRIMARY KEY,
    content VARCHAR(255),
    status INT2,
    create_time TIMESTAMP
)
SERVER source_db_server
OPTIONS (
    schema_name 'public',
    table_name 'source_table',
    -- 过滤条件，注意单引号需要转义为两个单引号
    where 'status = 1 AND create_time >= ''2023-01-01'''
);

执行到这一步，查询filtered_foreign_table已经只会返回符合条件的部分数据，但每次查询仍然会请求源库。 #

步骤5：创建物化视图实现本地缓存 #

通过物化视图将外部表数据缓存到本地，只有刷新时才会请求源库：

CREATE MATERIALIZED VIEW local_cached_view AS
SELECT * FROM filtered_foreign_table;

优化项：给物化视图加索引提升查询性能

CREATE UNIQUE INDEX idx_local_cached_view_id ON local_cached_view(id);

刷新缓存的命令

需要同步源库最新数据时执行即可：

-- 全量刷新，刷新过程中会锁表，不允许查询
REFRESH MATERIALIZED VIEW local_cached_view;

-- 并发刷新（PostgreSQL 9.4及以上版本支持，要求物化视图有唯一索引，刷新过程不锁读）
REFRESH MATERIALIZED VIEW CONCURRENTLY local_cached_view;

补充说明 #

• 如果需要定时自动刷新，可以搭配pg_cron插件或者操作系统定时任务配置刷新策略
• 如果源表过滤字段（本示例中的status、create_time）有索引，会大幅降低刷新时源库的查询压力
• 若业务要求强实时数据，无法使用缓存方案，只能直接查询外部表

内容的提问来源于stack exchange，提问作者user1632316