Table partitioning

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.

Partitioning strategies

Create a range-partitioned table

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

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:

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:

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:

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

From the app nothing changes:

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.

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.

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:

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), then drop it:

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.

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.
• 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.
• Branches copy partitions like any other table. See Branching.

Related

• Indexes
• Query performance and EXPLAIN
• SQL migrations
• Postgres reference: Table partitioning