在现有Postgres OLTP库中构建OLAP维度表及ETL流程可行吗？

你的方案是低成本、易落地的起步方案，非常适合当前场景：

• 无需额外引入数仓工具，利用现有Postgres资源快速验证维度建模的价值
• 预聚合的维度/事实表能直接满足仪表盘的低延迟查询需求，避免OLTP表的复杂关联
• 但需注意：同库运行ETL任务确实可能对OLTP业务产生影响，需通过增量逻辑优化和资源隔离降低风险

针对你担忧的ETL成本、数据一致性问题，Postgres 9.6的特性可以直接应对：

1. 避免高成本全表扫描 #

• 给OLTP订单表添加last_updated timestamp字段（默认now()，更新时自动刷新），每次ETL仅拉取last_updated > 上次同步时间的增量数据
• 为last_updated建立B-tree索引，确保增量查询的高效性
• 用专门的控制表（比如dw.etl_job_status）存储每个ETL任务的最后成功同步时间，避免硬编码或依赖外部存储

2. 解决重复导入与数据变更 #

• 利用Postgres 9.5+支持的INSERT ... ON CONFLICT DO UPDATE实现幂等Upsert：

  INSERT INTO dw.fact_sales (order_id, store_id, sales_amount, order_date, last_updated)
  SELECT order_id, store_id, amount, created_at, last_updated FROM public.orders
  WHERE last_updated > '2024-05-20 10:00:00'
  ON CONFLICT (order_id) DO UPDATE
  SET sales_amount = EXCLUDED.sales_amount, last_updated = EXCLUDED.last_updated;
  

  给事实表的order_id设置唯一约束，确保重复导入时自动更新最新数据

3. 处理延迟订单与数据修正 #

• 依赖last_updated字段捕获所有新增/修改的订单数据，包括后续状态变更（如退款、金额调整），每次ETL同步时自动覆盖事实表中的旧数据
• 若订单存在删除场景，可在事实表中添加is_deleted布尔字段，ETL时同步删除标记，而非物理删除（保留历史数据用于报表）

4. 降低ETL对OLTP的性能影响 #

• 开启只读事务执行ETL查询：SET TRANSACTION READ ONLY;，Postgres会跳过写锁，减少对OLTP业务的阻塞
• 先将增量数据导入临时表，再做维度关联与聚合：

  CREATE TEMP TABLE temp_orders AS
  SELECT * FROM public.orders WHERE last_updated > '2024-05-20 10:00:00';
  
  -- 基于临时表做关联聚合，再写入事实表
  INSERT INTO dw.fact_sales (order_id, store_id, sales_amount, order_date)
  SELECT o.order_id, d.store_id, o.amount, o.created_at
  FROM temp_orders o
  JOIN dw.dim_store d ON o.store_code = d.store_code
  ON CONFLICT (order_id) DO UPDATE ...;
  

• 利用Postgres 9.6新增的并行查询，在ETL聚合语句中添加SET max_parallel_workers_per_gather = 4;（根据服务器配置调整），加快聚合速度

• ETL调度：用Go编写定时任务（推荐robfig/cron库），嵌入现有后端服务或独立部署，负责触发ETL脚本、记录同步状态、失败重试
• 数据隔离：在Postgres中新建dw schema，将所有维度表、事实表、ETL控制表放在该schema下，与OLTP的publicschema物理隔离，同时给ETL用户分配public只读权限、dw读写权限
• 仪表盘对接：直接让前端查询dw下的预聚合事实表（如按天/门店聚合的fact_sales_daily），或基于维度模型编写简单的聚合查询，无需关联OLTP表

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