浅谈PostgreSQL 11 新特性之默认分区
文章目录
PosgtreSQL11支持为分区表创建一个默认（DEFAULT）的分区，用于存储无法匹配其他任何分区的数据。显然，只有RANGE分区表和LIST分区表需要默认分区。
CREATETABLEmeasurement(
city_idintnotnull,
logdatedatenotnull,
peaktempint,
unitsalesint
)PARTITIONBYRANGE(logdate);
CREATETABLEmeasurement_y2018PARTITIONOFmeasurement
FORVALUESFROM('2018-01-01')TO('2019-01-01');
以上示例只创建了2018年的分区，如果插入2017年的数据，系统将会无法找到相应的分区：
INSERTINTOmeasurement(city_id,logdate,peaktemp,unitsales) VALUES(1,'2017-10-01',50,200); ERROR:nopartitionofrelation"measurement"foundforrow DETAIL:Partitionkeyofthefailingrowcontains(logdate)=(2017-10-01).
使用默认分区可以解决这类问题。创建默认分区时使用DEFAULT子句替代FORVALUES子句。
CREATETABLEmeasurement_defaultPARTITIONOFmeasurementDEFAULT;
\d+measurement
Table"public.measurement"
Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id|integer||notnull||plain||
logdate|date||notnull||plain||
peaktemp|integer||||plain||
unitsales|integer||||plain||
Partitionkey:RANGE(logdate)
Partitions:measurement_y2018FORVALUESFROM('2018-01-01')TO('2019-01-01'),
measurement_defaultDEFAULT
有了默认分区之后，未定义分区的数据将会插入到默认分区中：
INSERTINTOmeasurement(city_id,logdate,peaktemp,unitsales) VALUES(1,'2017-10-01',50,200); INSERT01 select*frommeasurement_default; city_id|logdate|peaktemp|unitsales ---------+------------+----------+----------- 1|2017-10-01|50|200 (1row)
默认分区存在以下限制：
一个分区表只能拥有一个DEFAULT分区；
对于已经存储在DEFAULT分区中的数据，不能再创建相应的分区；参见下文示例；
如果将已有的表挂载为DEFAULT分区，将会检查该表中的所有数据；如果在已有的分区中存在相同的数据，将会产生一个错误；
哈希分区表不支持DEFAULT分区，实际上也不需要支持。
使用默认分区也可能导致一些不可预见的问题。例如，往measurement表中插入一条2019年的数据，由于没有创建相应的分区，该记录同样会分配到默认分区：
INSERTINTOmeasurement(city_id,logdate,peaktemp,unitsales) VALUES(1,'2019-03-25',66,100); INSERT01 select*frommeasurement_default; city_id|logdate|peaktemp|unitsales ---------+------------+----------+----------- 1|2017-10-01|50|200 1|2019-03-25|66|100 (2rows)
此时，如果再创建2019年的分区，操作将会失败。因为添加新的分区需要修改默认分区的范围（不再包含2019年的数据），但是默认分区中已经存在2019年的数据。
CREATETABLEmeasurement_y2019PARTITIONOFmeasurement
FORVALUESFROM('2019-01-01')TO('2020-01-01');
ERROR:updatedpartitionconstraintfordefaultpartition"measurement_default"wouldbeviolatedbysomerow
为了解决这个问题，可以先将默认分区从分区表中卸载（DETACHPARTITION），创建新的分区，将默认分区中的相应的数据移动到新的分区，最后重新挂载默认分区。
ALTERTABLEmeasurementDETACHPARTITIONmeasurement_default;
CREATETABLEmeasurement_y2019PARTITIONOFmeasurement
FORVALUESFROM('2019-01-01')TO('2020-01-01');
INSERTINTOmeasurement_y2019
SELECT*FROMmeasurement_defaultWHERElogdate>='2019-01-01'ANDlogdate<'2020-01-01';
INSERT01
DELETEFROMmeasurement_defaultWHERElogdate>='2019-01-01'ANDlogdate<'2020-01-01';
DELETE1
ALTERTABLEmeasurementATTACHPARTITIONmeasurement_defaultDEFAULT;
CREATETABLEmeasurement_y2020PARTITIONOFmeasurement
FORVALUESFROM('2020-01-01')TO('2021-01-01');
\d+measurement
Table"public.measurement"
Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id|integer||notnull||plain||
logdate|date||notnull||plain||
peaktemp|integer||||plain||
unitsales|integer||||plain||
Partitionkey:RANGE(logdate)
Partitions:measurement_y2018FORVALUESFROM('2018-01-01')TO('2019-01-01'),
measurement_y2019FORVALUESFROM('2019-01-01')TO('2020-01-01'),
measurement_y2020FORVALUESFROM('2020-01-01')TO('2021-01-01'),
measurement_defaultDEFAULT
官方文档：TablePartitioning
补充：postgresql10以上的自动分区分表功能
一.列分表
1.首先创建主分区表:
createtablefenbiao( idint, yearvarchar )partitionbylist(year)
这里设置的是根据year列进行数据分表;创建后使用navicat是看不到的;
2.创建分表:
createtablefenbiao_2017partitionoffenbiaoforvaluesin('2017')
createtablefenbiao_2018partitionoffenbiaoforvaluesin('2018')
这样这两天数据会依靠规则插入到不同分表中,如果插入一条不符合规则的数据,则会报错误:nopartitionofrelation"fenbiao"foundforrow.
二.范围分表
1.以year列为范围进行分表
createtablefenbiao2( idint, yearvarchar )partitionbyrange(year)
2.创建分表
createtablefenbiao2_2018_2020partitionoffenbiao2forvaluesfrom('2018')to('2020')
createtablefenbiao2_2020_2030partitionoffenbiao2forvaluesfrom('2020')to('2030')
注意:此时插入year=2020会插入到下面的表;如下面表范围为2021到2030,则会报错;同时插入2030也会报错;范围相当于时a<=year 以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。