News for hackers. Stuff that matters.
Re:Ask HN: Have you used SQLite as a primary database? by divbzero on Monday April 25, 2022 @01:04PM (#31157716)
The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern?

[1]: https://www.sqlite.org/whentouse.html

Re:Ask HN: Have you used SQLite as a primary database? by simonw on Monday April 25, 2022 @01:12PM (#31157836)
I think the way it's worded in that SQLite documentation page is still accurate:

> SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

If your application needs to support hundreds of concurrent writes a second you shouldn't use SQLite. But that's a pretty high bar!

Re:Ask HN: Have you used SQLite as a primary database? by zoomablemind on Monday April 25, 2022 @05:33PM (#31160950)
Also it should be noted that SQLite is a library, so it charges against the quotas for the main process.

This may become an issue with large run-time datasets even in read-only shared access scenarios.

Re:Ask HN: Have you used SQLite as a primary database? by woah on Monday April 25, 2022 @01:15PM (#31157878)
Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in more contention. I ended up just going back to non-WAL mode and implementing an exponential backoff in bash to retry writes.
Re:Ask HN: Have you used SQLite as a primary database? by srcreigh on Monday April 25, 2022 @02:53PM (#31159101)
are you ingesting the data under 1 transaction? This is a common SQLite issue as writes aren't slow but transactions are. By default 1 write = 1 txn but you can put millions of writes into 1 txn and get many orders of magnitude speedup
Re:Ask HN: Have you used SQLite as a primary database? by woah on Monday April 25, 2022 @06:23PM (#31161545)
Yes, each row is written with one call to the sqlite command line. I was going to do something where I wrote inserts into a file and flushed them into sqlite in batches, but sqlite is not the bottleneck, so once retries were taken care of, it was good enough.
Re:Ask HN: Have you used SQLite as a primary database? by hampelm on Monday April 25, 2022 @01:29PM (#31158064)
Thanks for this comment! I had tried some bulk imports into SQLite and mostly gave up after hitting similar limits, thinking I was doing something wrong with the configuration.