> I'd go as far as to say that many modern startups could get pretty far with SQLite + aggressive caching before even bringing in a big database like Postgres
Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).
My statement was definitely a reach, but I think there is a bunch of space in the life of an early startup for lack of highly available services. I've found startups to prioritize time-to-market (or time to PoC) over technical purity -- there's usually lots of unplanned outages early on that often have nothing to do with the database or are a result of bad application code/misunderstood requirements as well, so I'm not fully buying the availability emphasis, but a few things that I think could counteract it:
- I'm not sure I agree that the delay of the blocking write from a migration is so long as to cause significant (if any) damage to the business.
- If SQLite makes development and deployment easier (I think it does), this becomes a tradeoff between a deployment downtime/partial operation window likely on the order of milliseconds and hours of time spent setting up/managing the alternatives. With really robust systems like Postgres out there this is a pretty weak argument on my part, but there is at least something there.
- As I've also mentioned elsewhere, a high read-to-write workload would be less affected by this problem. If your startup produces aggregated business analytics dashboards for manufacturing plant stakeholders for example, your data set could be completely static.
- WAL mode[0] improves SQLite's resiliency to concurrent readers/writers.
- In the extreme case, you could treat SQLite as a hybrid row & JSON document store, and manage your own schemas (I do not endorse this approach), but you'd still be saving some time because you can do stuff like make use of SQLite's FTS search features, and you'd have access to other relational features that you might not get with other document stores.
All this said, SQLite is not for lots of concurrent writers, and they so themselves[1], but I think it can absolutely be done. More to the point, I think that SQLite can help you get something out and easily deployable, and is not hard to move off of later.
> My statement was definitely a reach, but I think there is a bunch of space in the life of an early startup for lack of highly available services.
I think there's room for a lot fewer 9s for most businesses generally, provided you have enough isolation that one thing being down doesn't also take down everything else. The exceptions are mainly ones that are trying to be someone else's infrastructure.
For lots of businesses, especially early on, maintenance windows are entirely fine and perfect bigco rolling deploys to giant HA clusters and such are way overkill that introduce all kinds of costs and complexity (=risk) that could be avoided by just accepting that every now and then some part of your site/service will be down. Just have backups, know how to restore from them quickly, and make sure you have scripted from-scratch deployments that actually work (having developers build them daily or weekly for their own local work does a pretty decent job of sussing out brokenness) and you're probably fine.
[EDIT] but of course that's the opposite of Résumé Driven Development and is very Not Cool and likely to be unpopular with clueless managers.
manager: "Why's our site down?"
you: "a deployment broke, it's fine, we can restore from scratch in ten minutes flat if we have to."
versus, also you: "as you know we follow Google's best practices and use Kubernetes and blah blah blah and you see [translated from Bullshit Speak] we don't actually understand it very well and it's super complex and it shit the bed for some reason but we're fixing it, and as you know this is all best practices and Google like and such as"
Unfortunately the latter is often "safer" than the former... for one's career, not for the product or service you're providing.
I can see what you mean, but most startups try to have users as early as possible, to demonstrate traction. Most also practice continuous deployment. In the early stage, a lot of refactoring usually happens, which often requires database migrations. If we are afraid to refactor our database because our service may be unavailable and disappoint our early adopters, then we lose velocity and peace of mind. A tool like PostgreSQL solves this, with a small admin overhead compared to SQLite.
And the admin overhead can be reduced by using a managed service (Amazon RDS, Google Cloud SQL, Digital Ocean Managed Databases, etc.). With a managed service, we can even go serverless for the rest of the app. With SQLite, we have to closely manage and backup our server since the data are stored on it.
> I'm not sure I agree that the delay of the blocking write from a migration is so long as to cause significant (if any) damage to the business.
I agree that most migrations, in the early stage of a project, run for a few seconds to a few minutes maximum. But in my experience that's usually enough to be noticeable by our users.
> If SQLite makes development and deployment easier (I think it does)
Modern startups need their services to be available, and this is difficult to achieve with SQLite because you can't run long database migrations without blocking writes during the migration. Personally, I'd use SQLite more often if there was a good solution to this (and also DROP COLUMN).