Database Partitioning Strategy

1.Pick the partition key before the schema

The partition key dictates every query for the next three years. Pick a column that appears in almost every WHERE clause and that distributes uniformly. For Meridian we usetenant_idhashed into 32 buckets — this isolates noisy neighbors and keeps a single tenant's data local to one partition for index-friendly scans.

2.Combine range and hash partitioning

Pure hash partitioning makes time-window pruning impossible; pure range partitioning creates hot tails. We use a two-level layout: hash on tenant_id at the top level, range on created_at month inside each hash partition. The planner prunes both axes and old months can be detached without a vacuum.

CREATE TABLE events (
  tenant_id   uuid       NOT NULL,
  created_at  timestamptz NOT NULL,
  payload     jsonb      NOT NULL
) PARTITION BY HASH (tenant_id);

CREATE TABLE events_h00
  PARTITION OF events FOR VALUES WITH (modulus 32, remainder 0)
  PARTITION BY RANGE (created_at);

CREATE TABLE events_h00_2026_06
  PARTITION OF events_h00
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

3.Automate the rolling window

A cron worker creates next month's partitions on the 25th and detaches month-old partitions on the 1st. Detached partitions stay queryable as standalone tables — we ship them to cold storage once the read traffic falls below one query per hour, which the partition stats view measures directly.