postgresql表分区
表分区
声明式划分
CREATE TABLE measurement (
city_id int not null,//城市id
logdate date not null,//日期
peaktemp int,//峰值温度
unitsales int//销量
);
CREATE TABLE measurement (city_id int not null,logdate date not null,peaktemp int,unitsales int);
CREATE TABLE measurement (city_id int not null,logdate date not null,peaktemp int,unitsales int) partition by range(logdate );
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01');
CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01');
insert into measurement values(1,'2006-02-08',36,120);
insert into measurement values(1,'2006-02-19',33,100);
insert into measurement values(1,'2006-03-08',32,103);
insert into measurement values(1,'2006-03-22',20,102);
insert into measurement values(1,'2007-11-08',24,101);
insert into measurement values(1,'2007-11-13',35,120);
insert into measurement values(1,'2007-12-23',31,119);
insert into measurement values(1,'2007-12-21',40,110);
insert into measurement values(1,'2008-01-20',22,88);
insert into measurement values(1,'2008-01-09',22,99);
mydb=# select * from measurement;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2006-02-08 | 36 | 120
1 | 2006-02-19 | 33 | 100
1 | 2006-03-08 | 32 | 103
1 | 2006-03-22 | 20 | 102
1 | 2007-11-08 | 24 | 101
1 | 2007-11-13 | 35 | 120
1 | 2007-12-23 | 31 | 119
1 | 2007-12-21 | 40 | 110
1 | 2008-01-20 | 22 | 88
1 | 2008-01-09 | 22 | 99
(10 rows)
mydb=# select * from measurement_y2008m01;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2008-01-20 | 22 | 88
1 | 2008-01-09 | 22 | 99
(2 rows)
mydb=# explain select * from measurement where logdate='2008-01-20';
QUERY PLAN
----------------------------------------------------------------------------
Append (cost=0.00..33.12 rows=9 width=16)
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=9 width=16)
Filter: (logdate = '2008-01-20'::date)
(3 rows)
分区表的CHECK约束和NOT NULL约束总是会被其所有的分区所继承。不允许在分区表上创建标记为NO INHERIT的CHECK约束
mydb=# \d+ measurement_y2008m01
Table "public.measurement_y2008m01"
Column | 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 | |
Partition of: measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
Partition constraint: ((logdate IS NOT NULL) AND (logdate >= '2008-01-01'::date) AND (logdate < '2008-02-01'::date))
mydb=#
剔除分区,剔除后该分区回作为一个单独的表存在
mydb=# alter table measurement detach partition measurement_y2008m01 ;
ALTER TABLE
mydb=#
mydb=# select * from measurement;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2006-02-08 | 36 | 120
1 | 2006-02-19 | 33 | 100
1 | 2006-03-08 | 32 | 103
1 | 2006-03-22 | 20 | 102
1 | 2007-11-08 | 24 | 101
1 | 2007-11-13 | 35 | 120
1 | 2007-12-23 | 31 | 119
1 | 2007-12-21 | 40 | 110
(8 rows)
将分区表分区添加进来 ^
mydb=# alter table measurement attach partition measurement_y2008m01 for VALUES FROM ('2008-01-01') TO ('2008-02-01');;
ALTER TABLE
mydb=#
mydb=# select * from measurement;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2006-02-08 | 36 | 120
1 | 2006-02-19 | 33 | 100
1 | 2006-03-08 | 32 | 103
1 | 2006-03-22 | 20 | 102
1 | 2007-11-08 | 24 | 101
1 | 2007-11-13 | 35 | 120
1 | 2007-12-23 | 31 | 119
1 | 2007-12-21 | 40 | 110
1 | 2008-01-20 | 22 | 88
1 | 2008-01-09 | 22 | 99
(10 rows)
mydb=#
DROP TABLE measurement_y2006m02;//删除分区表需要拿到父表measurement表的排它锁ACCESS EXCLUSIVE锁
也可以使用原来的语句格式添加分区
CREATE TABLE measurement_y2008m02 PARTITION OF measurement FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
或者使用语句使用LIKE子句创建一个新表，将表数据添加好后，将该表添加到分区当中，
CREATE TABLE measurement_y2008m02(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS) TABLESPACE fasttablespace;
//添加之前可以加上check约束，界定分区条件，负责会对隐士分区约束验证，同时对该分区持有ACCESS EXCLUSIVE锁定，并在父表上持有SHARE UPDATE EXCLUSIVE锁，在完成ATTACH PARTITION后，可能需要删除冗余CHECK约束
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02' -− possibly some other data preparation work
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
为了克服长时间锁，可以对分区表使用CREATE INDEX ON ONLY，这样索引被标记为无效，然后分区上的索引可以使用CONCURRENTLY分别的创建。
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);然后使用ALTER INDEX .. ATTACH PARTITION attached到父索引。 一旦所有分区的索引附加到父索引，父索引将自动标记为有效
ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);//给父表添加唯一约束
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);//给分区添加唯一约束
ALTER INDEX measurement_city_id_logdate_key ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;//分区添加完成之后再attach到父索引上
CREATE TABLE measurement (city_id int ,logdate date ,peaktemp int,unitsales int) partition by range(logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01');
CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01');
分区表添加约束
mydb=# alter table measurement_y2007m11 add constraint unique_mea unique(city_id);
ALTER TABLE
mydb=#
mydb=#
mydb=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------------+-------+----------+------------+-------------
public | accounts | table | postgres | 8192 bytes |
public | cities | table | postgres | 0 bytes |
public | measurement | table | postgres | 0 bytes |
public | measurement_y2006m02 | table | postgres | 0 bytes |
public | measurement_y2006m03 | table | postgres | 0 bytes |
public | measurement_y2007m11 | table | postgres | 0 bytes |
public | measurement_y2007m12 | table | postgres | 0 bytes |
public | measurement_y2008m01 | table | postgres | 0 bytes |
public | product | table | postgres | 16 kB |
public | rsl | table | user01 | 8192 bytes |
public | weather | table | postgres | 8192 bytes |
(11 rows)
主表不会有约束
mydb=# \d+ measurement
Table "public.measurement"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id | integer | | | | plain | |
logdate | date | | | | plain | |
peaktemp | integer | | | | plain | |
unitsales | integer | | | | plain | |
Partition key: RANGE (logdate)
Partitions: measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'),
measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'),
measurement_y2007m11 FOR VALUES FROM ('2007-11-01') TO ('2007-12-01'),
measurement_y2007m12 FOR VALUES FROM ('2007-12-01') TO ('2008-01-01'),
measurement_y2008m01 FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
mydb=# \d+ measurement_y2006m02
Table "public.measurement_y2006m02"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id | integer | | | | plain | |
logdate | date | | | | plain | |
peaktemp | integer | | | | plain | |
unitsales | integer | | | | plain | |
Partition of: measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
Partition constraint: ((logdate IS NOT NULL) AND (logdate >= '2006-02-01'::date) AND (logdate < '2006-03-01'::date))
mydb=# \d+ measurement_y2007m11
Table "public.measurement_y2007m11"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id | integer | | | | plain | |
logdate | date | | | | plain | |
peaktemp | integer | | | | plain | |
unitsales | integer | | | | plain | |
Partition of: measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01')
Partition constraint: ((logdate IS NOT NULL) AND (logdate >= '2007-11-01'::date) AND (logdate < '2007-12-01'::date))
Indexes:
"unique_mea" UNIQUE CONSTRAINT, btree (city_id)
当分区表中有了分区之后，无法在主分区表上添加索引约束，pg11可以
https://pgdash.io/blog/partition-postgres-11.html
