【PostgreSQL】PostgreSQL两层hash分区表（适用于大数据量表）

---

适用场景

表上数据量大（一年2亿左右，一个月2000万左右），没有区分度高的字段。
可以做20个一级分区+10个二级分区，可以使用10年。

一、创建主表

drop table if exists parent_table cascade;
CREATE TABLE parent_table (
    id serial PRIMARY KEY,
    -- Other columns
		name varchar(200),
		age int8
) PARTITION BY HASH(id);

二、创建一级分区表

/* 
drop table if exists child_table_level1_1 cascade;
drop table if exists child_table_level1_2 cascade;
 */
CREATE TABLE child_table_level1_1 PARTITION OF parent_table FOR VALUES WITH (MODULUS 2, REMAINDER 0) PARTITION BY HASH(id);
CREATE TABLE child_table_level1_2 PARTITION OF parent_table FOR VALUES WITH (MODULUS 2, REMAINDER 1) PARTITION BY HASH(id);

三、创建二级分区

/* 
drop table if exists child_table_level2_11 cascade;
drop table if exists child_table_level2_12 cascade;
drop table if exists child_table_level2_21 cascade;
drop table if exists child_table_level2_22 cascade;
 */
CREATE TABLE child_table_level2_11 PARTITION OF child_table_level1_1 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE child_table_level2_12 PARTITION OF child_table_level1_1 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
CREATE TABLE child_table_level2_21 PARTITION OF child_table_level1_2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE child_table_level2_22 PARTITION OF child_table_level1_2 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

四、测试数据，造数

insert into parent_table(name,age) 
select 'tzq'||generate_series(1,9999),18;

五、查询数据具体在哪个分区表

-- 查主表
select * from parent_table where id = 66; -- 在
-- 查一级分区表
select * from child_table_level1_1 where id = 66; -- 在
select * from child_table_level1_2 where id = 66;
-- 查二级分区
select * from child_table_level2_11 where id = 66; -- 在
select * from child_table_level2_12 where id = 66;
select * from child_table_level2_21 where id = 66;
select * from child_table_level2_22 where id = 66;

六、建立索引（参考）

对子表和父表上的关键列建立索引，以提高查询性能。

-- 主表
CREATE INDEX idx_parent_table_id ON parent_table (id);
-- 一级分区表
CREATE INDEX idx_child_table_level1_1_id ON child_table_level1_1 (id);
CREATE INDEX idx_child_table_level1_2_id ON child_table_level1_2 (id);
-- 二级分区
CREATE INDEX idx_child_table_level2_11_id ON child_table_level2_11 (id);
CREATE INDEX idx_child_table_level2_12_id ON child_table_level2_12 (id);
CREATE INDEX idx_child_table_level2_21_id ON child_table_level2_21 (id);
CREATE INDEX idx_child_table_level2_22_id ON child_table_level2_22 (id);

附：如果只是一层分区，可参考下面这样创建,本次场景不涉及

/* 
drop table if exists child_table_level1_1 cascade;
drop table if exists child_table_level1_2 cascade;
 */ 
CREATE TABLE child_table_level1_1 PARTITION OF parent_table FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE child_table_level1_2 PARTITION OF parent_table FOR VALUES WITH (MODULUS 2, REMAINDER 1);