当前位置：首页 > news >正文

postgresql16分区表解析

news 2025/8/25 11:09:55

PostgreSQL 16 引入了对分区表的多项改进，增强了其性能和可用性。本文介绍PostgreSQL 16 中分区表功能，包括基本概念、创建方法、管理技巧以及一些最佳实践。

分区表的基本概念

分区表是一种将大表物理分割成更小、更易管理的部分的技术。每个部分称为一个分区。通过合理地设计分区策略，可以显著提高查询性能，简化维护工作，并有助于更好地利用存储资源。

范围分区：基于某个列或一组列的值范围来划分数据。
列表分区：基于列中特定离散值来划分数据。
哈希分区：使用哈希函数根据键值将数据均匀分布到多个分区中。

创建分区表

1. 范围分区

-- 创建父表
CREATE TABLE sales (id SERIAL PRIMARY KEY,sale_date DATE NOT NULL,amount NUMERIC(10, 2)
) PARTITION BY RANGE (sale_date);-- 创建子分区
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

2. 列表分区

-- 创建父表
CREATE TABLE products (product_id INT PRIMARY KEY,category TEXT NOT NULL,price NUMERIC(10, 2)
) PARTITION BY LIST (category);-- 创建子分区
CREATE TABLE products_electronics PARTITION OF products
FOR VALUES IN ('Electronics');CREATE TABLE products_books PARTITION OF products
FOR VALUES IN ('Books');

3. 哈希分区

-- 创建父表
CREATE TABLE users (user_id INT PRIMARY KEY,username TEXT NOT NULL,email TEXT NOT NULL
) PARTITION BY HASH (user_id);-- 创建子分区
CREATE TABLE users_p1 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 0);CREATE TABLE users_p2 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 1);CREATE TABLE users_p3 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 2);CREATE TABLE users_p4 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

管理分区表

添加新分区

-- 为范围分区添加新的子分区
ALTER TABLE sales ATTACH PARTITION sales_2025
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

删除分区

-- 删除不再需要的分区
ALTER TABLE sales DETACH PARTITION sales_2023;

交换分区

-- 将现有表转换为分区
CREATE TABLE sales_2022 (LIKE sales INCLUDING ALL);
INSERT INTO sales_2022 SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2023-01-01';-- 交换分区
ALTER TABLE sales EXCHANGE PARTITION FOR (RANGE ('2022-01-01', '2023-01-01')) WITH TABLE sales_2022;