SQLite adds ordinary deletes to contentless FTS5 indexes

An index can identify a document without retaining a second copy of its text. FTS5's contentless tables support that arrangement, but their original update interface requires special handling. SQLite 3.43.0, released on August 24, adds contentless-delete tables. They keep the decision to omit stored content while supporting ordinary DELETE and replacement operations.

The change is useful when an application owns document content elsewhere and wants SQLite to answer search queries using row identifiers. It reduces the special treatment needed to remove a document from that search index. It does not turn the index into a source from which the application can retrieve the original text.

I like SQLite's ability to combine relational storage with indexes inside one application. Avoiding duplicate content is a useful option, provided the schema makes responsibility for the original document explicit. This release makes that option easier to maintain during ordinary record changes.

The original contentless contract

The FTS5 documentation at the release date describes a contentless table as one whose content option is an empty string. The table retains the information needed for full-text indexing without storing column contents for later retrieval. Reading a column other than rowid returns SQL NULL.

Original contentless tables do not support ordinary UPDATE or DELETE statements. They also require inserts to supply a non-NULL row identifier. Their special FTS5 delete command provides a deletion route, but that differs from the SQL operations an application usually applies to a table.

Replacement handling has another difference. REPLACE and INSERT OR REPLACE act as regular inserts for an original contentless table. An application cannot assume that naming a familiar conflict policy gives the index the same behavior as a conventional table.

These restrictions matter at the point where application data changes. An index used only during an initial load may be straightforward to populate. An index that tracks editable documents also needs a reliable way to remove old searchable state and install its replacement. That is the part of the contract this release changes.

Creating a contentless-delete table

The new table form adds contentless_delete=1 alongside the empty content option. For a search index with title and body fields, the definition is as follows.

CREATE VIRTUAL TABLE search_docs USING fts5(
  title,
  body,
  content='',
  contentless_delete=1
);

The two options have distinct purposes. An empty content string selects the omission of stored document content. The additional flag selects the new deletion behavior. An application that needs document text must still retrieve it from wherever it maintains that text.

An ordinary insert can associate searchable terms with an application-supplied row identifier. A normal delete can then remove that indexed document.

INSERT INTO search_docs(rowid, title, body)
VALUES(7, 'Storage notes', 'An index tracks searchable terms');

DELETE FROM search_docs WHERE rowid=7;

The new table form also supports INSERT OR REPLACE. That gives an application an option for replacing the indexed representation of a known document. The row identifier remains the connection between that representation and the content maintained by the application.

The documentation recommends contentless-delete tables for new code unless backward compatibility is required. This is a recommendation about choosing a table form when defining a schema. It does not mean an existing contentless table silently changes its behavior when opened by the new library.

Updates require complete indexed values

Support for UPDATE comes with an important condition. Every user-defined FTS5 column must receive a new value. For the two-column example, a supported update names both fields.

UPDATE search_docs
SET title='Revised storage notes',
    body='The document now has replacement searchable text'
WHERE rowid=7;

A statement updating only the title is outside that contract. The table does not retain the original body text as ordinary retrievable content, so a caller should not design an update path that assumes it can obtain that text from the contentless index. The application owning the document can provide its complete indexed representation.

This affects how partial document edits reach the search system. An API may accept an edit to one field, while its indexing step supplies all indexed fields after applying that edit. The external edit operation and the FTS5 update operation need not have identical payloads.

The special FTS5 delete command is not supported by contentless-delete tables. Code written for the original contentless form therefore needs an intentional change when selecting the new form. The familiar SQL operations are available, but the old special command is not an interchangeable alternative.

External content is a different arrangement

FTS5 also supports an external-content table that names another table or view in the same database. When it needs column values, it queries that content table using the configured row identifier. This gives FTS5 a defined way to obtain original values without storing its own duplicate.

The versioned documentation says the user remains responsible for keeping that search index consistent with its content table. It illustrates triggers that maintain the relationship. The presence of a content table does not make all index maintenance automatic.

A contentless-delete design makes a different choice. It does not name a source that FTS5 can query for document values. That can fit an application whose content is maintained elsewhere, but it also means the application must resolve row identifiers and supply full values for updates.

My preference is to choose between these forms based on who owns the document and who can supply its current values. The new deletion support reduces an interface restriction. It does not remove the need for a consistent relationship between searchable terms and the document they describe.

Calendar spans are another explicit contract

SQLite 3.43.0 also adds timediff() and new time-shift modifiers. The date-function documentation from August distinguishes a calendar-aware span from a precise elapsed count. Months have different lengths, so a result expressed in years and months cannot be treated as a fixed number of seconds.

The versioned date implementation defines the function with two arguments. It accepts ISO-8601 strings or Julian day numbers. Unix timestamps are not supported directly by this function. Its result describes the shift that takes the second date to the first.

For an application that needs elapsed days, subtracting julianday() values answers that question directly. Subtracting unixepoch() values expresses a difference in seconds. Choosing one of those calculations avoids interpreting a calendar span as a scalar duration.

The search-table change and the date function both make a previously awkward operation more direct. Their contracts still describe specific data models. A contentless index omits document values, and a calendar span accounts for calendar units. Correct application code follows those definitions when maintaining a document or calculating a time difference.