4 ms·

Is it reasonable to assume that in most current deployments of PostgreSQL or MySQL, SQLite would be at least an equally good choice? I was recently choosing a

Is it reasonable to assume that in most current deployments of PostgreSQL or MySQL, SQLite would be at least an equally good choice? I was recently choosing a database for a medium-size website and SQLite seemed like an obvious choice. One thing I was worried about was that the database locks for each write - but this is apparently not true anymore with write-ahead log.

deleted 6y ago
[deleted]
therealdrag0 6y ago
> “Most current deployments” I doubt it but we’re both guessing. Personally I’ve never worked on a professional project that had all readers/writers on a single computer. So in my bubble SQLite is not an option.
duskwuff 6y ago
Depends on the environment. SQLite will scale out reasonably well so long as it's only needed on one machine. As soon as you need a network-accessible database, traditional database servers start looking like a better option.
justinmeiners 6y ago
Yes, most wordpress or joomla sites come to mind. There is typically only one application communicating with it, the user doesn't typically doesn't admin the database directly (and if they did they want a file), medium traffic load (hundreds per second), and most of the queries are reads, with the occasional content update. As soon as you get into privilege levels or heavy loads, then those others make more sense.
Carpetsmoker 6y ago
I ran some performance/reliability benchmarks on the product I'm working on (which supports SQLite and PostgreSQL), and SQLite was about 30% faster than PostgreSQL. This won't hold true for all use cases; one table now has 11 million rows, and I'm not sure how well SQLite would perform on that. The benchmark was very simple anyway, and it's mostly a read-only where users don't update/insert new stuff. Would be interesting to re-test all of this.
rakoo 6y ago
As usual, it depends. The official website has a page helping you decide (https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html) TL;DR: if your data-access scenario is read-mostly write-sporadically, and your data being in a single place is ok, SQLite is fine. It does make sense for many applications

deleted 6y ago

[deleted]

therealdrag0 6y ago

> “Most current deployments” I doubt it but we’re both guessing. Personally I’ve never worked on a professional project that had all readers/writers on a single computer. So in my bubble SQLite is not an option.

duskwuff 6y ago

Depends on the environment. SQLite will scale out reasonably well so long as it's only needed on one machine. As soon as you need a network-accessible database, traditional database servers start looking like a better option.

justinmeiners 6y ago

Yes, most wordpress or joomla sites come to mind. There is typically only one application communicating with it, the user doesn't typically doesn't admin the database directly (and if they did they want a file), medium traffic load (hundreds per second), and most of the queries are reads, with the occasional content update. As soon as you get into privilege levels or heavy loads, then those others make more sense.

Carpetsmoker 6y ago

I ran some performance/reliability benchmarks on the product I'm working on (which supports SQLite and PostgreSQL), and SQLite was about 30% faster than PostgreSQL. This won't hold true for all use cases; one table now has 11 million rows, and I'm not sure how well SQLite would perform on that. The benchmark was very simple anyway, and it's mostly a read-only where users don't update/insert new stuff. Would be interesting to re-test all of this.

rakoo 6y ago

As usual, it depends. The official website has a page helping you decide (https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html) TL;DR: if your data-access scenario is read-mostly write-sporadically, and your data being in a single place is ok, SQLite is fine. It does make sense for many applications