PostgreSQL分区表实现

2023-04-24 14:27| 来源: 网络整理| 查看: 265

该博文用于自己学习记录，内容节选自： https://github.com/digoal/blog/blob/master/201610/20161024_01.md

pg_pathman 创建分区表时，需要指定主表的名字，主表必须已存在，主表可以有数据，也可以是空表。

如果主表有数据，那么可以配置是否需要在创建分区时，将数据迁移到分区，（不建议对大表这么做）。

如果主表有很多数据，建议使用后台非堵塞式的迁移方法。（调用partition_table_concurrently()函数进行迁移）。

如果在创建分区表前，使用set_init_callback(relation regclass, callback regproc DEFAULT 0)设置了回调函数，则创建分区时，每个分区表创建是，会自动调用对应的回调函数。

回调函数的传入参数和模式如下

$part_init_callback$(args JSONB) RETURNS VOID

传入参数如下

/* RANGE-partitioned table abc (for exp: child abc_4) */ { "parent": "abc", "parttype": "2", "partition": "abc_4", "range_max": "401", "range_min": "301" } /* HASH-partitioned table abc (for exp: child abc_0) */ { "parent": "abc", "parttype": "1", "partition": "abc_0" } 1. range分区

有4个管理函数用来创建范围分区

指定起始值、间隔、分区个数

create_range_partitions(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名 start_value ANYELEMENT, -- 开始值 p_interval ANYELEMENT, -- 间隔；任意类型，适合任意类型的分区表 p_count INTEGER DEFAULT NULL, -- 分多少个区 partition_data BOOLEAN DEFAULT TRUE) -- 是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() ) create_range_partitions(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名 start_value ANYELEMENT, -- 开始值 p_interval INTERVAL, -- 间隔；interval 类型，用于时间分区表 p_count INTEGER DEFAULT NULL, -- 分多少个区 partition_data BOOLEAN DEFAULT TRUE) -- 是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )

指定起始值、终值、间隔

create_partitions_from_range(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名 start_value ANYELEMENT, -- 开始值 end_value ANYELEMENT, -- 结束值 p_interval ANYELEMENT, -- 间隔；任意类型，适合任意类型的分区表 partition_data BOOLEAN DEFAULT TRUE) -- 是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() ) create_partitions_from_range(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名 start_value ANYELEMENT, -- 开始值 end_value ANYELEMENT, -- 结束值 p_interval INTERVAL, -- 间隔；interval 类型，用于时间分区表 partition_data BOOLEAN DEFAULT TRUE) -- 是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )

例子

建议

1. 分区列必须有not null约束2. 分区个数必须能覆盖已有的所有记录3. 建议使用非堵塞式迁移接口4. 建议数据迁移完成后，禁用主表

2. hash分区

有1个管理函数用来创建范围分区

指定起始值、间隔、分区个数

create_hash_partitions(relation REGCLASS, -- 主表OID attribute TEXT, -- 分区列名 partitions_count INTEGER, -- 打算创建多少个分区 partition_data BOOLEAN DEFAULT TRUE) -- 是否立即将数据从主表迁移到分区, 不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )

例子

创建需要分区的主表 postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- 分区列必须有not null约束 CREATE TABLE 插入一批测试数据，模拟已经有数据了的主表 postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); INSERT 0 10000 postgres=# select * from part_test limit 10; id | info | crt_time ----+----------------------------------+---------------------------- 1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879 2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048 3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059 4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065 5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407 6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076 7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081 8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087 9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092 10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097 (10 rows) 注意: 1. 分区列必须有not null约束创建128个分区 postgres=# select create_hash_partitions('part_test'::regclass, -- 主表OID 'crt_time', -- 分区列名 128, -- 打算创建多少个分区 false) ; -- 不迁移数据 create_hash_partitions ------------------------ 128 (1 row) postgres=# \d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_0, part_test_1, part_test_10, part_test_100, part_test_101, part_test_102, part_test_103, part_test_104, part_test_105, part_test_106, part_test_107, part_test_108, part_test_109, part_test_11, part_test_110, part_test_111, part_test_112, part_test_113, part_test_114, part_test_115, part_test_116, part_test_117, part_test_118, part_test_119, part_test_12, part_test_120, part_test_121, part_test_122, part_test_123, part_test_124, part_test_125, part_test_126, part_test_127, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_27, part_test_28, part_test_29, part_test_3, part_test_30, part_test_31, part_test_32, part_test_33, part_test_34, part_test_35, part_test_36, part_test_37, part_test_38, part_test_39, part_test_4, part_test_40, part_test_41, part_test_42, part_test_43, part_test_44, part_test_45, part_test_46, part_test_47, part_test_48, part_test_49, part_test_5, part_test_50, part_test_51, part_test_52, part_test_53, part_test_54, part_test_55, part_test_56, part_test_57, part_test_58, part_test_59, part_test_6, part_test_60, part_test_61, part_test_62, part_test_63, part_test_64, part_test_65, part_test_66, part_test_67, part_test_68, part_test_69, part_test_7, part_test_70, part_test_71, part_test_72, part_test_73, part_test_74, part_test_75, part_test_76, part_test_77, part_test_78, part_test_79, part_test_8, part_test_80, part_test_81, part_test_82, part_test_83, part_test_84, part_test_85, part_test_86, part_test_87, part_test_88, part_test_89, part_test_9, part_test_90, part_test_91, part_test_92, part_test_93, part_test_94, part_test_95, part_test_96, part_test_97, part_test_98, part_test_99 由于不迁移数据，所以数据还在主表 postgres=# select count(*) from only part_test; count ------- 10000 (1 row) 使用非堵塞式的迁移接口 partition_table_concurrently(relation REGCLASS, -- 主表OID batch_size INTEGER DEFAULT 1000, -- 一个事务批量迁移多少记录 sleep_time FLOAT8 DEFAULT 1.0) -- 获得行锁失败时，休眠多久再次获取，重试60次退出任务。 postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0); NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------ (1 row) 迁移结束后，主表数据已经没有了，全部在分区中 postgres=# select count(*) from only part_test; count ------- 0 (1 row) 数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了 postgres=# select set_enable_parent('part_test'::regclass, false); set_enable_parent ------------------- (1 row) 只查单个分区 postgres=# explain select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; QUERY PLAN --------------------------------------------------------------------------------- Append (cost=0.00..1.91 rows=1 width=45) -> Seq Scan on part_test_122 (cost=0.00..1.91 rows=1 width=45) Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone) (3 rows) 分区表约束如下很显然pg_pathman自动完成了转换，如果是传统的继承，select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; 这种写法是不能筛选分区的。 postgres=# \d+ part_test_122 Table "public.part_test_122" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122) Inherits: part_test

建议

1. 分区列必须有not null约束2. 建议使用非堵塞式迁移接口3. 建议数据迁移完成后，禁用主表4. pg_pathman不会受制于表达式的写法，所以select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;这样的写法也是能走哈希分区的。5. hash分区列不局限于int类型的列，会使用hash函数自动转换。

3. 数据迁移到分区

如果创建分区表时，未将主表数据迁移到分区，那么可以使用非堵塞式的迁移接口，将数据迁移到分区

可能类似如下做法

with tmp as (delete from 主表 limit xx nowait returning *) insert into 分区 select * from tmp

或者使用 select array_agg(ctid) from 主表 limit xx for update nowati 进行标示然后执行delete和insert。

1. 函数接口如下 partition_table_concurrently(relation REGCLASS, -- 主表OID batch_size INTEGER DEFAULT 1000, -- 一个事务批量迁移多少记录 sleep_time FLOAT8 DEFAULT 1.0) -- 获得行锁失败时，休眠多久再次获取，重试60次退出任务。 2. 例子 postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0); NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------ (1 row) 3. 如何停止迁移任务，调用如下函数接口 stop_concurrent_part_task(relation REGCLASS) 4. 查看后台的数据迁移任务 postgres=# select * from pathman_concurrent_part_tasks; userid | pid | dbid | relid | processed | status --------+-----+------+-------+-----------+-------- (0 rows) 4. 分裂范围分区

例如某个分区太大了，想分裂为两个分区，可以使用这种方法

仅支持范围分区表

split_range_partition(partition REGCLASS, -- 分区oid split_value ANYELEMENT, -- 分裂值 partition_name TEXT DEFAULT NULL) -- 分裂后新增的分区表名

例子

disable_pathman_for没有可逆操作，请慎用。

14. 全局禁止pg_pathman

与禁用单个分区主表不同，全局禁止只需要调整参数即可，不需要修改pg_pathman的元数据，同时它是可逆操作。

pg_pathman.enable

例子

$ vi $PGDATA/postgresql.conf pg_pathman.enable = off $ pg_ctl reload 分区表高级管理 1. 禁用主表

当主表的数据全部迁移到分区后，可以禁用主表。

接口函数如下

set_enable_parent(relation REGCLASS, value BOOLEAN) Include/exclude parent table into/from query plan. In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. You can use disable_parent() if you are never going to use parent table as a storage. Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions. If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. Otherwise it is enabled.

例子

select set_enable_parent('part_test', false); 2. 自动扩展分区

范围分区表，允许自动扩展分区。

如果新插入的数据不在已有的分区范围内，会自动创建分区。

set_auto(relation REGCLASS, value BOOLEAN) Enable/disable auto partition propagation (only for RANGE partitioning). It is enabled by default.

例子

postgres=# \d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9 postgres=# \d+ part_test_26 Table "public.part_test_26" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time = '2018-08-25 00:00:00'::timestamp without time zone AND crt_time >'parttype')::int = 1 then raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition'; insert into rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name); elsif ($1->>'parttype')::int = 2 then raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min'; insert into rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max', $1->>'range_min'); end if; end; $$ language plpgsql strict; 测试表 postgres=# create table tt(id int, info text, crt_time timestamp not null); CREATE TABLE 设置测试表的回调函数 select set_init_callback('tt'::regclass, 'f_callback_test'::regproc); 创建分区 postgres=# select create_range_partitions('tt'::regclass, -- 主表OID 'crt_time', -- 分区列名 '2016-10-25 00:00:00'::timestamp, -- 开始值 interval '1 month', -- 间隔；interval 类型，用于时间分区表 24, -- 分多少个区 false) ; create_range_partitions ------------------------- 24 (1 row) 检查回调函数是否已调用 postgres=# select * from rec_part_ddl; id | parent | parttype | partition | range_max | range_min ----+--------+----------+-----------+---------------------+--------------------- 1 | tt | 2 | tt_1 | 2016-11-25 00:00:00 | 2016-10-25 00:00:00 2 | tt | 2 | tt_2 | 2016-12-25 00:00:00 | 2016-11-25 00:00:00 3 | tt | 2 | tt_3 | 2017-01-25 00:00:00 | 2016-12-25 00:00:00 4 | tt | 2 | tt_4 | 2017-02-25 00:00:00 | 2017-01-25 00:00:00 5 | tt | 2 | tt_5 | 2017-03-25 00:00:00 | 2017-02-25 00:00:00 6 | tt | 2 | tt_6 | 2017-04-25 00:00:00 | 2017-03-25 00:00:00 7 | tt | 2 | tt_7 | 2017-05-25 00:00:00 | 2017-04-25 00:00:00 8 | tt | 2 | tt_8 | 2017-06-25 00:00:00 | 2017-05-25 00:00:00 9 | tt | 2 | tt_9 | 2017-07-25 00:00:00 | 2017-06-25 00:00:00 10 | tt | 2 | tt_10 | 2017-08-25 00:00:00 | 2017-07-25 00:00:00 11 | tt | 2 | tt_11 | 2017-09-25 00:00:00 | 2017-08-25 00:00:00 12 | tt | 2 | tt_12 | 2017-10-25 00:00:00 | 2017-09-25 00:00:00 13 | tt | 2 | tt_13 | 2017-11-25 00:00:00 | 2017-10-25 00:00:00 14 | tt | 2 | tt_14 | 2017-12-25 00:00:00 | 2017-11-25 00:00:00 15 | tt | 2 | tt_15 | 2018-01-25 00:00:00 | 2017-12-25 00:00:00 16 | tt | 2 | tt_16 | 2018-02-25 00:00:00 | 2018-01-25 00:00:00 17 | tt | 2 | tt_17 | 2018-03-25 00:00:00 | 2018-02-25 00:00:00 18 | tt | 2 | tt_18 | 2018-04-25 00:00:00 | 2018-03-25 00:00:00 19 | tt | 2 | tt_19 | 2018-05-25 00:00:00 | 2018-04-25 00:00:00 20 | tt | 2 | tt_20 | 2018-06-25 00:00:00 | 2018-05-25 00:00:00 21 | tt | 2 | tt_21 | 2018-07-25 00:00:00 | 2018-06-25 00:00:00 22 | tt | 2 | tt_22 | 2018-08-25 00:00:00 | 2018-07-25 00:00:00 23 | tt | 2 | tt_23 | 2018-09-25 00:00:00 | 2018-08-25 00:00:00 24 | tt | 2 | tt_24 | 2018-10-25 00:00:00 | 2018-09-25 00:00:00 (24 rows)

【本文地址】

公司简介

联系我们