This article doesn't even get into SQLite's full-text search feature, which really is surprisingly good. It's also very widely used - desktop and mobile apps that offer a search feature often build that using SQLite, so it's a very robust and well-trodden path at this point.
I've written a few tools to help work with FTS in SQLite:
I also put together this article exploring classic search relevance algorithms with SQLite - SQLite FTS5 has relevance built in, but FTS4 leaves it as an exercise for the developer which makes it a really fun tool for understanding how algorithms like BM25 actually work: https://simonwillison.net/2019/Jan/7/exploring-search-releva...
SQLite's FTS5 also has experimental support for trigram indexing[0], which is quite nice for having substring matches instead of just whole-token matches with LIKE and GLOB syntax.
No support yet for regexp searches over trigram indexes, though, unfortunately (Postgres has this.) I personally think that much more expressive indexed search syntax (more so than just regexp, even) is going to be the future and am actively working on a search engine in Zig for this purpose.
Actually pg_tgrm, like all things Postgres generally, is superior. It can rank searches by similarity (e.g. proportion of query trigrams present in the matching column's trigrams) and SQLite doesn't, which is a dealbreaker when dealing with anything that potentially includes typos or mutable word order; in many situations you wanna match (or rank high) 'Johnny Doe' when the query is 'Doe, John', also considering that languages with clear markings of grammatical case rely less on word order (like most Slavic ones) pg_tgrm is a godsend for properly hitting rows with such user input queries.
e.g. I discuss the fact that pg_tgrm indexes being built does not take advantage of multiple cores, so at large scales you'll benefit from splitting data into multiple tables.
Comments
This article doesn't even get into SQLite's full-text search feature, which really is surprisingly good. It's also very widely used - desktop and mobile apps that offer a search feature often build that using SQLite, so it's a very robust and well-trodden path at this point.
I've written a few tools to help work with FTS in SQLite:
- https://sqlite-utils.datasette.io/en/stable/cli.html#configu... - command-line utilities for enabling full-text search against an existing SQLite table
- https://sqlite-utils.datasette.io/en/stable/python-api.html#... - that same functionality as a Python library API
- https://docs.datasette.io/en/stable/full_text_search.html - Datasette spots full-text search enabled tables and adds a search interface to them
I also put together this article exploring classic search relevance algorithms with SQLite - SQLite FTS5 has relevance built in, but FTS4 leaves it as an exercise for the developer which makes it a really fun tool for understanding how algorithms like BM25 actually work: https://simonwillison.net/2019/Jan/7/exploring-search-releva...
SQLite's FTS5 also has experimental support for trigram indexing[0], which is quite nice for having substring matches instead of just whole-token matches with LIKE and GLOB syntax.
No support yet for regexp searches over trigram indexes, though, unfortunately (Postgres has this.) I personally think that much more expressive indexed search syntax (more so than just regexp, even) is going to be the future and am actively working on a search engine in Zig for this purpose.
[0] https://sqlite.org/fts5.html#the_experimental_trigram_tokeni...
Actually pg_tgrm, like all things Postgres generally, is superior. It can rank searches by similarity (e.g. proportion of query trigrams present in the matching column's trigrams) and SQLite doesn't, which is a dealbreaker when dealing with anything that potentially includes typos or mutable word order; in many situations you wanna match (or rank high) 'Johnny Doe' when the query is 'Doe, John', also considering that languages with clear markings of grammatical case rely less on word order (like most Slavic ones) pg_tgrm is a godsend for properly hitting rows with such user input queries.
Oh cool I didn't know PostgreSQL can use those for regular expressions! I was playing with those indexes for PostgreSQL just this morning: https://til.simonwillison.net/django/enabling-gin-index
If you're using Postgres Trigram indexes at larger scales, I might suggest reading over just the conclusions at the end of my article here:
https://devlog.hexops.com/2021/postgres-regex-search-over-10...
e.g. I discuss the fact that pg_tgrm indexes being built does not take advantage of multiple cores, so at large scales you'll benefit from splitting data into multiple tables.
Doesn't support languages other than English. At least, that was the case when I last tried it.
Is there a way to easily use SQLite FTS on Django?
Not that I've seen. You'd likely have to use a raw SQL query - or the RawSQL class as part of an expression in the ORM.