ClickHouse物化视图是写入触发的异步预聚合管道，配合ReplacingMergeTree可实现近实时去重与聚合，不锁表、无刷新阻塞，适合秒级更新场景。
预聚合表比物化视图更适合秒级更新场景
物化视图（MATERIALIZED VIEW）在 PostgreSQL 中刷新时会阻塞查询，哪怕用了 CONCURRENTLY，旧数据仍不可变，且刷新窗口内的新写入完全丢失。对运营看板、实时监控这类要求“延迟 ≤ 5 秒”的场景，它不是加速器，而是瓶颈源。
更可控的做法是建一张预聚合表（如 agg_1min），配合定时任务每分钟执行一次增量写入：
INSERT INTO agg_1min
SELECT time_bucket('1 minute', ts), tag_id, COUNT(*), AVG(value)
FROM raw_metrics
WHERE ts >= NOW() - INTERVAL '2 minutes'
GROUP BY 1, 2;关键点：
- WHERE 条件限定时间范围，避免全表扫描；
-
time_bucket()来自 TimescaleDB 扩展，若不用 TimescaleDB，可用DATE_TRUNC('minute', ts)替代； - 目标表主键建议设为
(bucket_time, tag_id)，便于 UPSERT 或去重； - 不要依赖自动刷新，用外部调度器（如 cron / Airflow）控制节奏和失败重试。
ClickHouse 的 ReplacingMergeTree + MATERIALIZED VIEW 是真流式预聚合
ClickHouse 不同于传统数据库：它的 MATERIALIZED VIEW 本质是写入触发的异步管道，配合 ReplacingMergeTree 引擎能实现近似实时的去重与聚合，且不锁表、不落盘临时结果。
典型结构：
CREATE TABLE raw_events ( ts DateTime, user_id UInt64, event_type String, amount Decimal(10,2) ) ENGINE = Kafka; -- 或 MergeTree <p>CREATE MATERIALIZED VIEW mv_user_daily ENGINE = ReplacingMergeTree(version) PARTITION BY toYYYYMMDD(ts) ORDER BY (user_id, ts) AS SELECT user_id, toDate(ts) AS dt, sum(amount) AS total_amount, count(*) AS event_cnt, max(_timestamp) AS version FROM raw_events GROUP BY user_id, toDate(ts);
注意：
-
version字段必须存在且参与 ORDER BY，否则ReplacingMergeTree无法识别新旧版本； - 写入原始表即自动触发聚合，无需手动 INSERT；
- 查结果时需加
FINAL（如SELECT * FROM mv_user_daily FINAL）才能看到合并后结果，但会牺牲部分性能； - 若只读最新状态，可改用
SummingMergeTree，省去FINAL开销。
MySQL 和 PostgreSQL 都该避免在 OLTP 主库上跑预聚合
哪怕你给 sales 表加了完美索引、调大了 sort_buffer_size，只要预聚合 SQL 还在主库执行，就可能拖慢订单写入——因为大查询会争抢 buffer pool、锁元数据、触发长事务 WAL 写入。
更稳妥的路径是解耦：
- 用
mysqldump/pg_dump或逻辑复制（如 Debezium）把增量变更同步到专用分析库； - 在分析库中建预聚合表，并关闭 autovacuum（PG）或定期
ANALYZE（MySQL），减少后台开销； - 聚合任务用低优先级运行（PG 加
SET statement_timeout = '30s';，MySQL 加LOW_PRIORITY）； - 如果业务允许 T+1，直接用离线数仓（如 Hive/Trino）做天粒度汇总，OLTP 库彻底减负。
预聚合字段类型必须和原始字段严格一致
一个常被忽略的失效点：user_id 在原表是 BIGINT，但你在预聚合表里定义成 INT，或者用了 CAST(user_id AS CHAR) 做分组键——这会导致后续 JOIN 或 WHERE 查询无法走索引，甚至隐式转换引发全表扫描。
验证方法很简单：
- 查预聚合表的
DESCRIBE agg_table或\d agg_table，确认字段类型； - 执行
EXPLAIN SELECT * FROM agg_table WHERE user_id = 12345，看是否显示Index Scan； - 避免在聚合键上用函数、表达式或字符串拼接，比如
CONCAT('U_', user_id)—— 宁可提前在写入链路中生成规范字段。
预聚合不是“建张表再定时 INSERT”就完事，真正难的是字段对齐、类型守恒、链路可观测。一旦某个环节类型漂移或时序错位，下游所有报表都会悄悄不准，而这种问题往往要等业务方质疑数据时才暴露。
