Comment by avolcano | Hacker News Reader

Comment by avolcano | Hacker News Reader

I've been building a web service on a cheapo DigitalOcean box lately, so I'm excited to see explorations in this space, especially with an eye towards staying cheap! I'd probably only use this particular tool if it could hook up to Backblaze B2 instead of S3, since life's too short to ever have to engage with the hell that is AWS for a hobby project, but since B2's API-compatible it seems like a feature that could be added in the future.

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.

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.

[1] https://github.com/benbjohnson/litestream/issues/41

[2] https://www.sqlite.org/json1.html

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

Thanks for linking this! It intuitively made some sense to me (no IPC/network overhead) but neat to see them point this out as an intentional advantage.

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"

The feature I think I miss is being able to connect to the production database from my laptop to do a quick check. With SQLite, I have to ssh into the server and run the SQLite CLI (or copy the whole file).

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.

https://www.backblaze.com/b2/docs/s3_compatible_api.html

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.

Reply on news.ycombinator.com