mirror of
https://github.com/supabase/supabase.git
synced 2026-10-06 18:05:11 +03:00
Chris Gwilliamsandgithub-actions[bot]
81415c6053
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? Blog post on using pg_partman instead of TimescaleDB to prepare for the upcoming deprecation ## What is the current behavior? ## What is the new behavior? Blog post to include migration information for those using Timescale ## Additional context Not to be merged until pg_partman is released in 15 and 17 images <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Documentation** * Added a comprehensive pg_partman guide covering setup, time- and integer-based partitioning, maintenance, automation, and resources. * Added a migration guide for moving from TimescaleDB hypertables to native PostgreSQL partitioning using pg_partman. * Updated TimescaleDB docs with migration notes and support guidance. * **New Features** * Listed pg_partman in the public extensions reference and added navigation entries linking to the pg_partman guide and migration guide. <sub>✏️ Tip: You can customize this high-level summary in your review settings.</sub> <!-- end of auto-generated comment: release notes by coderabbit.ai --> --------- Co-authored-by: github-actions[bot] <41898282+github-actions[bot]@users.noreply.github.com>
96 lines
2.3 KiB
Plaintext
96 lines
2.3 KiB
Plaintext
---
|
||
id: 'pg_partman'
|
||
title: 'pg_partman: partition management'
|
||
description: '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.
|
||
|
||
{/* prettier-ignore */}
|
||
```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.
|
||
|
||
{/* prettier-ignore */}
|
||
```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
|
||
|
||
{/* prettier-ignore */}
|
||
```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
|
||
|
||
{/* prettier-ignore */}
|
||
```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.
|
||
|
||
{/* prettier-ignore */}
|
||
```sql
|
||
call partman.run_maintenance_proc();
|
||
```
|
||
|
||
To automate this, schedule it using `pg_cron`.
|
||
|
||
{/* prettier-ignore */}
|
||
```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)
|