SQLite FTS5 Writes: Bulk Build or Trigger Sync?

The choice shows up during imports

For an SQLite app with full-text search, there are two common ways to keep an FTS5 index current. Insert the rows first and build the index once, or create the index and let triggers update it on every write. The first sounds faster for a bulk import. I measured the difference on a fixed 50,000-row workload to see how large it was.

Setup

I used Python 3.12.3 and SQLite 3.45.1 on an x86_64 machine. A seeded generator made 50,000 short notes, each with 25 vocabulary tokens. The same notes went through both paths.

In the offline path, I inserted all rows in one transaction, created an external-content FTS5 table, and ran rebuild. In the trigger path, I created the table and insert, update, and delete triggers first, then inserted the rows in one transaction. Both databases ended with the same indexed terms. I also updated one row and checked that the old term disappeared and the replacement term became searchable.

Each query timing is the median of 40 repeated MATCH queries after the data was loaded. The SQLite page cache was warm. The database file size was measured after building the index.

Results

Method	Insert time	Index build time	Total	Database file	needle query
Insert, then build	0.148 s	0.151 s	0.299 s	18.46 MB	0.0282 ms
Trigger-synced insert	1.168 s	included	1.168 s	17.63 MB	0.0258 ms

On this workload, trigger-synced insertion took 3.9 times as long from an empty database to a searchable one. The rare-term query was effectively the same speed. The trigger-synced file was about 4.6 percent smaller in this run, a difference I would treat as a storage observation rather than a property of either strategy.

The trigger path spent about 0.87 seconds more building this index while rows were inserted. That is small for 50,000 notes, but it can matter when an import has a tight latency budget. It is also not a reason by itself to remove triggers from an application that accepts ongoing writes.

Choosing a path

For a full refresh, bulk loading followed by rebuild is a good option when the application can build a replacement database or index and switch to it after validation. It shortens the import in this measurement and keeps intermediate writes away from the searchable index.

For normal application writes, triggers keep the index aligned with its content table without requiring every call site to remember a second write. The measured overhead is the cost of that simpler consistency boundary. A real workload with smaller transactions, different text, or concurrent readers may shift the tradeoff, so measure the import path that matters to your app.

The experiment used deterministic synthetic text and one local machine. It says nothing about ranking quality or cold-start latency. The benchmark script and raw results are available alongside this article's evidence record.