Repository navigation
Delete sqlite-vec vectors by rowid equality, in rowid order, which vec0 looks up instead of scanning - #1863
Draft
edwinyyyu wants to merge 2 commits into
Draft
Delete sqlite-vec vectors by rowid equality, in rowid order, which vec0 looks up instead of scanning#1863edwinyyyu wants to merge 2 commits into
edwinyyyu wants to merge 2 commits into
Conversation
`delete` removed a collection's vectors with `rowid IN (...)`, which vec0 plans as a scan of the whole table, so each delete read every vector in the collection. It now runs `rowid = :rowid` once per row, as the upsert path does, which vec0 plans as a lookup of one row. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Unsorted, the rowids come in uuid-index order, and a large delete took longer row by row than the scan it replaced. In rowid order, a delete is no slower than one scan of the table at every measured size, and far faster when it removes few rows. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Contributor
Author
|
Not clear that this is worth it. |
edwinyyyu
marked this pull request as draft
October 30, 2026 22:59
This branch has not been deployed
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Purpose of the change
SQLiteVecVectorStoreCollection.deleteremoved a collection's vectors withDELETE FROM [vec] WHERE rowid IN (...). sqlite-vec plansrowid IN (...)outside a KNN query as a scan of the wholevec0table, so each delete read every vector in the collection, and took longer as the collection grew. It now deletes withrowid = :rowid, one statement per row, as the upsert path already does; sqlite-vec plans that as a lookup of one row. The rows go in rowid order: in the order the uuid index returns them, a large delete took longer than the scan it replaces.This is the delete half of #1862. The issue's other half,
_fetch_vectors, needs a choice between forms and is left to the issue.Verification
At this PR's head
07cadc40c, on 2026-10-27:ruff checkandruff format --checkclean;ty checkclean as CI runs it (uv run --frozen --all-extras ty check --project packages/server);test_sqlite_vec_vector_store.py63 passed, including its delete tests.Measured through the store with 64-dimensional vectors, on sqlite-vec 0.1.9 and SQLite 3.50.4.
A delete of 2 records, median of 20, as the collection grows:
mainat d5a39a8One delete from a collection of 100,000 records, median of 3, each on a fresh copy of the same database:
mainat d5a39a8e5beea591)At 10,000 records deleted,
mainand this PR are within the runs' spread (0.287–0.498 s and 0.307–0.450 s).The query plans behind it:
DELETE FROM vec WHERE rowid IN (5, 6)SCAN vec VIRTUAL TABLE INDEX 0:1(full scan)DELETE FROM vec WHERE rowid = 5SCAN vec VIRTUAL TABLE INDEX 1:2!___(one row)🤖 Generated with Claude Code