title: Partition Large Tables for Better Performanceimpact: MEDIUM-HIGHimpactDescription: 5-20x faster queries and maintenance on large tablestags: partitioning, large-tables, time-series, performance对大表进行分区以获得更好的性能分区将大表拆分为更小的部分从而提升查询性能和维护操作效率。错误做法单个大表createtableevents(idbigintgenerated alwaysasidentity,created_at timestamptz,datajsonb);-- 5 亿行查询扫描所有数据select*fromeventswherecreated_at2024-01-01;-- 慢vacuum events;-- 耗时数小时并且会锁表正确做法按时间范围分区createtableevents(idbigintgenerated alwaysasidentity,created_at timestamptznotnull,datajsonb)partitionbyrange(created_at);-- 为每个月创建分区createtableevents_2024_01partitionofeventsforvaluesfrom(2024-01-01)to(2024-02-01);createtableevents_2024_02partitionofeventsforvaluesfrom(2024-02-01)to(2024-03-01);-- 查询只扫描相关分区select*fromeventswherecreated_at2024-01-15;-- 只扫描 events_2024_01 及之后-- 立即删除旧数据droptableevents_2023_01;-- 立即完成对比 DELETE 需要数小时何时需要分区表超过 1 亿行时序数据且按日期查询需要高效地删除旧数据参考表分区