Alerting tables (`alert`, `alert_notification`) grow unbounded — no retention/cleanup

Dear Coroot team, I stumbled in a situation with Coroot for which I would like a feedback from you, below a detailed report authored by me and an AI assistant. The core of the question is that Coroot's Postgres alerting tables grow without bound: resolved alerts and sent notifications are never deleted. There is no documented retention setting for them and no built-in cleanup. On our production install these are by far the two largest objects in the coroot database.

Both accumulate history since deployment and nothing ever removes old rows. We'd like to know the intended behavior and whether coroot should manage retention itself, before we add our own out-of-band cleanup.

Obviously, right now is not a real issue, but the behaviour should be monitored in the long run.

Environment

• coroot: 1.24.3
• Backend store: PostgreSQL 18.3 (CloudNativePG), dedicated coroot database
• Deployment: Kubernetes
• Tables: public.alert, public.alert_notification (also see note on incident_notification)

Evidence / metrics (as of 2026-08-04, ~77 days of data: 2026-05-19 → 2026-08-04)

alert_notification

Metric	Value
Total size	832 MB (all heap; 0 TOAST, 0 index)
Rows	698,602
Growth	~9,000 rows/day, ~10-12 MB/day, ~325 MB/month
details column	686 MB — 82% of the table (~1 KB JSON per row)
Dead tuples	~58 (autovacuum keeps up; not bloat)
n_tup_upd lifetime	~1,000,000
Deletes ever	0

alert

Metric	Value
Total size	524 MB (452 MB heap + 72 MB indexes)
Rows	403,242 — 403,200 resolved, 42 active
details column	332 MB (~1 KB JSON per row)
Dead tuples	~38,595 (~9.6%)
n_tup_upd lifetime	~6,467,180 (~16 updates/row)
Autovacuum	default (no per-table tuning)
Deletes ever	0

So in both cases the size is real, retained data (plus, for alert, meaningful update-driven bloat) — the growth is structural, not a vacuum misconfiguration.

Root cause

Neither table is ever pruned. A repo-wide search finds no DELETE FROM alert and no DELETE FROM alert_notification anywhere (the only alerting DELETE is DELETE FROM alerting_rule for user-initiated rule removal).

alert_notification (db/alert_notification.go)

Lifecycle is INSERT + UPDATE + SELECT, no DELETE:

• PutAlertNotification — INSERT one row per notification, sent_at = 0.
• UpdateAlertNotification — UPDATE ... SET sent_at, external_key WHERE project_id, alert_id, timestamp, destination (why lifetime updates ~1M > rows).
• Reads: GetNotSentAlertNotifications (WHERE timestamp >= $from AND sent_at = 0, frequent poll); GetPreviousAlertNotifications / ...ByAlertIds (WHERE project_id/alert_id = ...).
• No indexes — the Migrate DDL is a bare CREATE TABLE, so the poll and every UPDATE seq-scan the whole (unbounded) table.

alert (db/alert.go)

Lifecycle is INSERT + many UPDATEs + SELECT, no DELETE. Alerts are "resolved" by UPDATE ... SET resolved_at = ..., not removed:

• CreateAlert — INSERT.
• UpdateAlert, ResolveAlert, ResolveAlertsByRule, ResolveAlerts, SuppressAlerts, ClearSuppression, ReopenAlerts — all UPDATE (hence ~6.5M lifetime updates, ~16 per row).
• Reads: QueryAlerts, GetActiveOrSuppressedAlertByFingerprint, GetLatestAlertsByRule, etc., served by 5 indexes (alert_pkey, alert_project_resolved, alert_fingerprint_resolved, alert_fingerprint_suppressed, alert_project_rule_resolved).
• This table is well-indexed, so it's not a seq-scan problem — but the heavy update churn on default autovacuum leaves ~9.6% dead tuples and index bloat, and it still grows forever because resolved alerts are retained indefinitely.

Common structural issue: no retention. Docs only define TTL for the metric cache and ClickHouse (traces/logs/profiles/metrics); the Postgres store and these tables have none.

Note: incident_notification follows the same shape as alert_notification (no index); it's small today but worth including in any retention story.

Impact

• Unbounded disk growth in the coroot Postgres database (~1.35 GB already, mostly historical/resolved records).
• For alert_notification: continuously rising CPU/I/O, because the "not yet sent" poll and every per-notification UPDATE seq-scan an ever-larger unindexed table.
• For alert: growing dead-tuple/index bloat from heavy update churn on default autovacuum.
• Operators have no supported knob to cap either table and must resort to manual DELETEs.

Proposed solutions (any/all)

1. Built-in retention with cleanup logs for both tables — a configurable window with a periodic prune, and a log line per run (rows deleted, cutoff) so operators can observe it:
   • alert_notification: delete old, already-sent rows (timestamp < cutoff AND sent_at <> 0). Safe because coroot only reads recent rows (timestamp >= $from) or rows for a specific active alert_id. Suggested flag e.g. --alert-notifications-ttl (default ~30-90d).
   • alert: delete old resolved rows (resolved_at > 0 AND resolved_at < cutoff). This one needs care re: the Alerts history UI and references from incident / alert_notification (see question below). Suggested flag e.g. --resolved-alerts-ttl.
2. Add supporting indexes to alert_notification in the migration, e.g. on ("timestamp") and a partial (sent_at) WHERE sent_at = 0 for the hot poll, plus an index/PK for the UPDATE predicate (project_id, alert_id, timestamp, destination). These would also let pg_repack run online (currently impossible — no PK/unique index). (alert already has a PK + useful indexes.)
3. Documentation: if unbounded retention is intentional, please document it and a recommended manual cleanup so operators can plan capacity.

What we're asking

• Is unbounded growth of alert and alert_notification intended, or an oversight?
• If coroot should manage this, would you accept a PR for (1) retention + cleanup logs and (2) the alert_notification indexes? We're happy to contribute.
• In the meantime, is it safe for an operator to delete out-of-band:
  • alert_notification: old already-sent rows (sent_at <> 0, timestamp < cutoff)?
  • alert: old resolved rows (resolved_at > 0, resolved_at < cutoff) — do any of the Alerts UI, incident, or alert_notification still depend on resolved alert rows after resolution? Our reading of the code says the notification purge is safe; we'd like confirmation on the resolved-alert purge before touching it.

Thanks for coroot — happy to provide more diagnostics from our instance.