Ask HN: Have you used SQLite as a primary database?

Re:Ask HN: Have you used SQLite as a primary database? by ajani on Monday April 25, 2022 @07:09AM (#31152910)

Yes. For http://ht3.org which is a search engine I wrote for tech related articles. It works really well. It uses the fts5 extension, that allows full text searching. There are over a million indexed pages and it’s no trouble.

[ Parent | on HN ]

Re:Ask HN: Have you used SQLite as a primary database? by lep on Monday April 25, 2022 @08:18AM (#31153417)

I'm also using fts5 for some small projects but i haven't looked too deeply into it so i'm wondering if you have any interesting insights. Like what kind of index/options do you use? Maybe the trigram index? And your "across boundaries" mode is just word* in fts syntax?

[ Parent | on HN ]

Re:Ask HN: Have you used SQLite as a primary database? by ajani on Tuesday April 26, 2022 @04:30AM (#31165235)

Yes. Acute observation. It’s actually two separate indexes. A trigram index for sub word searching. Word* is exactly right and the other is a Unicode61 index with porter stemming (also remove diacritics). Trigram tends to work well with abbreviations and such. Whereas porter works well for general searches.

[ Parent | on HN ]

Re:Ask HN: Have you used SQLite as a primary database? by lep on Tuesday April 26, 2022 @05:30AM (#31165499)

Interesting. I might play around using two indexes and try to combine both search results. This could be nice for my less technical users.

[ Parent | on HN ]

Re:Ask HN: Have you used SQLite as a primary database? by barryhennessy on Monday April 25, 2022 @07:26AM (#31153038)

> The irritant-free web

This is a very respectable goal. I wish you great success!

Good full text search without pulling in another dependency would be quite a win. I'll add fts5 to my reading list. :)

Out of interest, what kind of compute and storage resources do you have underneath that?

[ Parent | on HN ]

Re:Ask HN: Have you used SQLite as a primary database? by ajani on Monday April 25, 2022 @08:06AM (#31153334)

Thank you for the appreciation :)

It’s a linode. Shared cpu Plan.

1 CPU Core 50 GB Storage 2 GB RAM

It’s just $10 per month.

With linode even shared cpus are powerful and I’m yet to hit any overload. I’m sure it will at some point, I might upgrade then.

[ Parent | on HN ]