Skip to content

0031. Daily FK-safe Control-plane DB Prune CronJob

  • Status: accepted
  • Date: 2026-07-10
  • Deciders: lusoris
  • Related: PR #2258

Context

The paperclip control-plane Postgres database accumulated rows without bound. Wake-requests, run-events, and per-run JSONB payloads grew continuously, which bloated the tables, degraded query latency, and contributed to disk-pressure toil. Cleanup had been performed ad hoc by hand, which is neither repeatable nor safe to run unattended, and the foreign-key relationships between the run and event tables make naive deletes prone to constraint violations.

Decision

Introduce paperclip-db-janitor, a daily Kubernetes CronJob that runs a single idempotent prune.sql against the control-plane database. The script deletes in foreign-key-safe order, creates the supporting indexes it depends on, and vacuums afterwards. Retention windows are parameterised through environment variables mapped to psql variables so they can be tuned in values.yaml without editing SQL: skipped wake-requests are pruned after 24 hours, run-events after 30 days, and run payloads after 2 days. The job is deployed through ArgoCD like every other workload.

Alternatives considered

  • Prune inside the paperclip application loop: rejected because the pod is single-threaded and already the bottleneck; adding delete/vacuum work to the hot path risks starving live request handling.
  • Table partitioning or row-level TTL in Postgres: heavier operational surface than a scheduled prune for the current data volume.
  • No pruning plus a larger disk: defers rather than fixes the growth and has already produced disk-full incidents.

Consequences

  • Table growth is bounded and query latency stays predictable.
  • Retention windows are tunable from values.yaml without SQL edits.
  • Adds one daily CronJob under argocd-apps/ai; the FK-safe ordering and index creation make it safe to re-run at any time.
  • Establishes the retention policy later refined by ADR 0035.

References

  • PR #2258 — feat(paperclip-db-janitor): daily FK-safe control-plane DB prune CronJob
  • apps/ai/paperclip-db-janitor/ (files/prune.sql, templates/cronjob.yaml, values.yaml), argocd-apps/ai/paperclip-db-janitor.yaml