4 ms·

I like SQLite (use it for android apps), but there’s no reason to use it for apps over a network. SQLite is meant to be embedded (also works great with desktop

I like SQLite (use it for android apps), but there’s no reason to use it for apps over a network. SQLite is meant to be embedded (also works great with desktop apps and local-only web apps). In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres. All that being said, absurdsql is great. There should be some optimizations in mirroring network state to IndexedDB that could be queried via SQl(ite). The SQLite documentation literally says use Postgres and not it for networked use cases (unless you use WAL or rollback, in which case why not just use Postgres?) https://www.sqlite.org/useovernet.html https://www.sqlite.org/useovernet.html —- As an aside, I think we need a new storage primitive. In the 2000s desktop apps were rampant and SQLite was more or less the standard. We then moved over to networked apps where RDBMS had its day. There was a moment where nosql was booming for the scaling but it turned out people like relations. We need an open source, distributed and relational store that’s easy to maintain build this decade.

fragmede 4y ago
I mean, Amazon and the rest of the cloud vendors will happily sell you managed SaaS database that's open source, distributed and a relational store. That makes it real easy to maintain. Cheaper than a team of DBAs, too, until it isn't.
tptacek 4y ago
For some very real modern full-stack workloads, the network is the problem. It implies a separate database tier, and network latency for every query. The latency is managed by careful query design, but a valid alternative to that work is to move the database closer to the app --- within NVMe latency rather than network latency. A key part of this idea is recognizing that reads have different requirements than writes, and that many applications are overwhelmingly read-intensive.
- WelcomeShorty 4y ago
  I found the key part of getting your application up to speed is 80% of the time, the query itself. Using stored procedures, bulk insert and the like, have speed up more "slow queries / databases" then anything else I've seen. Of course, if your coder already knows and does this, your last resort will be moving the DB closer to the app (the last 20% of optimisation).
  - tptacek 4y ago
    It's useful to keep in mind that part of the premise of SQLite is to mitigate the need to carefully design query patterns; famously, SQLite promotes the fact that the N+1 query pattern tends to work fine (again, because you have ultra-low latency to your database). So, yes, in an n-tier database architecture, 80% of your optimization work might go into meticulously minimizing your query load with better-designed queries. The point is: it doesn't have to be that way. The more important thing I think is, again, thinking separately about reads and writes. The issue is most apparent in edge-optimized applications, almost all of which are overwhelmingly read-heavy. You've got a (say) 100ms budget for the whole request, and "edge-optimized" with traditional n-tier databases practically implies that your database isn't always in the same data center as your app, so you can eat that budget up real, real quick going back and forth with Postgres. Even inside a database, you can be looking at a couple milliseconds per database hit, which adds up as you do multiple queries. SQLite with replication is a nice solution for that problem. Writes get handled roughly the way they would with Postgres, serialized into a single write master; reads get satisfied instantaneously from NVMe.
deleted 4y ago
[deleted]
galaxyLogic 4y ago
I think the question to ask is, do users need access to data created by other users? If not then that is the use-case for SQLite. It could still collect data to the server on a delayed basis for the benefit of the app-owners and for back-ups but that is not time-critical.
- jraph 4y ago
  And if yes, it's still a use case of SQLite if there are not too many (concurrent) writes. And "too many" might actually be a large number.
  - why_only_15 4y ago
    It feels bad to make a database choice which isn't capable of supporting lots of writes. Lots of things start small but get big unexpectedly, and if you choose sqlite then now you have to rearchitect things.
    - shimonamit 4y ago
      Note the difference between "lots of writes" and "lots of concurrent writes". "Lots of writes" in succession without heavy concurrency support is just fine for desktop/mobile apps. It is not okay for busy webapps.
      - why_only_15 4y ago
        Oh hmm what do you mean by concurrent here? Several writes in succession to the same place or writes that other users need to be able to read quickly?
    - boomlinde 4y ago
      > Lots of things start small but get big unexpectedly, and if you choose sqlite then now you have to rearchitect things. Lots of things predictably don't. It seems like bad engineering to operate as though everything will. Especially in appliances/"IoT" and other embedded use cases, that line of thinking can drive production costs up a lot.
jraph 4y ago
> The SQLite documentation literally says use Postgres and not it for networked use cases > https://www.sqlite.org/useovernet.html https://www.sqlite.org/useovernet.html It's not what this page says. This page suggests PostgreSQL if accessing the SQLite file from the application would induce a network connection (because of a network filesystem). Not if the application is accessed over the network. Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine. > (unless you use WAL or rollback, in which case why not just use Postgres?) Again. WAL / rollback is how SQLite works by default. The page does not suggest using WAL, it suggests using WAL, "but do all reads and writes from processes on the same machine that stores the database file", is you use a networked FS. And yes, in this case, you might as well use PostgreSQL indeed. > In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres. I disagree. I now use SQLite whenever I can for low traffic services, unless the app developers strongly advise something else.I'd say there's no reason to bother with a client/server database like PostgreSQL / MariaDB / MySQL if you can help it. It's one less database to administer, one less user and password to create and manage, and the SQLite file is saved using my regular backup routine. It works great and it's less work for me. You also get to use the database engine with probably the most extensive and comprehensive test suite in the world , by far[1]. It is probably more robust than anything else. SQLite is probably capable of handling high traffic too, especially if the majority of accesses are reads and there are only a few writes. Of course, Postgres is a really good choice too and you can't really be wrong when choosing it. It's very robust and works very well too, and does not have loosy typing. [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- Scarbutt 4y ago
  https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- 5e92cb50239222b 4y ago
  FWIW, it's perfectly fine to backup both MariaDB and PostgreSQL by simply copying files. It's one of the recommended methods to set up PostgreSQL replicas: https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-BASE-BACKUP https://www.postgresql.org/docs/current/continuous-archiving... (section 26.3.3.3) You might want to configure the database to put binary logs into a separate directory (or you might want to leave them there, depends on your requirements).
hosteur 4y ago
I am curious about what you find lacking in Postgres.
- endisneigh 4y ago
  Postgres doesn't scale well across machines. There are things like Citus that help, but still.
deleted 4y ago
[deleted]
farmin 4y ago
What's a good example of local only Web app that uses sqlite?

fragmede 4y ago

I mean, Amazon and the rest of the cloud vendors will happily sell you managed SaaS database that's open source, distributed and a relational store. That makes it real easy to maintain. Cheaper than a team of DBAs, too, until it isn't.

tptacek 4y ago

For some very real modern full-stack workloads, the network is the problem. It implies a separate database tier, and network latency for every query. The latency is managed by careful query design, but a valid alternative to that work is to move the database closer to the app --- within NVMe latency rather than network latency. A key part of this idea is recognizing that reads have different requirements than writes, and that many applications are overwhelmingly read-intensive.

WelcomeShorty 4y ago
I found the key part of getting your application up to speed is 80% of the time, the query itself. Using stored procedures, bulk insert and the like, have speed up more "slow queries / databases" then anything else I've seen. Of course, if your coder already knows and does this, your last resort will be moving the DB closer to the app (the last 20% of optimisation).
- tptacek 4y ago
  It's useful to keep in mind that part of the premise of SQLite is to mitigate the need to carefully design query patterns; famously, SQLite promotes the fact that the N+1 query pattern tends to work fine (again, because you have ultra-low latency to your database). So, yes, in an n-tier database architecture, 80% of your optimization work might go into meticulously minimizing your query load with better-designed queries. The point is: it doesn't have to be that way. The more important thing I think is, again, thinking separately about reads and writes. The issue is most apparent in edge-optimized applications, almost all of which are overwhelmingly read-heavy. You've got a (say) 100ms budget for the whole request, and "edge-optimized" with traditional n-tier databases practically implies that your database isn't always in the same data center as your app, so you can eat that budget up real, real quick going back and forth with Postgres. Even inside a database, you can be looking at a couple milliseconds per database hit, which adds up as you do multiple queries. SQLite with replication is a nice solution for that problem. Writes get handled roughly the way they would with Postgres, serialized into a single write master; reads get satisfied instantaneously from NVMe.

deleted 4y ago

[deleted]

galaxyLogic 4y ago

I think the question to ask is, do users need access to data created by other users? If not then that is the use-case for SQLite. It could still collect data to the server on a delayed basis for the benefit of the app-owners and for back-ups but that is not time-critical.

jraph 4y ago
And if yes, it's still a use case of SQLite if there are not too many (concurrent) writes. And "too many" might actually be a large number.
- why_only_15 4y ago
  It feels bad to make a database choice which isn't capable of supporting lots of writes. Lots of things start small but get big unexpectedly, and if you choose sqlite then now you have to rearchitect things.
  - shimonamit 4y ago
    Note the difference between "lots of writes" and "lots of concurrent writes". "Lots of writes" in succession without heavy concurrency support is just fine for desktop/mobile apps. It is not okay for busy webapps.
    - why_only_15 4y ago
      Oh hmm what do you mean by concurrent here? Several writes in succession to the same place or writes that other users need to be able to read quickly?
  - boomlinde 4y ago
    > Lots of things start small but get big unexpectedly, and if you choose sqlite then now you have to rearchitect things. Lots of things predictably don't. It seems like bad engineering to operate as though everything will. Especially in appliances/"IoT" and other embedded use cases, that line of thinking can drive production costs up a lot.

jraph 4y ago

> The SQLite documentation literally says use Postgres and not it for networked use cases > https://www.sqlite.org/useovernet.html https://www.sqlite.org/useovernet.html It's not what this page says. This page suggests PostgreSQL if accessing the SQLite file from the application would induce a network connection (because of a network filesystem). Not if the application is accessed over the network. Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine. > (unless you use WAL or rollback, in which case why not just use Postgres?) Again. WAL / rollback is how SQLite works by default. The page does not suggest using WAL, it suggests using WAL, "but do all reads and writes from processes on the same machine that stores the database file", is you use a networked FS. And yes, in this case, you might as well use PostgreSQL indeed. > In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres. I disagree. I now use SQLite whenever I can for low traffic services, unless the app developers strongly advise something else.I'd say there's no reason to bother with a client/server database like PostgreSQL / MariaDB / MySQL if you can help it. It's one less database to administer, one less user and password to create and manage, and the SQLite file is saved using my regular backup routine. It works great and it's less work for me. You also get to use the database engine with probably the most extensive and comprehensive test suite in the world , by far[1]. It is probably more robust than anything else. SQLite is probably capable of handling high traffic too, especially if the majority of accesses are reads and there are only a few writes. Of course, Postgres is a really good choice too and you can't really be wrong when choosing it. It's very robust and works very well too, and does not have loosy typing. [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html

Scarbutt 4y ago
https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
5e92cb50239222b 4y ago
FWIW, it's perfectly fine to backup both MariaDB and PostgreSQL by simply copying files. It's one of the recommended methods to set up PostgreSQL replicas: https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-BASE-BACKUP https://www.postgresql.org/docs/current/continuous-archiving... (section 26.3.3.3) You might want to configure the database to put binary logs into a separate directory (or you might want to leave them there, depends on your requirements).

hosteur 4y ago

I am curious about what you find lacking in Postgres.

endisneigh 4y ago
Postgres doesn't scale well across machines. There are things like Citus that help, but still.

deleted 4y ago

[deleted]

farmin 4y ago

What's a good example of local only Web app that uses sqlite?