PG数据库-分区

一、表分区

1. 【父表】【分区键】建索引：子表自动创建索引
2. 分区键上的索引只有父表需要，只用于父表找到子表，所以无需再子表上创建。
3. 非分区键上的索引会传播的子表上，自动创建。
4. 【父表】建索引：不希望所有子表自动建索引；增加ONLY关键字，只给父表创建索引；在使用alter index给某些子表建索引：

CREATE INDEX sex_age_idx ON ONLY sex_test_table(arr);

   5. 子表手动创建索引

CREATE INDEX sex_youth_age_idx ON cust_archived (age);
ALTER INDEX sex_age_idx ATTACH PARTITION sex_youth_age_idx ;

6.【父表】先建索引后建子表，子表索引会自动建。

二、表分区建议总结

1. 分区键离散，可以使用PARTITION BY LIST。按字符串匹配决定落入哪个分区。
2. 分区键连续，比如整形、日期等，可以使用PARTITION BY RANGE。
3. 分区键数据随机无规律或规律简单，可以使用PARTITION BY HASH，用hash函数打散数据。
4. 分区键数据随机有规律，规律复杂，可以使用多级混合分区，使数据平均分散、减少耦合。

每个分区都是一个普通PG表：

可以指定表空间：例如按月份分区的场景，可以把历史非活跃数据通过表空间指定到慢速廉价存储上，新的热数据保存到快速存储上。
可以指定并发度：热数据表定制并发度parallel_workers，查询自动使用并行查询。

三、查询建议

1. 不带分区键的查询 或 带分区键但涉及大部分分区表的查询 会使执行计划成倍增长，在分区表很多时会消耗大量内存。生成执行计划的时间也会变长（几千个分区时可能Planning time会超过Execution time）。
2. 分区数量的增长应该在设计时就有预期，根据表大小评估，一般最好不要上千。
3. 分区间如果没有数据依赖最好（比如按月份分区可以很方便的删除某一个分区），如果删除一个分区需要把部分数据调整到其他分区，新增一个分区需要从其他分区拿数据，这样效率会很差。

四、PARTITION BY LIST

分区键离散，可以使用PARTITION BY LIST。按字符串匹配决定落入哪个分区。

CREATE TABLE sex_test_table (id INTEGER, sex TEXT, age NUMERIC) PARTITION BY LIST(sex);

CREATE TABLE sex_man PARTITION OF sex_test_table FOR VALUES IN ('man');
CREATE TABLE sex_woman PARTITION OF sex_test_table FOR VALUES IN ('woman' );
CREATE TABLE sex_others PARTITION OF sex_test_table DEFAULT;

INSERT INTO sex_test_table VALUES (1,'man',63), (2,'woman',20), (3,'woman',99), (4,'middle',9);

注：带分区键查询，查询更快;不带分区键，会进行全表扫描

select * from sex_test_table where sex='woman' and age>10

五、PARTITION BY RANGE

   分区键值连续，可以考虑使用PARTITION BY RANGE。

• 整形分区键可以引用关键字：最小值MINVALUE、最大值MAXVALUE。
• 其他类型：无

 
CREATE TABLE sex_test_table (id INTEGER, sex TEXT, age NUMERIC) PARTITION BY RANGE(age);

CREATE TABLE sex_youth PARTITION OF sex_test_table FOR VALUES from (MINVALUE) TO (18);
CREATE TABLE sex_medium PARTITION OF sex_test_table FOR VALUES FROM (19) TO (45);
CREATE TABLE sex_old PARTITION OF sex_test_table FOR VALUES FROM (45) TO (MAXVALUE);

INSERT INTO sex_test_table VALUES (1,'man',63), (2,'woman',20), (3,'woman',99), (4,'middle',9);

六、PARTITION BY HASH

    分区键值本身没有规律进行平均差分，可以用Hash取模离散。

 CREATE TABLE sex_test_table (id INTEGER, sex TEXT, age NUMERIC) PARTITION BY hash(id);

CREATE TABLE sex_youth PARTITION OF sex_test_table FOR VALUES  WITH (modulus 3, remainder 0);
CREATE TABLE sex_medium PARTITION OF sex_test_table FOR VALUES  WITH (modulus 3, remainder 1);
CREATE TABLE sex_old PARTITION OF sex_test_table FOR VALUES  WITH (modulus 3, remainder 2);

INSERT INTO sex_test_table VALUES (1,'man',63), (2,'woman',20), (3,'woman',99), (4,'middle',9);

七、删除分区

ALTER TABLE customers DETACH PARTITION cust_others;