← Back to Blog

Preventing SQLite FTS5 Tokenization Hangs on Large Datasets

SQLite's FTS5 (Full-テキスト Search) extension is an incredibly powerful tool for adding lightning-fast search capabilities to local applications. It allows you to query millions of rows for specific text matches in milliseconds.

But if you are building an application that handles user-generated text of unpredictable sizes—like a clipboard manager, a log viewer, or a note-taking app—you might eventually run into a catastrophic performance issue: Tokenization Hangs.

The Problem with Massive Strings

When you insert a row into an FTS5 virtual table, SQLite has to tokenize the text. It breaks the string down into individual words (tokens) based on your chosen tokenizer (e.g., porter unicode61) and adds them to the search index.

Usually, this is practically instantaneous. But what happens if a user copies a 15MB minified JSON payload to their clipboard, or imports a massive unformatted XML dump?

The FTS5 tokenizer will attempt to parse the entire 15MB string as a single continuous stream of tokens. Depending on the exact nature of the text (especially pathological inputs like one giant string lacking whitespace or punctuation), this tokenization process can hit disproportionately expensive code paths in the tokenizer and stemmer. If your insert or trigger runs synchronously on whatever thread issued the write, this can cause noticeable CPU spikes and hang durations.

The Standard (But Flawed) Solution

A common approach to fix this is to handle the truncation in your application layer. Before inserting into SQLite, your backend code truncates the string:

let indexableテキスト = String(fullテキスト.prefix(8192))
db.insert(content: fullテキスト, searchIndex: indexableテキスト)

While this works, it pollutes your application logic. Worse, if you are using an External Content Table (where FTS5 automatically pulls data from a standard SQLite table to save disk space), you don't even have an explicit insert statement for the FTS table. The synchronization is usually handled by SQLite triggers.

The Elegant Solution: The 8,192-Character Trigger Hack

In L2Cache (a native macOS clipboard manager built for developers), we rely heavily on SQLite triggers to automatically keep our primary clips table in sync with our clips_fts virtual search table.

To prevent the app from hanging when a developer copies a massive node_modules error log, we implemented what we call the "8,192-Character Tokenization Hack" directly inside the SQLite trigger definition.

Instead of passing the entire raw new.content string into the FTS5 table, we use SQLite's native SUBSTR() function to strictly cap the indexed text at 8,192 characters:

CREATE TRIGGER clips_ai AFTER INSERT ON clips BEGIN
    INSERT INTO clips_fts(rowid, content, ai_title)
    VALUES (
        new.rowid, 
        SUBSTR(new.content, 1, 8192), -- The 8,192-Character Cap
        new.ai_title
    );
END

Note: If your content can be edited or deleted after insertion, you must also add matching AFTER UPDATE and AFTER DELETE triggers to keep the search index from accumulating orphaned rows or silently drifting from the primary table.

Why This Works Perfectly

By enforcing this limit at the database level:

• Application code stays clean: Your backend still just writes INSERT INTO clips (content) VALUES ('15MB_STRING'). It doesn't need to know about the search index limits.
• Zero data loss: The full 15MB string is safely stored in the primary clips table. Only the searchable index is truncated.
• Guaranteed performance: The FTS5 tokenizer is never fed more than 8,192 characters per row, ensuring predictable, single-digit millisecond execution times regardless of what the user copies.

If you are building an offline-first application that handles unpredictable user inputs, capping your FTS5 indexes via database triggers is one of the most effective defensive engineering practices you can adopt.