4. 关系数据库存储农业时序数据

温室环境监测产生的数据具有典型的时序特征：每台采集网关或传感器节点按固定周期上报温度、湿度、光照、CO₂浓度等指标，数据量随时间线性增长，写入频繁而查询多集中在时间区间与设备维度。关系数据库并非天生为时序场景设计，但借助合理的表结构、索引策略、批量写入与分区机制，PostgreSQL 完全能够胜任中小规模温室园区的数据存储需求。本节围绕“表怎么建、索引怎么加、数据怎么写、大表怎么切”四个关键问题展开。

一、表结构设计：三要素与字段类型选择

农业时序数据表的最小必要字段是时间戳、设备ID、指标值。以温室环境数据为例，一张典型的明细表可设计如下：

CREATE TABLE greenhouse_reading (
    ts          TIMESTAMPTZ NOT NULL,      -- 采集时间（带时区）
    device_id   INTEGER     NOT NULL,      -- 设备编号
    metric      SMALLINT    NOT NULL,      -- 指标编码：1温度 2湿度 3光照 4CO2
    value       REAL        NOT NULL,      -- 指标数值
    quality     SMALLINT    DEFAULT 0,     -- 质量标记：0正常 1可疑 2缺失补插
    PRIMARY KEY (device_id, metric, ts)
);

几个设计要点值得说明。第一，时间戳使用 TIMESTAMPTZ 而非 TIMESTAMP，因为温室可能跨时区部署或经历夏令时切换，带时区类型在存储时统一转为 UTC，避免区间查询出现一小时偏差。第二，指标采用编码而非为每个指标建一列，这种“窄表”结构在指标种类增加时无需改表，适合传感器类型多样的场景；若指标固定且查询常需横向对比，也可采用“宽表”（温度、湿度各占一列），代价是新增指标要执行 ALTER TABLE。第三，主键选择 (device_id, metric, ts) 而非自增 ID，既天然保证同一设备同一指标同一时刻不重复，又让主键索引直接服务于“按设备+指标+时间范围”的高频查询。

此外，value 使用 REAL（4字节浮点）而非 NUMERIC，是因为环境传感器精度通常在 0.1～0.01 量级，单精度足够且存储与比较开销更小；若涉及需要精确累加的计量数据，则应改用 NUMERIC。质量标记字段用于区分正常采集、异常可疑与补插数据，为后续清洗与统计排除提供依据，这一字段在农业场景中往往比想象中更重要。

二、索引优化：让时间区间查询走索引

时序表的查询模式高度集中：查某设备某指标在某个时间段内的曲线、查全园区某时刻的快照、按天聚合统计。主键索引已覆盖“设备+指标+时间”的组合查询，但“查全园区某时刻所有设备”这类查询无法利用主键前导列。此时可补充一个时间维度索引：

CREATE INDEX idx_reading_ts ON greenhouse_reading (ts);
CREATE INDEX idx_reading_metric_ts ON greenhouse_reading (metric, ts);

索引不是越多越好。每个索引都会增加写入时的维护成本，而时序表恰恰是写多读少的负载。实践中应遵循“按查询建索引”的原则：先用 EXPLAIN ANALYZE 观察真实执行计划，确认查询是否走了索引扫描、扫描行数是否接近返回行数。若发现某条按时间范围查询出现全表扫描，再针对性补索引。对于“最近一小时数据”这类热点查询，还可考虑部分索引，只索引近期数据，减小索引体积。

三、批量插入：减少往返与事务开销

温室园区若有 50 台设备、每台每 10 秒上报 4 个指标，日增数据约 172 万行。逐条 INSERT 会产生大量网络往返与事务开销，写入吞吐难以提升。推荐使用多行 VALUES 批量插入或 COPY 命令：

INSERT INTO greenhouse_reading (ts, device_id, metric, value)
VALUES
  ('2024-05-20 08:00:00+08', 101, 1, 24.6),
  ('2024-05-20 08:00:00+08', 101, 2, 68.2),
  ('2024-05-20 08:00:00+08', 102, 1, 25.1);

批量大小需要权衡：单批过大（如数万行）会拉长单事务、增加锁持有时间与内存占用；过小则收益不明显。经验上每批 500～2000 行较为稳妥，并配合应用层缓冲，将同一采集周期的多设备数据合并成一批提交。若数据来自文件导入或网关落盘日志，COPY ... FROM STDIN 的吞吐通常显著高于 INSERT。此外，批量写入期间可适当调大 synchronous_commit 的容忍度或使用 UNLOGGED 中间表暂存，但需明确这以牺牲部分持久性为代价，生产环境应谨慎评估。

四、分区表实践：按时间切分大表

当单表行数达到数千万甚至上亿，索引维护、 vacuum 清理与历史数据归档都会变得吃力。PostgreSQL 的声明式分区可将大表按时间范围切分为多个物理子表，查询时由分区裁剪自动只扫描相关分区。按月分区的建表示例：

CREATE TABLE greenhouse_reading (
    ts TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    metric SMALLINT NOT NULL,
    value REAL NOT NULL,
    quality SMALLINT DEFAULT 0
) PARTITION BY RANGE (ts);

CREATE TABLE greenhouse_reading_2024_05
    PARTITION OF greenhouse_reading
    FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');

分区带来的直接收益有三点：查询某月数据时只扫描对应分区，减少 I/O；删除过期数据时用 DROP TABLE 分区替代 DELETE，避免大量死元组与 vacuum 压力；各分区可独立建索引与维护。需要注意的是，分区键必须出现在高频查询的过滤条件中，否则分区裁剪失效，反而因扫描多个分区而变慢。实践中常配合定时任务提前创建下月分区，避免插入时因无匹配分区而报错。

五、落地检查：从建表到验证的完整闭环

将上述设计投入实际运行前，建议按以下顺序验证。首先建表并插入一批模拟数据（例如 10 台设备、每 10 秒一条、覆盖 3 天，约 26 万行），确认主键约束能正确拒绝重复记录。随后对典型查询执行 EXPLAIN ANALYZE，观察是否命中索引、分区裁剪是否生效。接着用批量插入脚本对比逐条插入与批量插入的耗时差异，记录每秒写入行数作为基线。最后模拟历史数据归档：创建新分区、将旧分区分离并导出，确认查询不受影响。这套验证流程能帮助在数据量尚小时暴露设计缺陷，避免上线后被动调整。

需要强调的是，关系数据库存储时序数据的优势在于事务一致性、SQL 表达力与运维成熟度，代价是写入吞吐与压缩率不及专用时序数据库。对于设备规模在数百台以内、保留周期在数年以内的温室园区，PostgreSQL 配合上述表结构、索引、批量写入与分区策略，是性价比较高的选择；当数据量继续增长时，再评估引入 TimescaleDB 扩展或专用时序库，而本节建立的表结构与查询习惯可以平滑迁移。