4 ms·

Many of the replies here attest to the simplicity and fast performance of SQLite particularly for serving pages or data. But how well does SQLite fare in concur

Many of the replies here attest to the simplicity and fast performance of SQLite particularly for serving pages or data. But how well does SQLite fare in concurrent write/insert situations? Although SQLite is not designed for this type of scenario, this discussion higlights there's a strong demand for a concurrent client/server RDMS that is simple, performant and easy to deploy. PostgreSQL is powerful and feature-rich, but not simple or easy to delploy. Hence the appeal of SQLite. For example, could SQLite power a discussion forum of moderate (or more) activity i.e. users posting comments? The Nim language forum is powered by SQLite, but activity in the forum is fairly low. [1] Between the simplicity of SQLite and the complex, heavyweight that is PostgreSQL, there is a wide gap between these database opposites. It's a shame there is no concurrent RDMS to fill that gap. (Note: Another poster mentions the concurrent Firebird RDMS as a possible alternative, but I haven't used it. [2]) [1] https://forum.nim-lang.org/ https://forum.nim-lang.org/ [2] https://firebirdsql.org/en/features/ https://firebirdsql.org/en/features/

simonw 4y ago
Most SQLite writes take less than a ms, so the concurrent writes scenario isn't actually a big problem - writes can be applied via a queue. Does your forum accept more than 1,000 writes per second? If not, SQLite should be fine.
Sohcahtoa82 4y ago
> But how well does SQLite fare in concurrent write/insert situations? It's pretty well-known that concurrent writes are SQLites weak point, and that if your application requires high numbers of writes, that it's not the proper solution. The SQLite devs even acknowledge this: > SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite. > [...] client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will. (https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html)
ttfkam 4y ago
Not simple or easy to deploy? On a server, it's "apt-get install postgresql" or "yum install postgresql". In the cloud, you choose RDS on AWS, Google Cloud SQL, Heroku Postgres, DigitalOcean managed DB, etc. Need to step it up? AWS Aurora or Aurora Serverless. Just because Postgres has all the features and knobs doesn't mean you have to use them or turn them. You can always use it like SQLite but just one or two things more that you need.

simonw 4y ago

Most SQLite writes take less than a ms, so the concurrent writes scenario isn't actually a big problem - writes can be applied via a queue. Does your forum accept more than 1,000 writes per second? If not, SQLite should be fine.

Sohcahtoa82 4y ago

> But how well does SQLite fare in concurrent write/insert situations? It's pretty well-known that concurrent writes are SQLites weak point, and that if your application requires high numbers of writes, that it's not the proper solution. The SQLite devs even acknowledge this: > SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite. > [...] client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will. (https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html)

ttfkam 4y ago

Not simple or easy to deploy? On a server, it's "apt-get install postgresql" or "yum install postgresql". In the cloud, you choose RDS on AWS, Google Cloud SQL, Heroku Postgres, DigitalOcean managed DB, etc. Need to step it up? AWS Aurora or Aurora Serverless. Just because Postgres has all the features and knobs doesn't mean you have to use them or turn them. You can always use it like SQLite but just one or two things more that you need.