作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
擅长主流数据Oracle、MySQL、PG、openGauss运维
备份恢复，安装迁移，性能优化、故障应急处理等
可提供技术业务：
1.DB故障处理/疑难杂症远程支援
2.Mysql/PG/Oracle/openGauss
数据库部署及数仓搭建
公众号：IT邦德
•••
@TOC
前言
PG表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分，本文做了详细的说明下面由邦德老师，给大家详细的介绍下PostgreSQL的分区吧~
🚀 1.分区表
🌈 1.1 什么是表分区
分区表就是根据分区策略，将数据数据分散到不同的子表中，
并通过父表建立关联关系，从而实现数据物理上的分区
PostgreSQL 10.x 之前的版本提供了一种“手动”方式使用分区表的方式，
需要使用继承 + 触发器的来实现分区表，
步骤较为繁琐，需要定义附表、子表、
子表的约束、创建子表索引，创建分区删除、修改，触发器等。
PostgreSQL 10.x 开始提供了内置分区表（内置是相对于 10.x 之前的手动方式）。
内置分区简化了操作，将部分操作内置，
最终简单三步就能够创建分区表。
但是只支持范围分区（RANGE）和列表分区（LIST），
11.x 版本添加了对 HASH 分区。
🌈 1.2 为什么需要表分区
1.可以让单表存储更多的数据。
2.分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，
也可以增加新的分区来支持新插入的数据。
另外，还可以对一个独立分区进行优化、检查、修复等操作。
3.部分查询能够从查询条件确定只落在少数分区上，查询速度会很快。
4.分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备。
5.可以使用分区表来避免某些特殊瓶颈，
可以备份和恢复单个分区。
🌈 1.3 分区表的优缺点
优点：
（1）**数据维护成本降低。某一部分数据需要失效，
不需要执行命令来更新数据，可以直接接触绑定关系，
接触绑定的数据和分区表都依然保留，需要时可以随时恢复。
（2）一个表只能放在一个物理空间上，使用分区表之后可以将不同的表放置在不同的物理空间上，
从而达到冷数据放在廉价的物理机器上，热点数据放置在性能强劲的机器上。
（3）直接从分区表查询数据比从一个大而全的全量数据表中读取数据效率更高。
缺点：
（1）性能上的两面性。性能上通过分区表的父表查数据相对于普通的数据全量表查询效率要低。
直接分区表中查询数据比在全量表中查询数据效率要高。
（2）10.x 版本不支持跨分区更新，11.x 版本中才支持快分区的更新。
（3）主键有可能重复。由于分区表的的主键约束都是分别建立的，
因此可能存在主键重复。只能通过一些策略来规避主键相同的问题。
🚀 2.分区表的类型
🌈 2.1 RANGE分区
对于范围分区，数据根据所选范围划分为段。当您需要访问时间序列数据时，提供日期（例如年份和月份）很有用
创建父表
CREATE TABLE orders (
id serial,
user_id int4,
create_time timestamp(0)
) PARTITION BY RANGE(create_time);
创建分区表
CREATE TABLE orders_hisotry PARTITION OF orders FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');
CREATE TABLE orders_202003 PARTITION OF orders FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE orders_202004 PARTITION OF orders FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE orders_202005 PARTITION OF orders FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE orders_202006 PARTITION OF orders FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
在分区上创建索引
CREATE INDEX order_idx_history_create_time ON orders_history USING btree(create_time);
CREATE INDEX order_idx_202003_create_time ON orders_202003 USING btree(create_time);
CREATE INDEX order_idx_202004_create_time ON orders_202004 USING btree(create_time);
CREATE INDEX order_idx_202005_create_time ON orders_202005 USING btree(create_time);
CREATE INDEX order_idx_202006_create_time ON orders_202006 USING btree(create_time);
🌈 2.2 LIST分区
在列表分区中，数据根据已指定的离散值进行分区。当需要对地区、部门等离散数据进行任意值分组时，这种方法效果很好
postgres=# CREATE TABLE cities (
city_id bigint not null,
name text not null,
population bigint
) PARTITION BY LIST (name);
CREATE TABLE cities_1 PARTITION OF cities FOR VALUES IN ('A');
CREATE TABLE cities_2 PARTITION OF cities FOR VALUES IN ('B');
CREATE TABLE cities_3 PARTITION OF cities FOR VALUES IN ('C');
CREATE TABLE cities_4 PARTITION OF cities FOR VALUES IN ('D');
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------------------+-------------------+----------+------------+-------------
public | cities | partitioned table | postgres | 0 bytes |
public | cities_1 | table | postgres | 8192 bytes |
public | cities_2 | table | postgres | 8192 bytes |
public | cities_3 | table | postgres | 8192 bytes |
public | cities_4 | table | postgres | 8192 bytes |
(5 rows)
postgres=# select * from pg_inherits;
inhrelid | inhparent | inhseqno
----------+-----------+----------
16727 | 16724 | 1
16733 | 16724 | 1
16739 | 16724 | 1
16745 | 16724 | 1
(4 rows)
————————————————
🌈 2.3 HASH 分区
每个分区的数据通过提供模数和余数来分区。
每个分区将包含模数除以分区键的哈希值得到给定余数的行。
当您希望通过几乎均匀地分布数据来避免对单个表的访问集中时，这种方法很有效。
CREATE TABLE emp (emp_id int, emp_name text, dep_code int)
PARTITION BY HASH (emp_id);
CREATE TABLE emp_0 PARTITION OF emp FOR VALUES WITH (MODULUS 3,REMAINDER 0);
CREATE TABLE emp_1 PARTITION OF emp FOR VALUES WITH (MODULUS 3,REMAINDER 1);
CREATE TABLE emp_2 PARTITION OF emp FOR VALUES WITH (MODULUS 3,REMAINDER 2);
🚀 3.分区表常用命令
查看分区表和每个分区的行数
SELECT relname,reltuples as rows FROM pg_class
WHERE relname IN ('emp','emp_0','emp_1','emp_2')
ORDER BY relname;
从分区表中分离分区
ALTER TABLE emp DETACH PARTITION emp_0;
重命名分区
ALTER TABLE emp_0 RENAME TO emp_0_bkp;
添加新的分区
ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN ('value');
删除现有的分区
ALTER TABLE table_name DROP PARTITION partition_name;
查看所有分区信息
SELECT * FROM pg_partitions WHERE tablename = 'table_name';
🚀 4.总结
简而言之，分区是关系数据库中用于将大表分解为较小分区的一种方法。这有助于更快地对大表执行查询。在本文中，您学习了以上3种类型的 PostgreSQL 分区以及如何使用它们
