Postgresql - Table Partitioning （三）

最新推荐文章于 2022-01-28 11:48:23 发布

|ChuckChen|

最新推荐文章于 2022-01-28 11:48:23 发布

阅读量242

点赞数

CC 4.0 BY-SA版权

分类专栏： Postgresql 文章标签： Postgresql Database Partition Table 分区表

本文链接：https://blog.youkuaiyun.com/chuckchen1222/article/details/80784463

Postgresql 专栏收录该内容

142 篇文章

订阅专栏

本文详细介绍了如何在数据库中创建分区表，包括主表及分区表的定义、子分区的创建方式、为分区表创建索引的方法以及配置参数的设置等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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.