印刻数据库

外观

PostgreSQL 分区表设计与管理 ​

分区策略选择 ​

范围分区 ​

范围分区是最常用的分区策略，适合按时间、数值等连续数据进行分区。

sql
-- 创建按日期范围分区的表
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023_jan PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

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

CREATE TABLE sales_2023_mar PARTITION OF sales
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

列表分区 ​

列表分区适合按离散值进行分区，如地区、状态等。

sql
-- 创建按地区列表分区的表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    region VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
)
PARTITION BY LIST (region);

-- 创建分区
CREATE TABLE orders_north PARTITION OF orders
    FOR VALUES IN ('North', 'Northeast');

CREATE TABLE orders_south PARTITION OF orders
    FOR VALUES IN ('South', 'Southeast');

CREATE TABLE orders_west PARTITION OF orders
    FOR VALUES IN ('West', 'Northwest', 'Southwest');

哈希分区 ​

哈希分区适合均匀分布数据，减少热点问题，适合无法按范围或列表分区的场景。

sql
-- 创建按用户ID哈希分区的表
CREATE TABLE user_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    log_time TIMESTAMP NOT NULL,
    action VARCHAR(50) NOT NULL,
    details JSONB
)
PARTITION BY HASH (user_id);

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

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

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

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

复合分区 ​

PostgreSQL 11及以上版本支持复合分区，可以结合多种分区策略。

sql
-- 创建复合分区表（范围+哈希）
CREATE TABLE sensor_data (
    sensor_id INT NOT NULL,
    measurement_time TIMESTAMP NOT NULL,
    temperature DECIMAL(5,2) NOT NULL,
    humidity DECIMAL(5,2) NOT NULL,
    pressure DECIMAL(6,2) NOT NULL
)
PARTITION BY RANGE (measurement_time)
    SUBPARTITION BY HASH (sensor_id)
    SUBPARTITION TEMPLATE (
        SUBPARTITION sp0 FOR VALUES WITH (MODULUS 8, REMAINDER 0),
        SUBPARTITION sp1 FOR VALUES WITH (MODULUS 8, REMAINDER 1),
        SUBPARTITION sp2 FOR VALUES WITH (MODULUS 8, REMAINDER 2),
        SUBPARTITION sp3 FOR VALUES WITH (MODULUS 8, REMAINDER 3),
        SUBPARTITION sp4 FOR VALUES WITH (MODULUS 8, REMAINDER 4),
        SUBPARTITION sp5 FOR VALUES WITH (MODULUS 8, REMAINDER 5),
        SUBPARTITION sp6 FOR VALUES WITH (MODULUS 8, REMAINDER 6),
        SUBPARTITION sp7 FOR VALUES WITH (MODULUS 8, REMAINDER 7)
    );

-- 创建主分区
CREATE TABLE sensor_data_2023_q1 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

分区表管理操作 ​

添加分区 ​

sql
-- 为范围分区表添加新分区
CREATE TABLE sales_2023_apr PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

-- 为列表分区表添加新分区
CREATE TABLE orders_east PARTITION OF orders
    FOR VALUES IN ('East', 'Northeast');

-- 为哈希分区表添加新分区
CREATE TABLE user_logs_p4 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 5, REMAINDER 4);

删除分区 ​

sql
-- 删除指定分区（数据也会被删除）
DROP TABLE sales_2023_jan;

-- 或者使用ALTER TABLE语句
ALTER TABLE sales DROP PARTITION sales_2023_jan;

拆分分区 ​

sql
-- 拆分现有分区
-- 首先创建新的子分区
CREATE TABLE sales_2023_may_1 PARTITION OF sales
    FOR VALUES FROM ('2023-05-01') TO ('2023-05-16');

CREATE TABLE sales_2023_may_2 PARTITION OF sales
    FOR VALUES FROM ('2023-05-16') TO ('2023-06-01');

-- 然后删除原分区（注意：需要先确保新分区覆盖了原分区的所有范围）
DROP TABLE sales_2023_may;

合并分区 ​

sql
-- 合并分区需要先删除子分区，然后重新创建合并后的分区
-- 删除子分区
DROP TABLE sales_2023_may_1;
DROP TABLE sales_2023_may_2;

-- 创建合并后的分区
CREATE TABLE sales_2023_may PARTITION OF sales
    FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');

交换分区 ​

交换分区是将分区表与普通表交换的操作，常用于数据加载和卸载。

sql
-- 创建与分区结构相同的普通表
CREATE TABLE sales_temp (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

-- 向普通表加载数据
COPY sales_temp FROM '/path/to/sales_data_2023_jun.csv' WITH CSV HEADER;

-- 交换分区
ALTER TABLE sales EXCHANGE PARTITION sales_2023_jun WITH TABLE sales_temp;

-- 交换后，sales_temp变为空表，原sales_temp的数据在sales_2023_jun分区中

分区表索引管理 ​

创建索引 ​

sql
-- 在分区表上创建索引（会自动创建在所有分区上）
CREATE INDEX idx_sales_date ON sales(sale_date);

-- 在分区表上创建唯一索引
CREATE UNIQUE INDEX idx_sales_unique ON sales(sale_id, sale_date);

-- 在特定分区上创建本地索引
CREATE INDEX idx_sales_2023_jan_product ON sales_2023_jan(product_id);

索引维护 ​

sql
-- 重建特定分区的索引
REINDEX INDEX idx_sales_2023_jan_product;

-- 分析分区表的统计信息
ANALYZE sales;

-- 分析特定分区
ANALYZE sales_2023_jan;

分区表查询优化 ​

分区裁剪 ​

确保查询条件包含分区键，以便PostgreSQL能跳过不必要的分区。

sql
-- 好的查询：包含分区键，会进行分区裁剪
SELECT * FROM sales 
WHERE sale_date BETWEEN '2023-03-01' AND '2023-03-31';

-- 差的查询：不包含分区键，需要扫描所有分区
SELECT * FROM sales 
WHERE product_id = 123;

分区表统计信息 ​

定期更新分区表的统计信息，确保查询优化器能做出正确的决策。

sql
-- 启用自动统计信息收集
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;

-- 手动分析分区表
ANALYZE VERBOSE sales;

并行查询 ​

对于大分区表查询，可以启用并行查询以提高性能。

sql
-- 设置并行查询相关参数
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;

分区表维护最佳实践 ​

1. 定期清理旧分区 ​

sql
-- 创建清理旧分区的函数
CREATE OR REPLACE FUNCTION cleanup_old_partitions()
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
BEGIN
    FOR partition_name IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE tablename LIKE 'sales_%' 
        AND RIGHT(tablename, 6)::INT < TO_CHAR(NOW() - INTERVAL '12 months', 'YYYYMM')
    LOOP
        EXECUTE format('DROP TABLE %I', partition_name);
        RAISE NOTICE 'Dropped partition: %', partition_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 执行清理
SELECT cleanup_old_partitions();

2. 预创建未来分区 ​

sql
-- 创建预创建未来分区的函数
CREATE OR REPLACE FUNCTION create_future_partitions()
RETURNS VOID AS $$
DECLARE
    i INT;
    start_date DATE;
    end_date DATE;
    partition_name TEXT;
BEGIN
    FOR i IN 0..11 LOOP
        start_date := DATE_TRUNC('month', NOW() + INTERVAL '1 month' * i);
        end_date := start_date + INTERVAL '1 month';
        partition_name := format('sales_%s', TO_CHAR(start_date, 'YYYY_mon'));
        
        -- 检查分区是否已存在
        IF NOT EXISTS (
            SELECT 1 FROM pg_tables WHERE tablename = partition_name
        ) THEN
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF sales FOR VALUES FROM (%L) TO (%L)',
                partition_name, start_date, end_date
            );
            RAISE NOTICE 'Created partition: %', partition_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 执行预创建
SELECT create_future_partitions();

3. 监控分区表大小 ​

sql
-- 查看各分区大小
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS indexes_size
FROM 
    pg_tables
WHERE 
    tablename LIKE 'sales_%'
ORDER BY 
    pg_total_relation_size(schemaname || '.' || tablename) DESC;

常见问题（FAQ） ​

Q1：分区表和继承表有什么区别？ ​

A1：分区表是继承表的一种特殊实现，PostgreSQL 10及以上版本支持声明式分区。主要区别：

  • 声明式分区由PostgreSQL自动管理分区关系
  • 分区表支持分区裁剪，查询性能更好
  • 分区表语法更简洁，管理更方便
  • 继承表更灵活，但需要手动管理分区关系

Q2：如何将普通表转换为分区表？ ​

A2：可以使用以下步骤：

  1. 创建分区表结构
  2. 创建与原表结构相同的分区
  3. 使用INSERT INTO...SELECT将数据迁移到分区
  4. 重命名原表，重命名分区表为原表名
  5. 重新创建索引和约束

Q3：分区表支持外键吗？ ​

A3：PostgreSQL 11及以上版本支持分区表的外键约束，但有一些限制：

  • 分区表可以作为外键的引用端
  • 分区表作为被引用端时，需要在每个分区上创建约束
  • 建议将外键约束放在分区表级别，由PostgreSQL自动管理

Q4：如何监控分区表的性能？ ​

A4：可以使用以下方法监控：

  • 使用EXPLAIN ANALYZE查看查询计划，确认是否进行了分区裁剪
  • 监控pg_stat_user_tables和pg_stat_user_indexes视图
  • 使用pg_stat_statements查看慢查询
  • 定期检查分区表的统计信息是否最新

Q5：分区表支持哪些PostgreSQL版本？ ​

A5：

  • PostgreSQL 10：支持基本的声明式分区（范围、列表）
  • PostgreSQL 11：支持哈希分区和复合分区
  • PostgreSQL 12：支持分区表的主键、外键和索引增强
  • PostgreSQL 13：支持分区表的并行查询优化
  • PostgreSQL 14：支持分区表的快速更新和删除

建议使用PostgreSQL 12及以上版本以获得更好的分区表支持。