使用 pg_partman 扩展管理 PostgreSQL 分区 - Amazon Relational Database Service
PostgreSQL pg_partman 扩展概述启用 pg_partman 扩展使用 create_parent 函数配置分区使用 run_maintenance_proc 函数配置分区维护

使用 pg_partman 扩展管理 PostgreSQL 分区

PostgreSQL 表分区为数据输入和报告的高性能处理提供了框架。对于需要非常快速地输入大量数据的数据库，请使用分区。分区还可以更快地查询大型表。分区有助于在不影响数据库实例的情况下维护数据，因为它需要的 I/O 资源较少。

通过使用分区，您可以将数据拆分为自定义大小的块进行处理。例如，您可以将时间序列数据分区为范围，例如每小时、每日、每周、每月、每季度、每年、自定义或这些范围的任意组合。对于时间序列数据示例，如果您按小时对表进行分区，则每个分区会包含一小时的数据。如果您按天对时间序列表进行分区，则分区会保存一天的数据，以此类推。分区键控制分区的大小。

在分区表上使用 INSERT 或 UPDATE SQL 命令时，数据库引擎会将数据路由到相应的分区。存储数据的 PostgreSQL 表分区是主表的子表。

在数据库查询读取期间，PostgreSQL 优化程序会检查查询的 WHERE 子句，如果可能的话，将数据库扫描定向到仅相关分区。

从版本 10 开始，PostgreSQL 使用声明性分区来实现表分区，这也称为本机 PostgreSQL 分区。在 PostgreSQL 版本 10 之前，使用触发器来实现分区。

PostgreSQL 表分区提供了以下功能：

  • 随时创建新分区。

  • 可变分区范围。

  • 使用数据定义语言 (DDL) 语句可分离和可重新连接的分区。

    例如，可分离的分区对于从主分区中删除历史数据但保留历史数据以供分析很有用。

  • 新分区继承了父数据库表的属性，包括以下属性：

    • 索引

    • 主键，其必须包括分区键列

    • 外键

    • 检查约束

    • 参考

  • 为完整表或每个特定分区创建索引。

您不能更改单个分区的架构。但是，您可以更改传播到分区的父表（例如添加新列）。

PostgreSQL pg_partman 扩展概述

您可以使用 PostgreSQL pg_partman 扩展自动创建和维护表分区。有关更多一般信息，请参阅 pg_partman 文档中的 PG 分区管理器。

注意

RDS for PostgreSQL 版本 12.5 及更高版本支持该 pg_partman 扩展。

您可以使用以下设置来配置 pg_partman，而不必手动创建每个分区：

  • 要分区的表

  • 分区类型

  • 分区键

  • 分区粒度

  • 分区预创建和管理选项

创建 PostgreSQL 分区表后，您可以通过调用 create_parent 函数向 pg_partman 注册该表。此举会根据传递给函数的参数创建必要的分区。

该 pg_partman 扩展还提供了 run_maintenance_proc 函数，您可以按计划调用该函数来自动管理分区。为确保根据需要创建正确的分区，请将此函数计划为定期运行（例如每小时）。您还可以确保自动删除分区。

启用 pg_partman 扩展

如果要管理分区的同一 PostgreSQL 数据库实例中有多个数据库，请为每个数据库分别启用 pg_partman 扩展。要为特定数据库启用 pg_partman 扩展，请创建分区维护架构，然后按如下所示创建 pg_partman 扩展。

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
注意

要创建 pg_partman 扩展，请确保您具有 rds_superuser 权限。

如果您收到以下错误，请向该账户授予 rds_superuser 权限或使用您的超级用户帐户。

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

要授予 rds_superuser 权限，请连接您的超级用户账户并运行以下命令。

GRANT rds_superuser TO user-or-role;

对于显示使用 pg_partman 扩展的示例，我们使用以下示例数据库表和分区。此数据库使用基于时间戳的分区表。架构 data_mart 包含名为 events 的表，当中包含名为 created_at 的列。events 表中包含以下设置：

  • 主键 event_id 和 created_at，其必须具有用于指导分区的列。

  • 用于强制 ck_valid_operation 表列值的检查约束 operation。

  • 两个外键，其中一个 (fk_orga_membership)) 指向外部表 organization，另一个 (fk_parent_event_id) 是自引用的外键。

  • 两个索引，其中一个 (idx_org_id) 用于外键，另一个 (idx_event_type) 用于事件类型。

以下 DDL 语句创建这些对象，这些对象自动包含在每个分区中。

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

使用 create_parent 函数配置分区

启用 pg_partman 扩展后，使用 create_parent 函数在分区维护架构中配置分区。以下示例使用在 events 中创建的 启用 pg_partman 扩展 表示例。按如下方式调用 create_parent 函数。

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

参数如下所示：

  • p_parent_table – 父分区表。此表必须已存在并完全限定（包括架构在内）。

  • p_control – 分区所依据的列。数据类型必须是整数或基于时间的。

  • p_type – 该类型是 'native' 或者 'partman'。为了提高性能和灵活性，您通常应该使用 native 类型。partman 类型依赖于继承。

  • p_interval – 每个分区的时间间隔或整数范围。示例值包括 daily、每小时等。

  • p_premake – 为支持新插入而提前创建的分区数量。

有关 create_parent 函数的完整说明，请参阅 pg_partman 文档中的创建函数。

使用 run_maintenance_proc 函数配置分区维护

您可以运行分区维护操作来自动创建新分区、分离分区或删除旧分区。分区维护依赖于 pg_partman 扩展和 pg_cron 扩展的 run_maintenance_proc 函数，其将启动内部调度程序。调度程序 pg_cron 自动执行数据库中定义的 SQL 语句、函数和程序。

以下示例使用在 events 中创建的 启用 pg_partman 扩展 表示例将分区维护操作设置为自动运行。作为先决条件，请将 pg_cron 添加到数据库实例的参数组中的 shared_preload_libraries 参数。

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

以下是前述示例的分步说明：

  1. 修改与数据库实例关联的参数组并将 pg_cron 添加到 shared_preload_libraries 参数值中。此更改需要重启数据库实例才能生效。有关更多信息，请参阅“在 Amazon RDS 中修改数据库参数组中的参数”。

  2. CREATE EXTENSION pg_cron; 使用具有 rds_superuser 权限的账户运行此命令。这将启用 pg_cron 扩展。有关更多信息，请参阅“使用 PostgreSQL pg_cron 扩展计划维护”。

  3. 运行命令 UPDATE partman.part_config 以调整 data_mart.events 表的 pg_partman 设置。

  4. 运行命令 SET… 配置 data_mart.events 表，其中包含以下子句：

    1. infinite_time_partitions = true, – 将表配置为能够在没有任何限制的情况下自动创建新分区。

    2. retention = '3 months', – 将表配置为最长保留三个月。

    3. retention_keep_table=true – 对表进行配置，以便在保留期到期时表不会自动删除。相反，早于保留期的分区只能从父表中分离。

  5. 运行命令 SELECT cron.schedule… 创建一个 pg_cron 函数调用。此调用定义了计划程序运行 pg_partman 维护程序的频率，partman.run_maintenance_proc。对于此示例，该程序每小时运行一次。

有关 run_maintenance_proc 函数的完整说明，请参阅 pg_partman 文档中的维护函数。