21–30 of 330 posts
Recently I stumbled over a potential fix[1], which I will try in my next project.
[1] https://ja.nsommer.dk/articles/thread-safe-async-sqlite3-ent...
When not to use sqlite:
- Is the data separated from the application by a network?
- Many concurrent writers?
- Data size > 280 TB
For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better.
SQLite proved to be phenomenal. We spec'ed hardware with enough RAM to hold the FR DB in memory, and damn SQLite is fast enough to keep up with the optimized FR system performing 24M face compares per second. With a 700M face training set, SQLite also proved instrumental in reducing the training time significantly. These daze, if given the opportunity to choose a DB I always choose SQLite. I use SQLite for my personal projects, and I go out of my way to not use MySQL because SQLite is so much faster.
Simon Willison has written about using SQLite for a "Baked in data" architecture which is a super interesting method for some situations: https://simonwillison.net/2021/Jul/28/baked-data/ As he notes https://www.mozilla.org/ uses this pattern: > They started using SQLite back in 2018 in a system they call Bedrock ... Their site content lives in a ~22MB SQLite database file, which is built and uploaded to S3 and then…
Unfortunately it was far too advanced for the org and no one else understood it so it was canned in favour of a connected solution under the guise of ubiquitous internet access being available. This is proving to be a poor technical decision so my solution may have some legs yet.
Our product is a self-hosted IoT & hub unit solution, so we have no requirements to work with thousands of users doing who knows what. For our use case, sqlite is perfect. We don’t need to work with millions of rows, don’t need to stress the relatively low-power server units with another long lived network process, have no requirements of authentication since the user owns all the data, and can easily get insights into the database both during development and during troubleshooting at customer locations.
I’d sooner leave the project than move to anything else.
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.
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?
This [0] is a good article with some benchmarks, misconceptions about speed, and limitations.
My preferred production DB is PostgreSQL. However, for small experiments, SQLite is more versatile due to fewer dependencies, single binary, zero install overhead etc., so I use it often, in particular for research experiments and systems prototyping. The only thing that ever bothered me was the lack of type enforcement, which has since been improved. Production uses: 0 (1 if my Ph.D. thesis code is included, which h…
Glad to hear its type enforcement situation is improving.