[BUG] sqlite-vec delete_orphans uses a correlated DELETE that stalls cleanup and blocks writers

[viggo-lee]

Bug Description

SQLiteVecIndex.delete_orphans performs a correlated DELETE over the sqlite-vec virtual table. On a synthetic 25,729-row / 512-dimensional database, the official query is still running after a 10-second query deadline and a concurrent writer fails with database is locked. An equivalent candidate-row query joined explicitly by vector rowid finishes in 1.152 seconds on the same workload.

This is reproducible without Codex, an MCP adapter, a watcher, a model download, or any private note data: only Python's sqlite3 and sqlite-vec are needed.

I verified the query text against both:

• The official PyPI basic-memory==0.23.2 wheel (SHA256 a1679a16319d8a7fb9c0486033551a47dedc0fbae7f5da81444eb3c4bf0ccecb).
• Current main at 3bf2d523c0a9, which still contains exactly the same SQL, although the method signature has since changed.

Steps To Reproduce

In an isolated Python environment with sqlite-vec==0.1.9, save the script below as reproduce_sqlitevec_cleanup.py and run:

python reproduce_sqlitevec_cleanup.py ./synthetic-cleanup-repro

The destination must not exist. The script creates only synthetic databases, compares the official SQL with the proposed SQL, bounds each query to 10 seconds, verifies the surviving vector rowids on completion, and probes a concurrent read/write. It leaves the synthetic directory for inspection. It does not open a Basic Memory database.

Self-contained reproducer

"""Synthetic-only reproducer for Basic Memory's correlated cleanup DELETE.

Requires sqlite-vec==0.1.9. Pass a NEW directory; no existing database is opened.
This script leaves that directory for inspection and prints its location.
"""
import argparse
import importlib.metadata
import json
import platform
import sqlite3
import struct
import threading
import time
from pathlib import Path

import sqlite_vec

OLD = (
    "DELETE FROM search_vector_embeddings WHERE rowid IN ("
    "SELECT id FROM search_vector_chunks "
    "WHERE project_id = :project_id AND NOT ("
    "vector_index = 'sqlite-vec' "
    "AND embedding_model = :embedding_identity "
    "AND search_vector_embeddings.source_hash = "
    "search_vector_chunks.source_hash "
    "AND embedding_status = 'ready'))"
)
PROPOSED = (
    "DELETE FROM search_vector_embeddings WHERE rowid IN ("
    "SELECT chunks.id FROM search_vector_chunks AS chunks "
    "JOIN search_vector_embeddings AS vectors ON vectors.rowid = chunks.id "
    "WHERE chunks.project_id = :project_id AND NOT ("
    "chunks.vector_index = 'sqlite-vec' "
    "AND chunks.embedding_model = :embedding_identity "
    "AND vectors.source_hash = chunks.source_hash "
    "AND chunks.embedding_status = 'ready'))"
)
PARAMS = {"project_id": 1, "embedding_identity": "synthetic-model"}


def connect(path):
    connection = sqlite3.connect(path, timeout=5)
    connection.enable_load_extension(True)
    sqlite_vec.load(connection)
    connection.enable_load_extension(False)
    return connection


def run(path, sql, count, limit):
    assert not path.exists(), "Refuse to use an existing database"
    c = connect(path)
    try:
        c.execute("PRAGMA journal_mode=WAL")
        c.execute("CREATE TABLE search_vector_chunks (id INTEGER PRIMARY KEY, "
                  "project_id INTEGER NOT NULL, source_hash TEXT NOT NULL, "
                  "embedding_model TEXT NOT NULL, vector_index TEXT NOT NULL, "
                  "embedding_status TEXT NOT NULL)")
        c.execute("CREATE INDEX chunks_project ON search_vector_chunks(project_id)")
        c.execute("CREATE VIRTUAL TABLE search_vector_embeddings USING "
                  "vec0(embedding float[512], +source_hash text)")
        c.execute("CREATE TABLE probe (id INTEGER PRIMARY KEY, value INTEGER)")
        c.execute("INSERT INTO probe VALUES (1, 0)")
        vector = struct.pack("<512f", *([0.125] * 512))
        expected = []
        for i in range(1, count + 1):
            project = 2 if i % 101 == 0 and i % 100 != 0 else 1
            current_hash = f"hash-{i}"
            stored_hash = "stale" if i % 100 == 0 or project == 2 else current_hash
            c.execute("INSERT INTO search_vector_chunks VALUES (?,?,?,?,?,?)",
                      (i, project, current_hash, "synthetic-model", "sqlite-vec", "ready"))
            c.execute("INSERT INTO search_vector_embeddings(rowid,embedding,source_hash) "
                      "VALUES (?,?,?)", (i, vector, stored_hash))
            if project != 1 or stored_hash == current_hash:
                expected.append(i)
        c.commit()
        manifest = c.execute("SELECT * FROM search_vector_chunks ORDER BY id").fetchall()
        plan = c.execute("EXPLAIN QUERY PLAN " + sql, PARAMS).fetchall()
        probe_result = {}

        def probe():
            time.sleep(0.02)
            p = sqlite3.connect(path, timeout=5)
            started = time.monotonic()
            try:
                probe_result["read"] = p.execute("SELECT value FROM probe").fetchone()[0]
                p.execute("UPDATE probe SET value=value+1 WHERE id=1")
                p.commit()
                probe_result["write_ok"] = True
            except sqlite3.OperationalError as error:
                probe_result.update(write_ok=False, error=str(error))
            finally:
                probe_result["seconds"] = round(time.monotonic() - started, 6)
                p.close()

        started = time.monotonic()
        c.set_progress_handler(lambda: int(time.monotonic() - started >= limit), 10000)
        worker = threading.Thread(target=probe)
        worker.start()
        result = {"query_plan": plan, "rows": count, "timeout_seconds": limit}
        try:
            c.execute(sql, PARAMS)
            c.commit()
            result.update(completed=True, cleanup_seconds=round(time.monotonic()-started, 6))
        except sqlite3.OperationalError as error:
            result.update(completed=False, elapsed_seconds=round(time.monotonic()-started, 6),
                          error=str(error))
            c.rollback()
        finally:
            c.set_progress_handler(None, 0)
            worker.join(timeout=6)
            assert not worker.is_alive()
        assert c.execute("SELECT * FROM search_vector_chunks ORDER BY id").fetchall() == manifest
        if result["completed"]:
            remaining = [r[0] for r in c.execute("SELECT rowid FROM search_vector_embeddings ORDER BY rowid")]
            assert remaining == expected
            result["row_set_correct"] = True
            result["remaining_vectors"] = len(remaining)
        result["concurrent_probe"] = probe_result
        return result
    finally:
        c.close()


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("directory", type=Path)
    parser.add_argument("--rows", type=int, default=25729)
    parser.add_argument("--timeout", type=float, default=10)
    args = parser.parse_args()
    args.directory.mkdir(parents=True, exist_ok=False)
    result = {"python": platform.python_version(), "sqlite": sqlite3.sqlite_version,
              "sqlite_vec": importlib.metadata.version("sqlite-vec"), "dimensions": 512}
    for name, sql in (("official", OLD), ("proposed", PROPOSED)):
        result[name] = run(args.directory / (name + ".db"), sql, args.rows, args.timeout)
        (args.directory / "result.json").write_text(json.dumps(result, indent=2) + "\n")
        print(json.dumps({name: result[name]}), flush=True)
    print("Synthetic files:", args.directory)

Expected Behavior

Project-scoped stale-vector cleanup should avoid repeatedly scanning the project's manifest for each vector, preserve other projects, and finish promptly at this scale.

Actual Behavior

Fresh SQL-only reproduction (same machine, equivalent separately seeded databases):

Measurement	Official query	Proposed rowid join
25,729 rows, 512 dimensions	Interrupted at 10.000422 s; not completed	Completed in 1.151697 s
Concurrent read in WAL mode	Succeeded	Succeeded
Concurrent write (5 s busy timeout)	database is locked after 5.203585 s	Succeeded in 1.191276 s
Result set	Timeout rolled back	Expected 25,472 vectors; manifest and other-project vectors preserved

The original full completion time is unknown, not “10 seconds”; the test intentionally interrupts it. These numbers measure cleanup, not query/search latency or end-to-end reindex latency.

EXPLAIN QUERY PLAN for the official query includes:

SCAN search_vector_embeddings VIRTUAL TABLE INDEX 0:1
CORRELATED LIST SUBQUERY 1
SEARCH search_vector_chunks USING INDEX chunks_project (project_id=?)

The candidate replaces CORRELATED LIST SUBQUERY with a one-time LIST SUBQUERY and a rowid lookup (VIRTUAL TABLE INDEX 5:2!___).

Environment

• OS: macOS 27.0, Apple Silicon / arm64
• Python: 3.12.10
• SQLite linked by Python: 3.47.1
• sqlite-vec: 0.1.9
• Basic Memory: official 0.23.2 SQL; current main SQL also checked as described above
• Python/uv installation; SQLite WAL mode
• No embedding model is needed for this reproduction; vectors are synthetic constants

Additional Context

A separate test invoking the real SQLiteVecIndex.delete_orphans method also reproduced the timeout and writer failure. A locally packaged minimal revision completed that method in 1.413152 s, with the concurrent write succeeding. Small fixtures checked valid vectors, stale hashes, wrong model/status/backend, orphan records, other projects, SQL NULL behavior, repeated cleanup, and rollback after an injected failure. That local revision is being removed in favor of upstream maintenance; it is not an official release.

This appears distinct from #1322's post-completion shutdown issue: the standalone SQL statement is still executing, and the Python-only reproduction does not start a BM CLI or server. I searched existing issues/PRs for delete_orphans, CORRELATED, and SQLite database is locked before filing.

Diagnosis/reproducer were prepared with AI assistance and executed locally; this report contains synthetic data only.

Possible Solution

Use an explicit vector-rowid join inside the candidate subquery rather than referencing search_vector_embeddings.source_hash from the outer DELETE. Keep the existing project/model/backend/status/hash predicates and SQL NULL semantics. The candidate SQL is included in the reproducer above. The separate global orphan cleanup and transaction boundary need not change.