作者
digoal
日期
2018-02-06
标签
PostgreSQL , 锁 , 粒度 , pg_pathman , 内置分区
背景
PostgreSQL 内置分区相比pg_pathman分区插件性能要差一大截：
《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》
主要原因:
1、与优化器有关，内置分区表，plan\bind时需要分析所有子表，所以子表越多，性能会越差。例如下面这个CASE就是分区过多引起的性能问题。
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
PPAS 10和pg_pathman插件都没有这个问题。
我们也可以通过这个profiling来对比 pg_pathman和内置分区的性能差异。
《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》
除了性能差异，实际上还有一个问题是锁的差异。
观察锁
1、pg_pathman，仅锁目标分区。对非目标分区执行DDL，不需要等待。
```
create table tbl_range(id int not null, info text, crt_time timestamp);
select create_range_partitions('tbl_range', 'id', 0, 100, 128);
begin;
insert into tbl_range values (1, 'test', now()) returning tableoid::regclass,*;
tableoid | id | info | crt_time
-------------+----+------+----------------------------
tbl_range_1 | 1 | test | 2018-02-06 20:15:23.305754
(1 row)
INSERT 0 1
```
postgres=# select relation::regclass,* from pg_locks ;
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
-------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
pg_locks | relation | 20699 | 11577 | | | | | | | | 4/18 | 41234 | AccessShareLock | t | t
| virtualxid | | | | | 4/18 | | | | | 4/18 | 41234 | ExclusiveLock | t | t
tbl_range_1 | relation | 20699 | 44464 | | | | | | | | 3/8819 | 41174 | RowExclusiveLock | t | t
tbl_range | relation | 20699 | 44456 | | | | | | | | 3/8819 | 41174 | RowExclusiveLock | t | t
| virtualxid | | | | | 3/8819 | | | | | 3/8819 | 41174 | ExclusiveLock | t | t
| transactionid | | | | | | 227217433 | | | | 3/8819 | 41174 | ExclusiveLock | t | f
(6 rows)
2、native partition table，锁所有分区。导致对任意分区执行DDL都需要等待。
CREATE TABLE orders (
order_id bigint not null,
cust_id bigint not null,
status text
) PARTITION BY HASH (order_id);
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
```
begin;
insert into orders values (1, 1, 'test') returning tableoid::regclass,*;
tableoid | order_id | cust_id | status
-----------+----------+---------+--------
orders_p1 | 1 | 1 | test
(1 row)
INSERT 0 1
```
postgres=# select relation::regclass,* from pg_locks ;
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
orders_p4 | relation | 16461 | 75121104 | | | | | | | | 3/57883 | 41256 | RowExclusiveLock | t | t
orders_p3 | relation | 16461 | 75121098 | | | | | | | | 3/57883 | 41256 | RowExclusiveLock | t | t
orders_p2 | relation | 16461 | 75121092 | | | | | | | | 3/57883 | 41256 | RowExclusiveLock | t | t
orders_p1 | relation | 16461 | 75121086 | | | | | | | | 3/57883 | 41256 | RowExclusiveLock | t | t
orders | relation | 16461 | 75121080 | | | | | | | | 3/57883 | 41256 | AccessShareLock | t | t
orders | relation | 16461 | 75121080 | | | | | | | | 3/57883 | 41256 | RowExclusiveLock | t | t
| virtualxid | | | | | 3/57883 | | | | | 3/57883 | 41256 | ExclusiveLock | t | t
pg_locks | relation | 16461 | 11621 | | | | | | | | 4/58165 | 41325 | AccessShareLock | t | t
| virtualxid | | | | | 4/58165 | | | | | 4/58165 | 41325 | ExclusiveLock | t | t
维护子表
在对主表有查询、插入时，维护子表，看看子表的反应。
insert into 主表 values (1,xxx); -- 写入A子表
1、TRUNCATE 非A子表
内置分区，锁冲突。
pg_pathman，无冲突。
2、detach 非A子表
内置分区，锁冲突。
pg_pathman，无冲突。
小结
内置分区表，只要操作主表，就需要对所有子表加锁（加相应的锁，例如sharedLock）.
pg_pathman，仅仅对主表，以及被访问调度子表加锁。
因此，当我们需要使用DDL来维护子表时（例如truncate, attach, detach子表），使用pg_pathman不会与操作主表的SQL冲突，而使用内置分区的话，读主表由于锁所有子表，所以与子表DDL操作会发生冲突。需要特别注意。
参考
《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》
《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》
《PostgreSQL 商用版本EPAS(阿里云ppas) - 分区表性能优化 (堪比pg_pathman)》
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
