← lasīt vairāk

The Events Table That Never Shrinks: Partitioning and Retention in Postgres

An append-only table grows forever, and DELETE doesn't shrink it. When range partitioning is worth the setup cost, why DROP beats DELETE for retention, and the Laravel-side plumbing that keeps the partitions coming.

The table everyone means when they say "large"

Every sufficiently old product has one: the pageview log, the webhook payload archive, the audit trail, the job history. It's append-only, nothing queries rows older than a couple of months, and it grows a few gigabytes a month. The instinct is to add a cleanup job with DELETE FROM events WHERE created_at < now() - interval '90 days'. That job runs, logs success, and the table keeps getting bigger.

Deleting rows doesn't return the disk. The rows become dead tuples that vacuum has to work through, the table and its indexes keep their allocated pages, and every cleanup run on a big table takes longer than the last. Retention by deletion is the right tool for millions of rows; for hundreds of millions, it's a treadmill.

Decide against partitioning first

Range partitioning has real setup costs, so let's be clear about when it's not worth them:

  • The table is under a few dozen GB with healthy indexes. A plain index handles your queries fine; partitioning changes nothing for you except the schema.
  • Retention isn't actually required. If keeping data forever is harmless, don't build machinery to delete it.
  • Queries regularly ignore the partition key. Partition pruning only helps when queries filter on the partition column. A table queried "by user across all time" gains little.

If you need partitioning, you know it: the table is fat, retention is real, and time is how the data is queried.

Range partitioning by month

Postgres makes the core setup short:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    payload jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE INDEX ON events_2026_10 (user_id, created_at);

Three things surprise people here:

  1. The primary key must include the partition column. PRIMARY KEY (id, created_at), not just id. Uniqueness across the whole table can't be enforced otherwise, and Postgres will refuse to create the index.
  2. Every partition gets its own indexes. You define them per partition, not once for the parent. This is why new partitions need a creation routine, not just a one-time migration.
  3. Laravel migrations won't manage partitions. Partitioning is plain SQL through DB::statement() in your own migrations.

Retention: swap, don't delete

The payoff of time-based partitioning is that retention becomes a metadata operation. To drop October 2024:

ALTER TABLE events DETACH PARTITION events_2024_10;
DROP TABLE events_2024_10;

Instant. No dead tuples, no vacuum debt, no long-running transaction. The disk space returns the moment DROP commits.

The supporting piece is a scheduled task that always has future partitions ready and prunes old ones. A small Laravel command does both jobs:

// Scheduled daily: create next month's partition ahead of time
$schema = Event::createPartitionFor(Carbon::now()->addMonth());
// and remove partitions older than the retention window
Event::dropPartitionsOlderThan(Carbon::now()->subDays(90));

The failure mode to guard against is a partition gap: if nobody creates the partition for November, every November 1st insert fails on the clock. Write the routine so it's idempotent ("create if missing"), put it in the schedule, and monitor for tables whose newest partition starts more than a month out.

One watch-out on queries

Pruning works when the query carries a literal or parameter on created_at. Where clauses built like "the last 30 days" are fine. A query that scans without the partition key touches every partition, and suddenly your clean per-month plan becomes a fan-out. Watch for ORM scopes that conditionally drop the date filter, and check EXPLAIN output once in a while to confirm only the expected partitions appear in the plan.

Combine it with the aggregate

Retention and reporting are two halves of the same decision. If the detail rows are only there to feed dashboards, compute the summaries into summary tables or materialized views first, then keep the raw detail for 90 days and drop the rest. That combination, aggressive rollups plus short retention, is the standard shape for an events pipeline on one database, and it's usually cheaper than any scale-out plan.