Hacker News Reader

Comment by rwmj | Hacker News Reader

I love sqlite but it's really difficult to recommend it in a situation where you need parallel r/w access to the database (ie. any web-type situation, even for light loads).

View on HN

The sqlite client implementation details definitely matter. The default one that Django uses to talk to sqlite will lock on parallel reads in addition to the typical issues with parallel writes, due to some specific issue in that client's code. So every single request locks the entire database for everybody.

I think there was a plan to fix this but I don't know if it happened yet because I'm still on the LTS release. And because I don't plan to use sqlite in production anyway.

If care is taken to cache query results and clear on insert/update, one can get stupendous performance from sqlite. I used Apache, mod_lua, and redis to implement a system that does this for example.

SQLite is wonderful for some things but write concurrency is not one of them. This means you really need to segregate reads from writes, and use a different path for that.

I actually think SQLite in general is fairly underrated, but there are important limits.

PostgreSQL is my go-to db, but I love the ability to have the callbacks to SQL functions being in my main code. For lots of data analysis work, the way custom functions and custom aggregates work in SQLite is wonderful.

But again, a chasing hammer is not the same as a machine hammer.

Reply on news.ycombinator.com