PostgreSQL 分区表——范围分区SQL实践

1、环境准备

1-1、新增原始表

CREATE TABLE t_common_work_order_log (work_order_log_id varchar(32) COLLATE pg_catalog.default NOT NULL,operation_type int2,work_order_id varchar(32) COLLATE pg_catalog.default,work_order_name varchar(100) COLLATE pg_catalog.default,work_order_type int4,biz_location_id int4,planned_completion_time timestamp(6),actual_completion_time timestamp(6),handle_user_account varchar(100) COLLATE pg_catalog.default,handle_user_name varchar(100) COLLATE pg_catalog.default,create_time timestamp(0),create_by_uuid varchar(32) COLLATE pg_catalog.default,create_by_account varchar(32) COLLATE pg_catalog.default,create_by_name varchar(100) COLLATE pg_catalog.default,last_update_time timestamp(0),last_update_uuid varchar(32) COLLATE pg_catalog.default,last_update_account varchar(32) COLLATE pg_catalog.default,last_update_name varchar(100) COLLATE pg_catalog.default,biz_attribute_1 varchar(255) COLLATE pg_catalog.default,biz_attribute_2 varchar(255) COLLATE pg_catalog.default,biz_attribute_3 varchar(255) COLLATE pg_catalog.default,biz_attribute_4 varchar(255) COLLATE pg_catalog.default,biz_attribute_5 varchar(255) COLLATE pg_catalog.default,biz_attribute_6 varchar(255) COLLATE pg_catalog.default,biz_attribute_7 varchar(255) COLLATE pg_catalog.default,biz_attribute_8 varchar(255) COLLATE pg_catalog.default,biz_attribute_9 varchar(255) COLLATE pg_catalog.default,biz_attribute_10 varchar(255) COLLATE pg_catalog.default,biz_attribute_11 varchar(255) COLLATE pg_catalog.default,biz_attribute_12 varchar(255) COLLATE pg_catalog.default,biz_attribute_13 varchar(255) COLLATE pg_catalog.default,biz_attribute_14 varchar(255) COLLATE pg_catalog.default,biz_attribute_15 varchar(255) COLLATE pg_catalog.default,biz_attribute_16 varchar(255) COLLATE pg_catalog.default,biz_attribute_17 varchar(255) COLLATE pg_catalog.default,biz_attribute_18 varchar(255) COLLATE pg_catalog.default,biz_attribute_19 varchar(255) COLLATE pg_catalog.default,biz_attribute_20 varchar(255) COLLATE pg_catalog.default
)
;ALTER TABLE t_common_work_order_log OWNER TO postgres;CREATE INDEX order_log_biz_location_id_index ON t_common_work_order_log USING btree (biz_location_id pg_catalog.int4_ops ASC NULLS LAST
);CREATE INDEX order_log_create_by_account_index ON t_common_work_order_log USING btree (create_by_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_create_time_index ON t_common_work_order_log USING btree (create_time pg_catalog.timestamp_ops ASC NULLS LAST
);CREATE INDEX order_log_handle_user_account_index ON t_common_work_order_log USING btree (handle_user_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_last_update_account_index ON t_common_work_order_log USING btree (last_update_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_last_update_time_index ON t_common_work_order_log USING btree (last_update_time pg_catalog.timestamp_ops ASC NULLS LAST
);CREATE INDEX order_log_operation_type_index ON t_common_work_order_log USING btree (operation_type pg_catalog.int2_ops ASC NULLS LAST
);CREATE INDEX order_log_work_order_name_index ON t_common_work_order_log USING btree (work_order_name COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_work_order_type_index ON t_common_work_order_log USING btree (work_order_type pg_catalog.int4_ops ASC NULLS LAST
);COMMENT ON COLUMN t_common_work_order_log.work_order_log_id IS '工单日志ID';COMMENT ON COLUMN t_common_work_order_log.operation_type IS '操作类型';COMMENT ON COLUMN t_common_work_order_log.work_order_id IS '工单ID';COMMENT ON COLUMN t_common_work_order_log.work_order_name IS '工单名称';COMMENT ON COLUMN t_common_work_order_log.work_order_type IS '工单类型';COMMENT ON COLUMN t_common_work_order_log.biz_location_id IS '业务位置ID';COMMENT ON COLUMN t_common_work_order_log.planned_completion_time IS '计划完成时间';COMMENT ON COLUMN t_common_work_order_log.actual_completion_time IS '实际完成时间';COMMENT ON COLUMN t_common_work_order_log.handle_user_account IS '处理人账号';COMMENT ON COLUMN t_common_work_order_log.handle_user_name IS '处理人名称';COMMENT ON COLUMN t_common_work_order_log.create_time IS '创建时间';COMMENT ON COLUMN t_common_work_order_log.create_by_uuid IS '创建人uuid';COMMENT ON COLUMN t_common_work_order_log.create_by_account IS '创建人账号';COMMENT ON COLUMN t_common_work_order_log.create_by_name IS '创建人名称';COMMENT ON COLUMN t_common_work_order_log.last_update_time IS '最后更新时间';COMMENT ON COLUMN t_common_work_order_log.last_update_uuid IS '最后跟新人uuid';COMMENT ON COLUMN t_common_work_order_log.last_update_account IS '最后更新人账号';COMMENT ON COLUMN t_common_work_order_log.last_update_name IS '最后更新人名称';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_1 IS '业务属性1';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_2 IS '业务属性2';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_3 IS '业务属性3';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_4 IS '业务属性4';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_5 IS '业务属性5';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_6 IS '业务属性6';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_7 IS '业务属性7';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_8 IS '业务属性8';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_9 IS '业务属性9';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_10 IS '业务属性10';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_11 IS '业务属性11';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_12 IS '业务属性12';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_13 IS '业务属性13';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_14 IS '业务属性14';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_15 IS '业务属性15';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_16 IS '业务属性16';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_17 IS '业务属性17';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_18 IS '业务属性18';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_19 IS '业务属性19';COMMENT ON COLUMN t_common_work_order_log.biz_attribute_20 IS '业务属性20';COMMENT ON TABLE t_common_work_order_log IS '某工单操作日志表';COMMENT ON INDEX order_log_biz_location_id_index IS '位置索引';COMMENT ON INDEX order_log_create_by_account_index IS '创建人索引';COMMENT ON INDEX order_log_create_time_index IS '创建时间索引';COMMENT ON INDEX order_log_handle_user_account_index IS '处理人索引';COMMENT ON INDEX order_log_last_update_account_index IS '最后更新人索引';COMMENT ON INDEX order_log_last_update_time_index IS '最后更新时间索引';COMMENT ON INDEX order_log_operation_type_index IS '操作类型索引';COMMENT ON INDEX order_log_work_order_name_index IS '工单名称索引';COMMENT ON INDEX order_log_work_order_type_index IS '工单类型索引';

1-2、执行脚本新增2400w行

-- 1. 首先创建随机中文名函数
CREATE OR REPLACE FUNCTION random_chinese_name() RETURNS varchar(100) AS $$
DECLAREsurnames varchar[] := ARRAY['张','王','李','赵','刘','陈','杨','黄','吴','周','徐','孙','马','朱','胡','林','郭','何','高','罗'];givennames varchar[] := ARRAY['伟','芳','娜','秀英','敏','静','丽','强','磊','军','洋','勇','艳','杰','娟','涛','明','超','秀兰','霞'];
BEGINRETURN surnames[floor(random()*array_length(surnames,1)+1)] || givennames[floor(random()*array_length(givennames,1)+1)];
END;
$$ LANGUAGE plpgsql;-- 2. 执行数据生成DO块
DO $$
DECLAREmonth_start timestamp;month_end timestamp;month_intervals interval[] := ARRAY[interval '0 months', interval '1 month', interval '2 months',interval '3 months', interval '4 months', interval '5 months',interval '6 months', interval '7 months', interval '8 months',interval '9 months', interval '10 months', interval '11 months'];month_interval interval;
BEGIN-- 设置维护内存提高性能SET LOCAL maintenance_work_mem = '1GB';SET LOCAL work_mem = '256MB';-- 为每个月生成200万条数据FOREACH month_interval IN ARRAY month_intervals LOOPmonth_start := (date '2024-01-01' + month_interval)::timestamp;month_end := (date '2024-01-01' + month_interval + interval '1 month')::timestamp;RAISE NOTICE 'Generating data for month: % (%)', to_char(month_start, 'YYYY-MM'), to_char(clock_timestamp(), 'YYYY-MM-DD HH24:MI:SS');-- 每月插入200万条INSERT INTO t_common_work_order_log (work_order_log_id,operation_type,work_order_id,work_order_name,work_order_type,biz_location_id,planned_completion_time,actual_completion_time,handle_user_account,handle_user_name,create_time,create_by_uuid,create_by_account,create_by_name,last_update_time,last_update_uuid,last_update_account,last_update_name,biz_attribute_1,biz_attribute_2,biz_attribute_3,biz_attribute_4,biz_attribute_5,biz_attribute_6,biz_attribute_7,biz_attribute_8,biz_attribute_9,biz_attribute_10,biz_attribute_11,biz_attribute_12,biz_attribute_13,biz_attribute_14,biz_attribute_15,biz_attribute_16,biz_attribute_17,biz_attribute_18,biz_attribute_19,biz_attribute_20)SELECT substr(md5(random()::text || i::text), 1, 32),floor(random() * 5 + 1)::int2,'WO' || (2000000*extract(month FROM month_start) + i)::varchar,(ARRAY['设备维修','软件升级','网络故障','数据迁移','常规维护'])[floor(random()*5+1)],floor(random() * 10 + 1)::int4,floor(random() * 100 + 1)::int4,month_start + random() * (month_end - month_start) + (interval '1 day' * floor(random() * 3 + 1)),CASE WHEN random() > 0.2 THEN month_start + random() * (month_end - month_start) + (interval '1 hour' * floor(random() * 4 - 2))ELSE NULL END,'user' || floor(random() * 100 + 1)::varchar,random_chinese_name(),month_start + random() * (month_end - month_start),substr(md5(random()::text || i::text), 1, 32),'admin' || floor(random() * 10 + 1)::varchar,'系统管理员' || floor(random() * 5 + 1)::varchar,month_start + random() * (month_end - month_start) + (interval '1 hour' * floor(random() * 24)),substr(md5(random()::text || i::text), 1, 32),'user' || floor(random() * 100 + 1)::varchar,random_chinese_name(),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text)FROM generate_series(1, 2000000) AS i;RAISE NOTICE 'Completed month: %, % records inserted', to_char(month_start, 'YYYY-MM'), 2000000;END LOOP;
END $$;

1-3、创建pg分区表-分区键为创建时间

-- 1-创建分区表
CREATE TABLE t_common_work_order_log_new (work_order_log_id varchar(32) NOT NULL,operation_type int2,work_order_id varchar(32),work_order_name varchar(100),work_order_type int4,biz_location_id int4,planned_completion_time timestamp,actual_completion_time timestamp,handle_user_account varchar(100),handle_user_name varchar(100),create_time timestamp(0),create_by_uuid varchar(32),create_by_account varchar(32),create_by_name varchar(100),last_update_time timestamp(0),last_update_uuid varchar(32),last_update_account varchar(32),last_update_name varchar(100),biz_attribute_1 varchar(255),biz_attribute_2 varchar(255),biz_attribute_3 varchar(255),biz_attribute_4 varchar(255),biz_attribute_5 varchar(255),biz_attribute_6 varchar(255),biz_attribute_7 varchar(255),biz_attribute_8 varchar(255),biz_attribute_9 varchar(255),biz_attribute_10 varchar(255),biz_attribute_11 varchar(255),biz_attribute_12 varchar(255),biz_attribute_13 varchar(255),biz_attribute_14 varchar(255),biz_attribute_15 varchar(255),biz_attribute_16 varchar(255),biz_attribute_17 varchar(255),biz_attribute_18 varchar(255),biz_attribute_19 varchar(255),biz_attribute_20 varchar(255)
) PARTITION BY RANGE (create_time)
;

1-4、创建24年所有分区

-- 2-创建所24年有分区
CREATE TABLE t_common_work_order_log_202401 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE t_common_work_order_log_202402 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE t_common_work_order_log_202403 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE t_common_work_order_log_202404 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
CREATE TABLE t_common_work_order_log_202405 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');
CREATE TABLE t_common_work_order_log_202406 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');
CREATE TABLE t_common_work_order_log_202407 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');
CREATE TABLE t_common_work_order_log_202408 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');
CREATE TABLE t_common_work_order_log_202409 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE t_common_work_order_log_202410 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE t_common_work_order_log_202411 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE t_common_work_order_log_202412 PARTITION OF t_common_work_order_log_new
FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

1-5、设置默认分区（兜底用）

当写入数据的创建时间没有匹配到已有分区时，会写入默认分区，避免数据库异常

-- 3. 设置默认分区（兜底用）
CREATE TABLE t_common_work_order_log_default PARTITION OF t_common_work_order_log_new DEFAULT;

1-6、迁移数据

-- 4. 迁移数据
INSERT INTO t_common_work_order_log_new 
SELECT * FROM t_common_work_order_log;

1-7、创建分区表索引

-- 5. 创建分区表索引
CREATE INDEX order_log_biz_location_id_p_index ON t_common_work_order_log_new USING btree (biz_location_id pg_catalog.int4_ops ASC NULLS LAST
);CREATE INDEX order_log_create_by_account_p_index ON t_common_work_order_log_new USING btree (create_by_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_create_time_p_index ON t_common_work_order_log_new USING btree (create_time pg_catalog.timestamp_ops ASC NULLS LAST
);CREATE INDEX order_log_handle_user_account_p_index ON t_common_work_order_log_new USING btree (handle_user_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_last_update_account_p_index ON t_common_work_order_log_new USING btree (last_update_account COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_last_update_time_p_index ON t_common_work_order_log_new USING btree (last_update_time pg_catalog.timestamp_ops ASC NULLS LAST
);CREATE INDEX order_log_operation_type_p_index ON t_common_work_order_log_new USING btree (operation_type pg_catalog.int2_ops ASC NULLS LAST
);CREATE INDEX order_log_work_order_name_p_index ON t_common_work_order_log_new USING btree (work_order_name COLLATE pg_catalog.default pg_catalog.text_ops ASC NULLS LAST
);CREATE INDEX order_log_work_order_type_p_index ON t_common_work_order_log_new USING btree (work_order_type pg_catalog.int4_ops ASC NULLS LAST
);

2、SQL增删改查测试

2-1、查询速度对比

2-1-1、查询总数量时间对比

• 非分区表首次查询约为800s，预热后查询为2.4s
• 分区表首次查询约为380s，预热后查询为1.26s

-- 819.769s/2.427s
SELECT COUNT(*) FROM t_common_work_order_log;
-- 387.680s/1.260s
SELECT COUNT(*) FROM t_common_work_order_log_new;

查询总数量反直觉分析

为什么查询总数量分区表会比非分区表快？
执行EXPLAIN ANALYZE可以发现，分区表为并行扫描，最后会将并行扫描结果汇总：

-- 819.769s/2.427s
EXPLAIN ANALYZE SELECT COUNT(*) FROM t_common_work_order_log;
-- 387.680s/1.260s
EXPLAIN ANALYZE SELECT COUNT(*) FROM t_common_work_order_log_new;

Finalize Aggregate  (cost=4126001.24..4126001.25 rows=1 width=8) (actual time=2554.156..2559.141 rows=1 loops=1)->  Gather  (cost=4126001.03..4126001.24 rows=2 width=8) (actual time=2554.069..2559.129 rows=3 loops=1)Workers Planned: 2Workers Launched: 2->  Partial Aggregate  (cost=4125001.03..4125001.04 rows=1 width=8) (actual time=2540.244..2540.245 rows=1 loops=3)->  Parallel Seq Scan on t_common_work_order_log  (cost=0.00..4100001.02 rows=10000002 width=0) (actual time=0.036..2256.003 rows=8000000 loops=3)
Planning Time: 0.155 ms
JIT:Functions: 8Options: Inlining true, Optimization true, Expressions true, Deforming trueTiming: Generation 0.595 ms, Inlining 66.620 ms, Optimization 16.470 ms, Emission 12.494 ms, Total 96.179 ms
Execution Time: 2559.602 ms

Finalize Aggregate  (cost=2071847.96..2071847.97 rows=1 width=8) (actual time=1737.444..1748.516 rows=1 loops=1)->  Gather  (cost=2071847.74..2071847.95 rows=2 width=8) (actual time=1737.313..1748.503 rows=3 loops=1)Workers Planned: 2Workers Launched: 2->  Partial Aggregate  (cost=2070847.74..2070847.75 rows=1 width=8) (actual time=1725.717..1725.721 rows=1 loops=3)->  Parallel Append  (cost=0.43..2045847.74 rows=10000000 width=0) (actual time=35.470..1495.583 rows=8000000 loops=3)->  Parallel Index Only Scan using t_common_work_order_log_202406_operation_type_idx on t_common_work_order_log_202406 t_common_work_order_log_new_6  (cost=0.43..274942.74 rows=833486 width=0) (actual time=42.229..175.568 rows=2000366 loops=1)Heap Fetches: 94752->  Parallel Index Only Scan using t_common_work_order_log_202407_operation_type_idx on t_common_work_order_log_202407 t_common_work_order_log_new_7  (cost=0.43..203921.26 rows=833669 width=0) (actual time=42.227..164.912 rows=2000805 loops=1)Heap Fetches: 67128->  Parallel Index Only Scan using t_common_work_order_log_202410_work_order_type_idx on t_common_work_order_log_202410 t_common_work_order_log_new_10  (cost=0.43..25125.31 rows=833677 width=0) (actual time=0.073..101.861 rows=2000824 loops=1)Heap Fetches: 0->  Parallel Index Only Scan using t_common_work_order_log_202404_work_order_type_idx on t_common_work_order_log_202404 t_common_work_order_log_new_4  (cost=0.43..25116.53 rows=833459 width=0) (actual time=0.055..101.724 rows=2000302 loops=1)Heap Fetches: 0->  Parallel Index Only Scan using t_common_work_order_log_202403_work_order_type_idx on t_common_work_order_log_202403 t_common_work_order_log_new_3  (cost=0.43..25115.34 rows=833405 width=0) (actual time=0.075..101.928 rows=2000172 loops=1)Heap Fetches: 0->  Parallel Index Only Scan using t_common_work_order_log_202402_work_order_type_idx on t_common_work_order_log_202402 t_common_work_order_log_new_2  (cost=0.43..25115.08 rows=833393 width=0) (actual time=0.049..101.426 rows=2000144 loops=1)Heap Fetches: 0->  Parallel Index Only Scan using t_common_work_order_log_202401_work_order_type_idx on t_common_work_order_log_202401 t_common_work_order_log_new_1  (cost=0.43..25114.91 rows=833385 width=0) (actual time=0.079..101.461 rows=2000125 loops=1)Heap Fetches: 0->  Parallel Index Only Scan using t_common_work_order_log_202412_work_order_type_idx on t_common_work_order_log_202412 t_common_work_order_log_new_12  (cost=0.43..22083.01 rows=732663 width=0) (actual time=0.044..89.077 rows=1758391 loops=1)Heap Fetches: 0->  Parallel Index Only Scan using t_common_work_order_log_default_work_order_type_idx on t_common_work_order_log_default t_common_work_order_log_new_13  (cost=0.29..3030.63 rows=100470 width=0) (actual time=0.050..12.340 rows=241127 loops=1)Heap Fetches: 0->  Parallel Seq Scan on t_common_work_order_log_202409 t_common_work_order_log_new_9  (cost=0.00..341619.16 rows=833216 width=0) (actual time=0.053..196.980 rows=666573 loops=3)->  Parallel Seq Scan on t_common_work_order_log_202411 t_common_work_order_log_new_11  (cost=0.00..341578.15 rows=833115 width=0) (actual time=0.048..296.090 rows=999738 loops=2)->  Parallel Seq Scan on t_common_work_order_log_202408 t_common_work_order_log_new_8  (cost=0.00..341574.07 rows=833107 width=0) (actual time=0.057..584.129 rows=1999457 loops=1)->  Parallel Seq Scan on t_common_work_order_log_202405 t_common_work_order_log_new_5  (cost=0.00..341511.55 rows=832955 width=0) (actual time=21.951..597.384 rows=1999092 loops=1)
Planning Time: 0.608 ms
JIT:Functions: 47Options: Inlining true, Optimization true, Expressions true, Deforming trueTiming: Generation 1.001 ms, Inlining 65.639 ms, Optimization 19.031 ms, Emission 21.833 ms, Total 107.504 ms
Execution Time: 1749.153 ms

2-2-1、带上分区键查询对比

带上分区键在分表区间内查询

查询2024年4月份总数据量

-- 原始表2.066s
SELECT COUNT(*) FROM t_common_work_order_log WHERE create_time > '2024-04-01' AND create_time < '2024-05-01';
-- 分区表0.715s
SELECT COUNT(*) FROM t_common_work_order_log_new WHERE create_time > '2024-04-01' AND create_time < '2024-05-01';

带上分区键跨分表区间查询

查询2024年5月~6月总数据量

-- 原始表3.303s
SELECT COUNT(*) FROM t_common_work_order_log WHERE create_time > '2024-05-01' AND create_time < '2024-07-01';
-- 分区表1.083s
SELECT COUNT(*) FROM t_common_work_order_log_new WHERE create_time > '2024-05-01' AND create_time < '2024-07-01';

2-2、删除速度对比

2-2-1、全量删除TRUNCATE

相同的数据量分区表删除耗时远远大于原始表

-- 清空速度对比：
TRUNCATE TABLE t_common_work_order_log;
TRUNCATE TABLE t_common_work_order_log_new;

2-2-2、删除某个月数据

原始表DELETE 145.372s

DELETE FROM t_common_work_order_log
WHERE create_time >= '2024-05-01 00:00:00' AND create_time < '2024-06-01 00:00:00';

分区表DELETE（未指定分区）225.896s

分区表DELETE（指定分区）242.770s

分区表TRUNCATE（指定分区）13.156s

2-2-3、删除一条数据

测试10次，每次删除一条

原始表 平均8.009s

-- 113.489s 10.768s 7.393s 6.713s 8.896s 6.089s 7.506s 8.005s 7.955s 6.838s
DELETE FROM t_common_work_order_log WHERE work_order_log_id = '0e817fcd240bf4ecbaf0b5d05e793b9f';

分区表（未带上分区键） 平均7.858s

-- 33.275s 7.227s 7.545s 8.674s 6.186s 10.045s 7.103s 5.707s 7.112s 8.972s
DELETE FROM t_common_work_order_log_new WHERE work_order_log_id = '8326be2f926c1437a8b623b044518888';

分区表（带上分区键） 平均1.567s

-- 0.346s 2.341s 1.003s 2.226s 0.620s 2.955s 1.191s 0.071s 7.196s 1.855s
DELETE 
FROMt_common_work_order_log_new 
WHEREcreate_time = '2024-3-27 23:58:50' AND work_order_log_id = '040ba71c39f80e1217296f262e8e999f';

2-3、写入速度对比

2-2-1、全量插入2400w数据，均匀分布在每个月

插入sql可以参考执行脚本新增2400w行

原始表 3387.520s

分区表 4988.111s

2-2-2、指定某个月插入10w数据

原始表 63.572s

-- 生成10万条6月数据
INSERT INTO t_common_work_order_log (work_order_log_id,operation_type,work_order_id,work_order_name,work_order_type,biz_location_id,planned_completion_time,actual_completion_time,handle_user_account,handle_user_name,create_time,create_by_uuid,create_by_account,create_by_name,last_update_time,last_update_uuid,last_update_account,last_update_name,biz_attribute_1,biz_attribute_2,biz_attribute_3,biz_attribute_4,biz_attribute_5,biz_attribute_6,biz_attribute_7,biz_attribute_8,biz_attribute_9,biz_attribute_10
)
SELECT -- work_order_log_id: UUID简写substr(md5(random()::text || i::text), 1, 32),-- operation_type: 1-5随机值floor(random() * 5 + 1)::int2,-- work_order_id: WO+6月标识+序号'WO202406' || lpad(i::text, 5, '0'),-- work_order_name: 随机工单类型(ARRAY['设备维修','软件升级','网络故障','数据迁移','常规维护'])[floor(random()*5+1)],-- work_order_type: 1-10随机值floor(random() * 10 + 1)::int4,-- biz_location_id: 1-100随机floor(random() * 100 + 1)::int4,-- planned_completion_time: 6月随机时间+1-3天(timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 day' * floor(random() * 3 + 1)),-- actual_completion_time: 80%有值，在计划时间±2小时CASE WHEN random() > 0.2 THEN (timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 hour' * floor(random() * 4 - 2))ELSE NULL END,-- handle_user_account: user+随机编号'user' || floor(random() * 50 + 1)::varchar,-- handle_user_name: 随机中文名random_chinese_name(),-- create_time: 6月随机时间timestamp '2024-06-01' + random() * interval '30 days',-- create_by_uuid: 随机UUID简写substr(md5(random()::text || i::text), 1, 32),-- create_by_account: admin+随机编号'admin' || floor(random() * 5 + 1)::varchar,-- create_by_name: 管理员+编号'系统管理员' || floor(random() * 3 + 1)::varchar,-- last_update_time: create_time+0-24小时timestamp '2024-06-01' + random() * interval '30 days' + (interval '1 hour' * floor(random() * 24)),-- last_update_uuid: 随机UUID简写substr(md5(random()::text || i::text), 1, 32),-- last_update_account: user+随机编号'user' || floor(random() * 50 + 1)::varchar,-- last_update_name: 随机中文名random_chinese_name(),-- biz_attribute_1-10: 随机MD5值md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text)
FROM generate_series(1, 100000) AS i;

分区表（未指定分区） 58.759s

-- 生成10万条6月数据
INSERT INTO t_common_work_order_log_new (work_order_log_id,operation_type,work_order_id,work_order_name,work_order_type,biz_location_id,planned_completion_time,actual_completion_time,handle_user_account,handle_user_name,create_time,create_by_uuid,create_by_account,create_by_name,last_update_time,last_update_uuid,last_update_account,last_update_name,biz_attribute_1,biz_attribute_2,biz_attribute_3,biz_attribute_4,biz_attribute_5,biz_attribute_6,biz_attribute_7,biz_attribute_8,biz_attribute_9,biz_attribute_10
)
SELECT -- work_order_log_id: UUID简写substr(md5(random()::text || i::text), 1, 32),-- operation_type: 1-5随机值floor(random() * 5 + 1)::int2,-- work_order_id: WO+6月标识+序号'WO202406' || lpad(i::text, 5, '0'),-- work_order_name: 随机工单类型(ARRAY['设备维修','软件升级','网络故障','数据迁移','常规维护'])[floor(random()*5+1)],-- work_order_type: 1-10随机值floor(random() * 10 + 1)::int4,-- biz_location_id: 1-100随机floor(random() * 100 + 1)::int4,-- planned_completion_time: 6月随机时间+1-3天(timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 day' * floor(random() * 3 + 1)),-- actual_completion_time: 80%有值，在计划时间±2小时CASE WHEN random() > 0.2 THEN (timestamp '2024-06-01' + random() * interval '30 days') + (interval '1 hour' * floor(random() * 4 - 2))ELSE NULL END,-- handle_user_account: user+随机编号'user' || floor(random() * 50 + 1)::varchar,-- handle_user_name: 随机中文名random_chinese_name(),-- create_time: 6月随机时间timestamp '2024-06-01' + random() * interval '30 days',-- create_by_uuid: 随机UUID简写substr(md5(random()::text || i::text), 1, 32),-- create_by_account: admin+随机编号'admin' || floor(random() * 5 + 1)::varchar,-- create_by_name: 管理员+编号'系统管理员' || floor(random() * 3 + 1)::varchar,-- last_update_time: create_time+0-24小时timestamp '2024-06-01' + random() * interval '30 days' + (interval '1 hour' * floor(random() * 24)),-- last_update_uuid: 随机UUID简写substr(md5(random()::text || i::text), 1, 32),-- last_update_account: user+随机编号'user' || floor(random() * 50 + 1)::varchar,-- last_update_name: 随机中文名random_chinese_name(),-- biz_attribute_1-10: 随机MD5值md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text), md5(random()::text), md5(random()::text),md5(random()::text)
FROM generate_series(1, 100000) AS i;

分区表（指定分区） 48.098s

2-4、更新速度对比

2-4-1、修改指定月份的处理人信息

原始表 538.133s

UPDATE t_common_work_order_log 
SET handle_user_account = 'hander-system' 
WHEREcreate_time > '2024-07-01' AND create_time < '2024-08-01';

分区表（未指定分区） 1252.286s

UPDATE t_common_work_order_log_new
SET handle_user_account = 'hander-system' 
WHEREcreate_time > '2024-07-01' AND create_time < '2024-08-01';

分区表（指定分区） 1252.286s

UPDATE t_common_work_order_log_new
SET handle_user_account = 'hander-system' 
WHEREcreate_time > '2024-07-01' AND create_time < '2024-08-01';

2-4-1、根据主键修处理人信息

原始表 12.597s

UPDATE t_common_work_order_log
SET handle_user_account = 'hander-system-byid' 
WHERE work_order_log_id = 'db3114ba4a676937269e2cf0ef7454b8';

分区表（未带上分区键） 10.870s

UPDATE t_common_work_order_log_new
SET handle_user_account = 'hander-system-byid' 
WHERE work_order_log_id = '9ac76e7e262cd7b374d6939a7e0f4144';

分区表（带上分区键） 0.438s

UPDATE t_common_work_order_log_new 
SET handle_user_account = 'hander-system-by shard id' 
WHEREcreate_time = '2024-12-31 23:59:19' AND work_order_log_id = '9ac76e7e262cd7b374d6939a7e0f4144';

3、总结

文本基于PostgreSQL原生分区能力，以创建时间作为分区键进行水平分表验证，要想发挥分区表的优势，多需要带上分区键进行操作，例如上文的删除以及更新操作中，带上分区键比未带分区键操作，性能存在10倍左右差异，但是进行大批量的操作，分区表的优势不明显，甚至有些场景还会非常慢。
PostgreSQL分区为数据库引擎内置功能，无需额外的应用层中间件，性能损失较小，提供了基础的分区能力，适用于如下场景：

• 数据量在单机可承受范围内（<5TB）
• 只需要表级别的分区
• 希望最小化技术栈复杂度
• 需要利用PG特定功能（如GIS、JSONB）