PostgreSQL中的分区表

PostgreSQL中的分区表

参考：https://www.xmmup.com/pgzhongdefenqubiao.html#PG_11xin_te_xing

PostgreSQL分区的意思是把逻辑上的一个大表分割成物理上的几块。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

分区表的具体好处：

• 某些类型的查询性能可以得到极大提升
• 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问
• 批量删除可以用简单的删除某个分区来实现
• 可以将很少用的数据移动到便宜、转速慢的存储介质上

PG10版本后可以通过声明式分区进行创建分区表，就是通过相应的DDL语句来创建分区表。

注意：

• 主表和分区表分别单独创建
• 范围分区支持多个字段组成的KEY
• 列表分区的KEY只能有一个字段

创建主表的语法

CREATE TABLE 表名( [列名称 数据类型]...)
PARTITION BY RANG ([列名称]...)
--范围分区的KEY值可由多个字段组成（最多32个字段）。

创建分区表语法：

CREATE TABLE 表名 PARTITION of 主表 FOR VALUES 
FROM (表达式) TO (表达式)

范围分区

create table tbp(n int, t text) partition by range(n); create table tbp_1 partition of tbp for values from (MINVALUE) to (10); create table tbp_2 partition of tbp for values from (10) to (100); create table tbp_3 partition of tbp for values from (100) to (1000); create table tbp_4 partition of tbp for values from (1000) to (MAXVALUE);

postgres=# \d
               List of relations
 Schema | Name  |       Type        |  Owner   
--------+-------+-------------------+----------
 public | tbp   | partitioned table | postgres
 public | tbp_1 | table             | postgres
 public | tbp_2 | table             | postgres
 public | tbp_3 | table             | postgres
 public | tbp_4 | table             | postgres
(5 rows)

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

postgres=# \d+ tbp
                              Partitioned table "public.tbp"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 n      | integer |           |          |         | plain    |              | 
 t      | text    |           |          |         | extended |              | 
Partition key: RANGE (n)
Partitions: tbp_1 FOR VALUES FROM (MINVALUE) TO (10),
            tbp_2 FOR VALUES FROM (10) TO (100),
            tbp_3 FOR VALUES FROM (100) TO (1000