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.
[1]: https://www.sqlite.org/whentouse.html