如何在Azure PostgreSQL灵活服务器中实现列表分区？

环境说明 #

• PostgreSQL 15
• Azure PostgreSQL灵活服务器
• pg_partman v4.7.1不支持列表分区，因此采用PostgreSQL原生列表分区功能实现需求

具体操作步骤 #

1. 创建分区父表 #

先创建与原Employee表结构一致的父表，指定分区类型为LIST，分区键为country：

CREATE TABLE Employee (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    country VARCHAR(50)
) PARTITION BY LIST (country);

2. 创建子分区 #

根据现有数据中的country值，创建对应子分区；同时可创建默认分区容纳未匹配的国家数据：

-- 美国分区
CREATE TABLE Employee_USA PARTITION OF Employee FOR VALUES IN ('USA');
-- 加拿大分区
CREATE TABLE Employee_Canada PARTITION OF Employee FOR VALUES IN ('Canada');
-- 英国分区
CREATE TABLE Employee_UK PARTITION OF Employee FOR VALUES IN ('UK');
-- 澳大利亚分区
CREATE TABLE Employee_Australia PARTITION OF Employee FOR VALUES IN ('Australia');
-- 默认分区（可选）
CREATE TABLE Employee_Default PARTITION OF Employee DEFAULT;

3. 迁移现有数据 #

若原表已有数据，先重命名原表避免冲突，再将数据迁移到新分区表：

-- 重命名原表
ALTER TABLE Employee RENAME TO Employee_old;
-- 迁移数据到分区父表（数据会自动路由到对应子分区）
INSERT INTO Employee SELECT * FROM Employee_old;

4. 验证分区数据分布 #

确认数据正确分配到各个子分区：

SELECT tableoid::regclass AS partition_name, COUNT(*)
FROM Employee
GROUP BY tableoid;

5. 添加索引优化性能 #

为父表创建索引会自动同步到所有子分区，也可单独为特定分区创建针对性索引：

-- 全局索引：为employee_id创建索引
CREATE INDEX idx_employee_id ON Employee(employee_id);
-- 单个分区索引：为美国分区的email字段创建索引
CREATE INDEX idx_employee_usa_email ON Employee_USA(email);

6. 后续维护操作 #

• 新增国家时，直接创建对应子分区：

CREATE TABLE Employee_Germany PARTITION OF Employee FOR VALUES IN ('Germany');

• 删除某个国家的分区时，直接删除对应子表：

DROP TABLE Employee_Germany;

注意事项 #

• 海量数据迁移时，建议分批插入或使用pg_dump/pg_restore工具，避免长时间锁表影响业务。
• 查询时需包含country字段，PostgreSQL才能直接路由到对应分区，发挥分区性能优势。

内容的提问来源于stack exchange，提问作者Shirantha Madusanka