Partitioning Big Tables and Archiving Old Data

Scenario

Example app: an event-tracking product stores user events in one events table: 4 billion rows, 30 million new rows a day. Retention is 90 days. A nightly job deletes expired rows.

What you see in production

• The nightly DELETE … WHERE created_at < now() - interval '90 days' runs for 5 hours, bloats the table, lags replicas by 20 minutes and competes with daytime traffic when it overruns.
• Queries for "last 24 hours" still touch huge indexes covering three months.

The naive code

DELETE FROM events WHERE created_at < now() - interval '90 days';   -- ~30M rows every night

Event.objects.filter(created_at__lt=timezone.now() - timedelta(days=90)).delete()

Reproduce it

Compare the work of row-by-row deletion with dropping a whole partition, and how many partitions a "last 24 hours" query needs to touch:

ROWS_PER_DAY = 30_000_000
partitions = {f"events_2026_{m:02}": ROWS_PER_DAY * 30 for m in range(7, 11)}   # monthly partitions

rows_deleted_per_night = ROWS_PER_DAY
print(f"DELETE: {rows_deleted_per_night:,} row deletions + index updates + WAL + VACUUM, every night")
print("DROP:   1 catalog change per month:", "DROP TABLE events_2026_07;")

query_range = ("2026-10-12", "2026-10-13")
touched = [p for p in partitions if p.endswith("10")]       # planner prunes by the partition key
print(f"query {query_range}: scans {len(touched)} of {len(partitions)} partitions -> {touched}")

DELETE: 30,000,000 row deletions + index updates + WAL + VACUUM, every night
DROP:   1 catalog change per month: DROP TABLE events_2026_07;
query ('2026-10-12', '2026-10-13'): scans 1 of 4 partitions -> ['events_2026_10']

Root cause

Deleting rows one by one is expensive: each delete writes WAL, updates every index, and leaves dead tuples for VACUUM. One giant table also means giant indexes, even though most queries only care about recent data.

The fix

Partition by time so data has a natural lifecycle: recent partitions are small and hot; old partitions are detached or dropped as a metadata operation.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  account_id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  payload jsonb,
  PRIMARY KEY (id, created_at)                      -- the partition key must be part of unique keys
) 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 (account_id, created_at);    -- created on every partition

-- retention: detach (no long lock with CONCURRENTLY, PG14+), archive, then drop
ALTER TABLE events DETACH PARTITION events_2026_07 CONCURRENTLY;
DROP TABLE events_2026_07;

CREATE TABLE events (
  id BIGINT NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, account_id BIGINT NOT NULL, payload JSON,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE events DROP PARTITION p2026_07;

def ensure_partition(conn, month_start):
    nxt = (month_start + timedelta(days=32)).replace(day=1)
    name = f"events_{month_start:%Y_%m}"
    conn.execute(text(f"CREATE TABLE IF NOT EXISTS {name} PARTITION OF events "
                      f"FOR VALUES FROM ('{month_start}') TO ('{nxt}')"))

Tools like pg_partman automate creating future partitions and retention.

Trade-offs

• Queries must filter by the partition key to benefit from pruning; a query by account_id alone touches every partition.
• Unique constraints must include the partition key, so global uniqueness of id alone isn't enforced by the database.
• Too many partitions (thousands) slow planning; pick a granularity (day/week/month) matching retention and query ranges.
• Partitioning organises one database's data; it doesn't add write capacity beyond one server — that's sharding.

Detect it in production

• Long-running batch DELETEs, dead-tuple counts (pg_stat_user_tables.n_dead_tup) and replica lag during cleanup.
• Monitor that future partitions exist (a missing partition makes inserts fail, or land in a default partition).
• EXPLAIN shows Partitions removed / pruned scans; queries scanning all partitions need a key filter.

Key points

• Partition large append-mostly tables by time; drop old partitions instead of deleting rows.
• Queries must include the partition key to prune.
• Automate partition creation and retention; partitioning ≠ sharding.