Migrate from TimescaleDB to pg_partman

Starting from Postgres 17, Supabase projects do not have the timescaledb extension available. If your project relies on TimescaleDB hypertables, you will need to migrate to standard Postgres tables before upgrading.

This guide shows one approach to migrate a hypertable to a native Postgres partitioned table and optionally configure pg_partman to automate ongoing partition maintenance. The approach outlined in this guide can also be used for traditional partitioned tables.

Before you begin

Test the migration path in a staging environment (for example by creating a copy of your production project or using branching).
Review your application for TimescaleDB-specific SQL usage (for example time_bucket(), compression policies). Those features are not provided by pg_partman.

Migration overview

Create a new partitioned table.
Copy data from the hypertable to the new table.
Swap over and drop the hypertable.
Configure pg_partman (optional) and schedule maintenance.

Example: Migrate `messages` from hypertable to native partitions

This example assumes a messages hypertable partitioned by sent_at.

1. Rename the existing hypertable

This keeps the original data in place while you create a new partitioned table with the original name.

sql

alter table public.messages rename to ht_messages;

2. Create a new partitioned table

When using native partitioning, the partitioning column must be included in any unique index (including the primary key).

sql

create table public.messages (
  like public.ht_messages including all,
  primary key (sent_at, id)
)
partition by range (sent_at);

3. Copy data into the new table

For large tables, consider copying in batches (for example by time range) during a maintenance window.

sql

insert into public.messages
select *
from public.ht_messages;

4. Drop the old hypertable (and TimescaleDB)

Only drop the extension once you’ve migrated all hypertables and no other objects depend on it.

sql

drop table public.ht_messages;

drop extension if exists timescaledb;

5. Configure `pg_partman` (optional)

Enable pg_partman and register your table so partitions are created ahead of time.

sql

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

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'
);

Keep partitions up to date

pg_partman requires running maintenance to pre-make partitions and apply retention policies.

sql

call partman.run_maintenance_proc();

To automate this, schedule it with pg_cron.

sql

create extension if not exists pg_cron;

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