5 ms·
It certainly has some good use cases and is not a toy, but there are some critical SQL features (like say: `last_at < NOW() - INTERVAL '10 minutes'`) that are n
by latchkey 4y ago
It certainly has some good use cases and is not a toy, but there are some critical SQL features (like say: `last_at < NOW() - INTERVAL '10 minutes'`) that are not easily compatible with SQLite and are with postgres, that would require a lot of code rewrite in case you need to scale upwards. Testing against multiple databases is hard, so migrating to postgres wouldn't be trivial either. Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain?
- 2muchcoffeeman 4y agoWeird comment. Don't use a DB that doesn't fit your use case. In this case if you plan to scale or want PGSQL features, don't use SQLite. But SQLite is great if you need to embed a db in an app or are just doing some local stuff.
- seabrookmx 4y agoWhere do you draw the line with compatibility? There's lots of small, DB specific differences like this between all the databases (mysql, sql server, postgres etc). For this particular one, you can replace interval with something like `last_at < DATETIME(CURRENT_TIMESTAMP, '-10 day')` > Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain? I think this is a fair point, if you think you'll run into sqlite's limitations in the future. litestream and friends make sqlite an option for some non-embedded use cases.. though they're hardly a panacea.
- vonseel 4y agoMoreover, many people are using ORMs which are going to hide the differences in a simple datetime query like that behind their query compilers.
- Gigachad 4y agoA lot of devs seem to have an obsession with using the tech that only just works for the use case. Like you pointed out, Postgres works in everything from tiny apps to global scale SaaS. I really just can't see any reason you would want to use SQLite outside of local apps. Sure, many web apps can run on it, but they can all work on Postgres.
- layoric 4y agoPostgres is a great default, no doubt, but it is still more complex to setup and maintain OR much more expensive to use as a managed offering than SQLite. Postgres extension upgrades can get pretty hairy, even on managed services. SQLite is a data file, so with projects like litestream, getting back to a working state is as simple as it gets, its a file. SQLite definitely has its own problems, but using Postgres over SQLite and vice versa is still making a trade off. There is a massive spectrum between "tiny app" and "global scale SaaS", and with hardware improvements, SQLite can handle quite a lot. Would I pick SQLite as the default to use when aiming to build another "global scale SaaS"? No, I would more likely default to Postgres as well, but it definitely shines in a lot more scenarios than just in a local app.
- wiseowise 4y ago> Like you pointed out, Postgres works in everything from tiny apps to global scale SaaS. So does SQLite.
- layoric 4y agoA lot of projects will never need beyond the scale that a SQLite host can offer, and gain from the lower bills and simplicity of a single host. $8/month of GCP gives you 0.6GB memory and a single shared vCPU (no SLA), and you still need an application server. So with the same 'micro' specs for an application server, you are paying $16/month. With SQLite you can easily deploy to services like Hetzner Cloud and get 4vCPU + 8GM RAM for ~$15/month and use Litestream for backup. There are definitely trade offs with SQLite around low write performance (due to single writer limitation), and definitely not as feature filled as Postgres, but I think a lot of people underestimate the read performance and simplicity of a single host setup. And now with sidecar setups like Litestream, backup and disaster recovery of data is also a lot simpler and again, cheaper. With added attention, hopefully SQLite will continue to improve with extensions like Spatialite and others. Schema changes are still a point of friction as your data grows larger, however fairly stable heavy read scenarios, the simplicity of a SQLite setup is very nice to use. Don't get me wrong, Postgres is a great default, but for a growing number of use cases, SQLite can be an extremely portable, cheap and efficient setup.
- endisneigh 4y agoWhy are you comparing GCP to Hetzner? You can easily deploy Postgres with auto backups to S3 using Hetzner as well.
- layoric 4y ago"easily deploy Postgres", how easy? What are the point in time restores like? One of the advantages of SQLite is its portability, so why wouldn't I compare it to Hetzner cloud since my experience of hosting is _exactly_ the same regardless of provider? The same can't be said for Postgres, unless you go self install route. And all I'm pointing out is the trade off, SQLite is simpler in some situations.
- endisneigh 4y agoDocked compose up - that simple. I don’t see how using Litestream is any simpler than using Postgres. With containers you’d have the same experience. It’s also trivial to move your Postgres database to another machine.
- anon291 4y agoHosting postgres on your own computer is free. Why would you possibly pay someone $8 / month for a postgres instance to replace SQLite? Postgres is free, and super easy to set up.
- switchbak 4y agoWe use an embedded postgres in our tests (it is just literally a vanilla postgres), the thing starts up fast and runs great. I'm sure there must be other embedded of examples out there. I'm sure I could find reasons to use sqlite, but I'd have to find reasons NOT to use postgres if I ever thought I'd need its capabilities.
- vonseel 4y agoRight, and if a project is small enough to only need a small app server, it's probably small enough to just run the DB on the same server in the background.
- waffle_ss 4y agoYou don’t even have to get that niche. SQLite can’t do ALTER COLUMN and triggers are very simple (e.g. you can’t assign to NEW in a BEFORE INSERT)