Background ingestion polling causes unbounded database load growth #1251

Summary

The semantic memory background ingestion task uses an O(n²) correlated subquery that runs every 2 seconds per worker. Combined with the set_ingested_history table being append-only (rows are never deleted after ingestion), this causes database CPU and cost to grow unboundedly over time — even with zero active users.

Impact

Production environment (Aurora Serverless v2):

• 14,055 rows across 64 sets in set_ingested_history
• ACU climbed from 2.5 → 12.3, hitting the 16 ACU cap regularly
• Writer CPU reaching 100% peaks since Mar 19
• Cost: $137 in the last 7 days, projected $589/month (vs ~$173/month baseline)
• ~$416/month wasted on the ingestion polling loop alone

Dev environment (Aurora Serverless v2):

• 2,460 rows caused ACU to climb from 1 → 8 over 9 days
• Writer CPU reached 36% avg / 95% peak — with zero active users
• After clearing the table, ACU dropped from 8.5 → 1.0 within minutes

Root Cause

Three compounding issues:

1. O(n²) correlated subquery in get_history_set_ids()

sqlalchemy_pgvector_semantic.py:681-722 generates:

SELECT DISTINCT set_id FROM set_ingested_history
WHERE (SELECT count(*) FROM set_ingested_history h1
       WHERE h1.set_id = set_ingested_history.set_id
         AND h1.ingested IS false) >= $1
   OR created_at <= $2 AND ingested IS false

The correlated subquery evaluates against every row in the table, even when all rows are already ingested and the result is empty. With 14,055 rows in prod this produces ~197M row comparisons per execution.

2. Every worker runs its own ingestion loop

semantic_memory.py:149 spawns a _background_ingestion_task on start(), which is called by every uvicorn worker. With MEMMACHINE_WORKERS=5, the expensive query runs ~2.5 times per second (5 workers × every 2s), all doing identical work.

3. set_ingested_history is append-only

Rows are inserted on message arrival and marked ingested=true after processing, but never deleted. The table grows with every user interaction, making the polling query progressively slower — even though ingested rows are never relevant again.

Suggested Fixes

1. Rewrite the query — replace the correlated subquery with GROUP BY / HAVING:

   uningested_sets = (
       select(SetIngestedHistory.set_id)
       .where(SetIngestedHistory.ingested.is_(False))
       .group_by(SetIngestedHistory.set_id)
       .having(func.count(SetIngestedHistory.set_id) >= min_uningested_messages)
   )

2. Single ingestion loop — ensure only one worker runs the background task (e.g., leader election or env-var guard), not all N workers.

3. Purge ingested rows — delete rows from set_ingested_history after successful ingestion, or add a periodic cleanup for old ingested=true rows.

4. Add composite index — (set_id, ingested) on set_ingested_history to speed up the uningested-row lookups.

5. Add backoff sleep — the loop only sleeps when dirty_sets is empty. When sets are found but processing fails (e.g., deadlock), it retries immediately in a tight loop.

Reproduction

Deploy with MEMMACHINE_WORKERS > 1, send enough messages to populate set_ingested_history, then observe database CPU climb even after all messages are ingested and no further API traffic occurs.

Related

• Semantic ingestion retries forever on context_length_exceeded errors #1252 — infinite retry on context_length_exceeded compounds this issue in prod