4 ms·

It's become popular to talk about how scalable SQLite is lately, but let's not forget the elephant in the room, it only allows a single writer at a time. It's

It's become popular to talk about how scalable SQLite is lately, but let's not forget the elephant in the room, it only allows a single writer at a time. 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.

sicp-enjoyer 4y ago
While most databases have more granular concurrency control, almost all application parallelism is handled by locking on critical resources, and having others wait for those resources become available. That's exactly what SQLite does, and it's not a big deal. If you have multiple processes, one might occasionally have to wait a few ms for the other to finish.
chasil 4y ago
It appears that a development branch for a new concurrent write mode has been launched. 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/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...
bob1029 4y ago
> it only allows a single writer at a time. 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.
- aynyc 4y ago
  How did you managed to do 1gb/sec? I tried everything I can think of, the most I managed to do 100-150MB/sec. As table grows, it slows down more and more.
- fmajid 4y ago
  The main thing to keep in mind is to keep your transactions short and not do unrelated tasks like calling REST APIs while holding a lock, otherwise readers will suffer.
gregors 4y ago
A long time ago I was on a project that used SQlite in production. At the time WAL mode didn't exist and the writer blocked the reads as well. We worked around the single writer issue by having multiple db's open. In our case it worked out due to the way our data was partitioned, I'm not necessarily advocating using that technique these days.

sicp-enjoyer 4y ago

While most databases have more granular concurrency control, almost all application parallelism is handled by locking on critical resources, and having others wait for those resources become available. That's exactly what SQLite does, and it's not a big deal. If you have multiple processes, one might occasionally have to wait a few ms for the other to finish.

chasil 4y ago

It appears that a development branch for a new concurrent write mode has been launched. 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/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

bob1029 4y ago

> it only allows a single writer at a time. 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.

aynyc 4y ago
How did you managed to do 1gb/sec? I tried everything I can think of, the most I managed to do 100-150MB/sec. As table grows, it slows down more and more.
fmajid 4y ago
The main thing to keep in mind is to keep your transactions short and not do unrelated tasks like calling REST APIs while holding a lock, otherwise readers will suffer.

gregors 4y ago

A long time ago I was on a project that used SQlite in production. At the time WAL mode didn't exist and the writer blocked the reads as well. We worked around the single writer issue by having multiple db's open. In our case it worked out due to the way our data was partitioned, I'm not necessarily advocating using that technique these days.