Search

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?

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 …

Today.

A few hundred users.

You'll want a second app server eventually and then you're stuck.

Its transaction() helper has .immediate() on it. The default is deferred. node:sqlite you'd write it yourself.

Point 1 is news to me, I'd never have found that. Does better-sqlite3 do that for you?

Oh good, a sensible answer. Now watch somebody suggest kubernetes.

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 …

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 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 m…