工业时序数据处理实战：百万级检测数据如何做到毫秒级查询

分享一个工业数据处理中的技术选型和优化经验。场景描述某3C代工厂产线上部署了多台视觉检测设备每台设备每秒钟产生30-50条检测结果。数据包含检测时间、产品ID、缺陷类型、缺陷坐标、判定结果良品/不良品等字段。业务需求查询任意7天的良率趋势响应时间要求在1秒以内查询某一批次产品的所有检测记录响应时间要求在500ms以内数据保留周期为1年初始方案及问题最初用PostgreSQL存储所有数据。表结构设计如下CREATE TABLE detection_results (id BIGSERIAL PRIMARY KEY,product_id VARCHAR(32),batch_no VARCHAR(32),defect_type VARCHAR(16),result VARCHAR(8),created_at TIMESTAMP);CREATE INDEX idx_created_at ON detection_results(created_at);数据量达到500万条时问题开始暴露7天趋势查询约80万条数据响应时间从200ms退化到15秒批量插入性能从5000条/秒降至2000条/秒索引膨胀存储空间从预期50GB增长到100GB尝试了索引优化、分区表、查询语句优化效果有限最多把15秒降到12秒。原因分析检测数据是典型的时间序列数据特征如下按时间顺序持续写入极少更新和删除查询几乎全部按时间范围过滤数据量大、写入频率高关系型数据库PostgreSQL擅长事务处理和多表关联查询但它的存储引擎和索引结构是为通用场景设计的不是为高频时序写入范围查询的场景优化的。简单说用PostgreSQL存时序数据就像用SUV跑F1赛道——能跑但跑不快。选型对比调研了时序数据库方案后选择了TDengine。对比维度PostgreSQLTDengine写入速度条/秒5000300007天趋势查询80万条15秒200ms单批次查询1000条50ms20ms存储空间500万条100GB40GB学习成本低团队熟悉中需学习TDengine的核心优势列式存储 时序索引按时间范围查询时只读取需要的列内置降采样和聚合函数趋势查询不需要传输原始数据高压缩比存储空间减少60%迁移方案采用渐进式迁移不推翻现有系统历史数据3个月以上保留在PostgreSQL用于长期归档查询新数据7天内写入TDengine用于实时分析和趋势查询查询层做路由7天内实时查询走TDengine7天以上历史查询走PostgreSQL应用层封装统一数据源接口上层业务无感知核心代码示意伪代码public class DataQueryService {public ListTrendData queryTrend(Date start, Date end) {long daysBetween ChronoUnit.DAYS.between(start, end);if (daysBetween 7) {return tdEngineDao.queryTrend(start, end); // 实时库} else {return pgDao.queryTrend(start, end); // 历史库}}}上线效果7天趋势查询响应时间15秒 → 200ms存储空间100GB → 40GB减少60%写入性能2000条/秒 → 30000条/秒管理层看板实时刷新不再等报表踩坑提醒⚠️ TDengine的SQL语法与PostgreSQL有差异迁移时注意函数替换⚠️ 分区键tables设计要合理避免单表数据过大⚠️ 历史数据迁移建议分批执行避免一次性写入压力