Don't use SQLite for production, for all the love I have for it, the client libraries are usually locking accesses and don't work properly with concurrent reads/writes.
It's designed for single client, and works very well in production for single client. Mobile apps, desktop apps, and anywhere you can serialize access it works great.
Let's say "don't use it for production for a multiuser server app" then I agree. But that isn't really a supported scenario at all.
Concurrent writes block each other, but following one-time "pragma journal=wal", read and writes can work concurrently, and you can expect x2-x5 performance (or ~2% degradation, depending on use pattern, but most people see x2 performance).
The cost is that access through network e.g. NFS or SMB in wal mode is impossible (but you shouldn't have done that anyway), and that you can't just ship the sqlite file - ship a dump/backup instead, or you'll have to do recovery on the wal file you sent.
Of course, it's a good idea to use pgsql from the get-go; but SQLite deserves more credit than it gets, and is much more capable than it is usually assumed to be.
If your site is light on writes and always will be, I think SQLite is a good choice. Especially if it reduces the complexity of the system up front. I use it for a lot of personal projects that never gain more than a few thousand impressions a month.
SQLite is also not too difficult to switch to a more advanced SQL in the future.
That's fair. As an aside, while it solved your crash issue and obviously scales, when you switched did you get an immediate measurable (and noticeable) performance hit due to the overhead of a proper concurrent database?
I don't mean to be dense but why did you just say "SQLite is a proper concurrent database as well" after sharing a story to the contrary and advising "Don't use SQLite for production".
I'm just trying to understand your advice and learn from your experience, I am just confused by this followup.
It's not an issue with SQLite itself, the python bindings don't work for concurrent access (and I think some other languages' bindings as well). If you're going to embed SQLite yourself, I think it works fine (I haven't tried it).
In theory, SQLite works quite well with concurrent accesses, I have just never tried it because the libraries in the languages I've used didn't work well for that.
ohhhhh, I get it. Yes, you mentioned this in your original comment ("the client libraries are usually locking accesses") but I guess I read too quickly.
Though unless you did a truly comprehensive shootout it might be fairer to write: "Be careful using sqlite in production, the client libraries I tried in Python did not handle concurrent reads/writes, whereas clients for postgresql handle it just fine."
