5 ms·

One possible disadvantage of SQLite is that it only allows one writer at a time (but writes don't block readers with write-ahead log enabled). I'm really curiou

One possible disadvantage of SQLite is that it only allows one writer at a time (but writes don't block readers with write-ahead log enabled). I'm really curious about whether Postgres performs better at concurrent writing, couldn't find any benchmarks. In theory, disk writes are always sequential, so I'm skeptical Postgres would do substantially better.

therealdrag0 6y ago
SQLite isn’t a db-server like most other mainstream databases. It’s more of a db-file; almost an excel file. This means it’s usecases are quite different and perf comparisons don’t make sense.
justinclift 6y ago
> I'm really curious about whether Postgres performs better at concurrent writing ... Very much so. PostgreSQL easily handles lots of concurrent writing. It's a use case where PostgreSQL is much better than SQLite. :)
- RivieraKid 6y ago
  I don't believe this without a benchmark.
  - justinclift 6y ago
    Generally that's a good approach. :) In this case though, it seems a bit weird. SQLite is widely known to be for single writer workloads, whereas PostgreSQL is similarly widely known for being extremely good in concurrent usage scenarios. Those are the things they're each designed for. eg: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html (the "High-volume Websites", "High Concurrency", and "Many concurrent writers?" pieces) Feel free to run benchmarks to demonstrate this to your own satisfaction though. :)
    - RivieraKid 6y ago
      Well I explained my rationale above - writes are sequential on the disk level regardless the database. So PostgreSQL shouldn't have much of an advantage in concurrent simple writes (it could even be slower than SQLite). PostgreSQL should be faster in concurrent complex transactions but the question is how complex and how much faster.
      - justinclift 6y ago
        Good point. That's a reasonable place to start investigating from. Personally, I can't be bothered (to much higher priority stuff to do). But if someone else gets around to testing this specifically, I'd be interested in the answers as well just to "make sure" the common understanding isn't wrong. :)

therealdrag0 6y ago

SQLite isn’t a db-server like most other mainstream databases. It’s more of a db-file; almost an excel file. This means it’s usecases are quite different and perf comparisons don’t make sense.

justinclift 6y ago

> I'm really curious about whether Postgres performs better at concurrent writing ... Very much so. PostgreSQL easily handles lots of concurrent writing. It's a use case where PostgreSQL is much better than SQLite. :)

RivieraKid 6y ago
I don't believe this without a benchmark.
- justinclift 6y ago
  Generally that's a good approach. :) In this case though, it seems a bit weird. SQLite is widely known to be for single writer workloads, whereas PostgreSQL is similarly widely known for being extremely good in concurrent usage scenarios. Those are the things they're each designed for. eg: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html (the "High-volume Websites", "High Concurrency", and "Many concurrent writers?" pieces) Feel free to run benchmarks to demonstrate this to your own satisfaction though. :)
  - RivieraKid 6y ago
    Well I explained my rationale above - writes are sequential on the disk level regardless the database. So PostgreSQL shouldn't have much of an advantage in concurrent simple writes (it could even be slower than SQLite). PostgreSQL should be faster in concurrent complex transactions but the question is how complex and how much faster.
    - justinclift 6y ago
      Good point. That's a reasonable place to start investigating from. Personally, I can't be bothered (to much higher priority stuff to do). But if someone else gets around to testing this specifically, I'd be interested in the answers as well just to "make sure" the common understanding isn't wrong. :)