Partitioning an existing table

Postgres has no alter table ... partition by. To partition a table that already has data, you build a new partitioned table next to it, copy the rows across, and swap the names. This walk-through uses an events table from a bookings app, split by month.

Before you start

• Is partitioning the fix? It helps when you regularly drop old data in bulk, or when almost every query filters on one column such as a date. For slow queries in general, an index is usually the answer; see Speeding up slow queries with indexes.
• The partition key must be in every unique constraint, including the primary key. A primary key on id alone has to become (id, created_at).
• You need room for a second copy of the table while both exist.

1. Create the partitioned table

create table public.events_new (
  id bigint generated by default as identity,
  booking_id bigint not null,
  kind text not null,
  payload jsonb,
  created_at timestamptz not null default now(),
  primary key (id, created_at)
) partition by range (created_at);

create index on public.events_new (booking_id);

Indexes created on the parent are created on every partition automatically.

2. Create partitions

One per month covering your existing data, the next few months, and a default partition to catch anything outside them:

create table public.events_2026_10 partition of public.events_new
  for values from ('2026-10-01') to ('2026-11-01');
create table public.events_2026_11 partition of public.events_new
  for values from ('2026-11-01') to ('2026-12-01');
create table public.events_2026_12 partition of public.events_new
  for values from ('2026-12-01') to ('2027-01-01');
create table public.events_default partition of public.events_new default;

3. Copy the data

For tables up to a few million rows, one statement is fine. For bigger tables, copy in date ranges so each step is short:

insert into public.events_new (id, booking_id, kind, payload, created_at)
overriding system value
select id, booking_id, kind, payload, created_at
from public.events
where created_at < '2026-11-01';

Then move the identity sequence past the copied ids:

select setval(pg_get_serial_sequence('public.events_new', 'id'),
              (select max(id) from public.events_new));

4. Swap names in one transaction

Stop writes first (see Freezing writes during a migration), copy any rows written since step 3, then:

begin;
alter table public.events rename to events_old;
alter table public.events_new rename to events;

alter table public.events enable row level security;
create policy "Users read events for their bookings"
on public.events for select
to authenticated
using ( booking_id in (select id from public.bookings where user_id = (select auth.uid())) );
commit;

Recreate every policy, grant, trigger and foreign key the old table had; none of them move with the data. Compare with select * from pg_policies where tablename = 'events_old';. Keep events_old until you're sure, then drop it.

5. Keep partitions coming

Create next month's partition before it's needed. A monthly pg_cron job can do it:

select cron.schedule('events-next-partition', '0 2 25 * *', $$
  do $do$
  declare
    m date := date_trunc('month', now() + interval '1 month');
  begin
    execute format(
      'create table if not exists public.%I partition of public.events for values from (%L) to (%L)',
      'events_' || to_char(m, 'YYYY_MM'), m, m + interval '1 month');
  end $do$;
$$);

Dropping a whole month later is instant: drop table public.events_2026_10;.

API and Realtime

• The Data API sees events as one table; partitions are an internal detail. Policies on the parent apply to queries through the parent.
• For Postgres Changes, add the parent to the publication with publish_via_partition_root so events arrive under the name events:

alter publication supabase_realtime set (publish_via_partition_root = true);
alter publication supabase_realtime add table public.events;

The first command changes the setting for every table in the publication.

On SupaHost

• Database size counts toward your plan's database quota while both copies exist. Disks grow automatically as they fill.
• Cron schedules are UTC. Jobs also appear in the dashboard's Cron tab.

Related

• Table partitioning
• Scheduled jobs with pg_cron
• Postgres Changes
• ALTER TABLE takes forever on a large table