# pg_partman: partition management

Automated partition management

[`pg_partman`](https://github.com/pgpartman/pg_partman) is a Postgres extension that automates the creation and maintenance of partitions for tables using Postgres native partitioning.

## Enable the extension

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

```sql
create schema if not exists partman;
create extension if not exists pg_partman with schema partman;
```

## Create a partitioned table

`pg_partman` requires your parent table to already be declared as a partitioned table.

```sql
create table public.messages (
  id bigint generated by default as identity,
  sent_at timestamptz not null,
  sender_id uuid,
  recipient_id uuid,
  body text,
  primary key (sent_at, id)
)
partition by range (sent_at);
```

## Set up partitioning

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

```sql
select partman.create_parent(
  p_parent_table := 'public.messages',
  p_control := 'sent_at',
  p_type := 'range',
  p_interval := '7 days',
  p_premake := 7,
  p_start_partition := '2025-01-01 00:00:00'
);
```

### Integer-based partitions

```sql
create table public.events (
  id bigint generated by default as identity,
  inserted_at timestamptz not null default now(),
  payload jsonb,
  primary key (id)
)
partition by range (id);

select partman.create_parent(
  p_parent_table := 'public.events',
  p_control := 'id',
  p_type := 'range',
  p_interval := '100000'
);
```

## Running maintenance

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

```sql
call partman.run_maintenance_proc();
```

To automate this, schedule it using `pg_cron`.

```sql
create extension if not exists pg_cron;

select
  cron.schedule('@hourly', $$call partman.run_maintenance_proc()$$);
```

## Resources

- Official [pg_partman documentation](https://github.com/pgpartman/pg_partman/blob/development/doc/pg_partman.md)