I also always wondered why sqlite isn't used more in websites. Especially if you split heavy write workloads to a separate database file it scales quite far.

There's no reasonable way to share a SQLite database between processes on separate machines or VMs.. What website doesn't use at least 2 instances for HA?

How do you make sure you don't lose data in a SQLite DB?

I throw together plenty of simple sites intended for personal use or just a small group. I don't need nor want the added complexity of going for HA. I can easily spin up a cheap VPS with an SSD and scale amazingly far with one instance and a SQLite DB. When it's for personal use or small group it's not a problem to take it offline for maintenance/deploys. I don't even use a load balancer (tho I often throw nginx in front to do mTLS and compression). I can build a site like that in 30 minutes.

I deal with massive scale all day every day for work (kuberntes/openshift/public clouds) so I find the simplicity amazingly refreshing.

I might also say the performance of my "single box" websites is also amazingly good. I've had even developer friends ask me how I get response times so fast, and they are often surprised when I tell them.

Many websites just run on a single machine somewhere, with maybe some caching in front or off-loaded static files. Even HN is as far as I know still just a single box.

Not sure about parent’s case, but I’ve used it for small blog-type applications where the only writes are from me (the admin) creating new entries. The rest is read only and behind caches, cdns, etc. The blog is effectively a singleton. Analytics and comments are handled by 3rd party providers.

You can use a regional GCP disk that replicates over all zones in a region (https://cloud.google.com/compute/docs/disks#repds). You cannot mount the same disk to many VMs, but you can force mount the disk to a new VM.

Just use DRDB and Linux-HA to replicate the disks hosting the master SQLite database and the standby database.

What’s not reasonable about TiDB?

How do you architect this ? This works only for single process web-apps right ? With no HA or failover ?

Right! But as I said, the low traffic volume doesn’t require more than this kind of architecture.

I do regular file backups and have a cron job restarting the web apps each 20 minutes, it’s enough for now. I would love to have a more solid architecture, but it matches the traffic volume and it’s good enough..

TBH, it'll work for a lot of multi-process apps, so long as the app runs on a single server and the app doesn't do anything foolish like write to the database on every page hit. For read-mostly applications -- like a blog, wiki, or even a small web forum -- SQLite is a surprisingly adequate database.