分区表概念

基础概念

分区表是将一个大表逻辑上划分为多个较小、更易管理的部分（称为分区）的技术，每个分区可以独立存储和管理。

• 主表/父表/Master Table 该表是创建子表的模板。它是一个正常的普通表，但正常情况下它并不储存任何数据。
• 子表/分区表/Child Table/Partition Table 这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表。

分区优势

再大数据场景下，分区表具有以下优势：

• 查询性能提升：通过分区裁剪（Partition Pruning），查询可以只扫描相关分区，减少I/O操作
• 维护效率提高：可以单独对某个分区进行备份、恢复、VACUUM或REINDEX操作
• I/O负载分散：不同分区可以存储在不同磁盘上，实现I/O并行化
• 生命周期管理：便于实现数据归档和过期数据清理
• 缓存利用率提升：热点数据可以更有效地利用内存缓存
• 并行查询优化：每个分区可以并行扫描，充分利用多核CPU资源

PostgreSQL 分区演进过程

• PG10：引入声明式分区（语法简化）
• PG11：支持主键/外键/索引自动继承
• PG12：性能大幅提升（分区裁剪优化）
• PG14：并行分区表扫描

分区表分类

PostgreSQL支持以下几种分区类型：

• 范围分区（Range Partitioning）：基于一个数值范围进行分区，如按日期范围、ID范围等
• 列表分区（List Partitioning）：基于一个离散值列表进行分区，如按地区、状态等
• 哈希分区（Hash Partitioning）：基于哈希算法将数据均匀分布到不同分区
• 复合分区：支持多级分区组合，如先按时间范围分区，再按哈希分区

分区类型对比

分区表创建

基本流程

1. 创建主表，并通过PARTITION BY子句指定分区方法与分区字段
2. 创建分区表，并通过PARTITION OF 主表 FOR VALUES ...指定分区范围

创建Range分区表

1. 创建主表，并通过PARTITION BY RANGE (分区字段)子句指定分区方法与分区字段，分区字段为时间
2. 创建分区表，并通过PARTITION OF 主表 FOR VALUES FROM (分区起始时间) TO (分区结束时间)指定分区范围

-- 创建主表
CREATE TABLE sales (
    id SERIAL,
    product_name VARCHAR(100) NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    customer_id INT
) PARTITION BY RANGE (sale_date);

-- 创建2023年1季度分区
CREATE TABLE sales_q1_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

-- 创建2023年2季度分区
CREATE TABLE sales_q2_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

-- 创建2023年3季度分区
CREATE TABLE sales_q3_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

-- 创建2023年4季度分区
CREATE TABLE sales_q4_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

-- 为分区表添加约束和索引
-- ALTER TABLE sales_q1_2023 ADD CONSTRAINT sales_q1_2023_pkey PRIMARY KEY (id, sale_date);
-- ALTER TABLE sales_q2_2023 ADD CONSTRAINT sales_q2_2023_pkey PRIMARY KEY (id, sale_date);
-- ALTER TABLE sales_q3_2023 ADD CONSTRAINT sales_q3_2023_pkey PRIMARY KEY (id, sale_date);
-- ALTER TABLE sales_q4_2023 ADD CONSTRAINT sales_q4_2023_pkey PRIMARY KEY (id, sale_date);

-- 在每个分区上创建索引
-- CREATE INDEX idx_sales_q1_2023_sale_date ON sales_q1_2023 (sale_date);
-- CREATE INDEX idx_sales_q2_2023_sale_date ON sales_q2_2023 (sale_date);
-- CREATE INDEX idx_sales_q3_2023_sale_date ON sales_q3_2023 (sale_date);
-- CREATE INDEX idx_sales_q4_2023_sale_date ON sales_q4_2023 (sale_date);

-- 插入测试数据
INSERT INTO sales (product_name, sale_date, amount, customer_id)
VALUES 
('Product A', '2023-02-15', 150.00, 101),
('Product B', '2023-05-20', 220.50, 102),
('Product C', '2023-08-10', 95.99, 103),
('Product D', '2023-11-30', 300.00, 104);

-- 查询示例：统计2023年第二季度的销售总额
SELECT SUM(amount) FROM sales 
WHERE sale_date >= '2023-04-01' AND sale_date < '2023-07-01';  

注意：

每个分区的定义必须指定对应于父表的分区方法和分区键的边界。注意，如果指定的边界使得新分区的值会与已有分区中的值重叠，则会导致错误。向父表中插入无法映射到任何现有分区的数据将会导致错误，这种情况下应该手工增加一个合适的分区。

创建List分区表

1. 创建主表，并通过PARTITION BY LIST (分区字段)子句指定分区方法与分区字段，分区字段为离散的数据列（分类）
2. 创建分区表，并通过PARTITION OF 主表 FOR VALUES IN (类型1, 类型2, ...)指定分区枚举

-- 创建主表
CREATE TABLE inventory (
    product_id SERIAL,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY LIST (category);

-- 创建电子产品分区
CREATE TABLE inventory_electronics PARTITION OF inventory
    FOR VALUES IN ('Electronics', 'Computers', 'Smartphones');

-- 创建服装分区
CREATE TABLE inventory_clothing PARTITION OF inventory
    FOR VALUES IN ('Clothing', 'Footwear', 'Accessories');

-- 创建食品分区
CREATE TABLE inventory_food PARTITION OF inventory
    FOR VALUES IN ('Grocery', 'Snacks', 'Beverages');

-- 创建家居分区
CREATE TABLE inventory_home PARTITION OF inventory
    FOR VALUES IN ('Furniture', 'Kitchenware', 'Home Decor');

-- 为未知类别创建默认分区
CREATE TABLE inventory_other PARTITION OF inventory
    DEFAULT;

-- 为分区表添加约束和索引
-- ALTER TABLE inventory_electronics ADD CONSTRAINT inventory_electronics_pkey PRIMARY KEY (product_id, category);
-- ALTER TABLE inventory_clothing ADD CONSTRAINT inventory_clothing_pkey PRIMARY KEY (product_id, category);
-- ALTER TABLE inventory_food ADD CONSTRAINT inventory_food_pkey PRIMARY KEY (product_id, category);
-- ALTER TABLE inventory_home ADD CONSTRAINT inventory_home_pkey PRIMARY KEY (product_id, category);
-- ALTER TABLE inventory_other ADD CONSTRAINT inventory_other_pkey PRIMARY KEY (product_id, category);

-- 在每个分区上创建索引
-- CREATE INDEX idx_inventory_electronics_category ON inventory_electronics (category);
-- CREATE INDEX idx_inventory_clothing_category ON inventory_clothing (category);
-- CREATE INDEX idx_inventory_food_category ON inventory_food (category);
-- CREATE INDEX idx_inventory_home_category ON inventory_home (category);
-- CREATE INDEX idx_inventory_other_category ON inventory_other (category);

-- 插入测试数据
INSERT INTO inventory (product_name, category, quantity, price)
VALUES 
('Laptop', 'Electronics', 50, 999.99),
('T-Shirt', 'Clothing', 200, 19.99),
('Chips', 'Snacks', 150, 4.99),
('Sofa', 'Furniture', 10, 799.99),
('Watch', 'Accessories', 30, 149.99),
('Camera', 'Electronics', 25, 599.99),
('Coffee', 'Beverages', 80, 12.99),
('Book', 'Other', 100, 24.99);

-- 查询示例：统计电子产品的总库存价值
SELECT category, SUM(quantity * price) AS total_value 
FROM inventory 
WHERE category IN ('Electronics', 'Computers', 'Smartphones')
GROUP BY category;    

创建Hash分区表

1. 创建主表，并通过PARTITION BY LIST (分区字段)子句指定分区方法与分区字段，分区字段为一个数字，一般为id
2. 创建分区表，并通过PARTITION OF 主表 FOR VALUES WITH (MODULUS 模数, REMAINDER 余数)指定分区的规则

-- 创建主表
CREATE TABLE user_logs (
    log_id BIGSERIAL,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT
) PARTITION BY HASH (user_id);

-- 创建4个Hash分区
CREATE TABLE user_logs_hash_0 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_logs_hash_1 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_logs_hash_2 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_logs_hash_3 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 为分区表添加约束和索引
-- ALTER TABLE user_logs_hash_0 ADD CONSTRAINT user_logs_hash_0_pkey PRIMARY KEY (log_id, user_id);
-- ALTER TABLE user_logs_hash_1 ADD CONSTRAINT user_logs_hash_1_pkey PRIMARY KEY (log_id, user_id);
-- ALTER TABLE user_logs_hash_2 ADD CONSTRAINT user_logs_hash_2_pkey PRIMARY KEY (log_id, user_id);
-- ALTER TABLE user_logs_hash_3 ADD CONSTRAINT user_logs_hash_3_pkey PRIMARY KEY (log_id, user_id);

-- 在每个分区上创建常用查询索引
-- CREATE INDEX idx_user_logs_hash_0_user_id ON user_logs_hash_0 (user_id);
-- CREATE INDEX idx_user_logs_hash_1_user_id ON user_logs_hash_1 (user_id);
-- CREATE INDEX idx_user_logs_hash_2_user_id ON user_logs_hash_2 (user_id);
-- CREATE INDEX idx_user_logs_hash_3_user_id ON user_logs_hash_3 (user_id);

-- CREATE INDEX idx_user_logs_hash_0_action_time ON user_logs_hash_0 (action_time);
-- CREATE INDEX idx_user_logs_hash_1_action_time ON user_logs_hash_1 (action_time);
-- CREATE INDEX idx_user_logs_hash_2_action_time ON user_logs_hash_2 (action_time);
-- CREATE INDEX idx_user_logs_hash_3_action_time ON user_logs_hash_3 (action_time);

-- 插入测试数据
INSERT INTO user_logs (user_id, action_type, ip_address)
VALUES 
(1001, 'login', '192.168.1.1'),
(1002, 'purchase', '192.168.1.2'),
(1003, 'logout', '192.168.1.3'),
(1004, 'search', '192.168.1.4'),
(1005, 'profile_update', '192.168.1.5'),
(1006, 'add_to_cart', '192.168.1.6'),
(1007, 'review', '192.168.1.7'),
(1008, 'wishlist', '192.168.1.8');

-- 查询示例：统计特定用户的行为记录
SELECT * FROM user_logs 
WHERE user_id = 1003;

-- 查询示例：统计所有分区中的总记录数
SELECT 
    'user_logs_hash_0' AS partition, 
    COUNT(*) AS record_count 
FROM user_logs_hash_0
UNION ALL
SELECT 'user_logs_hash_1', COUNT(*) FROM user_logs_hash_1
UNION ALL
SELECT 'user_logs_hash_2', COUNT(*) FROM user_logs_hash_2
UNION ALL
SELECT 'user_logs_hash_3', COUNT(*) FROM user_logs_hash_3;    

分区维护

基础维护

1. 删除分区 移除旧数据最简单的选择是删除掉不再需要的分区

 DROP TABLE 分区表名;

1. 脱离主表 把分区从分区表中移除，但是保留它作为一个独立的表

ALTER TABLE 主表 DETACH PARTITION 分区表;

分区表优化策略

索引策略

• 主表上的索引会自动应用到所有分区
• 为特定查询模式创建复合索引
• 考虑在分区键上创建本地索引

约束排除优化

约束排除（Constraint Exclusion）是 PostgreSQL 中一项重要的查询优化技术，特别适用于分区表。它通过分析查询条件与分区约束的关系，自动排除不满足条件的分区，从而减少不必要的数据扫描，提高查询效率。

约束排除默认是启用的（constraint_exclusion = partition）,但需要确保启用。

-- 查看当前设置
SHOW constraint_exclusion;  -- 默认为"partition"

-- 修改设置（会话级别）
SET constraint_exclusion = partition;  -- 仅对分区表启用
-- 或
SET constraint_exclusion = on;        -- 对所有表启用（可能影响性能）

并行查询配置

-- 调整并行查询参数
SET max_parallel_workers_per_gather = 8;
SET parallel_setup_cost = 100;
SET parallel_tuple_cost = 0.1;

分区键选择原则

• 选择高基数列且常用于查询条件的列
• 避免选择频繁更新的列作为分区键
• 考虑未来数据增长和查询模式变化

大数据场景下的最佳实践

分区粒度设计

• 时间序列数据：按天分区（每日数据量>1GB时）或按周/月分区
• 业务数据：按业务单元或ID范围分区，确保分区大小均衡
• 一般建议单个分区大小控制在1-10GB范围内

存储分层策略

-- 将历史分区移动到慢速存储
ALTER TABLE sensor_data_202201 SET TABLESPACE slow_hdd;

-- 使用表空间实现冷热分离
CREATE TABLESPACE fast_ssd LOCATION '/ssd_data';
CREATE TABLESPACE slow_hdd LOCATION '/hdd_data';

分区维护自动化

• 使用pg_partman管理分区生命周期
• 设置定时任务自动创建新分区和清理旧分区
• 监控分区增长情况并动态调整分区策略

查询优化技巧

-- 强制使用分区裁剪
SET enable_partition_pruning = on;

-- 使用分区键作为查询条件
EXPLAIN ANALYZE SELECT * FROM sensor_data 
WHERE recorded_at BETWEEN '2023-01-15' AND '2023-01-20';

监控与调优

-- 监控分区使用情况
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) 
FROM pg_class WHERE relname LIKE 'sensor_data%';

-- 识别性能瓶颈
SELECT * FROM pg_stat_user_tables WHERE schemaname = 'public';