PostgreSQL中如何实现多级分区（分区内分区）构建三级层级？

没问题，PostgreSQL 12及以上版本的声明式分区完全支持这种"分区内再分区"的多级层级需求。下面我会一步步带你实现：主表按id做一级分区，每个一级分区再按date做二级分区（也就是你说的三级层级：主表→id分区→date分区）。

1. 创建主分区表（一级） #

首先创建主表，指定按id列做范围分区。这里我用范围分区举例，你也可以根据业务需求改成列表分区：

CREATE TABLE main_table (
    id INT NOT NULL,
    event_date DATE NOT NULL,
    data TEXT
) PARTITION BY RANGE (id);

2. 创建二级分区（主表的子分区，按id划分） #

接下来创建主表的直接子分区（也就是你说的二级层级），关键是要给这些子分区也指定分区规则——按event_date范围分区，这样它们就成为了可以继续分区的分区表：

-- 第一个id区间的二级分区：id 0~1000
CREATE TABLE main_table_id_0_1000
    PARTITION OF main_table
    FOR VALUES FROM (0) TO (1001)
    PARTITION BY RANGE (event_date);

-- 第二个id区间的二级分区：id 1001~2000
CREATE TABLE main_table_id_1001_2000
    PARTITION OF main_table
    FOR VALUES FROM (1001) TO (2001)
    PARTITION BY RANGE (event_date);

注意：这里的区间是左闭右开的，比如FROM (0) TO (1001)会包含id=1000，但不包含id=1001，符合PostgreSQL分区的规则。

3. 创建三级分区（二级分区的子分区，按date划分） #

现在给每个二级分区创建基于event_date的三级分区，比如按年月拆分：

-- 给id_0_1000分区创建2023年1月、2月的三级分区
CREATE TABLE main_table_id_0_1000_202301
    PARTITION OF main_table_id_0_1000
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE main_table_id_0_1000_202302
    PARTITION OF main_table_id_0_1000
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- 给id_1001_2000分区创建对应的三级分区
CREATE TABLE main_table_id_1001_2000_202301
    PARTITION OF main_table_id_1001_2000
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE main_table_id_1001_2000_202302
    PARTITION OF main_table_id_1001_2000
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

4. 测试分区路由 #

插入几条测试数据，PostgreSQL会自动把数据路由到对应的三级分区，不需要手动指定：

INSERT INTO main_table VALUES
    (500, '2023-01-15', '用户A的操作日志'),
    (1500, '2023-02-20', '用户B的操作日志'),
    (800, '2023-02-05', '用户C的操作日志');

验证数据是否在正确的分区：

-- 应该返回用户A的数据
SELECT * FROM main_table_id_0_1000_202301;

-- 应该返回用户B的数据
SELECT * FROM main_table_id_1001_2000_202302;

-- 应该返回用户C的数据
SELECT * FROM main_table_id_0_1000_202302;

实用注意事项 #

• 版本要求：必须使用PostgreSQL 12+，之前的版本仅支持继承式分区，嵌套分区的体验很差。
• 默认分区：如果要处理不在指定范围内的id或日期，可以创建默认分区，避免插入数据失败：

  -- 主表的默认二级分区（处理id不在0~2000的数据）
  CREATE TABLE main_table_id_default
      PARTITION OF main_table
      DEFAULT
      PARTITION BY RANGE (event_date);
  
  -- 默认二级分区的默认三级分区
  CREATE TABLE main_table_id_default_date_default
      PARTITION OF main_table_id_default
      DEFAULT;
  

• 动态新增分区：后续可以用ALTER TABLE随时新增分区，比如新增一个id区间的二级分区，或者给已有二级分区新增月份的三级分区：

  -- 新增id 2001~3000的二级分区
  CREATE TABLE main_table_id_2001_3000
      PARTITION OF main_table
      FOR VALUES FROM (2001) TO (3001)
      PARTITION BY RANGE (event_date);
  
  -- 给这个二级分区新增2023年3月的三级分区
  CREATE TABLE main_table_id_2001_3000_202303
      PARTITION OF main_table_id_2001_3000
      FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
  

• 索引优化：可以直接在主表上创建索引，所有分区会自动继承这个索引，不用逐个分区创建：

  CREATE INDEX idx_main_table_event_date ON main_table(event_date);
  

内容的提问来源于stack exchange，提问作者Ali Hussain