SQLite FTS5 Extension
sqlite.org
sqlite.org
It also ships as part of the Python standard library, so if your machine has Python installed you have a high quality search engine ready to use without installing anything else.
I have a CLI tool (and Python library) for working with it here: https://sqlite-utils.datasette.io/en/stable/cli.html#configu...
Other than getting all my git access revoked and receiving a very concerned email from our security team, it was a great project.
I assume everything was fine once they explained what was going on.
For instance searching for ‘___missilestrike’ in Kyiv would ideally surface all incidents, regardless of launch mode (surface-to-surface, air-to-surface…), range, propulsion, warhead, or guidance system. In these languages a number of these would form the prefix of that compound word.
[0]: https://github.com/rhashimoto/wa-sqlite [1]: https://github.com/iansinnott/prompta/blob/master/src/lib/mi...
I switched to Pagefind [2] afterwards before finding out a sql.js-httpvfs [3] fork of sql.js that removes exactly the need to fully download a database (with HTTP range requests). I haven't got the chance to test sql.js-httpvfs out though, but it looks pretty sound and could be much more flexible than Pagefind. (Previously discussed at https://news.ycombinator.com/item?id=27016630 .)
We use it in the Fossil SCM project and users have reported success with Chinese and Russian, so it presumably works fine with any European/Germanic language.
> and the default stemming does not seem to work properly (given some short tests).
The Porter Stemmer is documented as only being useful for English.
Should work well for German, I’m using it with Nordic languages.
Just that you would need to tokenize the right characters for your target language (e.g. ÜüÖöÄäßẞ¹), maybe those are already included in Unicode61.
¹: Yeah there is now a capital Eszett
Do you have to do anything special to guarantee each sqlite instance executes in parallel with the others on its own core? Does the language you’re calling them from support concurrency or parallelism?
- 15m row db will return a search for an extremely common term in ~3s (9m rows have that term);
- When adding terms, so that only ~200k rows have the combination, response is ~2s
- That 15m db was in 16 shards. (I’m actually not sure why it should be so slow; doesn’t actually make sense given that a single 1m row db is not that slow, might look into it but not a huge priority)
- For a ~200k row db, search is ~150ms for three terms that appear in ~1.5k entries
- Obviously the network io is the slower piece anyway
This perf is fine for our use case — a research system for internal users. Certainly worth the trade-off of not having to deal with a more complex database like elasticsearch, or even PG with the tantivy extension (which we might switch to some day).
For sharding — we only shard the huge one. To make sure bm25 rankings are still _roughly_ the same as they would be if we did not shard, we just randomised the rows assigned to the num_core shards. It’s a 16 core machine so 16 shards.
We know/ensure each db file gets its own core because we use aioprocessing in python. It handles both multicore and async. Running a query with htop shows all the cores light up (unlike with unsharded).
It’s all about trade-offs in the end. We cache the first search then pre cache the next page (and so on) so the user only has to wait 2-3s when searching the big db for that initial search and after that it’s pretty snappy. Most searches are on much smaller dbs (thousands to hundreds of thousands of rows) and results there are often 10ms or something. No user complaints so far.
I wish it would have an API instead where I could put the parts of the query exactly (as nodes in a tree) without the need to translate to this irregular syntax.
The new one lives here but is under heavy construction: https://v2.sveltesociety.dev
Ironically, the Search doesn't work on the deployed version at the moment.
Edit: Looks like the search actually works if you're logged in.
It’s easy and works well
It is slower than Realm in some benchmarking (I forget if it’s reads or writes) and might be less memory friendly (Realm does lazy evaluation). You need to use some third party open source to make sqlite faster by precompiling model definitions, which I didn’t bother with. I currently only use it for fts5 and Realm for other needs, but would like to try using it for more.
I also recommend looking at https://skip.tools which has cross platform SQLite for iOS and Android (you write your app in swift and SwiftUI and it generates the Android project and kotlin code)
Btw I use GRDB in my iOS/macOS app here: https://reader.manabi.io Manabi Reader, a Japanese learning app. I use SQLite for dictionary searches which works ok for Japanese only because I’m only searching dictionary expressions and not sentences
It was good at finding the text I was searching for but in terms of ranking them, it felt like it needed a lot of post processing.
See https://www.sqlite.org/fts5.html#synonym_support for more details on the different approaches for implementing synonyms in custom tokenizers.
[1] https://www.sqlite.org/fts5.html#the_fts5vocab_virtual_table...
Although instead of levenshtein I use spellfix (maybe it uses that under the covers? not sure). If there is no match from the first search, I use the sqlite spellfix extension [0] to find matches. Then feed those candidates into the terms.