What is SQLite FTS5 Full-Text Search?

SQLite FTS5 (Full-Text Search version 5) is an embedded virtual table module developed by the SQLite consortium. It provides industrial-strength full-text indexing and retrieval capabilities directly inside an in-process SQLite database.

While large enterprise architectures deploy distributed search clusters (such as Elasticsearch, OpenSearch, or Algolia), desktop applications built on local-first principles require search engines that run entirely within client memory and storage. SQLite FTS5 is widely recognized as the gold standard for high-performance, embedded, zero-telemetry desktop search.

How an inverted index works in FTS5

When documents are indexed in SQLite FTS5, the engine tokenizes raw text into individual words (tokens) using configurable tokenizers (such as Unicode61 or Porter stemming). It constructs an inverted index that maps each unique word to the specific document IDs and word positions where it appears:

-- Creating an FTS5 virtual table in SQLite
CREATE VIRTUAL TABLE note_search USING fts5(
  title,
  body,
  tags,
  tokenize = 'unicode61 remove_diacritics 1'
);

-- Executing an instantaneous boolean phrase query
SELECT title, snippet(note_search, 1, '<b>', '</b>', '...', 15)
FROM note_search
WHERE note_search MATCH '"zero telemetry" AND macos*'
ORDER BY bm25(note_search)
LIMIT 20;

Key advantages of SQLite FTS5 for local note apps

1. In-process execution: FTS5 executes within the application’s own memory space. There are no socket connections, HTTP handshakes, or inter-process communication overheads.
2. ACID transaction guarantees: Index updates occur within atomic database transactions, guaranteeing that power interruptions or application restarts never corrupt the search catalog.
3. Deterministic Porter stemming: Words like “recording”, “recorded”, and “records” normalize to the stem “record”, enabling comprehensive semantic recall.
4. Sub-millisecond query latency: Even across vaults containing 50,000 Markdown documents and millions of words, FTS5 consistently returns ranked results in under 5 milliseconds on Apple Silicon NVMe drives.

SQLite FTS5 in Oyma

In Oyma on macOS, your notes are indexed into an embedded SQLite FTS5 catalog. Whenever you press ⌘O to trigger the unified command palette or search bar, Oyma executes sub-millisecond FTS5 queries with BM25 relevance scoring.

All index data resides exclusively inside your Mac’s local application support directory. No query, token, or search history ever leaves your device.