当前位置：首页 > article >正文

postgres创建分区表

article 2024/10/6 4:19:34

参考文档：
http://postgres.cn/docs/12/ddl-partitioning.html

51.36. pg_partitioned_table (postgres.cn)

创建分区表的步骤
1 创建分区表，后面加上PARTITION BY XXX (XXX )
2 创建分区，建表语句后面加上 PARTITION OF XXX
3 打开分区裁剪

-- 创建分区表，指定分区键为logdate

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

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

-- 确保参数enable_partition_pruning 为ON

postgres=# show enable_partition_pruning ;
 enable_partition_pruning 
--------------------------
 on
(1 row)

postgres=#

-- 创建分区

CREATE TABLE measurement_y2023m01 PARTITION OF measurement
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
	
CREATE TABLE measurement_y2023m02 PARTITION OF measurement
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

CREATE TABLE measurement_y2023m03 PARTITION OF measurement
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');


CREATE TABLE measurement_y2023m04 PARTITION OF measurement
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

CREATE TABLE measurement_y2023m05 PARTITION OF measurement
    FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');
    
postgres=# CREATE TABLE measurement_y2023m01 PARTITION OF measurement
postgres-#     FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE
postgres=# CREATE TABLE measurement_y2023m02 PARTITION OF measurement
postgres-#     FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
CREATE TABLE
postgres=# CREATE TABLE measurement_y2023m03 PARTITION OF measurement
postgres-#     FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
CREATE TABLE
postgres=# CREATE TABLE measurement_y2023m04 PARTITION OF measurement
postgres-#     FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');
CREATE TABLE
postgres=# CREATE TABLE measurement_y2023m05 PARTITION OF measurement
postgres-#     FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');
CREATE TABLE
postgres=#

-- 插入数据验证

insert into measurement values (1,date '2023-01-01',1,1);
insert into measurement values (2,date '2023-02-01',1,1);
insert into measurement values (3,date '2023-03-01',1,1);
insert into measurement values (4,date '2023-04-01',1,1);
insert into measurement values (5,date '2023-05-01',1,1);

postgres=# insert into measurement values (1,date '2023-01-01',1,1);
INSERT 0 1
postgres=# insert into measurement values (2,date '2023-02-01',1,1);
INSERT 0 1
postgres=# insert into measurement values (3,date '2023-03-01',1,1);
INSERT 0 1
postgres=# insert into measurement values (4,date '2023-04-01',1,1);
INSERT 0 1
postgres=# insert into measurement values (5,date '2023-05-01',1,1);
INSERT 0 1
postgres=#

-- 验证数据

select * from measurement;
select * from measurement_y2023m01;
select * from measurement_y2023m02;
select * from measurement_y2023m03;
select * from measurement_y2023m04;
select * from measurement_y2023m05;

postgres=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       1 | 2023-01-01 |        1 |         1
       2 | 2023-02-01 |        1 |         1
       3 | 2023-03-01 |        1 |         1
       4 | 2023-04-01 |        1 |         1
       5 | 2023-05-01 |        1 |         1
(5 rows)

postgres=# select * from measurement_y2023m01;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       1 | 2023-01-01 |        1 |         1
(1 row)

postgres=# select * from measurement_y2023m02;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       2 | 2023-02-01 |        1 |         1
(1 row)

postgres=# select * from measurement_y2023m03;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       3 | 2023-03-01 |        1 |         1
(1 row)

postgres=# select * from measurement_y2023m04;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       4 | 2023-04-01 |        1 |         1
(1 row)

postgres=# select * from measurement_y2023m05;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       5 | 2023-05-01 |        1 |         1
(1 row)

postgres=#

-- 插入6月份的数据，会提示没有分区。

insert into measurement values (6,date '2023-06-01',1,1);

postgres=# insert into measurement values (6,date '2023-06-01',1,1);
ERROR:  no partition of relation "measurement" found for row
DETAIL:  Partition key of the failing row contains (logdate) = (2023-06-01).
postgres=#

-- 新增加一个6月份的分区，使数据能插入到分区

CREATE TABLE measurement_y2023m06 PARTITION OF measurement
    FOR VALUES FROM ('2023-06-01') TO ('2023-07-01');
	
postgres=# CREATE TABLE measurement_y2023m06 PARTITION OF measurement
postgres-#     FOR VALUES FROM ('2023-06-01') TO ('2023-07-01');
CREATE TABLE
postgres=# insert into measurement values (6,date '2023-06-01',1,1);
INSERT 0 1
postgres=# 

postgres=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       1 | 2023-01-01 |        1 |         1
       2 | 2023-02-01 |        1 |         1
       3 | 2023-03-01 |        1 |         1
       4 | 2023-04-01 |        1 |         1
       5 | 2023-05-01 |        1 |         1
       6 | 2023-06-01 |        1 |         1
(6 rows)

postgres=# select * from measurement_y2023m06;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       6 | 2023-06-01 |        1 |         1
(1 row)

postgres=#

postgres=# select * from pg_partitioned_table ;
 partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs 
-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
     24632 | r         |         1 |         0 | 2         | 3122      | 0             | 
(1 row)

postgres=#

-- 关于该视图，查看官方文档：

目录pg_partitioned_table存放有关表如何被分区的信息。

表 51.36. pg_partitioned_table列

名称	类型	引用	描述
`partrelid`	`oid`	`pg_class.oid`	这个分区表的`pg_class`项的OID
`partstrat`	`char`		分区策略；`h` = 哈希分区表，`l` = 列表分区表，`r` = 范围分区表
`partnatts`	`int2`		分区键中的列数
`partdefid`	`oid`	`pg_class.oid`	这个分区表的默认分区的`pg_class`项的OID，如果这个分区表没有默认分区则为零。
`partattrs`	`int2vector`	`pg_attribute.attnum`	这是一个长度为`partnatts`值的数组，它指示哪些表列是分区键的组成部分。例如，值`1 3`表示第一个和第三个表列组成了分区键。这个数组中的零表示对应的分区键列是一个表达式而不是简单的列引用。
`partclass`	`oidvector`	`pg_opclass.oid`	对于分区键中的每一个列，这个域包含要使用的操作符类的OID。详见pg_opclass。
`partcollation`	`oidvector`	`pg_opclass.oid`	对于分区键中的每一个列，这个域包含要用于分区的排序规则的OID，如果该列不是一种可排序数据类型则为零。
`partexprs`	`pg_node_tree`		非简单列引用的分区键列的表达式树（以`nodeToString()`的表达方式）。这是一个列表，`partattrs`中每一个零项都有一个元素。如果所有分区键列都是简单列引用，则这个域为空。

-- 通过dt+ 命令，查看建立的分区表和分区

postgres=# \dt+ measurement*
                                                   List of relations
 Schema |         Name         |       Type        |  Owner   | Persistence | Access method |    Size    | Description 
--------+----------------------+-------------------+----------+-------------+---------------+------------+-------------
 public | measurement          | partitioned table | postgres | permanent   |               | 0 bytes    | 
 public | measurement_y2023m01 | table             | postgres | permanent   | heap          | 8192 bytes | 
 public | measurement_y2023m02 | table             | postgres | permanent   | heap          | 8192 bytes | 
 public | measurement_y2023m03 | table             | postgres | permanent   | heap          | 8192 bytes | 
 public | measurement_y2023m04 | table             | postgres | permanent   | heap          | 8192 bytes | 
 public | measurement_y2023m05 | table             | postgres | permanent   | heap          | 8192 bytes | 
 public | measurement_y2023m06 | table             | postgres | permanent   | heap          | 8192 bytes | 
(7 rows)

postgres=#

END

查看全文

http://www.kler.cn/news/9719.html