"SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performance."
https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
It's easy to get really stellar concurrent performance out of SQLite using a many reader, single writer model (ie many threads, single process). In testing we did it easily surpassed Postgres.
My experience pretty much matched what you describe and it was such a great opportunity to really lean on a fabulous piece of software. Given this was many years ago, before ES was stable and SOLR wasn't working well for us, I think it was the right choice. These days, ES is good enough that if I had to do it all over I'd go straight to it. None the less, I don't think SQLite should be ignored as an option when you have high levels of control over data access patterns.
Does anyone use this seriously in production on a typical web service? I wonder about how eg backups and stuff like that work out in real scenarios.
I've always been a big fan of SQLite and this is the one challenge I've always faced.
Can you give some more insights as to how you achieved that?
SQLite has progressed a lot in the last few years. It is no longer advisable to mock it over SQLITE_BUSY et al; you will come unstuck on the Internet very quickly ;-)
Unfortunately, I use SQLite through Python so I'm stuck with the system version :-(
Fair enough if you want to compile your own version, but there's a lot of people and Python devs who don't know how / won't go through the effort and that's what this project is for.
It's also a work in progress and not meant to be released yet. Shoot me an e-mail ajay@plasticityai.com if you have suggestions for how to improve it.
http://charlesleifer.com/blog/compiling-sqlite-for-use-with-...
Describes several handy ways to compile sqlite for use with python (2 or 3), as a system-wide shared library or compiled directly into the python module. This can also be applied to sqlcipher, etc.
If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand?
Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date.
Because anyone can submit links?
Not sure if it's in this library, but I'd want the ability to open a database that's already loaded in memory as a byte array.
But if someone published an alternative APSW wheel with JSON1, ICU, and FTS5 enabled, I'd be happy.
I keep seeing this statement. Why is it considered one of the most well-engineered software?
However, pointing this or any of the other practical shortcomings of SQLite out on hacker news is blasphemy and will invariably get you downvoted into oblivion by people who (apparently) never ran into them.
Either way, the fact that it is DO-178B certified for use on aircraft should tell you something about the reliability of it. It is certainly more reliable than most other software, OSS or otherwise.
I am curious what practical problems you encountered with SQLite. Could you perhaps expand on that?
With the default settings which I semi-affectionately refer to as paranoid mode, an untuned database can start to have worse performance after getting 500,000+ records going. Then things like indexes, RediSQL, and WAL mode start being more necessary rather than just best-practices.
But if you set your pragmas correctly and so on, SQLite scales up just fine. I haven't done a large scale Sqlite base simply due to caution around needing to use a 'real' database in production like SQL server or Postgres, Maria etc. Sqlite is excellent for ephemeral databases to be created, seeded with test data, run tests against, and deleted in repeatable automated testing.
Based on the link to the Expensify article, it sounds like Sqlite can scale up even better than Sql server under some circumstances.
But I have barely tried using it in production because of that aforementioned caution. What are some pitfalls to watch out for?
See: https://www.sqlite.org/testing.html https://www.sqlite.org/hirely.html
The bit about testing.
[1]:https://github.com/plasticityai/supersqlite/blob/01e54bbb829...
This has not been our experience. Our experience is that it sort of bogs down around 12GB in file size. linux box, ext3/4, spinning disk (not SSD)