Skip to content
Database

pg_partman：分区管理

pg_partman 是一个 Postgres 扩展，用于自动创建和维护使用 Postgres 原生分区的表的分区。

启用扩展 #

🌐 Enable the extension

要启用 pg_partman，为它创建一个专用的 schema，然后在里面启用扩展。

🌐 To enable pg_partman, create a dedicated schema for it and enable the extension there.

1
create schema if not exists partman;
2
create extension if not exists pg_partman with schema partman;

创建一个分区表 #

🌐 Create a partitioned table

pg_partman 需要你的父表已经被声明为分区表。

1
create table public.messages (
2
  id bigint generated by default as identity,
3
  sent_at timestamptz not null,
4
  sender_id uuid,
5
  recipient_id uuid,
6
  body text,
7
  primary key (sent_at, id)
8
)
9
partition by range (sent_at);

设置分区 #

🌐 Set up partitioning

你使用 partman.create_parent() 配置父表。该函数在创建初始分区时会短暂获取一个 ACCESS EXCLUSIVE 锁。

🌐 You configure the parent table using partman.create_parent(). The function takes an ACCESS EXCLUSIVE lock briefly while it creates the initial partitions.

基于时间的分区 #

🌐 Time-based partitions

1
select partman.create_parent(
2
  p_parent_table := 'public.messages',
3
  p_control := 'sent_at',
4
  p_type := 'range',
5
  p_interval := '7 days',
6
  p_premake := 7,
7
  p_start_partition := '2025-01-01 00:00:00'
8
);

基于整数的划分 #

🌐 Integer-based partitions

1
create table public.events (
2
  id bigint generated by default as identity,
3
  inserted_at timestamptz not null default now(),
4
  payload jsonb,
5
  primary key (id)
6
)
7
partition by range (id);
8
9
select partman.create_parent(
10
  p_parent_table := 'public.events',
11
  p_control := 'id',
12
  p_type := 'range',
13
  p_interval := '100000'
14
);

运行维护 #

🌐 Running maintenance

定期调用 pg_partman 维护很重要，这样未来的分区会被预先创建，保留策略也会被应用。

🌐 It’s important to call pg_partman maintenance regularly so future partitions are pre-created and retention policies are applied.

1
call partman.run_maintenance_proc();

要自动化这个，可以使用 pg_cron 来安排。

🌐 To automate this, schedule it using pg_cron.

1
create extension if not exists pg_cron;
2
3
select
4
  cron.schedule('@hourly', $$call partman.run_maintenance_proc()$$);

资源 #

🌐 Resources