# Partitioning an existing table

> Postgres can't turn an existing table into a partitioned one in place. How to create a partitioned copy, move the data, swap names, and keep RLS, the API and Realtime working.

Source: https://supahost.app/kb/partition-existing-table · Updated 2026-11-11

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](/kb/improve-query-performance).
- **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

```sql
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:

```sql
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:

```sql
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:

```sql
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](/kb/read-only-mode)), copy any rows written since step 3, then:

```sql
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:

```sql
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`:

```sql
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](/docs/database/partitioning)
- [Scheduled jobs with pg_cron](/docs/cron-queues/cron)
- [Postgres Changes](/docs/realtime/postgres-changes)
- [ALTER TABLE takes forever on a large table](/kb/slow-alter-table)