阅读中文版本

Archive Cold Rows to a History Table

Move old rows into an archive table in bounded batches so the hot table stays small and query plans stay stable.

CREATE TABLE IF NOT EXISTS events_history (LIKE events INCLUDING ALL);
WITH moved AS (
  DELETE FROM events WHERE created_at < now() - interval '90 days'
  RETURNING *
)
INSERT INTO events_history SELECT * FROM moved;

while true; do
  psql -c "DELETE FROM events WHERE ctid IN (SELECT ctid FROM events WHERE created_at < now() - interval '90 days' LIMIT 10000)"
  [ $(psql -tAc "SELECT count(*) FROM events WHERE created_at < now() - interval '90 days'") -eq 0 ] && break
  sleep 1
done

What this recipe does

Archive rows older than a threshold into a history table using batched moves rather than one massive delete.

A hot events table grows unbounded, slowing the working set and making autovacuum work harder than necessary.

Prerequisites

• PostgreSQL with a matching history table
• A retention threshold
• Maintenance window or throttle

Procedure

1. Create the history table with the same shape and move one batch.

CREATE TABLE IF NOT EXISTS events_history (LIKE events INCLUDING ALL);
WITH moved AS (
  DELETE FROM events WHERE created_at < now() - interval '90 days'
  RETURNING *
)
INSERT INTO events_history SELECT * FROM moved;

Expected result: A single statement moves and archives a batch atomically.

2. Loop in bounded batches to avoid long locks.

while true; do
  psql -c "DELETE FROM events WHERE ctid IN (SELECT ctid FROM events WHERE created_at < now() - interval '90 days' LIMIT 10000)"
  [ $(psql -tAc "SELECT count(*) FROM events WHERE created_at < now() - interval '90 days'") -eq 0 ] && break
  sleep 1
done

Expected result: Rows are removed in small chunks so locks stay short and vacuum keeps up.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the hot table row count drops, autovacuum pressure falls, and archive queries return the moved rows.

Troubleshooting

INSERT from a CTE is slow on huge volumes — Prefer batched loops with LIMIT and an explicit order to keep each transaction small.

Archived rows still queried — Update reporting queries to union the history table, or expose a view.

Limitations

• History table grows forever without its own retention policy.
• Batched deletes still generate WAL and vacuum load.
• Foreign keys may block moving rows.