I haven't tried Backblaze B2 but I agree with you on engaging in AWS hell. Litestream should work with any S3-compatible API. I've added an issue to add guides for B2 & Minio[1].
Regarding Postgres vs SQLite, I've found that I can use much simpler SQL calls with embedded databases when I don't need to worry about N+1 query performance issues. That makes many of the query features moot. That being said, there is a JSON extension for SQLite[2] although I haven't tried it.
I've used json1 in SQLite for a bunch of different things and it's great - super-fast, has all of the features that I need. Since it's part of SQLite core I trust it as having the same level of reliability as the rest of SQLite.
A bit more literature on the N+1 situation in SQLite: https://www.sqlite.org/np1queryprob.html
I have always found the JSON functionality in SQLite fantastic. I'm using SQLite in production with Python for a project right now. It does not have much traffic yet, though.
I'm using multiples DBs and one of them is a simulation of key-value store. It's like a (Python) dictionary that is really an SQLite database, then I use keys like:
db["users:1000:email"] = "email@email.com"
Many people also mention concurrency, but I think that if you make your INSERT/UPDATE/DELETE statements fast and short + use WAL mode + use PRAGMA synchronous = 1, and some other optimizations, you can get quite far.
B2 seems to have an S3 Compatible API now.
Also used to PostgreSQL, and was considering SQLite for a web service. The biggest issue is how to run database migrations without downtime (while still accepting writes). There is no CREATE INDEX CONCURRENTLY. No ALTER TABLE DROP COLUMN. If the database is small, then it should be fast and it's probably fine. The clients can just retry. But the if the database is bigger, and migration takes a few minutes, this is an issue. Not sure how others here are handling this.
That said, I've always been a little worried about trying SQLite since I'm so used to Postgres. I've currently got Postgres running alongside my app in a Docker container, which isn't too hard to manage. I'm curious whether anyone has switched from Postgres to SQLite in a web app context (whether in the same project, or when making a new project) and if they've found themselves missing any of the features Postgres offers. I've tried to research this before but always found just googling "sqlite vs postgres" just results in surface-level differences that mostly focus on performance, whereas I'm more curious about e.g. the differences in their JSON extensions.