适用于: Azure Database for PostgreSQL 灵活服务器
本文介绍如何使用 PostgreSQL 分区管理器 (pg_partman
) 扩展优化 Azure Database for PostgreSQL 灵活服务器。
当数据库中的表格规模变得很大时,往往会难以管理它们的清理频率、占用的空间,在如何保持索引的效率方面也会遇到难题。 这种状况会减缓查询速度并影响性能。 针对这些情况,解决方案是对大型表进行分区。
在本文中,你将使用 pg_partman
在 Azure Database for PostgreSQL 灵活服务器中为表创建基于范围的分区。
先决条件
若要启用 pg_partman
扩展,请执行以下步骤:
在 Azure 门户中,在
pg_partman
的服务器参数列表中选择azure.extensions
。CREATE EXTENSION pg_partman;
在
pg_partman_bgw
中包含相关的shared_preload_libraries
扩展。 它提供计划的功能run_maintenance()
。 它负责处理在part_config
中将automatic_maintenance
设置为ON
的分区集。可以在 Azure 门户中使用服务器参数来更改影响后台写入器 (BGW) 进程的以下配置选项:
pg_partman_bgw.dbname
:必需。 此参数应包含一个或多个需要运行run_maintenance()
的数据库。 如果有多个数据库,请使用逗号分隔的列表。 如果未设置任何内容,pg_partman_bgw
不会运行该过程。pg_partman_bgw.interval
:调用run_maintenance()
过程的间隔秒数。 默认值为3600
(1 小时)。 可以根据项目的要求更新此值。pg_partman_bgw.role
:run_maintenance()
过程的运行角色。 默认值为postgres
。 仅支持单个角色名称。pg_partman_bgw.analyze
:与p_analyze
的run_maintenance()
参数的用途相同。 默认情况下它设置为OFF
。pg_partman_bgw.jobmon
:与p_jobmon
的run_maintenance()
参数的用途相同。 默认情况下它设置为ON
。
注意
当标识功能使用序列时,来自父表的数据将获得新的序列值。 直接向子表添加数据时,它不会生成新的序列值。
pg_partman
扩展使用模板来控制表是否为UNLOGGED
。 这意味着ALTER TABLE
命令无法更改分区集的此状态。 通过更改模板上的状态,可以将其应用到所有将来的分区。 但对于现有子表,必须手动使用ALTER TABLE
命令。 这个错误显示出原因。
设置权限
无需超级用户角色即可使用 pg_partman
。 唯一的要求是运行 pg_partman
函数的角色对所有要在其中创建新对象的分区集和架构具有所有权。
建议为 pg_partman
创建一个单独的角色,并赋予其对 pg_partman
将操作的架构和所有对象的所有权:
CREATE ROLE partman_role WITH LOGIN;
CREATE SCHEMA partman;
GRANT ALL ON SCHEMA partman TO partman_role;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role;
GRANT ALL ON SCHEMA <partition_schema> TO partman_role;
GRANT TEMPORARY ON DATABASE <databasename> to partman_role; -- This allows temporary table creation to move data.
创建分区
pg_partman
扩展仅支持范围类型分区,而不支持基于触发器的分区。 以下代码演示 pg_partman
如何帮助对表进行分区:
CREATE SCHEMA partman;
CREATE TABLE partman.partition_test
(a_int INT, b_text TEXT,c_text TEXT,d_date TIMESTAMP DEFAULT now())
PARTITION BY RANGE(d_date);
CREATE INDEX idx_partition_date ON partman.partition_test(d_date);
使用 create_parent
函数,可以在分区表上设置所需的分区数:
SELECT public.create_parent(
p_parent_table := 'partman.partition_test',
p_control := 'd_date',
p_type := 'native',
p_interval := 'daily',
p_premake :=20,
p_start_partition := (now() - interval '10 days')::date::text
);
UPDATE public.part_config
SET infinite_time_partitions = true,
retention = '1 hour',
retention_keep_table=true
WHERE parent_table = 'partman.partition_test';
上述命令使用本机分区,根据 p_parent_table
列将 p_control
划分为较小的部分。 (另一个选项是基于触发器的分区,但 pg_partman
目前不支持它。)分区每天创建一次。
该示例提前创建 20 个未来分区,而不是使用默认值 4
。 它还会指定 p_start_partition
,其中提到了分区应从过去的哪个日期开始。
create_parent()
函数填充两个表:part_config
和 part_config_sub
。 还有一个维护函数 run_maintenance()
。 可以计划一个 cron
作业,以使此过程定期运行。 此函数会检查 part_config
表中的所有父表并为它们创建新分区或运行表集保留策略。 若要详细了解 pg_partman
中的函数和表,请参阅 GitHub 上的 PostgreSQL 分区管理器扩展文档。
若要在每次通过 pg_partman_bgw
扩展在后台运行 run_maintenance()
时创建新分区,请运行以下 UPDATE
语句:
UPDATE partman.part_config SET premake = premake+1 WHERE parent_table = 'partman.partition_test';
如果 premake 相同且 run_maintenance()
过程已运行,则不会为当天创建新分区。 对于第二天,因为基于当前日期定义 premake,所以在执行 run_maintenance()
函数时会为当天创建一个新分区。
使用以下 INSERT INTO
命令,为每个月份插入 100,000 行:
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(1,100000),GENERATE_SERIES(1, 100000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(1, 100000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-03-01',timestamp '2024-03-30', interval '1 day ') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(100000,200000),GENERATE_SERIES(100000,200000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(100000,200000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-04-01',timestamp '2024-04-30', interval '1 day') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(200000,300000),GENERATE_SERIES(200000,300000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(200000,300000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-05-01',timestamp '2024-05-30', interval '1 day') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(300000,400000),GENERATE_SERIES(300000,400000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(300000,400000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-06-01',timestamp '2024-06-30', interval '1 day') ;
INSERT INTO partman.partition_test SELECT GENERATE_SERIES(400000,500000),GENERATE_SERIES(400000,500000) || 'abcdefghijklmnopqrstuvwxyz',
GENERATE_SERIES(400000,500000) || 'zyxwvutsrqponmlkjihgfedcba', GENERATE_SERIES (timestamp '2024-07-01',timestamp '2024-07-30', interval '1 day') ;
在 PostgreSQL 上运行以下命令来查看创建的分区:
\d+ partman.partition_test;
下面是运行的 SELECT
语句的输出:
手动运行维护过程
可以手动运行 partman.run_maintenance()
命令,而不必使用 pg_partman_bgw
。 使用以下命令手动运行维护过程:
SELECT partman.run_maintenance(p_parent_table:='partman.partition_test');
警告
如果在创建分区之前插入数据,则数据将转到默认分区。 如果默认分区包含属于你稍后要创建的新分区的数据,那么你会收到默认分区冲突错误,并且该过程将不会运行。 更改之前建议的 premake 值,然后运行该过程。
计划维护过程
使用 pg_cron
运行维护过程:
首先,在服务器上启用
pg_cron
。 在 Azure 门户中,将pg_cron
添加到azure. extensions
、shared_preload_libraries
和cron.database_name
服务器参数。选择“保存”按钮并等待部署完成。
部署完成后,会自动创建
pg_cron
。 如果你尝试安装它,将会收到以下消息:CREATE EXTENSION pg_cron;
ERROR: extension "pg_cron" already exists
若要计划
cron
作业,请使用以下命令:SELECT cron.schedule_in_database('sample_job','@hourly', $$SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test')$$,'postgres');
若要查看所有
cron
作业,请使用以下命令:SELECT * FROM cron.job;
-[ RECORD 1 ]----------------------------------------------------------------------- jobid | 1 schedule | @hourly command | SELECT partman.run_maintenance(p_parent_table:= 'partman.partition_test') nodename | /tmp nodeport | 5432 database | postgres username | postgres active | t jobname | sample_job
若要检查作业的运行历史记录,请使用以下命令:
SELECT * FROM cron.job_run_details;
结果显示零条记录,因为尚未运行该作业。
若要取消计划
cron
作业,请使用以下命令:SELECT cron.unschedule(1);
常见问题解答
为什么
pg_partman_bgw
不根据我提供的间隔运行维护过程?检查服务器参数
pg_partman_bgw.dbname
,并使用正确的数据库名称对其进行更新。 此外,请检查服务器参数pg_partman_bgw.role
并提供适当的角色。 还应该确保使用同一用户而不是 Postgres 连接到服务器来创建扩展。在
pg_partman_bgw
运行维护过程时出现错误。 这是为什么?请参阅前面的解答。
如何将分区设置为从前一天开始?
p_start_partition
函数是指必须从中创建分区的日期。 运行以下命令:SELECT public.create_parent( p_parent_table := 'partman.partition_test', p_control := 'd_date', p_type := 'native', p_interval := 'daily', p_premake :=20, p_start_partition := (now() - interval '10 days')::date::text );
相关内容
- 扩展功能。