4 ms·
I've used SQLite for toy services "in production", and it was really just as bad as people think it is. Sure, you could handle a large amount of read-only queri
by tveita 4y ago
I've used SQLite for toy services "in production", and it was really just as bad as people think it is. Sure, you could handle a large amount of read-only queries, but it only took a tiny bit of write traffic in the mix to make the random latency spikes jarring.
This was pre-WAL, presumably enabling WAL would help a lot (but is still not the default, so beware). But the caveats were real, it's not like people just took one look at the name and though "'SQLite?' I better put a big warning in our documentation to not use this in production."
- LAC-Tech 4y agoThis was pre-WAL, presumably enabling WAL would help a lot Indeed it would! "WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently." https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html I think the people advocating for SQLite to be used in more places are all assuming write ahead logging is enabled.
- chasil 4y agoThere are many problems with WAL, as acknowledged by Dr. Hipp in the recent olap/duckdb paper. The chief problem that I see with WAL is that it breaks ACID with databases that are ATTACHed, as the documentation shows: https://sqlite.org/lang_attach.html https://sqlite.org/lang_attach.html
- gigatexal 4y agoHow often is attach really used though?
- chasil 4y agoHow often are distributed transactions used elsewhere with two-phase commit? All the time. I suspect this is similar.
- gigatexal 4y agoRight but that’s bending SQLite a lot no? I mean it’s meant to be used as a file that has a sql interface. But I digress.
- taberiand 4y agoI wonder how many times SQLite has been passed over for a more complex solution because people thought they needed highly scalable distributed two phase commit or whatever when all they really needed was a file with an SQL interface
- gigatexal 4y agoI'm guessing your response was sarcastic. I don't get it, though. If you need a distributed two-phase commit DB get one like CockroachDB or something like that. SQLite is not that.
- simonw 4y agoWhat are the other problems?
- chasil 4y agoOK, I'll download the PDF onto my phone and get the quote... ...I forgot how significant these problems are. These are quite serious. "However, WAL mode has notable disadvantages. To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine. Thus, WAL mode does not work on a network filesystem. It is not possible to change the page size after entering WAL mode. In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index." https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%20Present%2C%20and%20Future https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%... https://news.ycombinator.com/item?id=32684424 https://news.ycombinator.com/item?id=32684424
- yread 4y agoIt also complicates backups. I have another process that .backups the database but even when opening it in read-only it creates a .wal file. My first version of the backup script didn't delete it afterwards and the normal process didn't have the right to overwrite the backup's wal file so opening the db failed
- chasil 4y agoMaybe adjust the umask of the backup process?
- simonw 4y agoWAL was added 2010-07-21, so pre-WAL is over 12 years ago now!
- hinkley 4y agoLots of changes with branch prediction in that time too.