SQLite FTS5 performance on 2012 HDD-only server

I'm developing an app that uses the FTS5 module in SQLite for search functionality and it just so happens that I want to know in advance what I can expect from it. Unfortunately, there are hardly any articles on the web that explicitly test FTS5 INSERT/SELECT queries speed — besides LLM-generated articles full of nonsense, but with good SEO — so this blog entry is all about that.

Hardware

Dedicated server straight from 2012, running on Debian 13.1:

• CPU: Intel Xeon E3-1220 V2
• RAM: 32GiB DDR3 1600 MHz
  • 4 x Micron 18KSF1G72AZ-1G6P1 (8 GiB)
• Storage: 7.3TB, RAID 0 (306 iops)
  • 2 x Toshiba MC04ACA400E (3.6T)

Why use HDD-only system from 2012 in 2025? That's a good question, moving on. ¹

Database

I wanted to test FTS5 engine with relatively small amount of data, so I took hackernews dataset (all posts from HN til 2024) end inserted it into one table and the numbers are:

• 28GB in size (unicode61, porter)
• 74GB in size (trigram)
• 41'400'000 rows, 4 columns (by, text, title, url)

What's the Unicode61, Porter and Trigram?

Those are tokenizers that are available by default with SQLite3 FTS5 engine.

Porter itself is a wrapper around Unicode61 and also allows you search terms like "correction" to match similar words such as "corrected" or "correcting". And Trigram extends FTS5 to support substring matching in general, instead of the usual token matching, which is good for fuzzy search (and hence triple the size of the database).

It worth saying that default tokenizers are optimized mostly for English – good luck with searching anything that requires word segmentation support. If you want to support CJK, then use something like better-trigram. Testing this is out of scope of this blog entry.

SELECT queries performance with Unicode61 tokenizer

Let's start with realistic scenario and then we will bench it for real.

I want to search for 'test' everywhere: in the user's nickname, the text, the URL and the title. Also, I don't want to limit myself with pity LIMIT statements. Let's see:

SELECT * FROM hn('test');

Just shy of 385345 results — a pretty good amount to display to a user on a single page. How long did that take?

real    5m51.913s

Come to think of it maybe displaying 385345 results is suboptimal. Let's add LIMIT after all:

SELECT * FROM hn('test') LIMIT 50;

real    0m0.027s
user    0m0.019s
sys     0m0.008s

Much better! Let's also try a little bit more complicated queries

-- This equals to "Airbnb AND ruins AND everything"
SELECT * FROM hn('Airbnb ruins everything');

4 results
real    0m0.040s
user    0m0.024s
sys     0m0.016s

-- phrase "crypto scam" must appear at start of a column
SELECT * FROM hn('^crypto + scam')

13 results
real    0m0.114s
user    0m0.028s
sys     0m0.008s

...you know what? That's pretty good for my case. But to find more results, we can utilize Porter and Trigram.

SELECT comparison between Unicode61, Porter and Trigram

Let's do same queries as before and also record amount of results for each tokenizer. Those queries are being run once without prior runs, so no caching is involved.

We can clearly see that Trigram struggles, but sometimes returns the most results. What if we make some queries that utilize unique feature about Trigram – that it can be queried by a sequence of characters, not only by complete tokens?

So while Trigram is 3 times the size of Unicode61 database and quite a bit slower in cases when matching rows by tokens it is much faster in LIKE and GLOB queries...

But not with regex – that's linear scan for every party involved.

Benchmarkin'

Created using little python script that tries various PRAGMA combinations to check changes in INSERT speed as well as SELECT performance.

There's also quite a few variables:

• The performance measurements are specific to the Python sqlite3 standard library, which might not be representative of the underlying SQLite capabilities. And I also didn't bother with testing in other languages and different libraries.
• SELECTs are run once per benchmark, which means SQLite3 can't cache the response. Every benchmark creates a new SQLite3 database.

Run settings:

Runs: 3
Dataset limit: 100000

We run every benchmark 3 times, 100K rows in the database (around 5MB for unicode61, 15MB for trigram).

Query patterns:

QUERY_PATTERNS = {
    "simple_and": ["easy simple", "database performance optimization", "for some reason really long and"],
    "phrase_search": ['"machine learning"', 'ai + shit', '"data science"'],
    "prefix_search": ["python*", "data*", "web*"],
    "initial_token": ["^python", "^data", "^web"],
    "near_operator": ["NEAR(rust sqlite, 5)", "NEAR(react htmx, 3)", "NEAR(ml shit, 2)"],
    "column_filter": ["title: python", "text: database", "by: dawg"], "prefix_and_initial_token": ["^python*"],
    "complex": ["NEAR(rust python, 5) performance*", "^performance NEAR(rust python, 3)"]
}

Those are used to calculate SELECT timing median.

Sorting

Tables are sorted by Insert Time (s) – this is how much time was required to insert the whole dataset.

Maximum Performance Combinations

FTS5 table config: {'prefix': '2 3'}
FTS5 runtime config: {'pgsz': 60000, 'automerge': 16, 'crisismerge': 8}
Tokenizer used: porter
Transcation strategy: batch_500_commit_5000

FTS5 Tokenizer Performance

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Individual PRAGMA Performance

Synchronous

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Temp Store

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Cache Size

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Journal Mode

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Page Size

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Mmap Size

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Locking Mode

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Secure Delete

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Threads

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Fastest Performing Combinations (defined in INSERT_COMBOS)

Tokenizer used: unicode61
Transcation strategy: batch_1000_commit_1000

Transaction Strategy Impact

PRAGMAs: {'journal_mode': 'OFF', 'synchronous': 'OFF', 'locking_mode': 'EXCLUSIVE'}
Tokenizer used: unicode61
Transcation strategy: batch_10000_commit_10000