PostgreSQL时间范围分区与单表时间索引的CRUD性能差异咨询

直接给结论：只要你的数据量会持续增长、表生命周期超过1个月，按time列做Range分区同时在time列建索引的方案，性能全面碾压无分区仅建time索引的单表方案，数据量越大差距越明显，我自己在工业物联网、监控系统两类时序场景落地过近百套这类表结构，从来没见过单表方案在长期运行后能打过分区方案的情况。

分CRUD场景的具体性能差异 #

写入（Insert） #

• 分区方案优势：写入吞吐量是单表方案的3~10倍，写入延迟稳定，几乎不会出现随数据量上涨的毛刺。
  实际压测过的场景：单表写到2TB之后，写入P99延迟从最初的2ms涨到了80ms以上；按天做Range分区的表，跑了2年存了40TB数据，写入P99延迟一直稳定在1.5~3ms之间。
  原因很简单：时序数据都是按时间顺序写入的，Range分区下所有新写入的数据只会落到最新的1_{2个活跃分区上，这部分分区的数据、索引文件大小都维持在固定阈值（比如单分区10GB），写入时维护的是小尺寸的B+树，页分裂、索引节点调整的开销极低，活跃分区完全可以常驻内存，落盘都是顺序IO。而单表的索引会随着数据量无限膨胀，到TB级之后B+树的层级会从3层涨到4}5层，每次写入都要跨多层索引找位置，随机IO、页分裂的开销会跟着数据量线性上涨，数据量越大写入越慢。

查询（Select） #

• 带明确时间范围的查询（时序场景95%以上的查询都属于这类，比如查某设备最近1小时的数据）：分区方案性能是单表的数倍到数百倍不等。
  执行时数据库会先做分区裁剪，直接跳过所有不在时间范围内的分区，连这些分区的索引、数据文件碰都不会碰；如果查询范围刚好落在单个分区内，本质就是在一个10GB级的小表上走索引查询，索引层级浅、扫描的数据页少，很多时候热数据直接从内存就能返回结果。而单表哪怕走time索引，也要先在全量数据的大索引树上定位起止位置，索引扫描路径更长，碰到冷数据还会触发大量随机读IO，查询延迟会高很多。
• 全表扫描/全时间范围聚合查询：分区方案依然有优势，因为数据库可以启动多个并行线程同时扫描多个分区，计算和IO都能打满磁盘/CPU带宽；单表的并行扫描粒度更粗，调度开销更高，速度反而更慢。
  唯一的例外是查极小时间范围的最新数据，这时候两者性能几乎没差，分区方案多的那层分区元数据判断的开销也就亚毫秒级，完全感知不到。

更新（Update） #

• 分区方案优势明显，尤其是时序场景常见的近期数据补录、修正场景：这类操作只会命中最近的少量分区，锁粒度被限制在对应分区内，不会和其他分区的读写操作产生锁冲突，定位待更新行的路径更短。而单表方案的更新要在全量数据的大索引上定位行，数据量大了之后不仅定位慢，碰到长事务、锁等待的概率也高很多。

删除（Delete） #

• 分区方案是碾压级优势，这也是时序场景选Range分区最核心的原因之一。
  时序数据几乎都是有保留周期的，比如监控数据存3个月、工业数据存1年，到期就要删旧数据。Range分区下删过期数据直接执行DROP PARTITION就行，本质是操作系统层面删底层文件的元数据，不管分区里有几百GB还是几TB数据，都是毫秒到秒级完成，不需要逐行删数据、不需要改索引、不会产生大量日志，业务完全无感知。
  我早年踩过单表的坑：当时存设备上报数据的单表有12TB，要删3个月前的2TB旧数据，在线上跑了14个小时还没跑完，中途触发了主从延迟超过1小时的告警，差点影响线上业务；后来改成按天Range分区，删过期数据就是定时任务跑一句删分区的命令，从来没出过问题。
  反过来单表删旧数据要走逐行删除流程，不仅要扫全表索引定位所有待删行，还要写大量binlog、redo日志，删完还要做表空间碎片整理回收空间，几TB数据删几天都是常事，还会严重挤占线上业务的IO、CPU资源。

底层核心逻辑 #

说穿了原理非常简单：Range分区把本来会随着时间无限膨胀的单张大表，拆成了一堆大小固定、生命周期明确的独立小表，把所有CRUD操作的影响范围，从「全量无限增长的数据集」收敛到了「少量匹配时间范围的固定大小数据集」：

• 索引维护成本从随数据量线性上涨的O(n)，变成了固定常量级——毕竟每个分区的索引大小是固定的，不会随着总数据量变大而变胖
• 热数据的缓存命中率大幅提升，IO模式从大量随机IO变成顺序IO为主
• 过期数据清理的成本从O(n)直接降到了O(1)
• 锁粒度从全表/全索引级下沉到分区级，并发操作的冲突概率低很多

内容的提问来源于stack exchange，提问作者No Such Agency