云南建设投资集团网站首页,临沂企业建站系统,wordpress阅读排行榜,怎么做非法彩票网站吗什么是分区表 数据库分区表将表数据分成更小的物理分片#xff0c;以此提高性能、可用性、易管理性。分区表是关系型数据库中比较常见的对大表的优化方式#xff0c;数据库管理系统一般都提供了分区管理#xff0c;而业务可以直接访问分区表而不需要调整业务架构#xff0c…什么是分区表 数据库分区表将表数据分成更小的物理分片以此提高性能、可用性、易管理性。分区表是关系型数据库中比较常见的对大表的优化方式数据库管理系统一般都提供了分区管理而业务可以直接访问分区表而不需要调整业务架构当然好的性能需要合理的分区访问方式。
分区表是数据库中常见的技术而PostgreSQL中的分区表有许多专有的特性比如分区表实现方案多、分区为普通表、分区维护方案、SQL优化还有一些分区表的问题。
分区表的实现
PostgreSQL数据库有各式各样的分区实现方式。官方支持的有声明式分区和继承式分区而三方插件包括pathman、partman等等。在官方声明式分区实现后基本只推荐一种分区方式声明式分区。由于再拓展不同实现的分区表的功能、细节、历史等差别会使篇幅过长且未来意义不大本篇主要讨论的是声明式分区其他方式实现的分区功能只会简单介绍。但是由于历史存量和一些功能差异了解声明分区、继承分区、pathman还是有必要的。
声明分区表
声明分区也叫原生分区从PG10版本开始支持相当于“官方支持”的分区表也是最为推荐的分区方式。虽然与继承分区不一样但是其内部也是用继承表实现的。声明分区只支持3种分区方式range分区、list分区、hash分区
range分区 range分区表以范围进行分区分区边界为[t1,t2)
CREATE TABLE PUBLIC.LZLPARTITION1
(id int,name varchar(50) NULL, DATE_CREATED timestamp NOT NULL DEFAULT now()
) PARTITION BY RANGE(DATE_CREATED);alter table public.lzlpartition1 add primary key(id,DATE_CREATED)create table LZLPARTITION1_202301 partition of LZLPARTITION1 for values from (2023-01-01 00:00:00) to (2023-02-01 00:00:00);create table LZLPARTITION1_202302 partition of LZLPARTITION1 for values from (2023-02-01 00:00:00) to (2023-03-01 00:00:00);--往分区表添加一些数据INSERT INTO lzlpartition1 SELECT random() * 10000, md5(g::text),g
FROM generate_series(2023-01-01::date, 2023-02-28::date, 1 minute) as g;
INSERT 0 83521range分区的from t1 to t2为[t1,t2)范围下边界包含上边界不包含。
查看分区表每个分区也是单独的表
lzldb \d lzlpartition1Partitioned table public.lzlpartition1Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------------------------------------------------------------------------------------------------------------id | integer | | not null | | plain | | | name | character varying(50) | | | | extended | | | date_created | timestamp without time zone | | not null | now() | plain | | |
Partition key: RANGE (date_created)
Indexes:lzlpartition1_pkey PRIMARY KEY, btree (id, date_created)
Partitions: lzlpartition1_202301 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00),lzlpartition1_202302 FOR VALUES FROM (2023-02-01 00:00:00) TO (2023-03-01 00:00:00)lzldb \d lzlpartition1_202301Table public.lzlpartition1_202301Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------------------------------------------------------------------------------------------------------------id | integer | | not null | | plain | | | name | character varying(50) | | | | extended | | | date_created | timestamp without time zone | | not null | now() | plain | | |
Partition of: lzlpartition1 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00)
Partition constraint: ((date_created IS NOT NULL) AND (date_created 2023-01-01 00:00:00::timestamp without time zone) AND (date_created 2023-02-01 00:00:00::timestamp without time zone))
Indexes:lzlpartition1_202301_pkey PRIMARY KEY, btree (id, date_created)
Access method: heap分区上的主键、索引、字段null/CHECK约束自动创建。由于分区也是独立的表约束和索引也可以单独在分区上创建。attach则不会自动创建这些详见attach一节
list分区 list分区以指定的分区值将数据存放到对应的分区上
CREATE TABLE cities (city_id bigserial not null,name text,population bigint
) PARTITION BY LIST (left(lower(name), 1));CREATE TABLE cities_abPARTITION OF cities FOR VALUES IN (a, b);
CREATE TABLE cities_nullPARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id ! 0)
) FOR VALUES IN (null);insert into cities(name,population) values(Acity,10);
insert into cities(name,population) values(null,20);SELECT tableoid::regclass,* FROM cities;tableoid | city_id | name | population
------------------------------------------cities_ab | 1 | Acity | 10cities_null | 2 | [null] | 20list分区表可以创建null分区
hash分区 hash分区将数据散列存储在各个分区上以打散热点数据
CREATE TABLE orders (order_id int,name varchar(10)) PARTITION BY HASH (order_id);
CREATE TABLE orders_p1 PARTITION OF ordersFOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF ordersFOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF ordersFOR VALUES WITH (MODULUS 3, REMAINDER 2);不能创建默认分区也不能创建超过MODULUS的分区 CREATE TABLE orders_p2 PARTITION OF orders
- FOR VALUES WITH (MODULUS 3, REMAINDER 4);
ERROR: 42P16: remainder for hash partition must be less than modulus
LOCATION: transformPartitionBound, parse_utilcmd.c:3939 CREATE TABLE orders_p4 PARTITION OF orders default;
ERROR: 42P16: a hash-partitioned table may not have a default partition
LOCATION: transformPartitionBound, parse_utilcmd.c:3909插入数据
insert into orders values(generate_series(1,10000),a);
INSERT 0 10000
SELECT tableoid::regclass,count(*) FROM orders group by tableoid::regclass;tableoid | count
------------------orders_p1 | 3277orders_p3 | 3354orders_p2 | 3369select tableoid::regclass,* from orders limit 30;tableoid | order_id | name
---------------------------orders_p1 | 2 | aorders_p1 | 4 | aorders_p1 | 6 | aorders_p1 | 8 | aorders_p1 | 15 | aorders_p1 | 16 | aorders_p1 | 18 | aorders_p1 | 19 | aorders_p1 | 20 | ahash分区数据散列分布
--插入100条null数据insert into orders values(null,generate_series(1,100)::text);
INSERT 0 100SELECT tableoid::regclass,count(*) FROM orders where order_id is null group by tableoid::regclass;tableoid | count
------------------orders_p1 | 100
--null数据全部都放在了remainder 0的分区上
\d orders_p1Table public.orders_p1Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------------------------------------------------------------------------------------------order_id | integer | | | | plain | | name | character varying(10) | | | | extended | |
Partition of: orders FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition(412053::oid, 3, 0, order_id)hash分区表虽然没有null分区的概念但是可以存放null数据null数据存放在remainder 0上。
混合分区
分区下面也可以建立分区构成级联模式子分区可以有不同的分区方式这样的分区成为混合分区。 创建一个混合分区
create table part_1000(id bigserial not null,name varchar(10),createddate timestamp) partition by range(createddate);
create table part_2001 partition of part_1000 for values from (2023-01-01 00:00:00) to (2023-02-01 00:00:00) partition by list(name) ;
create table part_2002 partition of part_1000 for values from (2023-02-01 00:00:00) to (2023-03-01 00:00:00) partition by list(name) ;
create table part_2003 partition of part_1000 for values from (2023-03-01 00:00:00) to (2023-04-01 00:00:00) partition by list(name) ;
create table part_3001 partition of part_2001 FOR VALUES IN (abc);
create table part_3002 partition of part_2001 FOR VALUES IN (def);
create table part_3003 partition of part_2001 FOR VALUES IN (jkl);\d只能看到下一级的分区 \d part_1000Partitioned table dbmgr.part_1000Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------------------------------------------------------------------------------------------------------------------------id | bigint | | not null | nextval(part_1000_id_seq::regclass) | plain | | name | character varying(10) | | | | extended | | createddate | timestamp without time zone | | | | plain | |
Partition key: RANGE (createddate)
Partitions: part_2001 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00), PARTITIONED,part_2002 FOR VALUES FROM (2023-02-01 00:00:00) TO (2023-03-01 00:00:00), PARTITIONED,part_2003 FOR VALUES FROM (2023-03-01 00:00:00) TO (2023-04-01 00:00:00), PARTITIONED\d part_2001Partitioned table dbmgr.part_2001Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------------------------------------------------------------------------------------------------------------------------id | bigint | | not null | nextval(part_1000_id_seq::regclass) | plain | | name | character varying(10) | | | | extended | | createddate | timestamp without time zone | | | | plain | |
Partition of: part_1000 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00)
Partition constraint: ((createddate IS NOT NULL) AND (createddate 2023-01-01 00:00:00::timestamp without time zone) AND (createddate 2023-02-01 00:00:00::timestamp without time zone))
Partition key: LIST (name)
Partitions: part_3001 FOR VALUES IN (abc),part_3002 FOR VALUES IN (def),part_3003 FOR VALUES IN (jkl) 此时插入一条数据 insert into part_1000 values(random() * 10000,abc,2023-01-01 08:00:00);
INSERT 0 1SELECT tableoid::regclass,* FROM part_1000;tableoid | id | name | createddate
--------------------------------------------part_3001 | 6385 | abc | 2023-01-01 08:00:00数据存放在最底层的子分区中
声明分区特性小结
没有interval分区。没有自带的自动新增分区功能对于维护来说比较麻烦分区表的分区本身也是表这个特性比较特殊。这不仅仅造成pg可以灵活的操作子分区更重要的是功能和特性上的影响。truncatevacuumanalyze分区表会执行所有分区。truncate only不能在父表上执行但可以在存数据的子表上执行仅清除这个子分区。rangehash分区的分区键可以有多个列list分区的分区键只能是单个列或表达式。分区父表本身是空的最底层子分区可以存储数据default分区表会接收不在声明的范围中的数据如果没有default分区插入范围外的数据会直接报错如果要新增分区需要注意default分区中是否有这个新增分区的数据partition of创建的分区会自动创建分区表上的索引、约束、行级触发器attach不会处理任何索引、约束等等对象
继承分区表
继承分区也是官方支持的它利用了PGSQL的继承表特性来实现分区表的功能。继承分区表会比声明分区表更灵活。 继承分区表的实现需要到了PGSQL中的2个功能继承表和写入重定向。写入重定向可以通过rule或者trigger来实现。
创建继承分区表
创建继承分区表示例 1.创建父表
CREATE TABLE measurement (city_id int not null,logdate date not null,peaktemp int,unitsales int
);2.创建继承表指定约束范围
CREATE TABLE measurement_202308 (CHECK ( logdate DATE 2023-08-01 AND logdate DATE 2023-09-01 )
) INHERITS (measurement);
CREATE TABLE measurement_202309 (CHECK ( logdate DATE 2023-09-01 AND logdate DATE 2023-10-01 )
) INHERITS (measurement);3.创建规则或触发器将插入数据重定向到对应的继承表中
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGINIF ( NEW.logdate DATE 2023-08-01 ANDNEW.logdate DATE 2023-09-01 ) THENINSERT INTO measurement_202308 VALUES (NEW.*);ELSIF ( NEW.logdate DATE 2023-09-01 ANDNEW.logdate DATE 2023-10-01 ) THENINSERT INTO measurement_202309 VALUES (NEW.*);ELSERAISE EXCEPTION Date out of range. Fix the measurement_insert_trigger() function!;END IF;RETURN NULL;
END;
$$
LANGUAGE plpgsql;CREATE TRIGGER insert_measurement_triggerBEFORE INSERT ON measurementFOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();一个最基本的继承分区表就创建好了。
\d measurementTable public.measurementColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------city_id | integer | | not null | | plain | | logdate | date | | not null | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | |
Triggers:insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger()
Child tables: measurement_202308,measurement_202309
Access method: heap测试一下插入和查询数据
--插入范围外的数据会报错insert into measurement values(1001, now() - interval 31 day ,1,1);
ERROR: P0001: Date out of range. Fix the measurement_insert_trigger() function!
CONTEXT: PL/pgSQL function measurement_insert_trigger() line 10 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3889
--插入数据会重定向到子表上insert into measurement values(1001,now(),1,1);
INSERT 0 0
--查询父表会查到子表数据select tableoid::regclass,* from measurement;tableoid | city_id | logdate | peaktemp | unitsales
--------------------------------------------------------------measurement_202308 | 1001 | 2023-08-03 | 1 | 1RULE和trigger 除了触发器PGSQL还可以用rule来重定向插入。 rule语句参考
CREATE RULE measurement_insert_202308 AS
ON INSERT TO measurement WHERE( logdate DATE 2023-08-01 AND logdate DATE 2023-08-01 )
DO INSTEADINSERT INTO measurement_202308 VALUES (NEW.*);
CREATE RULE measurement_insert_202309 AS
ON INSERT TO measurement WHERE( logdate DATE 2023-09-01 AND logdate DATE 2023-09-01 )
DO INSTEADINSERT INTO measurement_202309 VALUES (NEW.*);规则和触发器的差异
rule性能相较trigger更差但在批量插入时由于rule只有一次检查性能会比trigger更好但其他情况下trigger更好COPY不会触发rule但会触发trigger。rule时可以将数据直接COPY到子表中当插入范围外数据时rule会将数据插入到父表中trigger则会直接报错
索引 为了提升性能还需要创建索引和开启constrain_exclusion。分区的索引一般都是必不可少继承表的索引需要手动在子表上创建。 创建索引示例
CREATE INDEX idx_measurement_202308_logdate ON measurement_202308 (logdate);
CREATE INDEX idx_measurement_202309_logdate ON measurement_202309 (logdate);插入一些数据查看执行计划
--2023-08-04只有1条数据让其可以走到索引insert into measurement values(1001,now()interval 1 day,1,1);
INSERT 0 0insert into orders values(generate_series(1,10000),a);insert into measurement values(generate_series(1,1000),now(),1,1);
INSERT 0 0 explain select * from measurement where logdate2023-08-04;QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------Append (cost0.00..5.17 rows2 width16)- Seq Scan on measurement measurement_1 (cost0.00..0.00 rows1 width16)Filter: (logdate 2023-08-04::date)- Index Scan using idx_measurement_202308_logdate on measurement_202308 measurement_2 (cost0.14..5.16 rows1 width16)Index Cond: (logdate 2023-08-04::date)上面的执行计划8月的分区走到了分区上的索引。constraint_exclusion默认打开了继承表的约束排除上面的查询排除了9月分区只扫描了8月。然而由于父表没有约束也加不了所以父表一定在执行计划里面但是父表一般都是空的所以影响不大。
constraint_exclusion
constraint_exclusion拥有控制优化器是否使用约束来减少非必要的访问表该参数在继承分区表优化上常见通过减少子表的访问提升SQL的性能该功能跟enable_partition_pruning参数类似enable_partition_pruning用于控制声明式分区表的分区裁剪。constraint_exclusion有3个值 on所有表都会检查约束 partition继承表和UNION ALL子查询检查约束默认值 off不会检查约束 约束排除只能发生在生成执行计划时不会发生在真正执行时分区裁剪是可以的。这意味着当使用绑定变量、变量值时不会发生约束排除。 例如在使用now()等优化器不知道具体值的函数时优化器无法排除根本不需要访问的分区 select now();now
-------------------------------2023-08-03 17:12:04.77265808
--优化器没有排除9月的分区explain select * from measurement where logdatenow();QUERY PLAN
-----------------------------------------------------------------------------------------------------Append (cost0.00..55.98 rows1628 width16)- Seq Scan on measurement measurement_1 (cost0.00..0.00 rows1 width16)Filter: (logdate now())- Seq Scan on measurement_202308 measurement_2 (cost0.00..21.15 rows1010 width16)Filter: (logdate now())- Bitmap Heap Scan on measurement_202309 measurement_3 (cost7.44..26.69 rows617 width16)Recheck Cond: (logdate now())- Bitmap Index Scan on idx_measurement_202309_logdate (cost0.00..7.28 rows617 width0)Index Cond: (logdate now())另外约束排除本身需要检查所有子表的约束如果子表约束过多生成执行计划的效率会受到影响所以继承分区不建议创建过多的子分区。
添加/删除继承分区表的分区
将一个继承分区做成普通表
ALTER TABLE measurement_202308 NO INHERIT measurement;将一个含有数据的普通表当成子表加入到继承分区表中
CREATE TABLE measurement_202310
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_202310 ADD CONSTRAINT measurement_202310_logdate_check
CHECK ( logdate DATE 2023-10-01 AND logdate DATE 2023-11-01 );
--insert into measurement_202310 values(2001,20231010,3,3);
ALTER TABLE measurement_202310 INHERIT measurement;继承分区表特性小结
继承分区要比声明分区更灵活但一些声明分区的特性也无法使用子表会继承父表上的约束所以如果不是全局约束不要在父表上设置索引不会继承索引只能在子表上一个个地创建声明分区只能有range、list、hash分区继承分区可以更多也可以是自定义的分区方式。删除一个子表不会导致触发器失效。PGSQL没有像ORACLE那样失效对象的概念索引有失效的概念一般来说使用trigger的插入重定向比rule效率更好新增分区时如果触发器函数中没有该分区的规则则需要更新触发器函数。继承分区可以多重继承约束排除不能在执行时进行排除所以建议使用固定值进行查询使用继承分区表时同样不要创建太多的子分区
pg_pathman
pg_pathman是三方插件实现的分区表功能。github上的pathman readme和使用pg_pathman插件的文章对pathman描述和使用已经非常详细这里仅摘几个重点汇总和做一些简单的测试。
pg_pathman基本知识
不再更新 NOTE: this project is not under development anymore pg_pathman支持postgres9.5到15PostgreSQL后续版本不会再支持已有版本也只做BUG修复不会再新增功能。 pg_pathman的出现是因为老版本的PostgreSQL分区表功能不完善而现在原生分区表也就是声明分区表已非常成熟pg_pathman也建议使用原生分区表存量的pg_pathman分区表也建议转移到原生分区表。曾经被许多用户认可的pg_pathman成为历史即使不再更新它的功能也比目前的原生分区表更多。 特性介绍 pg_pathman功能相当强大一些原生分区表不支持的功能pathman也支持。 pathman虽然强大但也不是完美的在实际使用过程中问题也很多。pg_pathman特性中比较需要关注的点包括
pg_pathman可以通过分区管理函数管理分区。支持replace、merge、split分区操作支持 attach、detach操作支持interval分区pg_pathman对分区表执行计划做了很多优化pg_pathman仅支持range和hash两种分区类型pathman_config表存储分区表配置信息提供分区任务视图分区信息缓存在内存中以生成执行计划
pg_pathman基本使用
创建pathman range分区
--普通表就是父表
CREATE TABLE journal (id SERIAL,dt TIMESTAMP NOT NULL,level INTEGER,msg TEXT);-- 子分区会自动创建父表上的索引
CREATE INDEX ON journal(dt);
--创建分区
select
create_range_partitions(journal::regclass, dt,2023-01-01 00:00:00::timestamp,interval 1 month, 6, false) ; --查看表定义\d journalTable public.journalColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------------------------------------------------------------------------------------------------------------------------id | integer | | not null | nextval(journal_id_seq::regclass) | plain | | dt | timestamp without time zone | | not null | | plain | | level | integer | | | | plain | | msg | text | | | | extended | |
Indexes:journal_dt_idx btree (dt)
Child tables: journal_1,journal_2,journal_3,journal_4,journal_5,journal_6
Access method: heap \d journal_6Table public.journal_6Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------------------------------------------------------------------------------------------------------------------------id | integer | | not null | nextval(journal_id_seq::regclass) | plain | | dt | timestamp without time zone | | not null | | plain | | level | integer | | | | plain | | msg | text | | | | extended | |
Indexes:journal_6_dt_idx btree (dt)
Check constraints:pathman_journal_6_check CHECK (dt 2023-06-01 00:00:00::timestamp without time zone AND dt 2023-07-01 00:00:00::timestamp without time zone)
Inherits: journal
Access method: heap--插入数据
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 10000, md5(g::text)
FROM generate_series(2023-01-01::date, 2023-02-28::date, 1 hour) as g;--插入还未创建对应分区的数据INSERT INTO journal (dt, level, msg) values(2023-07-01::date,11,1);
INSERT 0 1
--查看分区数据分布已成功创建interval分区SELECT tableoid::regclass AS partition, count(*) FROM journal group by partition;partition | count
------------------journal_7 | 1journal_2 | 649journal_1 | 744--查看执行计划
--已发生分区裁剪explain select * from journal where dt2023-01-01 22:00:00;QUERY PLAN
-----------------------------------------------------------------------------------------------------Append (cost0.00..5.30 rows2 width48)- Seq Scan on journal journal_1 (cost0.00..0.00 rows1 width48)Filter: (dt 2023-01-01 22:00:00::timestamp without time zone)- Index Scan using journal_1_dt_idx on journal_1 journal_1_1 (cost0.28..5.29 rows1 width49)Index Cond: (dt 2023-01-01 22:00:00::timestamp without time zone)创建pathman hash分区
--创建主表
CREATE TABLE items (id SERIAL PRIMARY KEY,name TEXT,code BIGINT);
--创建hash分区
select create_hash_partitions(items::regclass, id,3, false) ;
--插入数据
INSERT INTO items (id, name, code)
SELECT g, md5(g::text), random() * 100000
FROM generate_series(1, 1000) as g;SELECT tableoid::regclass AS partition, count(*) FROM items group by partition;partition | count
------------------items_2 | 344items_0 | 318items_1 | 338\d itemsTable public.itemsColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------------id | integer | | not null | nextval(items_id_seq::regclass) | plain | | name | text | | | | extended | | code | bigint | | | | plain | |
Indexes:items_pkey PRIMARY KEY, btree (id)
Child tables: items_0,items_1,items_2
Access method: heap \d items_1Table public.items_1Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------------id | integer | | not null | nextval(items_id_seq::regclass) | plain | | name | text | | | | extended | | code | bigint | | | | plain | |
Indexes:items_1_pkey PRIMARY KEY, btree (id)
Check constraints:pathman_items_1_check CHECK (get_hash_part_idx(hashint4(id), 3) 1)
Inherits: items
Access method: heapSELECT tableoid::regclass AS partition, count(*) FROM items group by partition;partition | count
------------------items_2 | 344items_0 | 318items_1 | 338pg分区表的优劣
分区表的优势
SQL性能提升。在某些场景下比如把大量的数据分成多个分区而SQL只需要查那一个分区的数据时SQL性能可能会极大的提升分区可以和索引配合使用。比如访问一个分区上的一个索引要比访问一个未分区的大索引要更高效。删除一个分区比删除多行数据更高效。这在时间范围分区中很常见删除一个用不到的历史分区是非常快的但是如果没有分区delete删除数据不仅慢还需要额外的维护操作vacuum更快。一个大表在回收旧版本信息或收集统计信息时会非常慢在vacuum还没执行完的时候可能SQL已经存在问题了。如果有分区的话vacuum会快很多。IO分散能力。不同的分区可以放在不同的路径、不同的磁盘上。极少使用数据可以放在便宜的磁盘上。更多的维护技巧。直接维护一个大表是非常困难的比如一个极大的表做vacuum时就有很多问题而分区表的各个分区可以单独运行vacuum。不仅如此attach/detach、本地索引/约束等可以在很多场景中灵活使用。
分区表的劣势
在pgsql中每个分区表的分区都可以当成普通表来对待。分区表过多会导致SQL解析时间较长和更多的内存负载甚至报错。参考之前的文章较少的分区也报错too many range table entries即使分区过多没有报错且在生成执行计划的时候没有做分区剪裁执行的时候有可能做那么explain出来的执行计划会非常多此时日志中也会打印长长的执行计划影响日志阅读。一些奇怪的问题不同用户查看到不同的执行计划
分区表的限制 没有原生的自动创建分区功能 只支持分区索引不支持全局索引 主键必须包含分区键。postgresql目前只能在各自的分区内判断唯一性所以有这个限制。oracle和mysql都没有这种限制。 唯一索引必须包含分区键。postgresql目前只能在各自的分区内判断唯一性。同理主键 无法创建定义在全局的约束 INSERT的BEFORE ROW触发器不能更新insert的那个分区 临时表分区和普通表分区不能在同一分区表下。 声明式分区的父表子表的列必须一致继承式分区的子表可以比父表的列更多。 声明式分区CHECK和NOT NULL约束总是继承的不能单独设置分区的这两种约束 range 不能存储NULL值hash分区没有null分区的概念但可以存储null值null值存放在remainder 0分区上list分区可以显示创建null分区存放null数据
什么时候该使用分区表
首先使用分区表必须要了解分区表所带来的优劣势和使用限制比如数据量很大的时候分区可以带来性能提升冷热数据分离也更好管理分区数据等等。应该结合本身业务情况、硬件资源选择是否分区和如何分区。但是总会有开发人员会问到底多少数据量该分区等等的问题使用分区表的建议只能给出一个笼统的回答如果你不知道怎么分区可以参考以下建议如果足够了解表分区请忽略
表数据够大而表上的sql总是或可以是带有分区键字段的时候冷热数据分离明显。比如新数据都在新的当月分区插入老的11个月分区都是只读数据的情况vaccum已经跑不过来了
分区表的权限
权限问题是分区表知识点中讨论的比较少的但是它仍然值得关注。 由于PostgreSQL数据库有“分区子表也是普通表”这样的概念这与其他几个常见的数据库Oracle、mysql是不同的。比如在oracle中不需要关心分区子表的权限但是在pg中却需要关注权限问题。
partition of/attach不会将主表的权限继承给子表
--把分区去表的select授权给一个普通用户grant select on lzlpartition1 to userlzl;
GRANT--查看权限只有主表有授权存量分区子表不会自动授权select grantee,table_schema,table_name,privilege_type from information_schema.table_privileges where granteeuserlzl;grantee | table_schema | table_name | privilege_type
------------------------------------------------------userlzl | public | lzlpartition1 | SELECT--partition of方式创建一个分区 create table LZLPARTITION1_202303 partition of LZLPARTITION1 for values from (2023-03-01 00:00:00) to (2023-04-01 00:00:00);
CREATE TABLE--attach方式创建一个分区CREATE TABLE lzlpartition1_202304
- (LIKE lzlpartition1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLEalter table lzlpartition1 attach partition lzlpartition1_202304 for values from (2023-04-01 00:00:00) to (2023-05-01 00:00:00);
ALTER TABLE--再次查看权限新增的子分区不会自动授权给其他用户但是新增子分区权限会自动授权给ownerselect grantee,table_schema,table_name,privilege_type from information_schema.table_privileges where granteeuserlzl;grantee | table_schema | table_name | privilege_type
------------------------------------------------------userlzl | public | lzlpartition1 | SELECT目前来看userlzl 这个用户对所有子表都没有访问权限但是有主表的权限 此时userlzl可以通过主表访问分区数据但不能通过直接访问子表访问数据 \c - userlzl;
You are now connected to database dbmgr as user userlzl.select * from LZLPARTITION1 where date_created2023-01-02 10:00:00;id | name | date_created
-------------------------------------------------------------2159 | d05d716da126ff4b44d934344cc4dd7a | 2023-01-02 10:00:00 select * from LZLPARTITION1_202301 where date_created2023-01-02 10:00:00;
ERROR: 42501: permission denied for table lzlpartition1_202301
LOCATION: aclcheck_error, aclchk.c:3466因为attach/detach不会处理权限此时如果我们把分区detach出来这个分区同样不能被userlzl访问 alter table lzlpartition1 detach partition lzlpartition1_202303;
ALTER TABLE\dp lzlpartition1_202303;Access privilegesSchema | Name | Type | Access privileges | Column privileges | Policies
-------------------------------------------------------------------------------------dbmgr | lzlpartition1_202303 | table | | | select * from LZLPARTITION1_202301 where date_created2023-01-02 10:00:00;
ERROR: 42501: permission denied for table lzlpartition1_202301 由此可知
分区子表和主表PostgreSQL在数据库中都是以普通表的形式存在他们都有各自的权限体系如果没有子表权限而主表有权限同样可以访问子表数据partition of、attach/detach都不会处理权限问题
但是分区表权限并不是仅仅控制是否能够访问没有分区子表权限可能导致执行计划异常参考文章不同用户查看到不同的执行计划 这个问题是一个偶发现象导致超级用户和一般用户看到的sql执行计划不一致实际上业务SQL执行计划异常却看不出来比较难定位。分区子表有各自的统计信息子表权限与父表不一致即便是partition of创建的分区导致用户可以通过主表访问子表的数据却不能查看子表的统计信息。权限问题导致了执行计划产生差异。 这与“权限只控制是否能访问表不控制如何访问表”的一般概念是违背的所以需要注意这个权限问题。 为了提供子表统计信息权限建议显示对用户授权所有子表查询权限就可以避免以上问题
grant select on table_partition_allname to username;分区表的维护
分区表ATTACH/DETACH基本操作
attach/detach可以将一个已存在的表作为分区添加/分离分区表。attach/detach在维护工作中很有用。 先来看看create table…partition of方式添加分区和drop table删除分区的锁情况
锁矩阵https://www.postgresql.org/docs/current/explicit-locking.html
申请的锁https://postgres-locks.husseinnasser.com
partition of新增分区
--session1 开启一个事务只读数据select * from lzlpartition1 where date_created2023-01-01 00:00:00;id | name | date_created
-------------------------------------------------------------8249 | 256ac66bb53d31bc6124294238d6410c | 2023-01-01 00:00:00--session3 查看锁情况。读取一个分区数据时要在子分区和主表上同时获得锁select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | AccessShareLock | t--session2 partition of方式添加分区create table LZLPARTITION1_202305 partition of LZLPARTITION1 for values from (2023-05-01 00:00:00) to (2023-06-01 00:00:00);
--等待--session3 再次查看锁select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
----------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 308525 | AccessExclusiveLock | f --此为partition of会话--session4 再随便来一个查询select * from lzlpartition1 where date_created2023-01-01 00:00:00;
--等待--session4再次查看锁select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
----------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 308525 | AccessExclusiveLock | frelation | dbmgr | lzlpartition1 | [null] | [null] | 84774 | AccessShareLock | f --查询阻塞partition of方式新增分区时会申请主表的 AccessExclusiveLock等待主表一切事务的同时也会阻塞主表的一切事务。
虽然partition of语句本身执行很快但是如果遇到主表上有长事务那么分区表上的所有操作都会长时间停滞。如果没有停机窗口直接使用partition of方式新增分区是不推荐的。
drop table删除分区
--session1 再次开启只读事务
--session2 删除分区表的子分区drop table lzlpartition1_202305;
--等待--session3 查看锁情况select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
----------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 308525 | AccessExclusiveLock | fdrop table删除子分区时会申请主表上的AccessExclusiveLock等待一切和阻塞一切。同样的在生产环境需要谨慎使用。
attach添加分区 attach将一个已存在的普通表附加到分区表上 虽然attach跟partition of都可以添加分区但是需要注意ATTACH不会自动创建索引、约束、默认值、行级触发器这点跟partition of是不同的。 先创建一个表
--减少繁琐的ddllike方式创建表
CREATE TABLE lzlpartition1_202305(LIKE lzlpartition1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS);再观察attach的是否被阻塞
--session1 开启读写事务
begin;
BEGINinsert into lzlpartition1 values(1234,abcd,2023-01-01 01:00:00);
INSERT 0 1
--session3 查看锁情况select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
-------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 311449 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | RowExclusiveLock | t
--dml语句会获得分区主表和对应分区子表的RowExclusiveLock--session2 attach新建的表到分区主表上alter table lzlpartition1 attach partition lzlpartition1_202305 for values from (2023-05-01 00:00:00) to (2023-06-01 00:00:00);
ALTER TABLEattach只会申请SHARE UPDATE EXCLUSIVE锁比ACCESS EXCLUSIVE低很多。
attach与读写都互不阻塞所以推荐以attach方式添加分区不影响业务可在线执行。
detach删除分区
detach将一个分区脱离分区表成为一个普通表
--session1 保持dml事务不提交
--session2 detach一个分区alter table lzlpartition1 detach partition lzlpartition1_202305;--等待--session3 查看锁情况 select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
----------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 311449 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 311449 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 308525 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 308525 | AccessExclusiveLock | f
detach跟attach不同detach申请了主表的AccessExclusiveLock等待一切和阻塞一切。 detach concurrently PostgreSQL 14开始detach新增了两种语法CONCURRENTLY 和FINALIZE ALTER TABLE [ IF EXISTS ] name DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ] detach concurrently内部会开启两次事务第一次事务会在主表和子表上都申请SHARE UPDATE EXCLUSIVE锁分区会标记为正在detach的状态此时会等待分区表上的所有事务提交。一旦这些事务全部都提交了第二次事务会申请主表上的 SHARE UPDATE EXCLUSIVE锁和那个子表上ACCESS EXCLUSIVE锁随后detach concurrently完成。
另外detach concurrently后的子分区会保留约束由分区约束转化为check约束保留在detach后的表上
DETACH CONCURRENTLY的限制
DETACH CONCURRENTLY不能放在事务块中分区表不能有default分区
concurrently的阻塞情况
--session1
lzldb begin;
BEGIN
lzldb* insert into lzlpartition1 values(1234,abcd,2023-01-01 01:00:00);
INSERT 0 1--session2 detach concurrently
lzldb alter table lzlpartition1 detach partition lzlpartition1_202301 concurrently;
--等待--session3 查看锁3691 | insert into lzlpartition1 values(1234,abcd,2023-01-01 01:00:00); | Client | ClientRead3940 | alter table lzlpartition1 detach partition lzlpartition1_202301 concurrently; | Lock | virtualxid3947 | select pid,query,wait_event_type,wait_event from pg_stat_activity; | |
--detach会话是3940非常奇怪detach的等待事件是virtualxid等待事件类型是Lock--查看锁的情况
lzldb select locktype,database,relation,virtualtransaction,pid,mode,granted from pg_locks where pid in (3691,3940);locktype | database | relation | virtualtransaction | pid | mode | granted
----------------------------------------------------------------------------------------virtualxid | | | 6/9 | 3940 | ExclusiveLock | trelation | 16387 | 40969 | 5/179 | 3691 | RowExclusiveLock | trelation | 16387 | 40963 | 5/179 | 3691 | RowExclusiveLock | tvirtualxid | | | 5/179 | 3691 | ExclusiveLock | tvirtualxid | | | 6/9 | 3940 | ShareLock | ftransactionid | | | 5/179 | 3691 | ExclusiveLock | t
--此时的detach还没有等待表上的锁而是在等待virtualxid的ShareLock--session4 做个插入
lzldb insert into lzlpartition1 values(12345,abcd,2023-01-01 01:00:00);
ERROR: no partition of relation lzlpartition1 found for row
DETAIL: Partition key of the failing row contains (date_created) (2023-01-01 01:00:00).
lzldb insert into lzlpartition1 values(12345,abcd,2023-02-01 01:00:00);
INSERT 0 1
--此时detach的分区已经不能插入其他分区可以插入
--如果通过分区插入会怎样呢可以正常插入
lzldb insert into lzlpartition1_202301 values(12345,abcd,2023-01-01 01:00:00);
INSERT 0 1
--注意此时它还是个分区表的分区还不是一个普通表不过它已经被标记为不可用了--\d查看分区处于DETACH PENDING状态
Partitions: lzlpartition1_202301 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00) (DETACH PENDING),lzlpartition1_202302 FOR VALUES FROM (2023-02-01 00:00:00) TO (2023-03-01 00:00:00)--把insert的session1提交/回滚
lzldb rollback;
ROLLBACK--session2立即完成
lzldb alter table lzlpartition1 detach partition lzlpartition1_202301 concurrently;
ALTER TABLE
FINALIZE
--session1
lzldb begin;
BEGIN
lzldb* insert into lzlpartition1 values(1234,abcd,2023-01-01 01:00:00);
INSERT 0 1--session2 detach concurrently手动cancel
lzldb alter table lzlpartition1 detach partition lzlpartition1_202301 concurrently;
^CCancel request sent
ERROR: canceling statement due to user request--\d查看分区表分区处于DETACH PENDING状态
Partitions: lzlpartition1_202301 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00) (DETACH PENDING),lzlpartition1_202302 FOR VALUES FROM (2023-02-01 00:00:00) TO (2023-03-01 00:00:00)--DETACH PENDING的分区SQL已经不会去访问了
lzldb explain select * from lzlpartition1;QUERY PLAN
-----------------------------------------------------------------------------------------Seq Scan on lzlpartition1_202302 lzlpartition1 (cost0.00..752.81 rows38881 width45)--finalize使之完成
lzldb alter table lzlpartition1 detach partition lzlpartition1_202301 finalize;
--等待--查看锁情况
lzldb select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;lzldb-# locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
-------------------------------------------------------------------------------------------------------------relation | lzldb | lzlpartition1 | | | 3691 | RowExclusiveLock | trelation | lzldb | lzlpartition1_202301 | | | 3940 | AccessExclusiveLock | frelation | lzldb | lzlpartition1 | | | 3940 | ShareUpdateExclusiveLock | trelation | lzldb | lzlpartition1_202301 | | | 3691 | RowExclusiveLock | t
--3940finalize申请了分区主表的ShareUpdateExclusiveLock和子表的AccessExclusiveLock
--由于是插入的是数据的分区刚好是detach的分区所以发生等待--session1结束
lzldb! rollback;
ROLLBACK
--session2立即完成
lzldb alter table lzlpartition1 detach partition lzlpartition1_202301 finalize;
ALTER TABLE
虽然detach的分区会申请8级锁但是一般业务也没有直接通过子分区写数据的所以只需要关注分区表的长事务尽快完成就行一般不需要担心造成该分区子表上的后续阻塞。
在线detach小结
detach concurrently的阻塞情况跟CIC有点类似不会阻塞其他事务但是其本身会等待已有的事务完成这点在lock上不太容易看出来在detach concurrently期间分区会处于DETACH PENDING中间状态该状态有点类似invisiblesql不会找到这个分区如果是长事务导致的DETACH PENDING应及时结束长事务如果是中断导致的DETACH PENDING可以使用FINALIZE使其完成detach。
利用约束减少attach时间
分区数据情况准备操作一个较大分区的attach操作 SELECT tableoid::regclass AS partition, count(*) FROM lzlpartition1 group by partition;partition | count
-------------------------------lzlpartition1_202301 | 2592001lzlpartition1_202302 | 38881注意这个202301的分区有一个partition constraint \d lzlpartition1_202301Table public.lzlpartition1_202301Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------------
id | integer | | not null | | plain | |
name | character varying(50) | | | | extended | |
date_created | timestamp without time zone | | not null | now() | plain | |
Partition of: lzlpartition1 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00)
Partition constraint: ((date_created IS NOT NULL) AND (date_created 2023-01-01 00:00:00::timestamp without time zone) AND (date_created 2023-02-01 00:00:00::timestamp without t
Indexes:lzlpartition1_202301_pkey PRIMARY KEY, btree (id, date_created)
Access method: heapdetach分区 alter table lzlpartition1 detach partition lzlpartition1_202301;--detach后partition constraint就没有了\d lzlpartition1_202301Table public.lzlpartition1_202301Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------------
id | integer | | not null | | plain | |
name | character varying(50) | | | | extended | |
date_created | timestamp without time zone | | not null | now() | plain | |
Indexes:lzlpartition1_202301_pkey PRIMARY KEY, btree (id, date_created)
Access method: heap不添加check约束attach alter table lzlpartition1 attach partition lzlpartition1_202301 for values from (2023-01-01 00:00:00) to (2023-02-01 00:00:00);
ALTER TABLE
Time: 343.498 ms由于要扫描分区数据是否满足分区范围attach耗时300ms
添加check约束attach alter table lzlpartition1 detach partition lzlpartition1_202301;alter table lzlpartition1_202301 add constraint chk_202301 CHECK
- ((date_created IS NOT NULL) AND (date_created 2023-01-01 00:00:00::timestamp without time zone) AND (date_created 2023-02-01 00:00:00::timestamp without time zone));
ALTER TABLE
Time: 355.458 ms上面添加check约束的耗时跟没有check时的attach操作耗时差不多因为添加check约束同样也要扫描检查所有数据。 check约束添加完后再attach此时的attach操作非常快就能完成 alter table lzlpartition1 attach partition lzlpartition1_202301 for values from (2023-01-01 00:00:00) to (2023-02-01 00:00:00);
ALTER TABLE
Time: 1.480 ms删除check约束 \d lzlpartition1_202301;Table public.lzlpartition1_202301Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------------id | integer | | not null | | plain | | name | character varying(50) | | | | extended | | date_created | timestamp without time zone | | not null | now() | plain | |
Partition of: lzlpartition1 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00)
Partition constraint: ((date_created IS NOT NULL) AND (date_created 2023-01-01 00:00:00::timestamp without time zone) AND (date_created 2023-02-01 00:00:00::timestamp without t
Indexes:lzlpartition1_202301_pkey PRIMARY KEY, btree (id, date_created)
Check constraints:chk_202301 CHECK (date_created IS NOT NULL AND date_created 2023-01-01 00:00:00::timestamp without time zone AND date_created 2023-02-01 00:00:00::timestamp without time
Access method: heap注意check constraint和partition constraint是不一样的概念虽然他俩的约束内容可以是一致的。attach使用了check约束但是不会进行合并可以显式删除这个多余的check。 alter table lzlpartition1_202301 drop constraint chk_202301;
ALTER TABLE另外需要关注drop constraint申请了当前子分区上AccessExclusiveLock这是最高级别的锁会阻塞任何操作。所以当前子分区上有事务谨慎执行drop constraint。 select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
----------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1 | [null] | [null] | 448243 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 448243 | RowExclusiveLock | trelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 444399 | AccessShareLock | trelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 444399 | AccessExclusiveLock | f --这个就是drop constraint会话relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 448243 | RowExclusiveLock | tso 在attach分区时先添加check约束是比较有用它可以减少attach的执行时间数据检查在attach前完成就可以了
分区表新增分区的正确姿势
我们现在知道attach可以在线执行而partition of/drop table/detach都会申请等待和阻塞一切的AccessExclusiveLock so 建议用attach新建分区。partition of/detach都会等待和阻塞一切事务而attach不会被只读/DML事务阻塞 所以添加分区应该使用attach并提前创建check约束删除约束时需要关注长事务问题。 分区表添加分区的正确姿势
--减少繁琐的ddllike方式创建表
CREATE TABLE lzlpartition1_202303(LIKE lzlpartition1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
--参考其他分区的Partition constraint添加表的check约束减少attach检查约束的时间
alter table lzlpartition1_202303 add constraint chk_202303 CHECK ((date_created IS NOT NULL) AND (date_created 2023-03-01 00:00:00::timestamp without time zone) AND (date_created 2023-04-01 00:00:00::timestamp without time zone));
--attach方式添加分区
alter table LZLPARTITION1 attach partition LZLPARTITION1_202303 for values from (2023-03-01 00:00:00) to (2023-04-01 00:00:00);
--可选。在新分区有事务之前删除多余的check约束
alter table lzlpartition1_202303 drop constraint chk_202303;分区索引的锁
只读事务时创建/删除分区索引
当分区上有共享锁时AccessShareLock也就是分区表上有查询事务的情况下 CREATE INDEX ON lzlpartition1创建成功注意没有加concurrentlyDROP INDEX lzlpartition1失败
--session1 开启事务读取分区表数据begin;
BEGINselect count(*) from lzlpartition1 where date_created2023-01-01 00:00:00 and date_created2023-01-02 00:00:00;count
-------86401
(1 row)
--session2 创建索引成功create index idx_datecreated on lzlpartition1(date_created);;
CREATE INDEX--session2 删除索引等待drop index idx_datecreated;--session3 查看锁select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
---------------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301_pkey | [null] | [null] | 300371 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 99598 | AccessExclusiveLock | frelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 300371 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 300371 | AccessShareLock | tcreate index没有申请表上的AccessExclusiveLock但是drop index申请了表上的AccessExclusiveLock。 从这个的例子可以得出 只读事务不会阻塞创建索引但会阻塞删除索引
更新事务时创建/删除分区索引
--session1 开启更新事务begin;
BEGINupdate lzlpartition1 set nameabc where date_created2023-01-01 10:00:00;
UPDATE 1
--session2 创建分区索引等待create index idx_datecreated on lzlpartition1(date_created);--session3 查看锁情况
select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
- where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
------------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301_pkey | [null] | [null] | 300371 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 99598 | ShareLock | frelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 300371 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 300371 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 300371 | RowExclusiveLock | tcreate index会话99598申请分区主表的ShareLock锁DML事务会话300371 持有该子分区和主表的RowExclusiveLock
create index无concurrently会话申请主表ShareLock 只读事务会话申请主表和子表的AccessShareLock 更新事务会话申请主表和子表的RowExclusiveLock AccessShareLock不阻塞ShareLock所以查询不阻塞create index无concurrently RowExclusiveLock阻塞ShareLock所以DML阻塞create index无concurrently
concurrently创建分区索引
注意在分区表上不能用concurrently创建索引 create index concurrently idx_datecreated on lzlpartition1(date_created);
ERROR: 0A000: cannot create index on partitioned table lzlpartition1 concurrently
LOCATION: DefineIndex, indexcmds.c:665有个patch https://commitfest.postgresql.org/35/2815/在解决这个问题。
目前可以在分区子表上concurrently创建索引。
--session1 仍然使用之前的DML事务
--session2 concurrently方式在子表上创建索引等待create index concurrently idx_datecreated_202301 on lzlpartition1_202301(date_created);--session3 查询锁情况select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
--------------------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301_pkey | [null] | [null] | 300371 | RowExclusiveLock | trelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 99598 | ShareUpdateExclusiveLock | trelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 300371 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 300371 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 300371 | RowExclusiveLock | tconcurrently申请的锁降低了一级跟ROW EXCL不冲突了。锁都不冲突了但是为什么concurrently本身还是被阻塞了呢 it must wait for all existing transactions that could potentially modify or use the index to terminate. 官方文档解释concurrently需要等待潜在修改/使用索引的事务完成我们这里的update语句更新了索引字段所以concurrently需要等待它完成。 虽然concurrently本身因为之前的DML语句没有完成但是这也有一个好处concurrently不会阻塞后续的DML语句。
--concurrently没有完成的情况下
--session4 更新一条记录update lzlpartition1 set nameabc where date_created2023-01-01 12:00:00;
UPDATE 1汇总分区索引的锁问题
分区表上只读/读写/创建索引时的锁跟普通表是差不多的只需要注意事务会在分区主表和子表上都会加锁所以后续阻塞链的锁更重时会影响所有分区只读事务不会阻塞create index但是会阻塞drop indexDML会阻塞create index也会阻塞create index concurrently但是concurrently不会阻塞DML虽然在分区表上create index可以自动在各个分区和未来分区上创建索引但是由于阻塞问题不建议在生产直接使用不能在分区主表上直接使用concurrently所以需要在各个分区子表上concurrently创建索引concurrently不会阻塞后续的事务但本身会被之前的长事务阻塞也可能导致创建的索引失效所以需要关注长事务问题
创建分区索引的正确姿势
虽然不能以concurrently方式在分区表上创建索引但可以在分区子表用concurrently创建索引需要用到语法 CREATE INDEX ON ONLY 在主表上创建一个无效索引不会在子分区自动创建索引 CREATE INDEX CONCURRENTLY concurrently方式在子分区上创建索引 ALTER INDEX .. ATTACH PARTITION将分区索引ATTACH到主索引上所有子分区索引ATTACH后分区主表索引自动标记为有效。 不过在执行这些命令时仍然需要关注锁的情况
下面观察上面两个语句申请锁和阻塞的情况 过程中全程开启session1的DML显示事务
ONLY创建索引的阻塞情况 CREATE INDEX IDX_DATECREATED ON ONLY lzlpartition1(date_created);
--等待--查看锁情况locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
-------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 448243 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 448243 | RowExclusiveLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 444399 | ShareLock | fONLY创建索引会申请ShareLock锁ShareLock跟RowExclusiveLock是相互阻塞的。所以虽然ONLY本身执行会很快但是ONLY创建索引也不是无脑使用。
--将DML事务结束后ONLY创建索引完成idx_datecreated btree (date_created) INVALIDCREATE INDEX ON ONLY在分区主表上创建了一个失效索引且不会在子分区创建索引。
ATTACH索引的阻塞情况
--将ONLY索引创建完成后再开启session1的DML显示事务begin;
BEGINinsert into lzlpartition1 values(1111,abc,2023-01-01 00:00:00);
INSERT 0 1--session2 concurrently创建子分区的索引create index concurrently idx_datecreated_202302 on lzlpartition1_202302(date_created);
CREATE INDEX --202302分区索引创建完成create index concurrently idx_datecreated_202304 on lzlpartition1_202304(date_created);
CREATE INDEX --202302分区索引创建完成create index concurrently idx_datecreated_202301 on lzlpartition1_202301(date_created);
----创建202302分区索引等待concurrently会等待潜在使用索引的事务完成我们这里的显示事务只插入了202301分区也只有这个分区的concurrently创建索引没有完成。
--完成session1的DML显示事务等待索引完成后然后再次开启事务commit;
COMMITbegin;
BEGINinsert into lzlpartition1 values(1111,abc,2023-01-01 00:00:01);
INSERT 0 1
--session2 attach索引 ALTER INDEX idx_datecreated ATTACH PARTITION idx_datecreated_202302;
ALTER INDEX --成功ATTACH\d idx_datecreatedPartitioned index public.idx_datecreatedColumn | Type | Key? | Definition | Storage | Stats target
--------------------------------------------------------------------------------------date_created | timestamp without time zone | yes | date_created | plain |
btree, for table public.lzlpartition1, invalid
Partitions: idx_datecreated_202302 --202302子分区索引已经attach索引仍为invalid
Access method: btree
--将剩余的子分区索引全部attachALTER INDEX idx_datecreated ATTACH PARTITION idx_datecreated_202301;
ALTER INDEX --成功ATTACHALTER INDEX idx_datecreated ATTACH PARTITION idx_datecreated_202304;
ALTER INDEX --成功ATTACH
--完成所有子分区索引attach后主表索引自动有效\d idx_datecreatedPartitioned index public.idx_datecreatedColumn | Type | Key? | Definition | Storage | Stats target
--------------------------------------------------------------------------------------date_created | timestamp without time zone | yes | date_created | plain |
btree, for table public.lzlpartition1
Partitions: idx_datecreated_202301,idx_datecreated_202302,idx_datecreated_202304
Access method: btreeattach不会被DML阻塞直接完成。此时用partition of创建的新分区也会自动创建子分区索引。
综上所述
CREATE INDEX ON ONLY会申请ShareLock锁跟DML申请的RowExclusiveLock是相互阻塞的CREATE INDEX CONCURRENTLY会申请ShareUpdateExclusiveLock锁不会阻塞DML申请的RowExclusiveLock但是CREATE INDEX CONCURRENTLY需要等待DML事务完成才能完成concurrently可以获得锁但不能完成ALTER INDEX .. ATTACH PARTITION会申请AccessShareLock这是最轻的锁跟DML申请的RowExclusiveLock相互不阻塞。查询申请的是AccessShareLock最轻的锁除非DDL申请AccessExclusiveLock最重的锁不然不会发生阻塞
所以直接在分区上create index会阻塞DML是不可取的 创建分区索引的正确姿势
--ONLY方式在分区主表上创建失效索引。快会阻塞后续dml会影响业务需要关注长事务
CREATE INDEX IDX_DATECREATED ON ONLY lzlpartition1(date_created);
--CONCURRENTLY在各个分区子表上创建索引。慢不会阻塞后续dml不会影响业务但需要关注DML长事务防止本身失败
create index concurrently idx_datecreated_202302 on lzlpartition1_202302(date_created);
--所有索引attach。快不会发生业务阻塞ALTER INDEX idx_datecreated ATTACH PARTITION idx_datecreated_202302;分区表添加主键和唯一索引
“主键索引”功能上等于“唯一索引null约束”但是主键只能有一个。分区表创建唯一索引可以参考上面的索引创建最佳实践only创建主表索引、concurrently创建子表索引、attach。 而主键虽然支持普通表using index语法但是目前不支持分区表这样使用 ALTER TABLE lzlpartition1 ADD CONSTRAINT pk_id_date_created PRIMARY KEY USING INDEX idx_uniq;
ERROR: 0A000: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables
LOCATION: ATExecAddIndexConstraint, tablecmds.c:8032也就是说可以通过提前创建not null约束attach索引的方式创建一个非空的唯一索引但是最后一步using index添加主键却不行。
下面看下直接添加/删除主键的阻塞情况
直接删除主键
--session 1 begin;
BEGIN
Time: 0.318 msselect * from lzlpartition1 where date_created2023-01-01 22:00:00;id | name | date_created
-------------------------------------------------------------7715 | beee680a86e1d12790489e9ab4a4351b | 2023-01-01 22:00:00--session2 删除主键等待 alter table lzlpartition1 drop constraint lzlpartition1_pkey;--session3 观察 select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
--------------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301_pkey | [null] | [null] | 21659 | AccessShareLock | trelation | dbmgr | lzlpartition1_202301 | [null] | [null] | 21659 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 95016 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 95016 | AccessExclusiveLock | frelation | dbmgr | lzlpartition1 | [null] | [null] | 21659 | AccessShareLock | t删除主键申请的是AccessExclusiveLock阻塞一切
直接添加主键
--session1事务结束session2的删除主键完成
--session1再次开启只读事务
--session2在分区表上添加主键等待ALTER TABLE lzlpartition1 ADD PRIMARY KEY(id, date_created);--session3 观察锁select l.locktype,d.datname,r.relname,l.virtualxid,l.transactionid,l.pid,l.mode,l.granted from pg_locks l left join pg_database d on l.databased.oid left join pg_class r on l.relationr.oid
where relname like %lzlpartition1%;locktype | datname | relname | virtualxid | transactionid | pid | mode | granted
---------------------------------------------------------------------------------------------------------relation | dbmgr | lzlpartition1_202301 | [null] | [null] | 21659 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 95016 | AccessShareLock | trelation | dbmgr | lzlpartition1 | [null] | [null] | 95016 | AccessExclusiveLock | f --添加主键的会话relation | dbmgr | lzlpartition1 | [null] | [null] | 21659 | AccessShareLock | t添加主键在主表上申请AccessExclusiveLock阻塞一切。 分区表上添加索引很慢主键又会造成后续的阻塞目前没有影响较小的在分区表上添加主键的办法。虽然没有达到目的可以考虑用“attach唯一索引非空约束”的办法或者只能申请较长的停分区表业务等待创建索引完成或者通过第三方同步工具将数据插入一个带主键的分区表。
hash分区表添加分区
如果新增后的分区数为之前的整数倍那么我们将会知道新分区的数据来自哪个老分区。比如将原本只有3个分区的hash分区表做成6个分区的我们可以知道分区数据来源
虽然了解了这种简单的数据特性但实际情况可能没有什么用因为新分区的hash分区总是被暴力插入的。从操作上“3-4”的新增分区操作和“3-6”的新增分区操作没有什么区别。 目前成熟的数据同步工具已经非常多了比如使用dts把表插入到新表中然后做表切换停机时间会很短生产环境应优先选择这个方案。 下面是主要是测试和观察hash分区表手动新增整数倍分区时的操作
分区信息
SELECT tableoid::regclass,count(*) FROM orders group by tableoid::regclass;tableoid | count
------------------orders_p1 | 3377orders_p3 | 3354orders_p2 | 33692. detach分区3个分区的hash原生分区表再添加3个分区ALTER TABLE orders DETACH PARTITION orders_p1;
ALTER TABLE orders DETACH PARTITION orders_p2;
ALTER TABLE orders DETACH PARTITION orders_p3;rename分区
ALTER TABLE orders_p1 RENAME TO bak_orders_p1;
ALTER TABLE orders_p2 RENAME TO bak_orders_p2;
ALTER TABLE orders_p3 RENAME TO bak_orders_p3;在老表上创建6个hash分区
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 6, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 6, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 6, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders FOR VALUES WITH (MODULUS 6, REMAINDER 3);
CREATE TABLE orders_p5 PARTITION OF orders FOR VALUES WITH (MODULUS 6, REMAINDER 4);
CREATE TABLE orders_p6 PARTITION OF orders FOR VALUES WITH (MODULUS 6, REMAINDER 5);查看分区信息 注意分区约束使用的函数
\d orders_p1Table public.orders_p1Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------------------------------------------------------------------------------------------order_id | integer | | | | plain | | name | character varying(10) | | | | extended | |
Partition of: orders FOR VALUES WITH (modulus 6, remainder 0)
Partition constraint: satisfies_hash_partition(412053::oid, 6, 0, order_id)
Access method: heap计算老分区数据需要插入到哪个新分区上 例如原先为modulus 3remainder 0的分区需要把数据分别插入到modulus 6remainder0和3两个分区中。 select count(*) from bak_orders_p1 where satisfies_hash_partition(412053::oid, 6, 0, order_id)true;count
-------1776select count(*) from bak_orders_p1 where satisfies_hash_partition(412053::oid, 6, 3, order_id)true;count
-------1601select count(*) from bak_orders_p1;count
-------33776.通过分区子表插入数据 可以直接把数据插入对应的分区子表上而不是通过分区主表插入
INSERT INTO orders_p1 SELECT * FROM bak_orders_p1 where satisfies_hash_partition(412053::oid, 6, 0, order_id)true;
INSERT INTO orders_p2 SELECT * FROM bak_orders_p2 where satisfies_hash_partition(412053::oid, 6, 1, order_id)true;
INSERT INTO orders_p3 SELECT * FROM bak_orders_p3 where satisfies_hash_partition(412053::oid, 6, 2, order_id)true;
INSERT INTO orders_p4 SELECT * FROM bak_orders_p1 where satisfies_hash_partition(412053::oid, 6, 3, order_id)true;
INSERT INTO orders_p5 SELECT * FROM bak_orders_p2 where satisfies_hash_partition(412053::oid, 6, 4, order_id)true;
INSERT INTO orders_p6 SELECT * FROM bak_orders_p3 where satisfies_hash_partition(412053::oid, 6, 5, order_id)true;验证3个老分区的数据已插入到6个新分区中
SELECT tableoid::regclass,count(*) FROM orders group by tableoid::regclass;
tableoid | count
------------------
orders_p3 | 1665
orders_p5 | 1678
orders_p1 | 1776
orders_p6 | 1689
orders_p4 | 1601
orders_p2 | 1691修改分区字段长度索引会重建
修改字段需要考虑三个方面表重写、索引重建、统计信息丢失
修改字段类型、字段长度减少都会重写表字段长度增加仅会丢失统计信息一个例外情况是将长度改小或者int4改int8会重写表字段长度增加不会重建索引一个例外情况是分区表字段长度增加会重建索引如果这个字段有索引
修改字段参考PostgreSQL学徒。
这里主要测试分区表将字段改长的场景如果存在索引的话可能会引起分区表上的事务阻塞。 普通表将有索引的字段改长
--新建普通表和索引create table t111(id int,name varchar(50));
CREATE TABLEinsert into t111 values(1001,abc);
INSERT 0 1create index idx111 on t111(name);
CREATE INDEX--索引文件relfilenode为417728select pg_relation_filepath(idx111);pg_relation_filepath
----------------------base/16398/417728
(1 row)--将字段改长alter table t111 alter column name type varchar(60);
ALTER TABLE
--索引文件relfilenode为417728未发生变化普通表索引未重建select pg_relation_filepath(idx111);pg_relation_filepath
----------------------base/16398/417728分区表将有索引的字段改长
--在分区表上创建一个索引create index idx_name on lzlpartition1(name);
CREATE INDEX
--查看其中一个分区上的索引\d lzlpartition1_202301Table dbmgr.lzlpartition1_202301Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------------------id | integer | | | | plain | | name | character varying(50) | | | | extended | | date_created | timestamp without time zone | | not null | now() | plain | |
Partition of: lzlpartition1 FOR VALUES FROM (2023-01-01 00:00:00) TO (2023-02-01 00:00:00)
Partition constraint: ((date_created IS NOT NULL) AND (date_created 2023-01-01 00:00:00::timestamp without time zone) AND (date_created 2023-02-01 00:00:00::timestamp without time zone))
Indexes:lzlpartition1_202301_name_idx btree (name)
Access method: heap select pg_relation_filepath(lzlpartition1_202301_name_idx) idx,pg_relation_filepath(lzlpartition1_202301) tbl;idx | tbl
--------------------------------------base/16398/417810 | base/16398/417800
(1 row)--将索引字段改大分区表索引重建alter table lzlpartition1 alter column name type varchar(60);
ALTER TABLEselect pg_relation_filepath(lzlpartition1_202301_name_idx) idx,pg_relation_filepath(lzlpartition1_202301) tbl;idx | tbl
--------------------------------------base/16398/417814 | base/16398/417800--将索引字段改小分区表重写alter table lzlpartition1 alter column name type varchar(40);
ALTER TABLE
Time: 609.585 msselect pg_relation_filepath(lzlpartition1_202301_name_idx) idx,pg_relation_filepath(lzlpartition1_202301) tbl;idx | tbl
--------------------------------------base/16398/417828 | base/16398/417825--将索引字段保持原样分区表索引重建alter table lzlpartition1 alter column name type varchar(40);
ALTER TABLEselect pg_relation_filepath(lzlpartition1_202301_name_idx) idx,pg_relation_filepath(lzlpartition1_202301) tbl;idx | tbl
--------------------------------------base/16398/417834 | base/16398/417825普通表改大字段长度只需要关注统计信息会丢失int到bigint除外但是分区表在改大字段长度时如果这个字段上有索引不仅会丢失统计信息还会重建索引。由于alter修改字段是8级锁所以重建索引期间会导致长时间阻塞。 建议先把索引删除修改完字段后“父表ONLY子表CICATTACH”的方式建索引。
分区表维护小结
partition of/drop table/DETACH需要 ACCESS EXCLUSIVE锁推荐ATTACH/DETACH CONCURRENTLY它们不会造成阻塞DETACH CONCURRENTLY需关注已有长事务attach表分区前可以提前在分区上创建约束这样会减去在attach时扫描分区数据的时间目前不支持分区表CIC创建索引可以通过在“主表上only子表上concurrentlyattach索引”的方式创建分区索引减少业务阻塞时间分区表不支持using index方式创建主键需要关注分区表修改字段长度这个例外情况
分区表的优化
分区裁剪
分区裁剪Partition Pruning可以为声明式分区提升性能是分区表优化非常重要的特性。如果没有分区裁剪那么查询会扫描所有分区。当有分区裁剪时优化器可以通过where条件过滤那些不需要访问的分区 分区裁剪依赖于分区约束Partition constraint(\d可以看到也就是说查询必须带有分区键条件才能进行裁剪。这个约束不同于一般约束constraint它在分区创建时自动创建。 分区裁剪由enable_partition_pruning参数控制默认为on。
--没有分区裁剪时会访问所有分区set enable_partition_pruningoff;
SET explain select count(*) from lzlpartition1 where date_created2023-01-01;QUERY PLAN
--------------------------------------------------------------------------------------------------Aggregate (cost1872.08..1872.09 rows1 width8)- Append (cost0.00..1872.07 rows4 width0)- Seq Scan on lzlpartition1_202301 lzlpartition1_1 (cost0.00..992.30 rows1 width0)Filter: (date_created 2023-01-01 00:00:00::timestamp without time zone)- Seq Scan on lzlpartition1_202302 lzlpartition1_2 (cost0.00..864.12 rows1 width0)Filter: (date_created 2023-01-01 00:00:00::timestamp without time zone)- Seq Scan on lzlpartition1_202304 lzlpartition1_3 (cost0.00..15.62 rows2 width0)Filter: (date_created 2023-01-01 00:00:00::timestamp without time zone)--有分区裁剪时不需要访问的分区被排除set enable_partition_pruningon;
SET explain select count(*) from lzlpartition1 where date_created2023-01-01;QUERY PLAN
------------------------------------------------------------------------------------------Aggregate (cost992.30..992.31 rows1 width8)- Seq Scan on lzlpartition1_202301 lzlpartition1 (cost0.00..992.30 rows1 width0)Filter: (date_created 2023-01-01 00:00:00::timestamp without time zone)
(3 rows)官方文档说生成执行计划时发生裁剪那么explain有Subplans Removed字样经测试有时候没有就像上面的explain例子 分区裁剪可能发生在两个阶段生成执行计划时、真正执行时 为什么会发生这样的情况呢因为有时候只有执行时才会知道那些分区可以裁剪。有两种情况 Parameterized Nested Loop Joins: The parameter from the outer side of the join can be used to determine the minimum set of inner side partitions to scan. Initplans: Once an initplan has been executed we can then determine which partitions match the value from the initplan.
模拟执行时发生裁剪从其他表拿数据优化器肯定不知道数据是什么就无法以此为依据在执行计划时发生分区裁剪
--创建一个其他表create table x(date_created timestamp);
CREATE TABLE insert into x values(2023-01-01 09:00:00);
INSERT 0 1--仅生成执行计划不执行没有发生裁剪explain select count(*) from lzlpartition1 where date_created(select date_created from x);QUERY PLAN
--------------------------------------------------------------------------------------------------Aggregate (cost1904.68..1904.69 rows1 width8)InitPlan 1 (returns $0)- Seq Scan on x (cost0.00..32.60 rows2260 width8)- Append (cost0.00..1872.07 rows4 width0)- Seq Scan on lzlpartition1_202301 lzlpartition1_1 (cost0.00..992.30 rows1 width0)Filter: (date_created $0)- Seq Scan on lzlpartition1_202302 lzlpartition1_2 (cost0.00..864.12 rows1 width0)Filter: (date_created $0)- Seq Scan on lzlpartition1_202304 lzlpartition1_3 (cost0.00..15.62 rows2 width0)Filter: (date_created $0)
(10 rows)--执行sql发生裁剪。关键字never executedexplain analyze select count(*) from lzlpartition1 where date_created(select date_created from x);QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost1904.68..1904.69 rows1 width8) (actual time5.680..5.682 rows1 loops1)InitPlan 1 (returns $0)- Seq Scan on x (cost0.00..32.60 rows2260 width8) (actual time0.013..0.014 rows1 loops1)- Append (cost0.00..1872.07 rows4 width0) (actual time0.029..5.676 rows2 loops1)- Seq Scan on lzlpartition1_202301 lzlpartition1_1 (cost0.00..992.30 rows1 width0) (actual time0.008..5.652 rows2 loops1)Filter: (date_created $0)Rows Removed by Filter: 45382- Seq Scan on lzlpartition1_202302 lzlpartition1_2 (cost0.00..864.12 rows1 width0) (never executed)Filter: (date_created $0)- Seq Scan on lzlpartition1_202304 lzlpartition1_3 (cost0.00..15.62 rows2 width0) (never executed)Filter: (date_created $0)Planning Time: 0.157 msExecution Time: 5.732 ms
(13 rows)partition wise join
partition wise join可以减少分区连接的代价。 假设有两个分区表t1、t2他们都有3个分区(p1,p2,p3)且分区定义一致t1的每个分区10条数据t2的每个分区20条数据
t1t2p110 rows20 rowsp210 rows20 rowsp310 rows20 rows此时t1和t2表进行连接
正常情况下需要把所有两个分区数据取出进行连接他们的行的连接比较次数为 (101010)*(202020)180次有partition wise join的情况下因为结构差不多只需要连接对应的分区如 t1.p1t2.p1 t1.p2t2.p2 t1.p3t2.p3 此时的连接比较次数为 (10*20)*390次
在分区特别多的情况下partition wise join的代价会小很多。 参数enable_partitionwise_join是否开启partition wise join默认关闭
partition wise join的前提条件非常苛刻
连接条件必须包含分区键分区键必须是相同的数据类型分区必须一一对应
看上去条件苛刻两个不同的用途的表能产生partition wise join的情况是也是比较少的比较常见的应该是两个表都是range时间分区。还有一种情况如果是分区表自我连接也符合partition wise join的前提
--未开启partition wise join的情况explain select p1.*,p2.name from lzlpartition1 p1,lzlpartition1 p2 where p1.date_createdp2.date_created and p2.name256ac66bb53d31bc6124294238d6410c;QUERY PLAN
----------------------------------------------------------------------------------------------------------------Hash Join (cost546.64..9256.34 rows182252 width288)Hash Cond: (p1.date_created p2.date_created)- Append (cost0.00..2085.46 rows85364 width150)- Seq Scan on lzlpartition1_202301 p1_1 (cost0.00..878.84 rows45384 width150)- Seq Scan on lzlpartition1_202302 p1_2 (cost0.00..765.30 rows39530 width150)- Seq Scan on lzlpartition1_202304 p1_3 (cost0.00..14.50 rows450 width150)- Hash (cost541.30..541.30 rows427 width146)- Append (cost7.17..541.30 rows427 width146)- Bitmap Heap Scan on lzlpartition1_202301 p2_1 (cost7.17..284.30 rows227 width146)Recheck Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Index Scan on lzlpartition1_202301_name_idx (cost0.00..7.12 rows227 width0)Index Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Heap Scan on lzlpartition1_202302 p2_2 (cost6.95..248.52 rows198 width146)Recheck Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Index Scan on lzlpartition1_202302_name_idx (cost0.00..6.90 rows198 width0)Index Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Heap Scan on lzlpartition1_202304 p2_3 (cost2.66..6.35 rows2 width146)Recheck Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Index Scan on lzlpartition1_202304_name_idx (cost0.00..2.66 rows2 width0)Index Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)
(20 rows)--开启partition wise join的情况set enable_partitionwise_join on;
SETM explain select p1.*,p2.name from lzlpartition1 p1,lzlpartition1 p2 where p1.date_createdp2.date_created and p2.name256ac66bb53d31bc6124294238d6410c;QUERY PLAN
----------------------------------------------------------------------------------------------------------------Append (cost287.14..2529.83 rows438 width288)- Hash Join (cost287.14..1338.49 rows232 width288)Hash Cond: (p1_1.date_created p2_1.date_created)- Seq Scan on lzlpartition1_202301 p1_1 (cost0.00..878.84 rows45384 width150)- Hash (cost284.30..284.30 rows227 width146)- Bitmap Heap Scan on lzlpartition1_202301 p2_1 (cost7.17..284.30 rows227 width146)Recheck Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Index Scan on lzlpartition1_202301_name_idx (cost0.00..7.12 rows227 width0)Index Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Hash Join (cost250.99..1166.55 rows202 width288)Hash Cond: (p1_2.date_created p2_2.date_created)- Seq Scan on lzlpartition1_202302 p1_2 (cost0.00..765.30 rows39530 width150)- Hash (cost248.52..248.52 rows198 width146)- Bitmap Heap Scan on lzlpartition1_202302 p2_2 (cost6.95..248.52 rows198 width146)Recheck Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Index Scan on lzlpartition1_202302_name_idx (cost0.00..6.90 rows198 width0)Index Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Hash Join (cost6.37..22.60 rows4 width288)Hash Cond: (p1_3.date_created p2_3.date_created)- Seq Scan on lzlpartition1_202304 p1_3 (cost0.00..14.50 rows450 width150)- Hash (cost6.35..6.35 rows2 width146)- Bitmap Heap Scan on lzlpartition1_202304 p2_3 (cost2.66..6.35 rows2 width146)Recheck Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)- Bitmap Index Scan on lzlpartition1_202304_name_idx (cost0.00..2.66 rows2 width0)Index Cond: ((name)::text 256ac66bb53d31bc6124294238d6410c::text)
(25 rows)在没有开启partition wise join的情况下优化器需要先访问分区表p2的所有分区数据符合条件的放一起(append)然后与分区表p1的所有分区数据通过分区键连接(Hash Join)。 在开启partition wise join的情况下优化器将p1、p2两个分区表实际上是一个访问了两次所对应的分区相连接 p1_1p2_1 Hash Join p1_2p2_2 Hash Join p1_3p2_3 Hash Join 然后再把数据合到一起append。 如果数据分区足够多再加上分区裁剪partition wise join会有很好的优化效果。
partition wise grouping/aggregation
分区表在进行分区数据聚合计算时分区可以各自算各自的不需要扫描所有分区数据进行聚合计算只需要各自分区的数据聚合计算完成后汇总返回即可。 没有partition wise grouping本质上是“先扫描所有分区再聚合计算”有partition wise grouping是“先分区聚合计算再汇合数据”。
partition wise grouping的优势如下
分区在foreign server时可以将聚合算子下推到foreign server聚合到hash表时每个分区而不是所有分区去使用内存hash表的空间可以减少内存使用聚合算法下方到各自的分区可以更好的使用索引、并行等等特性更少的数据对比。虽然数据扫描都是一样的但是减少了数据对比比如最后一个分区的数据不需要与第一个分区的数据进行对比
参数enable_partitionwise_aggregate是否开启partition wise grouping/aggregation默认关闭
partition wise aggregate示例 vacuum (analyze) lzlpartition1;--未开启wise aggset enable_partitionwise_aggregate off;
SETexplain select date_created,min(id),count(*) from lzlpartition1 group by date_created order by 1,2,3;QUERY PLAN
-------------------------------------------------------------------------------------------------------------Sort (cost10354.94..10562.89 rows83180 width20)Sort Key: lzlpartition1.date_created, (min(lzlpartition1.id)), (count(*))- HashAggregate (cost2725.69..3557.49 rows83180 width20)Group Key: lzlpartition1.date_created- Append (cost0.00..2085.46 rows85364 width12)- Seq Scan on lzlpartition1_202301 lzlpartition1_1 (cost0.00..878.84 rows45384 width12)- Seq Scan on lzlpartition1_202302 lzlpartition1_2 (cost0.00..765.30 rows39530 width12)- Seq Scan on lzlpartition1_202304 lzlpartition1_3 (cost0.00..14.50 rows450 width12)--开启wise aggset enable_partitionwise_aggregate on;
SETexplain select date_created,min(id),count(*) from lzlpartition1 group by date_created order by 1,2,3;QUERY PLAN
-------------------------------------------------------------------------------------------------------------Sort (cost10356.08..10564.32 rows83296 width20)Sort Key: lzlpartition1.date_created, (min(lzlpartition1.id)), (count(*))- Append (cost1219.22..3548.31 rows83296 width20)- HashAggregate (cost1219.22..1663.09 rows44387 width20)Group Key: lzlpartition1.date_created- Seq Scan on lzlpartition1_202301 lzlpartition1 (cost0.00..878.84 rows45384 width12)- HashAggregate (cost1061.77..1448.86 rows38709 width20)Group Key: lzlpartition1_1.date_created- Seq Scan on lzlpartition1_202302 lzlpartition1_1 (cost0.00..765.30 rows39530 width12)- HashAggregate (cost17.88..19.88 rows200 width20)Group Key: lzlpartition1_2.date_created- Seq Scan on lzlpartition1_202304 lzlpartition1_2 (cost0.00..14.50 rows450 width12)
(12 rows)无wise aggregate时先扫描所有数据再合并Append合并后再聚合计算HashAggregate partition wise aggregate先在分区聚合计算HashAggregate然后在合并结果Append。
partial aggregation 聚合算法可以下放到分区上进行计算此时聚合后的数据分为两种情况聚合数据不重复group包含分区键聚合数据有重复group不包含分区键。 当聚合数据不重复时只需要把各自分区算出来的聚合数据简单的加到一起append即可就像上面的案例当各自分区算出来的聚合数据重复时仍然需要再聚合计算一次Finalize Aggregate。不包含分区键的聚合计算就是partial aggregation。
partial aggregation示例
--group by不是分区键时show enable_partitionwise_aggregate;enable_partitionwise_aggregate
--------------------------------onexplain select id,count(*) from lzlpartition1 group by id ;QUERY PLAN
------------------------------------------------------------------------------------------------------------Finalize HashAggregate (cost2474.80..2573.80 rows9900 width12)Group Key: lzlpartition1.id- Append (cost1105.76..2377.47 rows19467 width12)- Partial HashAggregate (cost1105.76..1202.28 rows9652 width12)Group Key: lzlpartition1.id- Seq Scan on lzlpartition1_202301 lzlpartition1 (cost0.00..878.84 rows45384 width4)- Partial HashAggregate (cost962.95..1059.10 rows9615 width12)Group Key: lzlpartition1_1.id- Seq Scan on lzlpartition1_202302 lzlpartition1_1 (cost0.00..765.30 rows39530 width4)- Partial HashAggregate (cost16.75..18.75 rows200 width12)Group Key: lzlpartition1_2.id- Seq Scan on lzlpartition1_202304 lzlpartition1_2 (cost0.00..14.50 rows450 width4)
group by不包含分区键也可以进行聚合计算但是必须在稍后总聚合Finalize HashAggregate
即使没有group by也可能发生Partial Aggregate show enable_partitionwise_aggregate;enable_partitionwise_aggregate
--------------------------------onexplain select count(*) from lzlpartition1;QUERY PLAN
------------------------------------------------------------------------------------------------------------Finalize Aggregate (cost1872.10..1872.11 rows1 width8)- Append (cost992.30..1872.10 rows3 width8)- Partial Aggregate (cost992.30..992.31 rows1 width8)- Seq Scan on lzlpartition1_202301 lzlpartition1 (cost0.00..878.84 rows45384 width0)- Partial Aggregate (cost864.12..864.13 rows1 width8)- Seq Scan on lzlpartition1_202302 lzlpartition1_1 (cost0.00..765.30 rows39530 width0)- Partial Aggregate (cost15.62..15.63 rows1 width8)- Seq Scan on lzlpartition1_202304 lzlpartition1_2 (cost0.00..14.50 rows450 width0) explain select max(date_created) from lzlpartition1;QUERY PLAN
------------------------------------------------------------------------------------------------------------Finalize Aggregate (cost1872.10..1872.11 rows1 width8)- Append (cost992.30..1872.10 rows3 width8)- Partial Aggregate (cost992.30..992.31 rows1 width8)- Seq Scan on lzlpartition1_202301 lzlpartition1 (cost0.00..878.84 rows45384 width8)- Partial Aggregate (cost864.12..864.13 rows1 width8)- Seq Scan on lzlpartition1_202302 lzlpartition1_1 (cost0.00..765.30 rows39530 width8)- Partial Aggregate (cost15.62..15.63 rows1 width8)- Seq Scan on lzlpartition1_202304 lzlpartition1_2 (cost0.00..14.50 rows450 width8)
触发Partial Aggregate的前提不是group。应从Partial Aggregate的目的去考虑它的目的是把聚合下放到分区那么没有group的聚合其实也可以这么做就像上面两个例子他们都是在分区上聚合计算后Partial Aggregate汇总到一起再聚合计算一次 Finalize Aggregate如果没有打开参数这些聚合发生在扫描完所有分区后。
分区表的历史
声明式分区经过了多个版本的增强如今已非常成熟。对于历史版本的声明式分区功能增强如下
PG9.6以前
只能继承表实现分区功能
PG10
支持声明式分区支持range、list分区支持attach/detach表分区支持分区裁剪
PG11
增加支持HASH分区支持创建主键、外键、索引、触发器支持update分区键、自动创建分区上的索引支持default分区支持attach索引支持FOR EACH ROW触发器自动在已有/未来的子分区上创建新增enable_partition_pruning参数裁剪增强支持partition wise join支持partition wise aggregation
PG12
增强查询、插入、pruning、COPY性能支持外键约束to分区表支持非阻塞分区表ATTACH:ALTER TABLE ATTACH PARTITION
PG13
增强pruning增强partition wise join支持BEFORE triggers支持发布分区表支持订阅写入分区表
PG14
增强update、delete性能支持非阻塞分区表DETACHALTER TABLE ... DETACH PARTITION ... CONCURRENTLY支持reindex分区表的索引
PG15
增强执行计划生成减少多分区时执行计划生成时间增强排序支持cluster分区表
PG16
增强generated列的限制主表有generated列子分区也必须包含。增强查找range、list分区
参考
《PostgreSQL修炼之道》
https://mp.weixin.qq.com/s/NW8XOZNq0YlDZvx24H737Q https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-inherit.html https://www.postgresql.org/docs/13/sql-altertable.html https://github.com/postgrespro/pg_pathman https://developer.aliyun.com/article/62314 https://hevodata.com/learn/postgresql-partitions https://www.postgresql.fastware.com/postgresql-insider-prt-ove https://www.buckenhofer.com/2021/01/postgresql-partitioning-guide/ https://www.depesz.com/2018/05/01/waiting-for-postgresql-11-support-partition-pruning-at-execution-time/ https://blog.csdn.net/horses/article/details/86164273
http://www.pgsql.tech/article_0_10000102
https://brandur.org/fragments/postgres-partitioning-2022
