3 ms·

This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale. I say "inspiring" because using SQLite

This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale. I say "inspiring" because using SQLite reminds me of the simplicity and productivity from coding for the "early web" that lost 10-15 years ago. The days when you could spin up a website without worrying about a bunch of ancillary services and focus on the app itself. For me, SQLite's lack of online schema changes seems like perhaps the biggest blocker to actual production. I've never had a production project where the schema didn't change a lot.

djbusby 5y ago
I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)
- JohnBooty 5y ago
  Interesting, that's super cool to read. Tooling for dumping and restoring into a new schema are easy/simple/fast. Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB. (This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful article that points out potential gotchas, etc)
  - djbusby 5y ago
    I'm just using shell scripts and the SQLite CLI. I dump/restore files to the same FS so I can mv into place when ready. Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is. https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html
- tianzhou 5y ago
  We also used SQLite and leverage PRAGMA user_version to do the trick. But decided to move to PostgreSQL mainly due to the schema migration constraints. Our experience is SQLite is OK for small to medium projects, but when the business logic/data model becomes more complex, SQLite is not sufficient. More discussions are captured here https://news.ycombinator.com/item?id=31038614 https://news.ycombinator.com/item?id=31038614

djbusby 5y ago

I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)

JohnBooty 5y ago
Interesting, that's super cool to read. Tooling for dumping and restoring into a new schema are easy/simple/fast. Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB. (This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful article that points out potential gotchas, etc)
- djbusby 5y ago
  I'm just using shell scripts and the SQLite CLI. I dump/restore files to the same FS so I can mv into place when ready. Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is. https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html
tianzhou 5y ago
We also used SQLite and leverage PRAGMA user_version to do the trick. But decided to move to PostgreSQL mainly due to the schema migration constraints. Our experience is SQLite is OK for small to medium projects, but when the business logic/data model becomes more complex, SQLite is not sufficient. More discussions are captured here https://news.ycombinator.com/item?id=31038614 https://news.ycombinator.com/item?id=31038614