3. 目标数据模型设计
目标数据模型设计是迁移工程的核心蓝图，决定了数据在目标平台上的存储效率、查询性能与可维护性。本节从数据库选型、模型规范化、分区与索引策略三个维度展开，确保设计成果既符合工程分析场景的业务需求，又能充分利用目标引擎的底层能力。
3.1 目标数据库选型
工程分析数据平台通常面临两类核心负载：高并发点查询（如设备实时状态）与大规模时序聚合（如振动趋势分析）。根据负载特征，推荐以下两种目标数据库组合方案：
| 对比维度 | PostgreSQL | ClickHouse |
|---|---|---|
| 适用场景 | 元数据管理、配置表、关联查询频繁的维度模型 | 时序数据写入、大宽表聚合、OLAP 分析 |
| 写入性能 | 单行写入约 1k TPS（事务型） | 批量写入可达 50k+ rows/s（列存） |
| 查询延迟 | 点查询 < 10ms，复杂 JOIN 受数据量影响 | 聚合查询 < 100ms，JOIN 能力弱 |
| 存储压缩 | 行存，压缩比约 2:1 | 列存 + LZ4/ZSTD，压缩比 5:1 ~ 10:1 |
| 生态成熟度 | 丰富插件（PostGIS、pg_stat_statements） | 原生支持物化视图、TTL、分布式表 |
选型建议：采用 PostgreSQL + ClickHouse 混合架构。PostgreSQL 承载维度表、配置表与业务关联层；ClickHouse 承载原始时序数据与预聚合结果表。两者通过外部表或数据同步工具（如 Kafka + MaterializedMySQL）打通。
3.2 模型规范化设计
目标模型遵循 3NF + 宽表冗余 的混合范式：维度层保持第三范式以减少数据冗余，事实层采用星型或宽表模型以提升分析性能。以下为规范化设计的关键原则：
- 维度表（PostgreSQL）： 设备维度、测点维度、时间维度、工厂/产线维度。每个维度表包含代理键（SURROGATE_KEY）与自然键（BUSINESS_KEY），并记录生效时间范围以支持缓慢变化维（SCD Type 2）。
- 事实表（ClickHouse）： 原始振动数据表、温度数据表、报警事件表。事实表仅保留外键（引用维度代理键）与度量值，避免冗余文本字段。
- 聚合表（ClickHouse）： 按小时/天预聚合的统计表，使用 AggregatingMergeTree 引擎存储中间状态（如 count、avg、max、min、quantile）。
命名规范： 表名采用
层级_主题_粒度 格式。例如：dim_device（维度-设备）、fact_vibration_raw（事实-振动-原始）、agg_vibration_hourly（聚合-振动-小时）。
3.3 分区与索引策略
分区与索引是目标模型性能的保障。以下策略针对 ClickHouse 与 PostgreSQL 分别设计：
3.3.1 ClickHouse 分区与排序键
| 策略项 | 配置示例 | 说明 |
|---|---|---|
| 分区键 | toYYYYMMDD(timestamp) |
按天分区，便于 TTL 过期与分区裁剪 |
| 排序键 | (device_id, timestamp) |
先按设备 ID 排序，再按时间排序，加速设备级范围查询 |
| 二级索引 | INDEX idx_alarm alarm_level TYPE set(0) GRANULARITY 4 |
对低基数列（如报警等级）建跳数索引，减少扫描粒度 |
| TTL | TTL timestamp + INTERVAL 90 DAY |
原始数据保留 90 天，聚合数据保留 365 天 |
3.3.2 PostgreSQL 分区与索引
| 策略项 | 配置示例 | 说明 |
|---|---|---|
| 分区方式 | PARTITION BY RANGE (updated_at) |
按月范围分区，适用于维度表的 SCD 历史记录 |
| 主索引 | CREATE INDEX idx_device_business_key ON dim_device(business_key) |
自然键唯一索引，加速关联查询 |
| 覆盖索引 | CREATE INDEX idx_device_type_status ON dim_device(device_type, status) INCLUDE (device_name) |
减少回表次数，提升过滤查询性能 |
| 部分索引 | CREATE INDEX idx_active_device ON dim_device(device_id) WHERE status = 'active' |
仅索引活跃设备，缩小索引体积 |
避坑提示： ClickHouse 的
ORDER BY 键同时决定数据物理排序与稀疏索引结构，不要将高基数且不用于过滤的字段（如随机 ID）放入排序键首位。PostgreSQL 分区表需确保查询条件包含分区键，否则会触发全分区扫描。
3.4 目标模型架构总览
下图展示了目标数据模型的整体分层与数据流转关系：
3.5 模型迁移检查清单
在进入实际迁移前，建议对照以下清单完成目标模型设计验证：
- 数据库选型确认： 是否已根据数据特征（时序/维度/聚合）确定每个表的目标引擎？
- 分区策略验证： 分区键是否覆盖了 80% 以上查询的过滤条件？分区粒度是否导致小文件过多（ClickHouse 建议每个分区数据量 > 1GB）？
- 索引覆盖度检查： 是否对高频查询的 WHERE 与 JOIN 字段建立了索引？是否存在冗余索引（重复或未被使用的索引）？
- 命名规范一致性： 所有表名、字段名是否遵循统一命名规则？是否已建立数据字典文档？
- TTL 与生命周期： 是否已为 ClickHouse 表配置 TTL 策略？PostgreSQL 分区表是否已规划自动清理脚本？
交付物： 完成本节设计后，应输出
target_model_design.md 文档，包含：数据库选型矩阵、ER 图（或表结构定义）、分区与索引 DDL 脚本、TTL 策略配置。该文档将作为后续迁移脚本开发的基线。