Programming discussion
I keep reading both "just use SQLite" and "you'll regret it". Small app, a few hundred users at most, single VPS, mostly reads. It's a Node process. Postgres feels like one more thing to run and back up, but I don't want to migrate in six months.
Who's run SQLite in production and what bit you?
This browser holds the management token for this post.
Keep this private. Anyone with the token can resolve, edit, or delete this discussion.
Not a web app, but my sensor box writes every few seconds and has for years. WAL mode, busy_timeout set, no problems. Use .backup or VACUUM INTO for backups and not cp.
I run Postgres for a living and I'd say use SQLite for this. The question that matters is how many writers and whether they hold transactions open. One writer at a time, so if you do a slow thing inside a write transaction, everyone queues behind you.
Things that bite:
Oh good, a sensible answer. Now watch somebody suggest kubernetes.
Point 1 is news to me, I'd never have found that. Does better-sqlite3 do that for you?
Its transaction() helper has .immediate() on it. The default is deferred. node:sqlite you'd write it yourself.
You'll want a second app server eventually and then you're stuck.
A few hundred users.
Today.
If it gets there, migrating to Postgres is a known week of work, and you've saved a couple of years of running it. The bigger trap is type sloppiness. SQLite will let you put text in an INTEGER column and the Postgres import will then find every one of those. Add CHECKs or use STRICT tables from the start.
Ok this has been really useful. Going with SQLite, WAL, BEGIN IMMEDIATE on writes. One more: backups. Is a nightly .backup to another machine enough?
It's what I do. Test a restore or you don't have a backup.
If losing a day of writes would hurt, look at Litestream, it ships the WAL to object storage continuously. Haven't needed it but people I trust like it.
Nobody's said it so I will: most of what you want from a database at this size is one file you can copy somewhere and open with a CLI.
counterpoint, had a corrupted sqlite db on a VPS whose provider had a flaky disk. postgres wouldn't have saved me either but you do feel alone. PRAGMA integrity_check in your backup job, please
Update about ten days in. It's been boring, which is good. Hit one locked database error, it was my own fault, a batch import holding a transaction during an HTTP call. marta_k called that one exactly.
Happens in Postgres as well, just with more connections piling up before you notice.
Still on it. See the STRICT tables thread, I ended up doing that as well.