SCADA历史数据库的存储优化

工业现场的SCADA（数据采集与监视控制系统）每时每刻都在产生海量数据。如果不加干预，历史数据库会在数月内膨胀至数百TB，导致查询卡顿、备份失败，甚至系统崩溃。优化存储的核心不在于购买更贵的硬盘，而在于“只存储有价值的数据”。

第一阶段：源头数据压缩

大多数SCADA测点的数据并非时刻都在变化。例如，一个水箱的液位在半小时内可能一直保持在 5.0米，此时每一秒记录一个 5.0 都是巨大的浪费。

1. 配置 死区（Deadband）压缩策略。

   进入SCADA服务器的配置界面，找到模拟量测点设置项。

2. 设定 合理的阈值参数。

   对于一般压力和液位传感器，将死区设置为量程的 0.1% 到 0.5%。例如，量程为 0-10MPa 的传感器，设置死区为 0.01MPa。只有当新数值与上一次存储的数值差超过 0.01 时，才触发写入动作。

   其判断逻辑如下：

   $$ |V_{current} - V_{last}| > \text{Deadband} $$

   如果满足该条件，则存储 $V_{current}$；否则，舍弃该数值。

3. 应用 这种策略到所有非关键高精度的测点上。

   对于温度、液位等变化缓慢的量，此举通常能减少 70% 以上的存储量。

第二阶段：实施多级降采样

即使使用了死区压缩，高频采集的原始数据依然过于庞大。我们需要对历史数据进行“分级处理”，保留秒级的实时数据，将分钟级、小时级的数据用于长期趋势分析。

1. 创建 两个额外的数据表。

   在数据库中建立 history_minute 和 history_hour 两张表，结构通常包含：测点ID、时间戳、平均值、最大值、最小值。

2. 编写 定时聚合脚本。

   使用SQL作业或脚本语言（如Python），每隔 5分钟 运行一次以下逻辑：从实时表中读取过去 5分钟 的数据，计算平均值，然后 插入 到 history_minute 表中。

   SQL聚合逻辑示例：

   INSERT INTO history_minute (tag_id, time_stamp, avg_val, max_val, min_val)
   SELECT 
       tag_id,
       DATE_TRUNC('hour', time_stamp) + INTERVAL '5 minute' * FLOOR(DATE_PART('minute', time_stamp) / 5),
       AVG(value),
       MAX(value),
       MIN(value)
   FROM realtime_data
   WHERE time_stamp >= NOW() - INTERVAL '5 minutes'
   GROUP BY tag_id, time_stamp;

3. 执行 数据清理任务。

   在确认聚合数据准确写入后，删除 实时表中已经超过 7天 的原始秒级数据。这样实时表始终保持在一个轻量级的状态。

第三阶段：数据库表分区

当单张历史表的数据行数超过千万级时，索引效率会急剧下降。必须按时间将物理表切分为多个文件。

1. 切换 数据库管理工具到查询模式。

2. 执行 分区创建命令。

   以按月分区为例，将 history_minute 表改造为分区表。以下为PostgreSQL语法的示例：

   -- 创建主表
   CREATE TABLE history_minute (
       id SERIAL,
       tag_id INT,
       time_stamp TIMESTAMP,
       avg_val FLOAT
   ) PARTITION BY RANGE (time_stamp);
   
   -- 创建2023年10月的分区
   CREATE TABLE history_minute_202310 PARTITION OF history_minute
       FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');

3. 建立 自动化维护作业。

   编写 脚本，在每个月的月底自动 创建 下个月的分区，并在数据保留期限（如3年）到期后，删除 最旧的分区。

   删除旧分区比执行 DELETE 语句快无数倍，因为它直接删除物理文件，不会产生大量事务日志。

第四阶段：冷热数据分层存储

将访问频率不同的数据存放在不同的介质上，平衡性能与成本。

1. 评估 数据访问热度。

   • 热数据：最近 3个月 的数据，经常用于报表生成和故障追溯。
   • 冷数据：1年 以前的数据，仅用于合规性检查或长期趋势分析。

2. 迁移 冷数据至低成本存储。

   利用操作系统或数据库自带功能，将冷数据所在的表空间文件 移动 到大容量HDD机械硬盘阵列中，或者 压缩 为归档文件。

3. 保持 热数据在高速存储。

   确保实时表和近期分区所在的表空间位于高性能SSD固态硬盘上。

以下对比了不同存储策略的效果：

4. 挂载 磁盘卷。

   在Linux环境下，使用 mount 命令将SSD挂载到 /data/hot，将HDD挂载到 /data/cold。

   mount /dev/sdb1 /data/hot
   mount /dev/sdc1 /data/cold

5. 指定 数据库表空间路径。

   修改数据库配置，将 hot_tables 表空间的目录指向 /data/hot，将 cold_tables 指向 /data/cold。