Docs
Sign in
On this page

Partitioning large tables

Split huge tables (events, logs, metrics) into partitions by time or ID with native Postgres partitioning and pg_partman, maintained automatically by a cron job.

Partitioning stores one big table as many smaller tables (partitions) behind a single name. Queries that filter on the partition key only read the partitions they need, old data can be dropped or archived one partition at a time instead of with a slow DELETE, and vacuum works on small tables.

#Native partitioning

SQL
create table public.events (
  id bigint generated always as identity,
  created_at timestamptz not null default now(),
  user_id uuid,
  payload jsonb,
  primary key (id, created_at)          -- the partition key must be part of the primary key
) partition by range (created_at);

alter table public.events enable row level security;

Row Level Security, the REST API and realtime work on the parent table as usual; policies on the parent apply to every partition.

#Automatic partitions with pg_partman

Enable pg_partman in Studio → Database → Extensions (it installs into the partman schema), then let it create the partitions:

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

-- one partition per day, 3 days created ahead
select partman.create_parent(
  p_parent_table := 'public.events',
  p_control      := 'created_at',
  p_interval     := '1 day',
  p_premake      := 3
);

#Keep it maintained with a cron job

pg_partman creates future partitions (and drops expired ones) when its maintenance runs. Schedule it with pg_cron:

SQL
select cron.schedule('partman-maintenance', '*/15 * * * *', $$call partman.run_maintenance_proc()$$);

#Retention: drop or archive old partitions

SQL
update partman.part_config
   set retention = '90 days', retention_keep_table = false   -- drop partitions older than 90 days
 where parent_table = 'public.events';

To archive instead of dropping, set retention_keep_table = true (partitions are detached and kept), export them with stitchdb db dump or COPY to a file, upload it to Storage, then drop the detached table.

#Tips

  • Always filter on the partition key (where created_at >= ...) so Postgres can skip partitions.
  • Indexes created on the parent are created on every partition automatically.
  • Unique constraints must include the partition key.
  • Partitioned tables of large projects get autovacuum tuned automatically; see Large databases.
Was this helpful?