Hacker News Reader

Comment by jaequery | Hacker News Reader

is it good enough? yes. in fact, its probably an overkill for most. i think the question of good enough wouldve been perfect for sqlite.

View on HN

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).

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.

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.

This a thousand of times.

My biggest concern for sqlite WAS that it is only embedded and not clients server which is a problem if I have more process trying to access it.

I solved it writing a Redis modules that embed sqlite [1] so you get a client server sqlite for all your relational/ACID need and redis for everything else.

[1]: https://github.com/redbeardlab/redisql

This looks pretty cool.

Thanks, someone else call it the "worst idea ever"...

It is nice to have some positive feedback once in a while.

Definitely if you have suggestions or feedback (the negative) please please share it. Either open an issue, write me an email or keep this conversation going ;)

Best

Reply on news.ycombinator.com