PostgreSQL中的分区表_MambaCloud_postgresql 分区表

未知 02-07 6468

PostgreSQL中的分区表

参考：https://·/pgzhongdefenqubiao.html#PG_11xin_te_xing

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

分区表的具体好处：

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

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

注意：

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

创建主表的语法

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

创建分区表语法：

多个KEY

分区表支持把普通表绑定成父表的一个分区，也支持把分区绑定为普通表。

若普通表中有数据，ATTACH操作时，默认会做数据校验。

获取系统信息（系统表）

pg_partitioned_table记录主表信息的系统表

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

分区的信息记录在pg_class相关的字段中

名称类型引用描述relispartitionboolean表是否为分区（TRUE 是）relpartboundpg_node_tree分区约束

hash分区

hash分区语法

--主表 CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE) PARTITION BY HASH (column_name) --分区表 CREATE TABLE table_name PARTITION OF parent_table FOR VALUES WITH (modulus numeric_literal, REMAINDER numeric_literal)

哈希分区支持多列分区。示例：

PG11新特性，防止插入失败，对于不符合分区约束的数据会插入到默认分区。目前rang/list支持默认分区，hash分区不支持。

语法：

CREATE TABLE table_name PARTITION OF parent_table FOR VALUES DEFAULT ATTACH/DETACH分区

语法：

ALTER TABLE name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } ALTER TABLE name DETACH PARTITION partition_name

示例

create table test_hash(id int, date date) partition by hash(id); create table test_hash_1 partition of test_hash for values with(modulus 2, remainder 0); create table test_hash_2 partition of test_hash for values with(modulus 2, remainder 1); postgres=# \d+ test_hash Partitioned table "public.test_hash" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | date | date | | | | plain | | Partition key: HASH (id) Partitions: test_hash_1 FOR VALUES WITH (modulus 2, remainder 0), test_hash_2 FOR VALUES WITH (modulus 2, remainder 1) postgres=# alter table test_hash detach partition test_hash_2; ALTER TABLE postgres=# \d+ test_hash Partitioned table "public.test_hash" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | date | date | | | | plain | | Partition key: HASH (id) Partitions: test_hash_1 FOR VALUES WITH (modulus 2, remainder 0) postgres=# create table test_hash_attach (id int, date date); CREATE TABLE postgres=# alter table test_hash attach partition test_hash_attach for values with (modulus 2, remainder 1); ALTER TABLE postgres=# \d+ test_hash Partitioned table "public.test_hash" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | date | date | | | | plain | | Partition key: HASH (id) Partitions: test_hash_1 FOR VALUES WITH (modulus 2, remainder 0), test_hash_attach FOR VALUES WITH (modulus 2, remainder 1) 分区管理删除分区

直接删除对应的分区表

drop table tbl_partition_202110;

另一个方法就是先将分区表移除，但是保留访问权限

alter table tbl_partition_202110 no inherit tbl_partition;

和直接DROP相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以访问，因为此时该表已经被还原成一个普通的数据表。在这时就可以对该表进行必要的维护操作。如数据清理、归档等。然后可以考虑是直接删除该表，还是先清空该表，然后再重新继承主表。

alter table tbl_partition_202110 inher tbl_partition; 增加分区

1、可以通过新建分区表来增加一个分区

2、可以直接让普通表绑定主表，从而达到新增分区的目的