PostgreSQL分区表：基础语法与运维实践

引言

简介：什么是数据库分区

数据库分区是一种将大型表物理上分割成多个较小的部分的技术。每个部分称为一个分区，这些分区可以分布在不同的存储设备上，以提高查询性能和管理效率。

为什么使用分区表

• 提高查询性能：通过减少需要扫描的数据量，查询速度可以显著提升。
• 简化数据管理：分区使得数据的备份、恢复和归档更加容易。
• 优化存储：可以将不同分区放在不同的存储介质上，以平衡性能和成本。
• 增强可维护性：分区表的维护操作（如索引重建、数据清理）可以逐分区进行，减少对整个系统的干扰。

PostgreSQL中的分区支持概述

PostgreSQL从9.0版本开始引入了分区支持，并在后续版本中不断改进。目前，PostgreSQL支持多种分区类型，包括范围分区、列表分区和散列分区。通过这些分区技术，可以有效地管理和优化大规模数据表。

分区类型

范围分区（Range Partitioning）

范围分区是根据一个或多个列的值范围来划分表。例如，可以根据日期或数值范围来创建分区。

示例

CREATE TABLE sales (
    id SERIAL ,
    sale_date DATE NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id,sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

这个语句创建了一个名为 sales 的表，该表有三个字段：id（一个自动递增的主键）、sale_date（一个非空的日期字段）和 amount（一个数值字段）。重要的是，这个表被声明为一个分区表，基于 sale_date 字段的范围进行分区。并创建了 sales 表的两个分区子表：sales_2022 和 sales_2023。每个子表都负责存储 sales 表中特定日期范围内的数据。其中，sales_2022 存储 sale_date 从 2022-01-01 到 2022-12-31 的数据，sales_2023 存储 sale_date 从 2023-01-01 到 2023-12-31 的数据。

列表分区（List Partitioning）

列表分区是根据一个或多个列的具体值来划分表。适用于列值有限且已知的情况。

示例

CREATE TABLE employees (
    id SERIAL ,
    department VARCHAR(50) NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id,department)
) PARTITION BY LIST (department);

CREATE TABLE employees_sales PARTITION OF employees FOR VALUES IN ('Sales');
CREATE TABLE employees_marketing PARTITION OF employees FOR VALUES IN ('Marketing');

这个语句创建了一个名为 employees 的表，该表有三个字段：id（一个自动递增的主键）、department（一个非空的 VARCHAR 类型字段，用于存储部门名称）和 name（一个 VARCHAR 类型字段，用于存储员工姓名）。这个表被声明为一个分区表，基于 department 字段的列表值进行分区。并创建了 employees 表的两个分区子表：employees_sales 和 employees_marketing。employees_sales 负责存储 department 字段值为 ‘Sales’ 的员工数据，而 employees_marketing 负责存储 department 字段值为 ‘Marketing’ 的员工数据。