使用pg_partman实现PostgreSQL的时间分区管理

DBA巫师 2024-05-22

812

加入我们的微信群，你将获得不仅仅是知识，还能享受免费最新GPT-4o模型微信机器人+Oracle MOS免费查询+职业发展规划咨询+数据库大佬交流，很多志同道合的小伙伴，欢迎加群一起探讨、学习、进步！

“选择”比“努力”更重要。

扫描下方二维码添加作者微信，回复“DBA理想”即可开启你的数据库学习之旅。

引言

当我们需要管理大量的数据表，或者在构建自动化的数据归档系统时，PostgreSQL的时间分区功能无疑是一种极其强大的工具。这种功能可以帮助我们更好地组织和管理数据，提高数据处理和查询的效率。在这篇文章中，我将详细整理并介绍一下如何使用pg_partman这个工具进行数据分析。我会从基本的使用方法开始，然后逐渐深入到更高级的选项和技巧。无论你是初次接触时间分区，还是已经有一定的使用经验，我都希望你能从这篇文章中找到有用的信息和建议。

时间分区间隔

pg_partman 是一个在处理时间分区时非常有用的工具，但它的一个限制是它只支持用户选择预设的间隔。这可能在某些情况下限制了其使用的灵活性。最新版本的 pg_partman 支持 PostgreSQL 所有的时间间隔值，这显著增加了它的通用性和应用范围。当创建父表时，用户可以通过 p_interval
字段来设置分区间隔，这提供了更高的自定义能力和灵活性。

SELECT partman.create_parent(
    p_parent_table := 'partman_test.time_taptest_table',
    p_control := 'col3',
    p_interval := '1 day',
    p_template_table := 'partman_test.time_taptest_table_template'
);

其他示例包括：

p_interval := '1 month'
p_interval := '1 year'

尽管这些间隔设置已经足够灵活，但一些常见的业务间隔（如每周和每季度）仍然需要特殊处理。幸运的是，pg_partman 提供了使这些间隔设置变得简单的方法。让我们深入了解这些示例。

每周分区

每周分区是一个非常常见的分区间隔。在最初，我发现ISO周日期标准可以解决处理周数的问题，比如闰年、起始日、53周年等。然而，在pg_partman 5版本中，声明式分区使得这一过程变得更加灵活。通过新的方法，用户可以选择任意一天作为一周的起始日。但这种灵活性也带来了一些复杂性。

当您在pg_partman中设定分区间隔为1周时，周的起始日将是您运行create_parent()
那一天。例如，如果今天是星期三，那么分区命名模式和子表约束将如下所示：

CREATE TABLE time_stuff(id int GENERATED ALWAYS AS IDENTITY, created_at timestamptz NOT NULL) PARTITION BY RANGE (created_at);


SELECT partman.create_parent('public.time_stuff', 'created_at', '1 week');

这样生成的分区表如下：

Partitions: time_stuff_p20240417 FOR VALUES FROM ('2024-04-17 00:00:00-04') TO ('2024-04-24 00:00:00-04'),
            time_stuff_p20240424 FOR VALUES FROM ('2024-04-24 00:00:00-04') TO ('2024-05-01 00:00:00-04'),
            ...

在2024年4月24日（星期三）运行这些语句，因此分区时间从当天开始，每个子表的下界每隔7天变化一次。因此实现了每周分区的目标，但这不是常见的起始日。幸运的是，pg_partman 提供了一个简单的解决方案：指定分区的起始日期。例如，如果我们希望每周从星期天开始，只需选择一个星期天的日期作为初始创建的子表的起始日期：

SELECT partman.create_parent('public.time_stuff', 'created_at', '1 week', p_start_partition => '2024-04-14');

这样生成的分区表如下：

Partitions: time_stuff_p20240414 FOR VALUES FROM ('2024-04-14 00:00:00-04') TO ('2024-04-21 00:00:00-04'),
            time_stuff_p20240421 FOR VALUES FROM ('2024-04-21 00:00:00-04') TO ('2024-04-28 00:00:00-04'),
            ...

每季度分区

每季度分区是一个在大小分区间隔之间的良好平衡。虽然 PostgreSQL 对季度时间戳格式有一些有限的支持，但以前版本的 partman 源代码非常复杂，并将季度固定在4个预定义的月份块中。在 pg_partman 5.x 版本中，我们可以允许任何任意的 3 个月间隔。尽管丧失了之前较好的季度后缀模式（YYYYq#，如 2024q2），但灵活性大大增加。

例如，在2024年4月运行分区创建语句：

SELECT partman.create_parent('public.time_stuff', 'created_at', '3 months');

生成的分区表如下：

Partitions: time_stuff_p20240401 FOR VALUES FROM ('2024-04-01 00:00:00-04') TO ('2024-07-01 00:00:00-04'),
            time_stuff_p20240701 FOR VALUES FROM ('2024-07-01 00:00:00-04') TO ('2024-10-01 00:00:00-04'),
            ...

为了将季度分区设置在任意月份，我们可以通过指定分区的起始日期来实现：

SELECT partman.create_parent('public.time_stuff', 'created_at', '3 months', p_start_partition => '2024-03-15');

生成的分区表如下：

Partitions: time_stuff_p20240301 FOR VALUES FROM ('2024-03-01 00:00:00-05') TO ('2024-06-01 00:00:00-04'),
            time_stuff_p20240601 FOR VALUES FROM ('2024-06-01 00:00:00-04') TO ('2024-09-01 00:00:00-04'),
            ...

任意间隔

对于任意自定义间隔，pg_partman 在 5.0 版本之前存在一些问题。例如，如果我们想每 9 周分区一次，并希望从 2024 年 4 月 22 日星期一开始：

SELECT partman.create_parent('public.time_stuff', 'created_at', '9 weeks', p_start_partition => '2024-04-22');

生成的分区表可能如下：

Partitions: time_stuff_p20240401 FOR VALUES FROM ('2024-04-01 00:00:00-04') TO ('2024-06-03 00:00:00-04'),
            ...

这是由于 partman 总是尝试将间隔四舍五入到每月的第一天。在 4.6.0 版本中，通过 p_date_trunc_interval
参数解决了这一问题：

SELECT partman.create_parent('public.time_stuff', 'created_at', '9 weeks', p_start_partition => '2024-04-22', p_date_trunc_interval => 'week');

生成的分区表如下：

Partitions: time_stuff_p20240422 FOR VALUES FROM ('2024-04-22 00:00:00-04') TO ('2024-06-24 00:00:00-04'),
            time_stuff_p20240624 FOR VALUES FROM ('2024-06-24 00:00:00-04') TO ('2024-08-26 00:00:00-04'),
            ...

该参数接受 PostgreSQL 内置函数 date_trunc
的有效值，用于指定分区的下界。

结论

pg_partman 提供了对时间分区的全面支持，满足各种需求。希望本文能帮助您了解其基本和高级功能，并解决一些更复杂的分区需求。后面会有下载地址：https://github.com/pgpartman/pg_partman

postgresql

文章转载自DBA巫师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

使用pg_partman实现PostgreSQL的时间分区管理

评论