Search

Still on it. See the STRICT tables thread, I ended up doing that as well.

Map's fine. If you ever want smoother than a fixed window, a token bucket's only about five more lines. Probably don't need it.

Also count failed writes, otherwise a client can hammer you with bad requests for free.

A Map is fine for a single process. Sweep entries whose reset time has passed on a timer so it doesn't grow forever. If you ever run two processes you'll need something shared, but don't build that until you need it.

Single node process, small API. I want to allow ~20 writes a minute per client and reject the rest. Everything I find assumes redis or a gateway. Is a Map of counters with a reset timestamp good enough? What happens to memory if a lot of di…

Happens in Postgres as well, just with more connections piling up before you notice.

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.

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

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.

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.

It's what I do. Test a restore or you don't have a backup.

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…