It's obviously easier to manage and maintain due to it being an embedded database, but that seems to be a very separate issue from the data structure involved, which definitely has disadvantages compared to a typical SQL system.
It's obviously easier to manage and maintain due to it being an embedded database, but that seems to be a very separate issue from the data structure involved, which definitely has disadvantages compared to a typical SQL system.
This is true. However, in my testing with a relatively old NVMe device, I can push well over a gigabyte/second to the database with that single connection. One trick I employ is to only ever maintain 1 connection and to use an inter-thread messaging framework like Disruptor to serialize everything beforehand to a single batched writer. Another, much simpler option, would be to just lean on the fact that SQLite serializes all writes by default and to take a little bit of a performance hit on the lock contention (assuming a small # of writers).
I know for a fact that I can get more write throughput out of SQLite in an in-process setting than I could if I had to go across the network to a hosted SQL instance. In many cases this difference can be measured as more than 1 order of magnitude, both in latency and throughput figures.
Going vertical on hardware in 2022 is an extremely viable strategy.
These appear to be page-level locks, and row-level locks should not be expected for this effort.
https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...