Postgresql - Table Partitioning （三）

介绍了分区表之后，我们来看一下如何使用。

1. 创建分区表。 partition table

CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int) PARTITION BY RANGE (logdate);

2. 创建分区。 partitions

CREATE TABLE measurement_y2018m01 PARTITION OF measurement

FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');

......

CREATE TABLE measurement_y2018m11 PARTITION OF measurement

FOR VALUES FROM ('2018-11-01') TO ('2018-12-01');

CREATE TABLE measurement_y2018m12 PARTITION OF measurement

FOR VALUES FROM ('2018-12-01') TO ('2019-01-01');

创建子分区 sub-partitioning

CREATE TABLE measurement_y2019m01 PARTITION OF measurement

FOR VALUES FROM ('2019-01-01') TO ('2019-02-01')

PARTITION BY RANGE (peaktemp);

CREATE TABLE measurement_y2019m02 PARTITION OF measurement

FOR VALUES FROM ('2019-02-01') TO ('2019-03-01')

WITH (parallel_workers = 4);

3. 创建索引在分区键上

CREATE INDEX ON measurement_y2018m01 (logdate);

......

CREATE INDEX ON measurement_y2018m12 (logdate);

4. 参数设置

Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized as desired.