Pg11数据库分区

一、前言

项目初始评估数据量，为日后数据的不断增长做好必要的保障，当数据量达到一定程度时，会影响系统性能，将表分区，可以更好的解决这个问题。

二、分区的优点

1. 大大提高查询性能，分区后可直接查询对应的子表
2. 插入、更新操作的性能更高；插入时，只对子表进行顺序扫描，比扫描整张表高效的多
3. 批量删除时，可以效率更高；对于不需要的数据，可以直接Drop掉整张子表。
4. 对于一些使用频率比较低的数据，可以将对应的子表迁移到成本更低的存储介质上面，降低成本
   注意：并不是分区就一定能提高性能，如果表本身比较小，分区的话，只会更加消耗性能，根据官方的建议，当单张表的体积大于当前服务器内存的时候，建议分区。

三、分区的方式

目前有两种分区方式，一是利用pg内置的分区组件，二是利用插件pg-partman，pg内置组件只能在才创建表时进行分区(这就要求我们在使用初期就规划好)，而pg-partman可以将已经有数据的表进行分区。
省去下载插件的流程，我这里使用的pg内置的分区方式。Pg10之前使用的是通过触发器来进行分区，我这里介绍pg11的分区方式，不需要创建触发器，pg-11分区方式有RANG、LIST、HASH三种，我们现在使用的是RANGE，所以下面介绍一下pg-11 RANGE方式创建分区表的方法

四、操作实例

我现在使用的是RANGE分区

创建测试表

CREATE TABLE mytable (
    id serial,
    event_date date,
    data text,
    PRIMARY KEY (event_date, id)
) PARTITION BY RANGE (event_date);

PRIMARY KEY (event_date, id) --分区键要添加到主键中
PARTITION BY RANGE (event_date) – 将根据"event_date"列的值进行范围分区

分区表

CREATE TABLE mytable_2022 PARTITION OF mytable
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

**

1. CREATE TABLE mytable_2022 创建了一个名为"mytable_2022"的新分区表。
2. PARTITION OF mytable 指定了该分区表是"mytable"的子表，用于存储"mytable"表中特定范围的数据。、
3. FOR VALUES FROM (‘2022-01-01’) TO (‘2023-01-01’) 指定了该分区表应该存储的数据范围。在这个例子中，分区表"mytable_2022"将存储范围在2022年1月1日到2023年1月1日之间的数据。

指定工作空间的分区表

	如果某张表数据量过大我们可以指定数据空间用于存储接下来的数据创建新的工作空间

--创建新的工作空间
CREATE TABLESPACE test_tablespace LOCATION 'C:\\app\\aa';
-- 分区表指定工作空间	
CREATE TABLE mytable_2023 PARTITION OF mytable
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
		TABLESPACE test_tablespace;

验证数据

插入数据
INSERT INTO mytable (event_date, data)
VALUES ('2023-01-01', 'some data');
查看某张表的工作空间
SELECT pg_tablespace.spcname AS tablespace_name
FROM pg_tablespace, pg_class
WHERE pg_tablespace.oid = pg_class.reltablespace
AND relname = 'mytable_2023';
查看所有的工作空间
SHOW data_directory;