4 ms·

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 L

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_tokenizer https://sqlite.org/fts5.html#the_experimental_trigram_tokeni...

simonw 5y ago
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 https://til.simonwillison.net/django/enabling-gin-index
- slimsag 5y ago
  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-10000-github-repositories 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.
Uberphallus 5y ago
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.

simonw 5y ago

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 https://til.simonwillison.net/django/enabling-gin-index

slimsag 5y ago
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-10000-github-repositories 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.

Uberphallus 5y ago

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.