Summary
PurgeStaleFiles, PurgeFilesOutsideRetainedSet, and the related cleanup routines in DbWriter.cs:588-595, 628-635, 685-693 iterate the stale-id list and execute one DELETE per id. On large purges (e.g. ripping out 5000 stale entries after a git checkout of an unrelated branch), this is 5000 separate round-trips, each acquiring and releasing the WAL write lock. Round-trip cost dominates; one DELETE FROM files WHERE id IN (...) in chunks of ~500 ids would collapse this to ~10 statements with one lock acquisition each.
Where
src/CodeIndex/Database/DbWriter.cs:588-595 (PurgeStaleFiles loop)src/CodeIndex/Database/DbWriter.cs:628-635 (PurgeFilesOutsideRetainedSet)src/CodeIndex/Database/DbWriter.cs:685-693 (additional purge site)
Suggested approach
(1) Introduce a DeleteFilesByIdBatched(IReadOnlyList<long> ids, int chunkSize = 500) helper that issues DELETE FROM files WHERE id IN (?, ?, ...) per chunk, reusing prepared statements where possible. (2) Apply the helper at all three call sites. (3) Wrap the batched deletes in a single transaction so cascading deletes (symbols, references, chunks) commit atomically with the parent. (4) Add a benchmark fixture with 5000 stale-id deletes; fail CI if total wall time regresses by 2x. (5) Cross-link with the just-filed #1804 (TransactionScope state machine) — the batched delete should ride the same transaction primitive. (6) Cross-link with #1565 (DbReader per-instance schema scan) — same family of round-trip waste.
Summary
PurgeStaleFiles,PurgeFilesOutsideRetainedSet, and the related cleanup routines inDbWriter.cs:588-595, 628-635, 685-693iterate the stale-id list and execute oneDELETEper id. On large purges (e.g. ripping out 5000 stale entries after agit checkoutof an unrelated branch), this is 5000 separate round-trips, each acquiring and releasing the WAL write lock. Round-trip cost dominates; oneDELETE FROM files WHERE id IN (...)in chunks of ~500 ids would collapse this to ~10 statements with one lock acquisition each.Where
src/CodeIndex/Database/DbWriter.cs:588-595(PurgeStaleFiles loop)src/CodeIndex/Database/DbWriter.cs:628-635(PurgeFilesOutsideRetainedSet)src/CodeIndex/Database/DbWriter.cs:685-693(additional purge site)Suggested approach
(1) Introduce a
DeleteFilesByIdBatched(IReadOnlyList<long> ids, int chunkSize = 500)helper that issuesDELETE FROM files WHERE id IN (?, ?, ...)per chunk, reusing prepared statements where possible. (2) Apply the helper at all three call sites. (3) Wrap the batched deletes in a single transaction so cascading deletes (symbols, references, chunks) commit atomically with the parent. (4) Add a benchmark fixture with 5000 stale-id deletes; fail CI if total wall time regresses by 2x. (5) Cross-link with the just-filed #1804 (TransactionScope state machine) — the batched delete should ride the same transaction primitive. (6) Cross-link with #1565 (DbReader per-instance schema scan) — same family of round-trip waste.