fix: replace O(n²) ingestion polling query with GROUP BY/HAVING by xiongzubiao · Pull Request #1253 · MemMachine/MemMachine

xiongzubiao · 2026-03-23T17:40:49Z

Purpose of the change

Fix the O(n²) background ingestion polling query that causes unbounded database CPU and cost growth, even with zero active users.

Description

The get_history_set_ids() method used a correlated subquery that evaluated against every row in set_ingested_history, running every 2 seconds per worker. Combined with the table being append-only, this caused Aurora ACU usage to climb from 1 → 8 over 9 days on dev (~$300+ wasted RDS cost).

This PR:

Rewrites the query from O(n²) correlated subquery to GROUP BY/HAVING (single table scan)
Purges fully-ingested rows via new purge_ingested_rows() method across all storage backends (PG, Neo4j, in-memory), preventing unbounded table growth. Only purges sets with no pending messages to preserve the (set_id, history_id) duplicate guard
Adds exponential backoff on ingestion failure (2s → 60s cap, resets on success)
Makes sleep interruptible via asyncio.Event so stop() returns promptly during backoff
Adds composite index (set_id, ingested) on set_ingested_history via alembic migration + model __table_args__
Handles partial batch failure — purge runs after every batch regardless; it naturally skips sets that still have uningested rows

Fixes/Closes

Fixes #1251

Type of change

Bug fix (non-breaking change which fixes an issue)

How Has This Been Tested?

Unit Test
Integration Test
test_sqlalchemy_vs_alembic schema sync integration test passes (validates model matches migration)
Cross-AI code review by Gemini, Claude, and Codex — all findings addressed

Checklist

My code follows the style guidelines of this project (See STYLE_GUIDE.md)
I have performed a self-review of my own code
My changes generate no new warnings
I have added unit tests that prove my fix is effective or that my feature works
New and existing unit tests pass locally with my changes
I have checked my code and corrected any misspellings

Maintainer Checklist

Confirmed all checks passed
Contributor has signed the commit(s)
Reviewed the code
Run, Tested, and Verified the change(s) work as expected

Screenshots/Gifs

N/A

Further comments

Not included: Single-worker leader election for the ingestion loop. This is a larger architectural change that warrants its own issue — the query fix + purge alone eliminate the unbounded growth problem.
Migration: c7a2f8e31b90 chains from b65f7f4a9d2c. The composite index speeds up both the rewritten query and the purge operation.

🤖 Generated with Claude Code

chatgpt-codex-connector

💡 Codex Review

Here are some automated review suggestions for this pull request.

Reviewed commit: a04e0fd657

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

Open a pull request for review
Mark a draft as ready
Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

The background ingestion task used a correlated subquery in get_history_set_ids() that scanned every row in set_ingested_history, causing unbounded database CPU growth. This commit: - Rewrites the query to use GROUP BY/HAVING (O(n) instead of O(n²)) - Adds purge_ingested_rows() to delete processed rows after ingestion - Adds exponential backoff on ingestion failure (2s → 60s cap) - Adds composite index (set_id, ingested) via alembic migration - Adds sleep between successful ingestion cycles to prevent tight loops - Logs purged row count for operational visibility Co-Authored-By: Claude Opus 4.6 (1M context) <[email protected]>

- Add composite index to SetIngestedHistory model definition via __table_args__ so alembic autogeneration stays in sync (o-love) - Purge after partial batch failure — purge always runs on all dirty_sets since purge_ingested_rows skips sets with pending messages (codex P1) - Make backoff sleep interruptible via asyncio.Event so stop() returns promptly (codex P2) - Fix ty type check: cast session.execute result to CursorResult Co-Authored-By: Claude Opus 4.6 (1M context) <[email protected]>

Copilot

Pull request overview

Addresses unbounded DB load from background ingestion polling by optimizing the polling query, adding post-ingestion cleanup, and improving ingestion-loop behavior under failure.

Changes:

Rewrote get_history_set_ids() to avoid an O(n²) correlated subquery by using GROUP BY/HAVING + UNION.
Added purge_ingested_rows() across storage backends to delete fully-ingested rows and prevent unbounded growth.
Added a composite index (set_id, ingested) via Alembic (and model metadata) to support the new query patterns.

Reviewed changes

Copilot reviewed 8 out of 8 changed files in this pull request and generated 5 comments.

Show a summary per file

File	Description
packages/server/src/memmachine_server/semantic_memory/storage/storage_base.py	Adds abstract `purge_ingested_rows()` API to all semantic storage implementations.
packages/server/src/memmachine_server/semantic_memory/storage/sqlalchemy_pgvector_semantic.py	Rewrites polling query, adds purge implementation, and adds composite index on the table.
packages/server/src/memmachine_server/semantic_memory/storage/neo4j_semantic_storage.py	Adds Neo4j implementation of purge for ingested history nodes.
packages/server/src/memmachine_server/semantic_memory/storage/alembic_pg/versions/c7a2f8e31b90_add_ingested_composite_index.py	Adds Alembic migration creating the composite index.
packages/server/src/memmachine_server/semantic_memory/semantic_memory.py	Adds shutdown-aware sleep and failure backoff for the ingestion background loop.
packages/server/server_tests/memmachine_server/semantic_memory/storage/test_semantic_storage.py	Adds tests covering purging behavior for fully/partially ingested sets and empty input.
packages/server/server_tests/memmachine_server/semantic_memory/storage/in_memory_semantic_storage.py	Adds in-memory purge implementation used by tests.
packages/server/server_tests/memmachine_server/semantic_memory/mock_semantic_memory_objects.py	Updates mock interface with new purge method.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

- Add coalesce(sum(cnt), 0) in Neo4j purge to handle null on zero rows - Add cycle sleep on success path to prevent tight polling loop - Make migration idempotent with if_not_exists=True - Replace NOT IN with NOT EXISTS in purge query for better plans/NULL safety - Use asyncio.TimeoutError explicitly in interruptible sleep Co-Authored-By: Claude Opus 4.6 (1M context) <[email protected]>

Copilot

Pull request overview

Copilot reviewed 8 out of 8 changed files in this pull request and generated 2 comments.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

Reset backoff_sec when no dirty sets are found so error-induced backoff doesn't persist across long idle periods. Expand purge_ingested_rows docstring to clarify it returns total history rows deleted. Co-Authored-By: Claude Opus 4.6 (1M context) <[email protected]>

chatgpt-codex-connector Bot reviewed Mar 23, 2026

View reviewed changes

Comment thread packages/server/src/memmachine_server/semantic_memory/semantic_memory.py Outdated

Comment thread packages/server/src/memmachine_server/semantic_memory/semantic_memory.py Outdated

xiongzubiao force-pushed the fix/ingestion-polling-1251-pr branch from a04e0fd to fa83c3a Compare March 23, 2026 17:54

o-love reviewed Mar 23, 2026

View reviewed changes

Comment thread packages/server/src/memmachine_server/semantic_memory/storage/sqlalchemy_pgvector_semantic.py

xiongzubiao and others added 2 commits March 23, 2026 18:31

xiongzubiao force-pushed the fix/ingestion-polling-1251-pr branch from fa83c3a to 131a125 Compare March 23, 2026 18:31

xiongzubiao requested a review from Copilot March 23, 2026 18:32

Copilot AI reviewed Mar 23, 2026

View reviewed changes

xiongzubiao requested a review from Copilot March 23, 2026 18:52

Copilot AI reviewed Mar 23, 2026

View reviewed changes

Comment thread packages/server/src/memmachine_server/semantic_memory/storage/storage_base.py Outdated

Comment thread packages/server/src/memmachine_server/semantic_memory/semantic_memory.py

Copilot started reviewing on behalf of xiongzubiao March 23, 2026 19:05 View session

xiongzubiao requested review from malatewang and o-love March 23, 2026 19:32

o-love approved these changes Mar 23, 2026

View reviewed changes

malatewang approved these changes Mar 23, 2026

View reviewed changes

Merge branch 'main' into fix/ingestion-polling-1251-pr

aa61cb7

xiongzubiao merged commit c7b5e97 into main Mar 23, 2026
52 checks passed

xiongzubiao deleted the fix/ingestion-polling-1251-pr branch March 23, 2026 23:59

leomem mentioned this pull request Sep 19, 2026

[Feat]: Make the semantic ingestion poll interval configurable (currently hardcoded at 2s) #1699

Open

Navigation Menu

Repository navigation

fix: replace O(n²) ingestion polling query with GROUP BY/HAVING - #1253

fix: replace O(n²) ingestion polling query with GROUP BY/HAVING#1253
xiongzubiao merged 5 commits into
mainfrom
fix/ingestion-polling-1251-pr

xiongzubiao commented Mar 23, 2026 •

edited

Loading

Uh oh!

chatgpt-codex-connector Bot left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

xiongzubiao commented Mar 23, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Purpose of the change

Description

Fixes/Closes

Type of change

How Has This Been Tested?

Checklist

Maintainer Checklist

Screenshots/Gifs

Further comments

Uh oh!

chatgpt-codex-connector Bot left a comment

Choose a reason for hiding this comment

💡 Codex Review

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

xiongzubiao commented Mar 23, 2026 •

edited

Loading