openclaw - 💡(How to fix) Fix LCM: FTS index grows unbounded — compaction deletes messages but never cleans FTS entries [1 participants]

Bug Description

LCM's message compaction removes rows from the messages table but never deletes the corresponding entries from the messages_fts FTS5 virtual table. Over time this causes the FTS index to grow far larger than the actual message count.

Impact

• Database bloat: Our LCM database grew to 618MB with 501,681 FTS content rows for only 22,732 actual messages (22:1 ratio)
• Memory pressure: The gateway process grew to 2.2GB RSS, likely due to loading the bloated FTS index into memory
• Performance degradation: Response latency increased noticeably as the database grew

Root Cause

In the compaction code path, when old messages are summarized and the originals are deleted from messages, no corresponding DELETE is issued against the messages_fts table. FTS5 does not cascade deletes from external tables — it requires explicit delete commands:

INSERT INTO messages_fts(messages_fts, rowid, content) VALUES('delete', <rowid>, <original_content>);

Or alternatively, the FTS table could be defined with content=messages (external content mode) so it stays in sync automatically.

Reproduction

-- Check for orphaned FTS entries
SELECT COUNT(*) FROM messages_fts_content 
WHERE id NOT IN (SELECT message_id FROM messages);

-- Compare actual vs FTS row counts
SELECT 
  (SELECT COUNT(*) FROM messages) as actual_messages,
  (SELECT COUNT(*) FROM messages_fts_content) as fts_rows;

Workaround

Drop and recreate the FTS tables, then repopulate from current messages:

DROP TABLE IF EXISTS messages_fts;
CREATE VIRTUAL TABLE messages_fts USING fts5(content, tokenize='porter unicode61');
INSERT INTO messages_fts(rowid, content) SELECT message_id, content FROM messages;
VACUUM;

This reduced our database from 618MB to 88MB.

Suggested Fix

Add FTS cleanup to the compaction transaction — when messages are deleted during compaction, also delete their FTS entries in the same transaction. Alternatively, switch to FTS5 external content mode (content=messages) which handles this automatically.

Environment

• OpenClaw running on Ubuntu 22.04 VPS (8GB RAM)
• ~22K messages across ~3,300 conversations
• LCM enabled with compaction threshold 0.6