4 ms·

Indeed, SQLite should not be used anywhere that scaling or concurrency is a concern, or in general any external customer facing features. In a similar vein, an

Indeed, SQLite should not be used anywhere that scaling or concurrency is a concern, or in general any external customer facing features. In a similar vein, an internal subsystem can probably do away with a dedicated database server. Latter is likely more common.

MobiusHorizons 4y ago
Care to justify that? The evidence seems to be against you for most read heavy workloads, especially if latency is any concern.
tptacek 4y ago
That's silly. SQLite works fine for all sorts of customer features, and, deployed carefully, is fine with concurrency (writes are serialized, per database, but SQLite makes it easy to use multiple databases). SQLite has this weird reputation, I think, because frameworks like Rails used it as their "test" database, and the industry has broadly slept on how capable SQLite is in serverside applications.
- AlphaSite 4y ago
  I think if you use a lot of database features, sqlite isn’t really powerful enough for a lot of use cases IMO. Or atleast that’s the impression I have.
  - tptacek 4y ago
    Which features would those be?
    - fierro 4y ago
      won't long running write transactions block eachother? Not all apps can avoid the need for these kinds of transactions.
    - luckylion 4y ago
      Concurrent inserts. There are applications where you don't care about that, but that excludes a lot of web-apps, or things running with multiple processes. If that changes, I'd agree with your point, but currently that's a big constraint.
      - tptacek 4y ago
        How exactly does that exclude a lot of web apps? Most web apps are very read heavy. And SQLite serializes writes to individual databases, not across all of them. It's trivial in SQLite to query across multiple databases. Further, some form of write serialization is common to most database clusters: the most common cluster architecture is single write leader, multiple write replicas, which have the effect of serializing writes as well. Those clusters are all also taking advantage of the read-heaviness of applications.
        luckylion 4y ago
        Many web apps also write a lot. Are you suggesting to have individual databases per process and then read from all of them and aggregate the data you want in memory? That would sound too much like I'm building a database, which I don't want to do. I'm not talking about clusters and replication, just a single database on a single server, like Wordpress for example. It can handle large amounts of concurrent users if you're using InnoDB (which has been the default since how long?) and nobody will even notice that there's anyone else because it's not locking the table (or the whole database) to write, and you don't have to wait or deal with frequent busy timeouts.
        simonw 4y ago
        Most SQLite writes can be measured in microseconds. If your app is doing less than 1,000 writes a second you probably won't even notice that SQLite is serializing your writes rather than applying them concurrently.
        luckylion 4y ago

MobiusHorizons 4y ago

Care to justify that? The evidence seems to be against you for most read heavy workloads, especially if latency is any concern.

tptacek 4y ago

That's silly. SQLite works fine for all sorts of customer features, and, deployed carefully, is fine with concurrency (writes are serialized, per database, but SQLite makes it easy to use multiple databases). SQLite has this weird reputation, I think, because frameworks like Rails used it as their "test" database, and the industry has broadly slept on how capable SQLite is in serverside applications.

AlphaSite 4y ago
I think if you use a lot of database features, sqlite isn’t really powerful enough for a lot of use cases IMO. Or atleast that’s the impression I have.
- tptacek 4y ago
  Which features would those be?
  - fierro 4y ago
    won't long running write transactions block eachother? Not all apps can avoid the need for these kinds of transactions.
  - luckylion 4y ago
    Concurrent inserts. There are applications where you don't care about that, but that excludes a lot of web-apps, or things running with multiple processes. If that changes, I'd agree with your point, but currently that's a big constraint.
    - tptacek 4y ago
      How exactly does that exclude a lot of web apps? Most web apps are very read heavy. And SQLite serializes writes to individual databases, not across all of them. It's trivial in SQLite to query across multiple databases. Further, some form of write serialization is common to most database clusters: the most common cluster architecture is single write leader, multiple write replicas, which have the effect of serializing writes as well. Those clusters are all also taking advantage of the read-heaviness of applications.
      - luckylion 4y ago
        Many web apps also write a lot. Are you suggesting to have individual databases per process and then read from all of them and aggregate the data you want in memory? That would sound too much like I'm building a database, which I don't want to do. I'm not talking about clusters and replication, just a single database on a single server, like Wordpress for example. It can handle large amounts of concurrent users if you're using InnoDB (which has been the default since how long?) and nobody will even notice that there's anyone else because it's not locking the table (or the whole database) to write, and you don't have to wait or deal with frequent busy timeouts.
        simonw 4y ago
        Most SQLite writes can be measured in microseconds. If your app is doing less than 1,000 writes a second you probably won't even notice that SQLite is serializing your writes rather than applying them concurrently.
        luckylion 4y ago