A single incremental sync makes the embedding refresh read about 900 MB of the archive, even when only a few hundred messages changed

[dqtz5vpvj9-create]

agentsview version

main at d0395b5 (v0.42.0 plus two docs commits); the same code is in v0.41.1.

Install method

Built from source

OS / platform

Linux x86_64 (Debian-based), 31 GB RAM

Which agent and version

Not agent-specific; observed with a mixed Claude Code / Codex archive of 1,637 sessions (12.7 GB sessions.db, 1.2 GB vectors.db)

Which model(s)

Not applicable

What happened, and what did you expect

Scope

This is one of three independent causes behind a daemon whose memory footprint tracks the archive size instead of the change rate; the other two are filed separately: #1585 (glibc retention of freed SQLite memory) and #1586 (tool-result content stored twice). This issue covers the largest and most frequent one: the read amplification of the after-sync embedding refresh. Per incremental sync it reads roughly 900 MB of database pages that are never touched again, against a measured UI working set of about 85 MB.

What happened

With [vector] enabled = true and [vector.embed] run_after_sync = true, every sync completion schedules an embedding refresh 30 s later, and that refresh reads the entire message corpus twice, regardless of how much changed.

Measured with posix_fadvise(DONTNEED) on the database files followed by fincore sampling every 5 s: a 7-message incremental append to one live session was followed, about 35 s later, by sessions.db going from 2 MB to 640 MB resident and vectors.db from 0 to 275 MB, with no API requests in the window. The pattern reproduced on two independent runs. cgroup memory.stat reported all of it as inactive_file; over a day this accumulates to several GB of page cache that nothing ever touches again. For comparison, replaying the real UI request mix from the same day (session detail, usage, children, messages, projects, stats, usage summary, search) had a file working set of 72 MB of sessions.db plus 11 MB of the usage cache.

Two queries are responsible:

1. DB.ScanEmbeddableUnits (internal/db/messages.go) carries a since watermark, but optionalSinceClause wraps the column in a function:

   AND (NULLIF(s.ended_at, '') IS NULL OR datetime(NULLIF(s.ended_at, '')) >= datetime(?))

   The query is FROM messages m JOIN sessions s ... ORDER BY m.session_id, m.ordinal, so on the real archive the plan is:

   SCAN m USING INDEX idx_messages_session_ordinal
   SEARCH s USING INDEX sqlite_autoindex_sessions_1 (id=?)
   

   It scans all 693,237 messages and reads every content column, then filters each row through the sessions probe. Only 550 messages in 8 sessions were newer than the watermark at the time: a 1,260x overshoot, and it accounts for the 640 MB (the messages table is 384 MB plus indexes).

2. Index.countPending (internal/vector/build.go) runs SELECT content FROM <docs> d WHERE NOT EXISTS (...) and splits every document's content in Go just to compute the progress-bar denominator. That reads the whole 228 MB vector_messages table on every refresh and accounts for the 275 MB.

Expected behavior

The cost of an after-sync refresh should be proportional to what changed. Driving the scan from sessions with a sargable predicate on ended_at (it is already an RFC 3339 string, so a plain comparison works; the NULL case can be a separate branch) and then reading messages through idx_messages_session_ordinal for those few sessions turns the plan into a handful of SEARCHes. countPending can count rows via the stamps/docs anti-join on doc_key and content_hash without fetching content; the chunk count is only used for progress reporting.

Neither change touches the stored format or the embedding output.

Sample session file or snippet

20:51:15 incremental claude <session>: 10 new message(s)
20:51:50 incremental claude <session>: 7 new message(s)
                    sessions.db   vectors.db   (fincore RES)
20:51:44               1.8M           0
20:51:54             647.3M       264.3M
20:52:04             637.9M       274.0M

Steps to reproduce

1. Enable [vector] with run_after_sync = true on an archive with a few hundred thousand messages.
2. Evict the database files from the page cache (vmtouch -e or posix_fadvise(DONTNEED)) and watch fincore on sessions.db and vectors.db.
3. Let one small incremental sync happen; about 30 s later both files become hundreds of MB resident.
4. EXPLAIN QUERY PLAN on the ScanEmbeddableUnits query with a recent since value shows SCAN m USING INDEX idx_messages_session_ordinal.

Checklist

• I searched existing issues
• I removed secrets and private data from any attached session files