The Delete That Was Refused

A search index that quietly stops deleting is a strange kind of broken. Nothing errors. Nothing wrong appears on screen. Results look right for months. Then one day a search returns nothing, for a term you can see with your own eyes in a note you have open.

This is how that happens, and why it took counting rows by hand to notice.

The setup

The notes feature indexes every note twice: once into SQLite’s FTS5 for keyword search, once into sqlite-vec for semantic search. Both indexes are keyed on the id of a chunk row, so all three tables line up:

note_chunks(id, note_id, text, ...)

CREATE VIRTUAL TABLE vec_note_chunks USING vec0(
  chunk_id integer primary key,
  embedding float[1024] distance_metric=cosine
)

CREATE VIRTUAL TABLE fts_note_chunks USING fts5(
  title, text, content=''
)

That content='' is the interesting part. It makes the FTS5 table contentless.

What contentless means, and why you would want it

An FTS5 table normally stores two things. There is the inverted index, which maps each token to the rows containing it, and there is a private copy of the text that was indexed. The copy exists so FTS5 can return the original column values, and so it can re-read a row when that row changes.

If you already keep the text somewhere else, that copy is pure duplication. We keep it in note_chunks.text, so a contentless table halves the storage and changes nothing about what searches can find. It is a sensible optimisation and a well-documented one.

The cost is stated plainly in the SQLite documentation:

Contentless FTS5 tables do not support UPDATE or DELETE statements, or INSERT statements that do not supply a non-NULL value for the rowid field.

The reason is mechanical rather than arbitrary. To remove a row from an inverted index, the engine has to know which tokens that row contributed, so it can subtract exactly those postings and leave every other row’s entries intact. It normally works this out by re-reading its stored copy of the text and tokenizing it again. With no stored copy, it cannot reconstruct the token list, so it will not guess. It refuses:

cannot DELETE from contentless fts5 table: fts_note_chunks

For years the only way around this was to hand the original values back to FTS5 yourself, through a special command spelled as an insert:

INSERT INTO ft(ft, rowid, a, b, c) VALUES('delete', 14, $a, $b, $c);

You supply the values that were indexed, FTS5 re-tokenizes them, and subtracts. It works, but it means every deletion path has to still have the original text on hand, which rather undercuts the point of not storing it.

SQLite 3.43.0 fixed this properly by adding contentless-delete tables:

CREATE VIRTUAL TABLE fts_note_chunks USING fts5(
  title, text, content='', contentless_delete=1
)

These keep just enough extra bookkeeping to support a real DELETE without the original text. The documentation is unambiguous about which you should reach for:

Unless backwards compatibility is required, new code should prefer contentless-delete tables to contentless tables.

I had copied the older form.

Three things had to line up

A single mistake is usually visible. This one hid because three separate, individually reasonable decisions stacked.

First, the error was discarded. The purge path looked like this:

Sqler.sql(db, "DELETE FROM fts_note_chunks WHERE rowid = ?", [cid])
Sqler.sql(db, "DELETE FROM vec_note_chunks WHERE chunk_id = ?", [cid])

Sqler.sql/3 returns {:error, reason} rather than raising. Both return values went nowhere. Fire-and-forget is a perfectly normal shape for a delete, right up until the delete is refused.

Second, only one of the two indexes was affected. The chunk row itself is an ordinary table and deleted fine. The vector row deleted fine too, because vec0 supports DELETE. Only the FTS side leaked, so nothing about the code’s structure looked suspicious and the two adjacent lines behaved differently for reasons invisible at the call site.

Third, and this is the one that matters, the results still looked correct. Keyword search returns rowids from FTS, then hydrates each one back to its note:

with [chunk_row] <- Sqler.sql(db, "SELECT * FROM note_chunks WHERE id = ?", [chunk_id]),
     ...
else
  _ -> nil       # then dropped by Enum.reject(&is_nil/1)
end

A stale index row points at a chunk id that no longer exists. The lookup fails, hydrate returns nil, and the result is filtered out before anyone sees it. Deleted content never appears in search results. The user-visible behaviour is exactly what you would want.

So the obvious test passes. Mine did. I deleted a note, searched for a word that only that note contained, got zero results, and ticked the box. The assertion was true. It was also measuring the wrong thing.

Why a mask is worse than a failure

If stale rows were merely inert, this would be a tidiness problem. They are not inert, because the LIMIT runs inside SQLite, long before hydration gets a say:

SELECT f.rowid, bm25(fts_note_chunks) AS rank
  FROM fts_note_chunks f
 WHERE fts_note_chunks MATCH ?
 ORDER BY rank
 LIMIT ?

SQLite ranks every match in the index, alive or dead, takes the best k, and hands them over. Hydration then discards whichever of those are dead. Nothing goes back to ask for replacements.

Work it through. You have two hundred notes, you have deleted or edited a hundred and fifty of them over a few months, and you search with k = 10. The index still holds entries for all two hundred. If the ten best-ranked matches happen to be dead rows, all ten are discarded and you get no results at all, for a query with live matches sitting right there. No error, no warning, an empty page.

Note that editing counts as much as deleting. Every update purges the note’s chunks and re-indexes it, so an actively maintained note leaves a trail of dead rows behind it. The most-used notes rot fastest.

Two smaller effects ride along. bm25 scores from corpus statistics, including the total document count and how many documents contain each term, so dead rows skew the ranking of the results that do survive. And the index grows without bound, since nothing was ever removable.

The failure mode is therefore: silent, gradual, worst for your most active content, and shaped like “search is a bit rubbish lately” rather than like a bug.

What actually found it

Not the search assertion. Counting rows:

chunks before delete   [[1]]
chunks after           [[0]]
fts total after        [[3]]     <- should have been 1
vec total after        [[1]]

The chunk table emptied and the vector table was right, but the FTS table held three rows where the surviving note accounted for one. That number had no innocent explanation, and running the DELETE by hand produced the error immediately.

The lesson generalises past FTS5. A test that asserts on user-visible output cannot see corruption that the read path is masking. If a query filters, joins, or drops anything on its way to the caller, then testing the caller’s view tests the filter as much as the data. To see the data you have to assert on the invariant directly: after deleting a note, every table that referenced it should be empty of it. That assertion would have failed on day one.

The fix, in two parts

The schema:

CREATE VIRTUAL TABLE IF NOT EXISTS fts_note_chunks USING fts5(
  title, text, content='', contentless_delete=1
)

And the discarded error:

defp check_delete(db, statement, id, label) do
  case Sqler.sql(db, statement, [id]) do
    {:error, reason} ->
      Logger.warning("Notes: #{label} delete failed for chunk #{id}: #{inspect(reason)}")
    _ -> :ok
  end
end

The first makes deletion work. The second is the one that matters longer term. The bug survived as long as it did because nothing anywhere looked at what the database said in response. A logged warning would have turned a silent months-long decay into a line in the log on the first delete.

Worth knowing when you set this option: contentless-delete tables support DELETE and INSERT OR REPLACE, but they do not support the old special 'delete' command. The two mechanisms are alternatives, not layers. An UPDATE must also supply new values for every user-defined column. And the option can only be set when the table is created, which matters for the next section.

The same bug, elsewhere

BlogStore in the same codebase uses the identical pattern: fts_blog_chunks is contentless, and its delete path removes by rowid. Deleting a blog post leaves its index entries behind, with the same hydration mask and the same LIMIT k consequence.

It is untouched for now because it is not a one-line change. Virtual table options are fixed at creation, so there is no ALTER that adds contentless_delete=1. It needs a rebuild: create the new table, repopulate from blog_chunks, which still holds all the text, and drop the old one. The repopulate step largely exists already as a backfill function. It is straightforward work, but it is a migration rather than an edit, and it deserves its own change and its own verification.

The short version

Contentless FTS5 saves real space and quietly forbids deletion. If your delete path ignores return values, the index silently accumulates dead rows. Your search results stay correct, because the join back to real rows drops the dead ones, so nothing looks wrong. Meanwhile the LIMIT is being spent on rows that will be thrown away, and one day a search for something that plainly exists returns nothing.

Use contentless_delete=1 on anything new. Check what your database returns when you tell it to delete something. And when you test a delete, assert that the rows are gone, not that the search looks tidy.