SQLite is really great. By using it, you don't have to install and maintain another service, and you don't have to think about things like network security. From that point of view, that's clearly simpler.

But it also introduces a few challenges. It's not as easy to connect to your database remotely to inspect it, with something like SequelPro for MySQL. It's not possible to create an index or drop a column without blocking all writes, which can be annoying if your database is large. Database migrations in general are harder with SQLite because ALTER TABLE is limited. [1]

One last thing regarding losing the few seconds of data. If you use something like Google Cloud Regional Persistent Disk, then your data are replicated synchronously in two different data centers, which means you can lose your server, restart another one, and not lose any data. Can still be combined with Litestream for backup to S3 with point-in-time restores.

[1] https://sqlite.org/lang_altertable.html

If you can lose the last few seconds then yes that's fine. But for most applications I've been working on, we didn't have that flexibility (committed means durable).

I don't see any operational complexity with Litestream.io. I think that's an awesome tool. But it's not that different of managing PostgreSQL backups with something like WAL-E.

The complexity of managing your own database server only exists if you don't use a managed service. Then there is no server to maintain and they do all the things you mentioned for you.