News for hackers. Stuff that matters.
Re:SQLite is not a toy database (2021) by endisneigh on Tuesday August 16, 2022 @12:58AM (#32479349)
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

—-

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.

Re:SQLite is not a toy database (2021) by jraph on Tuesday August 16, 2022 @01:43AM (#32479558)
> The SQLite documentation literally says use Postgres and not it for networked use cases

> 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

Re:SQLite is not a toy database (2021) by 5e92cb50239222b on Tuesday August 16, 2022 @02:02AM (#32479646)
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... (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).

Re:SQLite is not a toy database (2021) by stormdennis on Tuesday August 16, 2022 @03:10AM (#32480027)
Isn't that a bit different? As I understand it, he's talking about just copying the sql db file while you're talking about running a cluster wide backup tool to create a separate set of files, that you can then copy elsewhere as a backup.
Re:SQLite is not a toy database (2021) by Scarbutt on Tuesday August 16, 2022 @01:56AM (#32479620)
Re:SQLite is not a toy database (2021) by menzoic on Tuesday August 16, 2022 @02:09AM (#32479682)
> Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine

If preventing data loss is important for your app then this would be more hassle than using a networked db because now you have to replicate SQLite across a network to another machine.

Re:SQLite is not a toy database (2021) by samb1729 on Tuesday August 16, 2022 @02:49AM (#32479900)
> If preventing data loss is important for your app then this would be more hassle than using a networked db

How so? A networked database requires a persistent connection, while SQLite backups work fine with an intermittent connection. The best case (100% network uptime) and worst case (total network outage) are the same, but in the middle, SQLite-with-backups is more resilient to brief outages.

Re:SQLite is not a toy database (2021) by tptacek on Tuesday August 16, 2022 @01:35AM (#32479520)
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.
Re:SQLite is not a toy database (2021) by WelcomeShorty on Tuesday August 16, 2022 @11:30AM (#32484128)
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).

Re:SQLite is not a toy database (2021) by tptacek on Tuesday August 16, 2022 @01:36PM (#32486093)
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.

Re:SQLite is not a toy database (2021) by galaxyLogic on Tuesday August 16, 2022 @01:39AM (#32479538)
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.

Re:SQLite is not a toy database (2021) by jraph on Tuesday August 16, 2022 @01:45AM (#32479569)
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.
Re:SQLite is not a toy database (2021) by euroderf on Tuesday August 16, 2022 @04:01AM (#32480361)
I'd like to see someone advance the SQLite bandwagon by (A) showing how to queue up DB writes into a single thread in a variety of popular application programming languages (that have libraries for SQLite), and then (B.1) testing them for performance, and (B.2) for whether they measurably impede reads.
Re:SQLite is not a toy database (2021) by why_only_15 on Tuesday August 16, 2022 @02:00AM (#32479636)
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.
Re:SQLite is not a toy database (2021) by shimonamit on Tuesday August 16, 2022 @02:22AM (#32479747)
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.
Re:SQLite is not a toy database (2021) by why_only_15 on Tuesday August 16, 2022 @04:18PM (#32488289)
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?
Re:SQLite is not a toy database (2021) by nl on Tuesday August 16, 2022 @02:45AM (#32479876)
"Many concurrent writes" is challenging to many parts of most architectures, not just databases. For example it means your caching strategy has to be more complicated than with most read-heavy apps.
Re:SQLite is not a toy database (2021) by boomlinde on Tuesday August 16, 2022 @02:32AM (#32479800)
> 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.

Re:SQLite is not a toy database (2021) by why_only_15 on Tuesday August 16, 2022 @04:17PM (#32488271)
The majority of new things people build fail (say 95%), but it's still worth building for the success case because essentially all of the value comes in the success case. It increases cost on average but it means your website doesn't go down under load the second it's successful. This is only true to an extent of course -- don't build your startup like it's Google. But to a certain degree it's worthwhile architecting for some scale before you have it.

In the context of embedded things of course you should use something like SQLite as opposed to postgres, because there aren't going to suddenly be millions of people using your CO2 monitor (for example). But for web stuff you can plausibly have user counts that span 6 or 7 orders of magnitude, so you want to be prepared for that.

Re:SQLite is not a toy database (2021) by fragmede on Tuesday August 16, 2022 @02:32AM (#32479803)
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.
Re:SQLite is not a toy database (2021) by hosteur on Tuesday August 16, 2022 @01:52AM (#32479602)
I am curious about what you find lacking in Postgres.
Re:SQLite is not a toy database (2021) by endisneigh on Tuesday August 16, 2022 @10:26PM (#32491595)
Postgres doesn't scale well across machines. There are things like Citus that help, but still.
Re:SQLite is not a toy database (2021) by farmin on Tuesday August 16, 2022 @03:11AM (#32480041)
What's a good example of local only Web app that uses sqlite?