SQL语句实现实时传感器数据高效插入时序表

实时传感器数据要写入时序表，可千万别图省事用循环INSERT。每条SQL语句都像要独立过一次海关——网络往返、事务开销、WAL写入，一层层下来，100条/秒就能把系统拖成龟速。今天咱们就聊聊，怎么绕开这些坑。

为什么单条INSERT在实时场景下败下阵来

单条INSERT默认开隐式事务，每次都要刷盘——如果synchronous_commit开着，那更是雪上加霜。再加上索引逐条更新和MVCC版本链维护，CPU和I/O全耗在协议解析和锁竞争上了。更糟糕的是，传感器数据往往是成批来的，比如MQTT一次推送50到200个点，硬拆成单行插入，资源全浪费了。

• PostgreSQL里单条INSERT平均耗时约0.5到2毫秒（含网络），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更靠谱。
• COPY有一个麻烦：如果某一行违反约束，整批全挂。可以提前用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 < '2024-06-02' ——加上WHERE子句可以触发分区剪枝，还能用ON CONFLICT处理重复。
• 中转表定期TRUNCATE（比DELETE快得多，而且不锁表），但千万别在COPY过程中截断数据。

连接与事务配置必须调优

语句写得再好，如果连接池没配对，也是白费。默认JDBC或psycopg2的auto-commit模式会让每个COPY都单独提交，批量优势全被抵消掉了。

• 禁用自动提交，显式用BEGIN → COPY → COMMIT包裹整批操作。
• 连接池（比如PgBouncer）设为transaction模式，别用session模式，否则连接没法复用。
• 调整work_mem到8到16MB（对大批次排序或去重有帮助），但别调太高，防止并发一多就OOM。
• 如果用了逻辑复制或CDC工具（比如Debezium），一定要确认它解析WAL的延迟能否跟上写入节奏——宁可降采样也不要让下游积压。

说到底，真正卡住性能的往往不是SQL写法本身，而是没有意识到COPY必须跟连接事务控制、分区裁剪和WAL策略协同工作。少调一个work_mem，或多开一个未关闭的事务，吞吐量直接腰斩。