表分区简介1

文档用途

了解Postgresql表分区

详细信息

Postgreslq内核中支持表分区（Table partitioning）包括：范围（range）、列表（list）

PostgreSQL offers built-in support for the following forms of partitioning:

Range Partitioning

The table is partitioned into “ranges” defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects.

List Partitioning

The table is partitioned by explicitly listing which key values appear in each partition.

If your application needs to use other forms of partitioning not listed above, alternative methods such as inheritance and UNION ALL views can be used instead. Such methods offer flexibility but do not have some of the performance benefits of built-in declarative partitioning.

事例：

1、范围分区

createtablet_range(idint,namevarchar(100),i_timetimestampnotnull)partitionbyrange(id);postgres=# \d+ t_rangecreatetablet_range_1partitionoft_rangeforvaluesfrom(1)to(1000);createtablet_range_2partitionoft_rangeforvaluesfrom(1000)to(3000);createtablet_range_3partitionoft_rangeforvaluesfrom(3000)to(5000);createtablet_range_4partitionoft_rangeforvaluesfrom(5000)to(8000);createtablet_range_5partitionoft_rangeforvaluesfrom(8000)to(10000);postgres=# \d+ t_rangeinsertintot_rangeselectid,md5(random()::text),current_date-idfromgenerate_series(1,10001)t(id);postgres=>insertintot_rangeselectid,md5(random()::text),current_date-idfromgenerate_series(1,10001)t(id);ERROR:nopartitionofrelation"t_range"foundforrowDETAIL:Partitionkeyofthe failingrowcontains(id)=(10000).postgres=>INSERTINTOt_rangeSELECTgenerate_series(1,9999),md5(random()::text),clock_timestamp();selectcount(*)fromt_range_1;selectcount(*)fromt_range_5;selectcount(*)fromonly t_range;

不允许分区字段修改后跨越分区

postgres=# update t_range set id=2000 where id=500;ERROR: newrowforrelation"t_range_1"violatespartitionconstraintDETAIL: Failingrowcontains(2000,7ed9bf07d00e369c74997bee774cea69,2017-03-2400:00:00).postgres=# update t_range set id=200 where id=500;UPDATE1

非分区字段的字段修改操作会下发到子表上。

truncate主表

postgres=# truncate table t_range;TRUNCATETABLEpostgres=# select count(*) from t_range_5;count-------0(1row)

会清除所有子表的数据。

2、list 分区

创建主表

createtablet_list(idint,namevarchar(100),regionvarchar(50))partitionbylist(region);createtablet_list_1partitionoft_listforvaluesin('beijing');createtablet_list_2partitionoft_listforvaluesin('nanjing');createtablet_list_3partitionoft_listforvaluesin('shanghai');createtablet_list_4partitionoft_listforvaluesin('chongqing');createtablet_list_5partitionoft_listforvaluesin('hangzhou');postgres=# \d+ t_listinsertintot_listvalues(1,'a','beijing');insertintot_listvalues(2,'b','nanjing');insertintot_listvalues(3,'c','chongqing');insertintot_listvalues(4,'d','hangzhou');insertintot_listvalues(5,'e','shanghai');insertintot_listvalues(6,'f','chongqing');insertintot_listvalues(7,'g','nanjing');insertintot_listvalues(8,'h','beijing');postgres=>select*fromt_list_2;id|name|region----+------+---------2|b|nanjing7|g|nanjing(2rows)

使用explain查看执行计划，验证分区的使用

postgres=# explain select * from t_list where region='nanjing';

添加删除分区

添加分区

createtablet_list_6(liket_list);altertablet_list attachpartitiont_list_6forvaluesin('guangzhou');

分区离线

altertablet_range detachpartitiont_range_5;postgres=>select*fromt_rangewhereid=9001;id|name|i_time----+------+--------(0rows)postgres=>select*fromt_range_5whereid=9001;id|name|i_time------+----------------------------------+----------------------------9001|2ff1f5c36a0ef69b1b5d283a2312f019|2019-03-2009:47:14.304022(1row)altertablet_list detachpartitiont_list_6;select*fromt_listwhereregion='guangzhou';select*fromt_list_6;

detach后，表还是存在的，成为独立的表。