I suggest you run some simple load tests that do inserts, you'll find WAL is not sufficient for concurrency. Multiple reader, single writers handle this well though.
You can put all your writes in a queue to be processed by a finite set of writers. I think most CRUD applications can afford the delay and if not you can pair it with a write through cache.
I specifically cited concurrent writes. SQLite is fantastic for read-only data. Those websites are using SQLite for what it's designed for. But, if they were trying to use SQLite to do even dozens of writes per second, it would crash and burn.
Nothing fancy, just a small table that holds some blog posts with an ID, a title, some content, and a creation timestamp.
I ran the benchmark with and without WAL enabled on my Macbook Air (2020, M1) with some SSD drive inside. Results:
$ make benchmark
go test -bench=.
goos: darwin
goarch: arm64
pkg: sqlite
BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6441 191735 ns/op
BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102559 11205 ns/op
PASS
ok sqlite 3.725s
That's around 89k writes per second in parallel on all available cores with WAL enabled. I know this is a trivial setup, but adjust to your liking. You'll find that SQLite probably doesn't crash and burn with dozens of writes.
This is single-threaded, which a production app would not be. That's when things get scary with SQLite.
Yes, I was incorrect when I said SQLite couldn't handle lots of writes quickly; what I should have said is that it can't handle lots of writes from multiple threads quickly.
$ make benchmark
go test -bench=.
goos: darwin
goarch: arm64
pkg: sqlite
BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 100 16317415 ns/op
BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 1196 1198679 ns/op
BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 58455 17461 ns/op
PASS
ok sqlite 4.557s
1e9/1198679 = 834 writes per second is still far from crash and burn territory when using just WAL mode.
Of course, it gets more interesting when there are also concurrent readers, as people are trying out elsewhere in the discussion. But the point still stands: it can handle way, waaaay better than "fives of authors" in a not-at-all scary way.
You should duplicate this test against postgres inserts. I did the same thing for sqlite; in go using a mutex on inserts, and sqlite still out performs postgres on inserts (on the same machine).
I'm not understanding this. Wouldn't the lower bound on performance be the single thread? Any worse performance and you queue the write requests to match the single thread performance.
As my test above shows, use a mutex for writes in Go directly and whatever locking performance problems SQLite exhibits disappear. I suspect because of the polling used in SQLite as described elsewhere on this page.
