如何借助SQL语句将实时采集的传感器数据高效插入时序表？

作者：袖梨 2026-06-22

实时传感器数据插入时序表应避免循环INSERT，因其隐式事务、WAL刷盘、索引逐条更新导致性能瓶颈；推荐用二进制COPY批量导入（1000–10000行/批），或结合UNLOGGED中转表+分区表INSERT...SELECT提升吞吐。

实时传感器数据插入时序表，不能直接用普通 INSERT INTO ... VALUES 循环执行——每条语句的网络往返、事务开销和 WAL 写入会迅速成为瓶颈，100 条/秒就可能卡住。

为什么 `INSERT ... VALUES` 在实时写入场景下很快失效

单条 INSERT 默认开启隐式事务，每次都要刷盘（尤其在 synchronous_commit = on 时），还会触发索引逐条更新和 MVCC 版本链维护。传感器数据往往批量到达（如 MQTT 批量推送 50–200 点/秒），硬拆成单行插入，CPU 和 I/O 都浪费在协议解析和锁竞争上。

PostgreSQL 中单条 INSERT 平均耗时约 0.5–2ms（含网络），1000 条就是 0.5–2 秒
即使关掉 fsync，WAL 日志仍需序列化、加锁、写入缓冲区，高并发下 pg_stat_activity 里大量 idle in transaction 或 active 状态堆积
时序表若带 PRIMARY KEY (device_id, ts) 或 UNIQUE 约束，每条都查唯一性，B-tree 深度随数据增长而上升

用 `COPY FROM STDIN` 替代循环 `INSERT`

COPY 是 PostgreSQL 原生批量加载接口，绕过 SQL 解析层，直接写入堆页，速度通常比等量 INSERT 快 5–20 倍。关键在于客户端必须控制好批次大小和提交节奏。

单次 COPY 推荐 1000–10000 行；小于 100 行收益不明显，大于 50000 行可能触发内存溢出或 WAL 检查点压力
务必使用二进制格式（COPY ... FROM STDIN WITH (FORMAT BINARY)），避免文本解析开销；时间戳字段用 int8（微秒级 epoch）而非 timestamptz 字符串
客户端需预分配 byte[] 缓冲区拼接二进制帧，不要用字符串拼接再转 byte —— Go/Python 的 struct.pack 或 Rust 的 bincode 更稳
错误处理：若某批中某行违反约束，整批失败；可提前用 INSERT ... ON CONFLICT DO NOTHING 做兜底，但 COPY 本身不支持冲突忽略

示例（psql 命令行调试用）：

COPY sensor_readings (device_id, ts, temperature, humidity) FROM STDIN WITH (FORMAT BINARY);

分区表 + `INSERT ... SELECT` 中转表提升吞吐

当单表超千万行后，COPY 到主表仍会因索引维护变慢。更稳的做法是先写入无索引、无约束的“中转表”（sensor_staging），再定时或按批次用 INSERT ... SELECT 落到按天/小时分区的主表。

中转表用 UNLOGGED（不写 WAL），插入快 2–3 倍，但崩溃会丢数据——适合可重传的传感器场景
主表按 ts 范围分区（如 PARTITION BY RANGE (ts)），新数据总落在最新分区，避免全表扫描和索引分裂
INSERT INTO sensor_readings_20240601 SELECT * FROM sensor_staging WHERE ts >= '2024-06-01' AND ts —— 带 <code>WHERE 的 SELECT 能触发分区剪枝，且可加 ON CONFLICT 处理重复
中转表定期 TRUNCATE（比 DELETE 快，且不锁表），注意别在 COPY 过程中截断