5 ms·

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 th

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.

sharps_xp 4y ago
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.
- belmont_sup 4y ago
  It sounds simpler to setup postgres and never have to set up a queue (unless you’re using an in memory queue, and that has its drawbacks). Although I definitely would like to use SQLite just for the cost savings for something. Litefs/litestream looks great.
thomascgalvin 4y ago
Concurrent writes are the bane of SQLite's existence, but for a one-person blog, you shouldn't run into any issues. You would have trouble scaling up to fives of authors, though, which would be a deal breaker for any serious production app.
- markusw 4y ago
  @levelsio famously runs a dozen websites all backed by SQLite, with 200M reqs / month: https://twitter.com/levelsio/status/1520356430800617472 https://twitter.com/levelsio/status/1520356430800617472 Expensify got 4 million request _per second_ out of a custom SQLite-based setup (on a huge machine, but still): https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... I would call those serious production apps.
  - thomascgalvin 4y ago
    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.
    - markusw 4y ago
      Well, that claim is fairly easy to test. So I did: https://gist.github.com/markuswustenberg/0030360d7d4679f2656f7ecc8d865150 https://gist.github.com/markuswustenberg/0030360d7d4679f2656... 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.
      - thomascgalvin 4y ago
        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.
        cy_hauser 4y ago
        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.
        4y ago
infamia 4y ago
I was surprised at the amount of performance SQLite provided. I get about 700 inserts/sec with the WAL enabled sitting behind a Django app. https://imgur.com/a/a3U41Zo https://imgur.com/a/a3U41Zo Without the WAL enabled, I get a around 400req/sec. edit: clarity

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.

sharps_xp 4y ago

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.

belmont_sup 4y ago
It sounds simpler to setup postgres and never have to set up a queue (unless you’re using an in memory queue, and that has its drawbacks). Although I definitely would like to use SQLite just for the cost savings for something. Litefs/litestream looks great.

thomascgalvin 4y ago

Concurrent writes are the bane of SQLite's existence, but for a one-person blog, you shouldn't run into any issues. You would have trouble scaling up to fives of authors, though, which would be a deal breaker for any serious production app.

markusw 4y ago
@levelsio famously runs a dozen websites all backed by SQLite, with 200M reqs / month: https://twitter.com/levelsio/status/1520356430800617472 https://twitter.com/levelsio/status/1520356430800617472 Expensify got 4 million request _per second_ out of a custom SQLite-based setup (on a huge machine, but still): https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... I would call those serious production apps.
- thomascgalvin 4y ago
  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.
  - markusw 4y ago
    Well, that claim is fairly easy to test. So I did: https://gist.github.com/markuswustenberg/0030360d7d4679f2656f7ecc8d865150 https://gist.github.com/markuswustenberg/0030360d7d4679f2656... 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.
    - thomascgalvin 4y ago
      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.
      - cy_hauser 4y ago
        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.
        4y ago

infamia 4y ago

I was surprised at the amount of performance SQLite provided. I get about 700 inserts/sec with the WAL enabled sitting behind a Django app. https://imgur.com/a/a3U41Zo https://imgur.com/a/a3U41Zo Without the WAL enabled, I get a around 400req/sec. edit: clarity