What am I missing?
- Postgres backups are not trivial. They aren't hard, but well SQLite is just that one file
- Postgres isn't really built for many-cardinal-DB setups (talking on order of 100s of DBs). What does this mean in practice? If you are setting up a multi-tenant system, you're going to quickly realize that you're paying a decent cost because your data is laid out by insertion order. Combine this with MVCC meaning that no amount of indices will give you a nice COUNT, and suddenly your 1% largest tenants will cause perf issues across the board.
SQLite, meanwhile, one file per customer is not a crazy idea. You'll have to do tricks to "map/reduce" for cross-tenant stuff, of course. But your sharding story is much nicer.
- PSQL upgrades are non-trivial if you don't want downtime. There's logical upgrades, but you gotta be real fucking confident (did you remember to copy over your counters? No? Enjoy your downtime on switchover).
That being said, I think a lot of people see SQLite as this magical thing because of people like fly posting about it, without really grokking that the SQLite stuff that gets posted is either "you actually only have one writer" or "you will report successful writes that won't successfully commit later on". The fundamentals of databases don't stop existing with a bunch of SQLite middleware!
But SQLite at least matches the conceptual model of "I run a program on a file", which is much easier to grok than the client-server based stuff in general. But PSQL has way more straightforward conflict management in general
Grabbing a copy of the file won't necessarily work: you need at atomic snapshot. You can create those using the backup API or by using VACUUM INTO, but in both cases you'll need enough spare disk space to create a fresh copy of your database.
I'm having great results from Litestream streaming to S3, so that's definitely a good option here.
How do you get enough time to back up that file when it's in use? Lock the file until you've copied it? zfs?
https://www.sqlite.org/backup.html
> The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database file, replacing any original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue without excessive delays while a backup of an online database is made.
I immediately started to have problems as expected, and later migrated to an Access DB. It didn't support concurrent writes, but it was an improvement beyond comprehension. Even "orders of magnitude" doesn't cut it because you get many more luxuries than performance like data-type consistency, ACID compliance, relational integrity, SQL and whatnot.
You haven't lived until you've built an in-memory database with background threads persisting to JSON files. Oh, the nightmares.
Different requirements, expertise, and priorities. I don't like generic advice too much because there are so many different situations and priorities, but I've used files, SQLite and PostgreSQL according to the priorities:
- One time we needed to store some internal data from an application to compare certain things between batch launches. Yes, we could have translated form the application code to a database, but it was far easier to just dump the structure to a file in JSON, with the file uniquely named for each batch type. We didn't really need transactions, or ACID, or anything like that, and it worked well enough and, importantly, fast enough.
- Another time we had a library that provided some primitives for management of an application, and needed a database to manage instance information. We went for SQLite there, as it was far easier to setup, debug and develop for. Also, far easier to deploy, because for SQLite it's just "create a file when installing this library", while for PostgreSQL it would be far more complicated.
- Another situation, we needed a database to store flow data at high speed, and which needed to be queried by several monitoring/dashboarding tools. There the choice was PostgreSQL, because anything else wouldn't scale.
In other words, it really depends on the situation. Saying "use PostgreSQL always" is not going to really solve anything, you need to know what to apply to each situation.
We're responding to this below. The requirement is a Web2 app with "millions in revenue".
>Because it requires no setup and has been used to scale typcial Web2 applications to millions in revenue on a single cheap VM.
Millions in revenue doesn't really say anything about the performance required.
The assumption is that this is a Web2 company which usually means user-generated content. I assumed that there'd be high reads, decent amount of writes.
We're not trying to write a detailed spec here. Just going off a few details and make assumptions.
This has only been practical within the last 6 years or so. Big servers were just too expensive. We started using cheap commodity low-resource PCs, which are cheaper to buy/scale/replace, but limits what your apps can do. Bare metal went down hard and there wasn't an API to quickly stand up a replacement. NAS/SAN was expensive. Network bandwidth was limited.
The cloud has made everything cheaper and easier and faster and bigger, changing what designs are feasible. You can spin up a new instance with a 6GB database in memory in a virtual machine with network-attached storage and a gigabit pipe for like $20/month. That's crazy.
What you're missing is complexity and second order effects.
Making decisions about databases early results in needing to make a lot of secondary decisions about all sorts of things far earlier than you would if you just left it all in sqlite on one VM.
It's not for everyone. People have varying levels of experience and needs. I'll almost always setup a MySQL db but I'd be lying if I said it never resulted in navel gazing and premature db/application design I didn't need.
how come?
Postgres is also using the file system, it’s just got some extra layers in-front like networking and authentication.
People often refer to those “extra layers” as complexity.
> how come?
Why is it that more code is more stuff that can go wrong? That’s just how it is.
You are at A, you want to go to C. We are debating which B to pick between:
1. a filesystem + implementing library over that to cover the interface between A and C.
2. Postgres (which is the same as above - just not invented here)
Now, both of you are imagining a world where #2 somehow implies more complexity and also more second order effects than #1 would.
Nobody uses all of Postgres, but the cost of not using more than 1% of what's available is hardly larger than implementing that 1% yourself for most use cases. And please don't make the misstake of thinking that I'm trying to sell Postgres over any other database product - this is all to do with the worse is better idea that so many of you cultivate.
No we are not.
Postgres isn't a webserver that can handle 1m http GET requests per second; it doesn't distribute data from 30 event collectors to a reporting database without configuration. *Other stuff needs to be written to satisfy the business.* That's life.
> both of you are imagining a world where #2 somehow implies more complexity and also more second order effects than #1 would.
Most studies into product resolution agree that there's zero difference (from a pass/fail perspective) between writing it yourself or trying to reuse third-party applications. Here's one from some quick ddging: https://standishgroup.com/sample_research_files/CHAOSReport2... and see the resolution of all software products surveyed (page 6).
That means that if you can, it is still cheaper to build new software and test it than to try to use Postgres and scale it, because once software is written the cost is (effectively) zero, whereas the cost to support Postgres is a body -- that I might only be able to use 1% of, and that I might need to hire two so that one can go on holiday sometime, with a real salary that we need to pay.
Now maybe you're working for a company whose software never gets finished. In that case, by all means use Postgres in your application, since you can share the churn on that part with every other person using Postgres in the world, but I'm going kayaking today.
Like what? What could be easier than going to AWS, click a few things to spin up a Postgres instance, click a few more things to scale automatically without downtime, create read replicas, have automatic backups, one-click restore?
I feel like it's the opposite. Trying to put SQLite on a VM via Kubernetes will likely have a lot of secondary decisions that will make "scaling to millions" far harder and more expensive.
Not do that and not have to maintain that? Just a regular AWS instance with SQLite will be far easier to setup, with regular filesystem backup which is easier to manage and restore.
> Trying to put SQLite on a VM via Kubernetes will likely have a lot of secondary decisions that will make "scaling to millions" far harder and more expensive.
The issue is assuming that "scaling to millions" is a design goal from the start. For a lot of projects, the best-case-scenario does not require it. For another big set of them, they will never get to it. For the few that do, it's possible that the cost of maintaining the more complex solution while it isn't needed plus the cost of adapting over time (because, let's be honest, it's almost impossible to get the scaling solution right from the start) is more than the cost of just implementing the complex solution when it's actually needed.
You'd have less maintenance using RDS than you would with an SQLite hosted on a VM.
>The issue is assuming that "scaling to millions" is a design goal from the start.
I was responding to the OP who said you can scale to "millions of revenue" on SQLite. This part is true. You could. But it'd be easier to do it on a managed Postgres instance.
Considering that the maintenance I need to do for SQLite databases is practically zero...
> I was responding to the OP who said you can scale to "millions of revenue" on SQLite.
Millions of revenue doesn't necessarily need a high performance database. You could have millions on a page that deals with less than one request per second.
I also do zero maintenance on AWS RDS. Didn't have to setup it up either. Just a few clicks, copy and paste connection string, done. No VM to configure. No SSL to configure. No backup to configure.
Two clicks for a read only replica. A few more clicks and I can have multi-node failover. You'd want a failover if you have "millions in revenue" no? How do you plan to set that up with SQLite on a VM?
The question isn't about what you do when you get there the question is if you get there or if you flounder about deciding between self managed MySQL, postgress, rds, MongoDB, gcp, AWS or Azure.
Companies with more revenue than "millions" are still running AWS RDS.
I meant switch off sqllite by the time you're running a real business critical application off your db.
That's your opinion.
> it eliminates a whole host of data related problems and smoothens development
I don't find this to be true, but maybe you have different problems than I do.
At first i was writing in a large json file. Each time i did a change, I would write the file and close it.
It worked pretty well. I now use sqlite+dataset and the port was trivial.
I guess there are better ways to do it than open(), but using dataset is quite simple.
Maybe creating folders and duplicating data works, but it seems more complex than using sqlite and dataset.
Cloud SQL postgres on GCP with <50 connections is like ~$8/month and has automated daily backups.
The differences in SQL syntax between SQLite and Postgres, namely around date math (last_ping_at < NOW() - INTERVAL '10 minutes'), make SQLite a non-starter imho... you're going to end up having to rewrite a lot of sql queries.
Where "some" may include "none" ;)
only that hn is full, FULL of trolls who give the absolute worst advice possible. it's a sport maybe or it's entirely innocent and related to the Dunning Kruger effect but nothing brings it out more than database threads.