Table partitioning strategy

Problem

The license_events table grows unbounded. Queries against the last 30 days scan millions of stale rows. Vacuum churn and index bloat degrade dashboard performance.

Strategy

Partition by RANGE (created_at) with monthly child tables. Keep 6 months hot, detach older partitions to cold storage. Use a default partition to catch edge cases.

DDL sketch

CREATE TABLE license_events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  license_key TEXT      NOT NULL,
  event_type  TEXT      NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE license_events_2025_06
  PARTITION OF license_events
  FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

Maintenance

• Cron job creates next month's partition on the 25th.
• DETACH PARTITION for months older than 6.
• Cold partitions live in a separate tablespace on cheaper storage.
• Dashboard queries always include created_at to enable partition pruning.