HGDB中分区表使用例子

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：4.5,6.0,9.0

文档用途

本文介绍HGDB中原生支持的分区类型范围分区、列表分区和哈希分区，及其使用的例子；简单维护及注意事项。

详细信息

分区表是一种数据库表的组织方式，用于将大量数据分割成更小的、更易管理的子表。这有助于提高查询性能、简化数据维护以及改善数据的存储和管理。分区表是一个逻辑上由多个子表组成的表。每个子表通常包含表中的一部分数据。分区表的数据分割依赖于一个或多个分区键；这是一个或多个表列，它们的值用于将数据分配到相应的子表中。分区键的选择通常取决于查询和数据的分布需求。

在数据库中创建数据表时，从业务、数据治理、维护等方面考虑，我们会把表创建成分区表；表分区有几个好处：

在某些情况下，特别是当表中大多数访问量很大的记录位于单个分区或少数几个分区中时，查询性能可以得到显著提高；分区可以看作是索引的一种替代方式，它使得那些经常被使用的索引部分更容易放在内存中，减少磁盘访问从而提高查询速度；这对于处理大体量数据非常有用。

当查询或更新操作访问单个分区的大部分记录时，通过对该分区进行顺序扫描，而不是使用索引在整个表中进行随机访问读取，从而提高查询效率。

如果在数据库设计时考虑到批量操作的情况，则可以通过添加或删除分区来实现批量加载和删除。使用"DROP TABLE"删除单个分区或执行"ALTER TABLE DETACH PARTITION"比执行批量操作要快得多。这些命令还可以避免由批量DELETE操作引起的VACUUM开销。

可以将访问量比较小的“冷数据”，保存在“成本”相对比较小的存储介质中，实现资源利用最大化。

PostgreSQL原生支持范围分区、列表分区和哈希分区。

范围分区：根据一个或多个列定义的“范围”进行分区，不同分区分配的值范围之间不重叠。例如，可以按日期范围进行分区，或者按特定业务对象的标识范围进行分区。每个范围包含下边界而不包含上边界。例如，如果一个分区的范围是从1到10，下一个分区的范围是从10到20，那么值10属于第二个分区而不是第一个。

列表分区：通过明确列出每个分区中出现的键值来进行分区。

哈希分区：表通过为每个分区指定模数和余数来进行分区。每个分区将保存使分区键的哈希值除以指定的模数产生指定余数的行。

分区表（主表）本身不存储数据，数据都存储在分区（子表）中，每个分区根据其分区界限定义存储数据的子集；插入分区表的所有行都将根据分区键的值路由到适当的一个分区中。分区（子表）本身也可以被定义为分区表（主表），从而产生子分区（二级分区）。尽管所有分区必须与其分区父表具有相同的列，但分区可以拥有自己的索引、约束和默认值。常规表格和分区表不能互相转换；但是，可以将现有的常规表格或分区表作为分区添加到分区表中；或者从分区表中删除一个分区，将其转换为独立表格；这非常有利于数据维护。分区还可以是外部表，同时需要确保外部表的内容满足分区规则。

早期很多用户使用PostgreSQL的继承特性实现了“分区表”，目前PostgreSQL的分区表功能已经很完善了，建议抛弃之前的做法。

分区表一般也是用在大体量数据的情况下，在实际使用中，在尽可能的情况下最好去访问分区（子表），而不是访问分区表（主表），这样可以充分发挥分区表的功能，来提高访问性能。
1.1 范围分区

示例：

假设我们有一个名为sales_data的表，其中包含销售数据，我们想要根据销售日期将数据分为不同的季度分区。

首先，我们创建一个分区表并定义分区规则：

CREATE TABLE sales_data (

    product_id int,

    order_date date,

    amount numeric

) PARTITION BY RANGE (order_date);

接下来，我们创建两个分区，每个分区包含不同范围的订单日期：

-- 创建第一个分区，包含2023年第一季度的数据

CREATE TABLE sales_data_q1 PARTITION

OF sales_data

    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');



-- 创建第二个分区，包含2023年第二季度的数据

CREATE TABLE sales_data_q2 PARTITION

OF sales_data

    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

现在，我们已经创建了两个分区，一个包含第一季度的数据，另一个包含第二季度的数据。数据插入时，会根据订单日期的范围自动路由到相应的分区。例如：

-- 插入销售数据

INSERT INTO sales_data (product_id, order_date, amount)

VALUES (1, '2023-02-15', 500.00),(2, '2023-05-20', 750.00);

1.2 列表分区

示例：

假设我们有一个名为"employees"的表，我们希望根据员工的部门将数据分为不同的分区。

首先，我们创建一个分区表并定义分区规则：

CREATE TABLE employees (

    employee_name text,

    department text

) PARTITION BY LIST (department);

接下来，我们创建两个分区，每个分区包含不同部门的员工数据：

-- 创建部门A的分区

CREATE TABLE employees_A PARTITION

OF employees

    FOR VALUES IN ('HR', 'Finance', 'Admin');



-- 创建部门B的分区

CREATE TABLE employees_B PARTITION

OF employees

    FOR VALUES IN ('Engineering', 'Sales', 'Marketing');

现在，我们已经创建了两个分区，一个包含部门A的员工数据，另一个包含部门B的员工数据。数据插入时，会根据部门名称自动路由到相应的分区。例如：

-- 插入员工数据

INSERT INTO employees (employee_name, department)

VALUES ('Alice', 'HR'),('Bob', 'Engineering');

1.3 哈希分区

示例：

假设我们有一个名为 “sales” 的表，我们想要根据客户的 ID 来进行哈希分区。

首先，我们创建一个分区表并定义分区规则：

CREATE TABLE sales (

    customer_id integer,

    order_date date,

    total_amount numeric

) PARTITION BY HASH (customer_id);

接下来，我们创建几个分区，这些分区将根据客户的 ID 哈希值来存储数据。假设我们创建了四个分区：

-- 创建哈希分区1

CREATE TABLE sales_1 PARTITION

OF sales

    FOR VALUES WITH (MODULUS 4, REMAINDER 0);



-- 创建哈希分区2

CREATE TABLE sales_2 PARTITION

OF sales

    FOR VALUES WITH (MODULUS 4, REMAINDER 1);



-- 创建哈希分区3

CREATE TABLE sales_3 PARTITION

OF sales

    FOR VALUES WITH (MODULUS 4, REMAINDER 2);



-- 创建哈希分区4

CREATE TABLE sales_4 PARTITION

OF sales

    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

在这里，我们使用 MODULUS和REMAINDER来定义了四个哈希分区，分别对客户ID的哈希值取模数，以确保数据均匀分布到这四个分区中。

现在，当我们插入数据时，数据将根据客户的 ID 的哈希值路由到相应的分区：

INSERT INTO sales (customer_id, order_date, total_amount)

VALUES (101, '2023-09-27', 100.50),(202, '2023-09-28', 75.25);

1.4 分区表二级分区

二级分区是指在已经分区的表上再次进行分区，以进一步细分数据。

示例：

假设要存储城市的销售订单数据，一级分区使用城市，二级分区使用年份。

首先，创建主分区表（按城市分区）：

CREATE TABLE sales (

    order_id int,

    order_date date,

    city_id int

) PARTITION BY LIST (city_id);

接下来，为每个城市创建一个子分区表。假设有城市A、城市B和城市C，分别对应城市ID 1、2和3：

-- 创建城市A的分区表

CREATE TABLE sales_city_a PARTITION OF sales

    FOR VALUES IN (1);



-- 创建城市B的分区表

CREATE TABLE sales_city_b PARTITION OF sales

    FOR VALUES IN (2);



-- 创建城市C的分区表

CREATE TABLE sales_city_c PARTITION OF sales

    FOR VALUES IN (3);

现在，已经完成了城市级别的分区。接下来，可以在每个城市的分区表中再次按年份进行分区：

-- 创建城市A中的年份分区

CREATE TABLE sales_city_a_2021 PARTITION OF sales_city_a

    FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

CREATE TABLE sales_city_a_2022 PARTITION OF sales_city_a

    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');



-- 创建城市B中的年份分区

CREATE TABLE sales_city_b_2021 PARTITION OF sales_city_b

    FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

CREATE TABLE sales_city_b_2022 PARTITION OF sales_city_b

    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');



-- 创建城市C中的年份分区

CREATE TABLE sales_city_c_2021 PARTITION OF sales_city_c

    FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

CREATE TABLE sales_city_c_2022 PARTITION OF sales_city_c

    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

1.5 主键必须包含分区键

在PostgreSQL分区表中，主键必须包含分区键。分区表的目的之一是将大量数据分散到多个分区中，以提高查询性能和管理大型数据集。如果主键不包含分区键，那么主键的唯一性将在整个表范围内进行强制执行，这可能导致在不同分区之间存在重复的主键值，破坏了唯一性要求。包含分区键的主键有助于PostgreSQL优化查询性能。由于每个分区都有自己的主键范围，如果查询涉及分区键，数据库可以更容易地定位到包含所需数据的分区，从而加速查询。如果主键不包含分区键，数据库可能需要在所有分区中进行全表扫描，性能会受到不必要的影响。当我们创建的分区表主键，不包含分区键时，就会报错：

postgres=# CREATE TABLE p_tab (

    c1 serial,

    c2 date,

    c3 varchar(10),

    PRIMARY KEY(c1)

) PARTITION BY RANGE (c2);

ERROR:  unique constraint on partitioned table must include all partitioning columns

DETAIL:  PRIMARY KEY constraint on table "p_tab" lacks column "c2" which is part of the partition key.



postgres=# CREATE TABLE p_tab (

    c1 serial,

    c2 date,

    c3 varchar(10),

    PRIMARY KEY(c1,c2)

) PARTITION BY RANGE (c2);

CREATE TABLE

1.6 分区维护

在分区表中，删除大量不再使用的数据的最简单的方法是删除不再需要的分区：

DROP TABLE sales_1;

还可以从分区表中剔除分区，使其成为独立的表：

ALTER TABLE sales DETACH PARTITION sales_1;

ALTER TABLE sales DETACH PARTITION sales_1 CONCURRENTLY;

随着时间的推移，当现有分区不再满足业务需求时，可以添加新分区：

CREATE TABLE sales_5 PARTITION OF sales_5

    FOR VALUES FROM ('2024-00-01') TO ('2024-04-01');