DevOps知识中心
DevOps知识中心

理解和实现 PostgreSQL 14+ 中的声明式表分区

探索 PostgreSQL 14+ 版本中的原生声明式分区功能。本指南详细介绍了范围（range）、列表（list）和哈希（hash）这三种分区类型，并提供了创建和管理分区表的实用 SQL 示例。了解如何通过利用分区剪枝和高效的维护策略，优化查询性能，并简化超大型数据集的数据管理。

39 浏览量

PostgreSQL 14+ 中声明式表分区的理解与实现

PostgreSQL 长期以来一直是一个强大且多功能的​​关系数据库，但随着数据集的增长，管理和查询庞大的表可能会成为一项重大挑战。性能下降，维护任务变得繁琐，整体系统效率也会受到影响。PostgreSQL 10 引入了声明式分区作为解决这些问题的原生方案，其功能在后续版本，尤其是 PostgreSQL 14 及更高版本中得到了持续成熟。

声明式分区允许您将大型表分解成更小、更易于管理的块，称为分区。这种策略不仅通过允许数据库仅扫描相关的分区来提高查询性能，还简化了数据归档、删除和索引管理等维护操作。本文将指导您了解 PostgreSQL 中声明式分区的核心概念，探索其不同类型，并提供有关如何实现它的实用示例，以优化您的数据库。

什么是声明式表分区？

声明式分区是一项数据库功能，它允许您根据预定义的一组规则，将单个逻辑表（父表或分区表）拆分成多个物理表（子表或分区表）。每个分区包含父表中数据的一个子集。分区键决定了一行数据属于哪个分区。

声明式分区的关键优势包括：

  • 提高查询性能：对分区键进行筛选的查询速度会显著加快，因为 PostgreSQL 可以修剪（消除）不包含相关数据的分区，这一过程称为分区修剪。
  • 更轻松的数据管理：通过分离或删除单个分区来执行删除旧数据或归档等操作，比在单个大表上执行大规模 DELETE 操作要高效得多。
  • 简化的维护：索引和 vacuum 操作可以按分区进行管理，减少对整个表的影响。
  • 增强的可用性：对单个分区的维护通常可以以对整个表的干扰最小的方式完成。

声明式分区的类型

PostgreSQL 支持几种声明式分区方法，每种方法都适用于不同的数据分布模式：

1. 范围分区 (Range Partitioning)

范围分区根据特定列（例如，日期、数字）中的连续值范围来划分数据。

用例：非常适合时间序列数据，如日志、事件数据或销售记录，在这种情况下，您经常需要查询特定日期或数字范围内的数据。

示例：按 sale_date 列对 sales 表进行分区。

创建范围分区表

首先，创建父表，指定分区方法和键：

CREATE TABLE sales (
    sale_id SERIAL,
    product_name VARCHAR(100),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

接下来，创建各个分区。每个分区都使用 FOR VALUES 子句定义，指定它将包含的范围。

-- 2023 年 1 月销售额分区
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-01-31');

-- 2023 年 2 月销售额分区
CREATE TABLE sales_2023_02
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-02-28');

-- 2023 年 3 月销售额分区
CREATE TABLE sales_2023_03
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-03-31');

提示：定义范围时，请确保它们是连续的并涵盖所有可能的值。避免范围重叠。TO 值是不包含在内的（独占的）。

2. 列表分区 (List Partitioning)

列表分区根据列中一组离散的值来划分数据。

用例：适用于具有固定、已知值集的列，例如地理区域、状态代码或产品类别。

示例：按 region 列对 orders 表进行分区。

创建列表分区表

使用 PARTITION BY LIST 定义父表：

CREATE TABLE orders (
    order_id SERIAL,
    customer_name VARCHAR(100),
    order_total NUMERIC(10, 2),
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

为特定区域创建分区：

-- 'North America' 区域的订单分区
CREATE TABLE orders_north_america
    PARTITION OF orders ()
    FOR VALUES IN ('North America');

-- 'Europe' 区域的订单分区
CREATE TABLE orders_europe
    PARTITION OF orders ()
    FOR VALUES IN ('Europe');

-- 'Asia' 区域的订单分区
CREATE TABLE orders_asia
    PARTITION OF orders ()
    FOR VALUES IN ('Asia');

重要提示：如果您插入的 region 值与任何现有分区的 IN 列表都不匹配，并且没有 DEFAULT 分区，则插入将失败。您可以创建一个 DEFAULT 分区来捕获所有其他值。

创建默认分区

-- 捕获所有未明确列出的区域的默认分区
CREATE TABLE orders_other
    PARTITION OF orders ()
    DEFAULT;

3. 哈希分区 (Hash Partitioning)

哈希分区根据分区键的哈希值将数据分布到多个分区中。

用例：当您有大量数据，并且希望在没有明确的基于范围或列表的分布的情况下将其均匀分布到各个分区时非常有用。它有利于负载均衡。

示例：按 user_id 对 users 表进行分区。

创建哈希分区表

使用 PARTITION BY HASH 定义父表并指定分区数量：

CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (user_id);

如果您没有指定分区，PostgreSQL 会自动为您创建分区，但通常建议明确创建它们，特别是当您想控制分区的数量和命名时。

创建显式哈希分区

-- 创建 4 个哈希分区
CREATE TABLE users_p0
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE users_p1
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE users_p2
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE users_p3
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

注意：使用哈希分区时，您需要指定 modulus（分区总数）和 remainder（此分区的值）。

实现声明式分区：最佳实践

  • 选择正确的分区键：分区键应与您最常查询的筛选条件和数据管理操作保持一致。良好的键能显著提高性能。
  • 考虑分区数量：分区太少可能无法提供足够的好处，而分区太多可能会增加开销。目标是实现管理性和性能之间的平衡。对于范围分区，请考虑您的数据增长率和保留策略。
  • 使用 pg_partman 实现自动化：对于范围分区，特别是时间序列数据，请考虑使用 pg_partman 等扩展。它可以自动创建新分区以及归档/删除旧分区，从而大大减少手动工作。
  • 策略性索引：子表上的索引是独立的。您可以根据需要为单个分区创建索引。考虑在分区键上创建索引以实现有效的修剪。
  • 分区修剪：确保您的查询通过在 WHERE 子句中包含分区键来利用分区修剪。EXPLAIN 命令可以显示是否正在发生修剪。
  • DEFAULT 分区：对于列表分区，DEFAULT 分区对于避免在意外出现新值时插入错误至关重要。
  • 数据类型：确保分区键的数据类型在父表和子表之间是适当且一致的。

管理分区

分区的附加和分离 (Attaching and Detaching)

虽然分区是通过 CREATE TABLE ... PARTITION OF ... 直接创建的，但您也可以将现有表分离和附加为分区。这对于迁移数据或管理大型数据集很有用。

分离分区：要分离分区，您首先需要将其变成一个常规表，然后将其与父表分离。在较新的 PostgreSQL 版本中，您可以直接分离。

-- 分离 sales_2023_01 分区
ALTER TABLE sales DETACH PARTITION sales_2023_01;

将表附加为分区：您可以将一个常规表（符合父表模式）附加为新分区。

-- 假设 'old_sales_data' 是一个具有与 'sales' 相同模式的常规表
CREATE TABLE sales_2022_12
    PARTITION OF sales ()
    FOR VALUES FROM ('2022-12-01') TO ('2022-12-31');

-- 将现有表附加到新的分区槽位
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2022-12-31');

-- 如果您有一个预先创建的表，您需要先将其设为分区:
-- CREATE TABLE sales_2022_12 (LIKE sales INCLUDING ALL);
-- ... populate sales_2022_12 ...
-- ALTER TABLE sales ATTACH PARTITION sales_2022_12 FOR VALUES FROM ('2022-12-01') TO ('2022-12-31');

删除分区

删除分区是一个快速操作，因为它只删除分区表，而不删除其中的数据（除非明确指定）。这比 DELETE 快得多。

-- 要删除分区，只需删除子表即可
DROP TABLE sales_2023_01;

示例：使用分区修剪提高查询性能

考虑前面显示的按 sale_date 分区的 sales 表。

没有分区修剪的查询（假设在一个未分区表上）：

SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

如果 sales 是一个庞大、未分区的表，此查询将扫描整个表。然而，使用声明式分区后：

-- 此查询将仅扫描 sales_2023_01 分区
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

PostgreSQL 的查询规划器识别出 sale_date 是分区键，并且指定的范围完全落入 sales_2023_01 分区内。因此，它将只扫描该分区，从而大幅减少 I/O 并提高性能。

要验证这一点，请使用 EXPLAIN：

EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

输出将显示一个 PartitionPrune 步骤，表明不相关的分区已被排除。

结论

PostgreSQL 14+ 中的声明式分区是管理和优化大型数据集的一项强大功能。通过根据范围、列表或哈希策略智能地划分表，您可以在查询性能、数据管理效率和整体数据库可维护性方面实现显著改进。了解可用的分区类型并在实现过程中应用最佳实践，是释放此功能对您的应用程序的全部潜力的关键。