The primary reason for me is that Postgres has stronger default constraints. If you care about keeping your data logically consistent then Postgres has more of that out of the box.

SQLite just makes the tradeoff to be simpler since often it doesn't matter. But don't make the mistake that it doesn't matter. Since PG helps avoid data problems and you might need to scale out web servers that is why Django for instance recommends switching to Postgres (or whatever you're actually going to use) ASAP cause there are differences. You may end up relying on PG to reject things SQLite doesn't care about by default. SQLite might let you get away with inserting data which PG refuses to handle.

Not to mention the DB specific features can differ. Like PG's JSON field types or etc.

Not sure why you were downvoted because that is a legitimate concern and it's my only beef with sqlite3. I will kill for an embedded PostgreSQL. If sqlite3 becomes that I'll absolutely pay for a license if they require it.

Check out DuckDB! It is designed for OLAP instead of OLTP, but it uses Postgres syntax and types! It's columnar and lightning fast for big queries.

Been looking for an excuse to use it, this might just be the one. :)

> then what's the reason people aren't using it for websites?

I'd guess the reason to be that people keep hearing things like "don't use SQLite for websites" and thus don't even try.

> Why do you say it works for "small" websites but presumably not large ones?

Not the GP, but the main reason I wouldn't use SQLite for a large website is that SQLite itself doesn't offer much re: failover/replication (i.e. multiple servers, one database), and I haven't used RQLite enough (or at all; I should fix that) to be comfortable with it in production. Because of that, I'm more likely to reach for / recommend PostgreSQL instead.

That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.

I'm in the process of adding read replication to Litestream[1] so folks can scale out the read-side of their SQLite applications to multiple nodes (or replicate to edge nodes for low-latency).

[1]: https://litestream.io/

Amazing. This was missing for my use-case and I gave up on LiteStream because of it but now it seems I have to revisit!

Wonderful job.

Neat. I'll have to give that a whirl.

Would this enable any node to be a writer (i.e. would it lock the DB across all nodes)? Or would I have to designate some "master" server with exclusive write access and have any other servers forward write requests to that server?

It would be the latter. You'd need to have a single primary and replicas would need to redirect writes to that node.

> That being said, if your website has crazy "web scale" FAANGesque needs and you're at the point where you need to write your own replicated datastore, using SQLite as a base and building your own replication layer on top of it (or using RQLite and maybe adjusting it for your needs) seems like a reasonable way to go.

Exactly what Bloomberg did with Comdb2: https://github.com/bloomberg/comdb2

Crazy web scale folks flip from Mongo (NoSQL) to Postgres: https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-h...

Great article. It took them 10 months to successfully migrate! No mention of how many man-hours, but probably a lot.

Depending on the use case (SaaS offerings with per customer shards) you can actually scale sqlite quite high. Expensify scaled to 4 mio qps (queries per second) [1], so if one of your customers is exceeding that, you better be looking at another DBMS but below that - well sqlite is an option.

[1] https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

I would use SQLite on a single-machine web server. However, I prefer to build my web stuff as stateless machine images that connect to a separate database instance, because it lets me scale them independently.

I should revisit this policy now that you can run a truly huge site off a 1U slot (I work for an alexa top10k, and our compute would fit comfortably in 1U); computers are so fast that vertical scaling is probably a viable option.

That's exactly what I am leaning to in my current job. Looking at the load of our servers I can comfortably put all our software on my gaming machine (which is mid-range!) -- with the DB included -- and I bet no request ever will be above 100ms.

IMO a lot of organizations should start re-investing in on-premise as well. Having a mid-range AMD EPYC server can serve most businesses out there without ever having more than 40% CPU usage.

That, plus scaling down Kubernetes clusters. Most companies absolutely didn't need them in the first place.

I don’t know about on-prem for serving external workloads; getting a reliable, redundant, high-speed connection can cost far more than paying for colocation at a DC that’ll do at least as good a job. If you’re lucky they’ll even install fire suppression systems.

Yep, can't argue with that.

Still, for the cloud I started preferring going for Rust (where applicable!) and optimizing the hell out of the performance hot-spots. So far the results have only been crushing successes. Horizontal scaling has been employed only as a means of backup instances with a load balancer in front of them. And for blue/green deployments.

Horizontal scaling hasn't been at all necessary otherwise. A $25 instance never got north of 15% CPU for one of the services I rewrote. I/O utilization never got beyond 60% and is usually 3-5%.

But I can agree that the cloud has unquestionable benefits. It's just that I feel that their number is gradually dwindling.

I think one valid fear is that a web application might, for multiple reasons, scale beyond a single process on a single server. This is common enough that using an embedded database can be problematic, compared to a separate RDBMS process that can be shared between processes. Some web applications are just never going to scale out for any reason, and for those SQLite might be appropriate.

Agreed, but it’s not only a question of scaling. Any app with high uptime requirements will need more than a single process from day 1.

It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.

I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.

Most web servers support greaceful restarts, i.e. reloading code and configuration without downtime. Usually you just send a pre-specified and documented signal to the process to do it.

For a web server serving from the filesystem, sure. That doesn't help you deploy a new version of a web application you're making code changes to (which is the kind of thing that would need a database).

I was actually talking about both. For example for python, which is what I usually use, both uwsgi and gunicorn support graceful restarts. Admittedly I don't have much experience with other languages, but it's technically possible. If your application doesn't support it out of the box, you can still achieve it with something like systemd's socket activation.

Sqlite is catching up quickly with SQL support, but like with any fast moving technology - unless they're really interested in it - most of devs are lagging few years behind with their knowledge on what can be done with it.

For instance I remember that at some point sqlite didn't have foreign keys, so I couldn't run my existing migrations on it (without rewriting everything) which was a big issue for me at the time. Now I see that they've added the support for referential integrity in the meanwhile, but I had no idea about it because simply there's so many other libs and technologies to follow - one just can't keep track of every single tool in the world obviously - so some great tools just fall out of focus.

My guess is the word will slowly get out and in a few years people will probably shift to using it more in a web world - but it will take some time.

If writes are infrequent I think it might be fine. Otherwise I’d be wary.