Review request

Reading tracker on SQLite, should I move to Postgres before I open it up?

Solved · 6 views · asked 3 weeks ago by ines_data · edited

Repo or live app

github.com/inesferreira/shelfwork ↗

Unsure about: DeploymentPerformance

Books, sessions, notes. Cursor set it up with SQLite and a file next to the app, which has been lovely for a year of single-user use. I want to let a reading group of about 40 people in. Everyone tells me to move to Postgres and nobody tells me why, so I would rather hear the actual reason or be told to stay put.

Comment

2 answers

Marked as helpful by the asker

olu_backend3 weeks ago · edited

40 people reading and occasionally writing is nothing for SQLite. Concurrency is not your reason to move.

Deployment is. The question that decides it: where does that file live once you deploy? On Vercel or any serverless platform the filesystem is ephemeral and per-instance, so your database silently resets and different instances see different data. That is not a scale problem, it is an immediate data loss problem.

So: if you deploy to one long-lived machine with a real disk and a backup, stay on SQLite, turn on WAL with pragma journal_mode=wal so a writer does not block readers, and get on with it. If you are going serverless, move to Postgres now, because no amount of tuning fixes a disk that disappears.

Comment

mira_dev3 weeks ago · edited

One thing to check before the group arrives either way: SQLite's default busy_timeout is 0, so a write that meets another write fails instantly instead of waiting. Set it to a few seconds. That single setting is behind most of the "SQLite cannot handle concurrency" stories.

Comment

Keyboard shortcuts

Reading tracker on SQLite, should I move to Postgres before I open it up?

Repo or live app

2 answers

Your answer

Reading tracker on SQLite, should I move to Postgres before I open it up?

Repo or live app

2 answers

Related

Your answer