Grouped alerts: getPreviousAlertHistories reads every history row of the last 7 days, so check-alerts slows down and runs out of memory as history grows #3508

What happened?

One grouped alert with many groups made every alert check on our deployment slow, and eventually crash-looped the app.

• The alert: interval 1 minute, group-by on an attribute with 1,337 groups. HyperDX writes one AlertHistory row per group per evaluation, about 1.9 M rows a day for this alert.
• After 24 hours (1.24 M rows for this alert), the getPreviousAlertHistories aggregation for it took up to 241 s and examined 1.2 M documents per run (MongoDB slow-query log, plan IXSCAN { alert: 1, group: 1, createdAt: -1 }). Every other alert's lookup took about 1 s.
• check-alerts waits for every alert's lookup, so every alert was evaluated only every 4–5 minutes. After a restart, the catch-up run peaked at 900 MiB: the app container (512 MiB limit) was OOMKilled, the next start had more to catch up, and it crash-looped.
• With the alert deleted, a check run takes 7 s.

The lookback is 7 days, so the scan keeps growing for a week after an alert is created: about 13 M rows for this alert.

The cause, as far as we can tell (main 2bfc630):

• getPreviousAlertHistories matches all rows of the last 7 days for the alert, then $sort + $group with $first per group:

  hyperdx/packages/api/src/tasks/checkAlerts/index.ts

  Lines 1842 to 1897 in 2bfc630

  	export const getPreviousAlertHistories = async (
  	alertIds: string[],
  	now: Date,
  	sharedQueue?: PQueue,
  	) => {
  	const lookbackDate = new Date(now.getTime() - ms('7d'));
  	// Concurrency-limited per-alert queries to avoid overwhelming the connection
  	// pool when there are many alerts (e.g., 200+ alert IDs).
  	const queue =
  	sharedQueue ?? new PQueue({ concurrency: ALERT_HISTORY_QUERY_CONCURRENCY });
  	const results = await Promise.all(
  	alertIds.map(alertId =>
  	queue.add(async () => {
  	const id = new mongoose.Types.ObjectId(alertId);
  	return AlertHistory.aggregate<AggregatedAlertHistory>([
  	{
  	$match: {
  	alert: id,
  	createdAt: { $lte: now, $gte: lookbackDate },
  	// ERROR rows record failed evaluations; they must not count as
  	// "window evaluated" or the failed window would never be
  	// retried/backfilled.
  	state: { $ne: AlertState.ERROR },
  	},
  	},
  	// With a single alert value, the compound index {alert: 1, group: 1, createdAt: -1}
  	// delivers results already in this sort order — this is an index-backed no-op sort.
  	{
  	$sort: { alert: 1, group: 1, createdAt: -1 },
  	},
  	// Group by {alert, group}, taking the first (latest) document's fields.
  	// Using $first on individual fields instead of $first: '$$ROOT' allows
  	// DocumentDB to avoid fetching full documents when not needed.
  	{
  	$group: {
  	_id: {
  	alert: '$alert',
  	group: '$group',
  	},
  	createdAt: { $first: '$createdAt' },
  	state: { $first: '$state' },
  	fired: { $first: '$fired' },
  	},
  	},
  	{
  	$project: {
  	_id: '$_id.alert',
  	createdAt: 1,
  	state: 1,
  	group: '$_id.group',
  	fired: 1,
  	},
  	},
  	]);

• The comment expects $group + $first to "short-circuit per group" on the {alert, group, createdAt} index. But the $match also filters on state ($ne: ERROR), and $group reads state and fired. Neither is in the index, so MongoDB fetches every document in the window, with no DISTINCT_SCAN and no covered plan.

Reproduced on main (2bfc630, MongoDB 5.0.32 from docker-compose.ci.yml). We seeded one alert with 1,337 groups and timed getPreviousAlertHistories, then ran explain('executionStats') on the same pipeline:

The cost grows with history, not with the number of groups. At the full 7-day lookback this alert has ~13.5 M rows, so ~23 s per check on a laptop; it was 241 s on our deployment's MongoDB at 1.2 M rows.

Possible fixes:

• Bound the scan by the previous evaluation (the last row above). All rows of one evaluation share createdAt (providers/default.ts, "All histories in one execution share the same createdAt"). So: find the alert's latest createdAt with the {alert, createdAt} index, read that evaluation's rows (one per group), and fall back to a bounded lookup for groups missing from it.
• Or keep each group's last state on a small per-alert document instead of deriving it from history.
• A covering index alone isn't enough: it skips the document fetches, but it's only ~1.8× faster and still linear in history.

Steps to reproduce

Reproduces on play.hyperdx.io? Didn't try.

1. Seed alerthistories for one alert: 1,337 groups × 1,000 evaluations one minute apart, all state: 'OK', within the last 7 days.
2. Call getPreviousAlertHistories([alertId], now). It takes seconds and grows linearly with the number of evaluations stored, while returning only 1,337 rows.
3. explain('executionStats') on its pipeline shows FETCH > IXSCAN with totalDocsExamined equal to every row in the window.

On a live deployment: a 1-minute alert whose group-by yields ~1,000+ groups. Within a day, MongoDB's slow-query log shows this aggregate taking minutes, and check-alerts finished in … grows the same way.

How are you running HyperDX?

Helm (ClickStack chart 2.1.0) with the hyperdx:2-nightly image (2.42.0 plus the Node bump from #3456), MongoDB 5.0.32 (community server, mongodb-kubernetes operator), our own ClickHouse 26.2.5.45. The same query is on main 2bfc630.

Where does it show up?

The alert task (check-alerts) in the app container. The UI and API go down with it, because they share the container.

Debug info

MongoDB slow-query log entry: ns: hyperdx.alerthistories, planSummary: IXSCAN { alert: 1, group: 1, createdAt: -1 }, docsExamined: 1202993, durationMillis: 240900.

Logs

check-alerts finished in 295000 ms with the alert, 7200 ms after deleting it.

Written with help from an AI assistant (Claude Code). The behaviour comes from our own deployment, and the table from a test on main.