# Table partitioning

> Split large time-series or tenant tables into partitions in a SupaHost project with Postgres declarative partitioning, keep RLS safe, create partitions with pg_cron and drop old data cheaply.

Source: https://supahost.app/docs/database/partitioning · Updated 2026-11-11

A partitioned table looks like one table to your app but is stored as several smaller tables, called partitions, each holding a slice of the rows: one month of events, one region, one group of tenants. Queries that filter on the partition key only read the partitions they need, and removing a month of old data becomes a quick `drop` instead of a slow `delete`.

Partitioning adds moving parts, so use it when a table is genuinely large (tens of millions of rows or many gigabytes) and you query or expire it by one column, usually time. For smaller tables, a good index does the job. See [Indexes](/docs/database/indexes).

## Partitioning strategies

| Strategy | Rows go to a partition by | Typical use |
|---|---|---|
| Range | A range of values, such as one month of `created_at` | Logs, events, messages, metrics |
| List | Explicit values, such as `region in ('eu', 'uk')` | Data split by region or category |
| Hash | A hash of the key, spread evenly over N partitions | Spreading a large tenant table evenly |

## Create a range-partitioned table

A team chat app keeps every message, and most reads are for recent ones. Partition by month:

```sql
create table public.messages (
  id bigint generated always as identity,
  channel_id bigint not null,
  user_id uuid not null default auth.uid(),
  body text not null,
  created_at timestamptz not null default now(),
  primary key (id, created_at)
) partition by range (created_at);
```

The primary key must include the partition key (`created_at`), because Postgres can only enforce uniqueness within each partition. The same applies to any unique constraint.

## Keep partitions out of the Data API

Each partition is a real table. If you create partitions in `public`, the Data API exposes them as their own endpoints, and RLS policies on the parent table **don't apply** when someone queries a partition directly. Put partitions in a schema the Data API doesn't serve:

```sql
create schema if not exists partitions;
revoke all on schema partitions from anon, authenticated;

create table partitions.messages_2026_11 partition of public.messages
  for values from ('2026-11-01') to ('2026-12-01');

create table partitions.messages_2026_12 partition of public.messages
  for values from ('2026-12-01') to ('2027-01-01');

create table partitions.messages_default partition of public.messages default;
```

The default partition catches rows that don't fit any range, so an insert never fails because next month's partition is missing. Keep it empty in normal operation; if rows pile up there, a partition is missing.

The Data API only serves `public`, `storage`, `graphql_public` and `pgmq_public` on SupaHost, and that list can't be changed, so a `partitions` schema stays private.

## Add RLS and indexes on the parent

Enable RLS and create policies on the parent table. Queries through `public.messages` apply them to every partition:

```sql
alter table public.messages enable row level security;

create policy "Members read their channels"
  on public.messages for select
  to authenticated
  using (
    exists (
      select 1 from public.channel_members m
      where m.channel_id = messages.channel_id
        and m.user_id = (select auth.uid())
    )
  );

create policy "Members post as themselves"
  on public.messages for insert
  to authenticated
  with check ((select auth.uid()) = user_id);
```

An index created on the parent is created on every existing and future partition automatically:

```sql
create index messages_channel_created_idx on public.messages (channel_id, created_at desc);
```

From the app nothing changes:

```ts
const { data } = await supabase
  .from('messages')
  .select('id, body, created_at')
  .eq('channel_id', 42)
  .gte('created_at', new Date(Date.now() - 7 * 864e5).toISOString())
  .order('created_at', { ascending: false })
  .limit(50)
```

The `created_at` filter lets Postgres skip partitions outside the last week (partition pruning). Check it with `explain`: only the relevant partitions should appear in the plan.

## Create partitions ahead of time with pg_cron

Rather than remembering to add a partition each month, schedule a job. pg_cron is built into every SupaHost project; don't run `create extension pg_cron`.

```sql
create or replace function public.create_next_message_partition()
returns void
language plpgsql
set search_path = ''
as $$
declare
  start_month date := date_trunc('month', now() + interval '1 month');
  end_month   date := start_month + interval '1 month';
  part_name   text := 'messages_' || to_char(start_month, 'YYYY_MM');
begin
  execute format(
    'create table if not exists partitions.%I partition of public.messages
       for values from (%L) to (%L)',
    part_name, start_month, end_month
  );
end;
$$;

revoke execute on function public.create_next_message_partition() from public, anon, authenticated;

select cron.schedule('create-message-partition', '0 3 20 * *',
  $$select public.create_next_message_partition()$$);
```

This runs at 03:00 UTC on the 20th of each month and creates next month's partition. Jobs show up in Dashboard → your project → **Cron** tab, where you can check recent runs. See [Scheduled jobs with pg_cron](/docs/cron-queues/cron).

## Remove old data

Detach a partition to take it out of the table without deleting anything. Detaching is quick but briefly locks the parent table, so set a lock timeout to give up rather than queue behind busy queries:

```sql
set lock_timeout = '5s';
alter table public.messages detach partition partitions.messages_2025_01;
```

Postgres also has `detach partition ... concurrently`, which avoids that lock, but it isn't allowed while the table has a default partition, and it can't run inside a transaction.

The detached table still holds its rows. Export or archive it first if you may need it, for example with a `pg_dump` from the **Backups** tab ([Exporting your data](/docs/platform/exports)), then drop it:

```sql
drop table partitions.messages_2025_01;
```

Dropping a partition frees its disk space straight away, unlike `delete`, which leaves dead rows for vacuum to clean up. See [Database size not shrinking after deleting data](/kb/disk-not-shrinking).

## Things to know

- **Realtime Postgres Changes**: test subscriptions on partitioned tables before relying on them; replication of partitioned tables has extra settings and isn't covered here. For chat-style apps, Broadcast is often a better fit: see [Broadcast](/docs/realtime/broadcast).
- **Foreign keys pointing at a partitioned table** must reference the full primary key, including the partition key.
- **Converting an existing table** needs a new partitioned table and a data copy. See [Partitioning an existing table](/kb/partition-existing-table).
- **Branches** copy partitions like any other table. See [Branching](/docs/platform/branching).

## Related

- [Indexes](/docs/database/indexes)
- [Query performance and EXPLAIN](/docs/database/query-performance)
- [SQL migrations](/docs/database/migrations)
- Postgres reference: [Table partitioning](https://www.postgresql.org/docs/17/ddl-partitioning.html)