I find comparing SQLite with Postgres moot to begin with. I use SQLite when I don't want to run a database server out-of-band, or when I want to need to copy a single file to copy an entire database. For that, it is unparallelled, easily the best in the world, by far.
You can reflect on your early experience with SQLite while simultaneously reflecting on why Postgres is a great database. If it wasn't that way, we'd all be running SQLite for our web services, but we're not.
All technologies, and especially databases, are best applied with thoughtful consideration of the context. I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres. Just the same as a use case best served by Postgres is not suitable for SQLite.
Sure. I can also reflect on my early experience with a Ferrari Enzo while I simultaneously reflect on how much I love my horse, but it should be clear that that's just irrelevant musing, rather than a direct comparison.
> I didn't get the sense the author was suggesting their SQLite use case would actually be better served by Postgres.
I got exactly that sense, from "I tried SQLite, it lacked these things. Postgres really is the best database".
Your example underscores why it doesn't read that way for me. A Ferrari Enzo and a horse are such different transportation methods that the two barely overlap in use case. A horse needs no gasoline, can work off road, and is much quieter. The Ferrari can travel much faster and farther in a go, doesn't get spooked by loud noises, and doesn't require as much daily maintenance.
I wouldn't bring a Ferrari Enzo to do a horse's job, nor a horse to do a Ferrari Enzo's job.
The same is even more true for Postgres and SQLite. Postgres is a poor fit for an on-device database for a mobile app or embedded device, and SQLite is a poor fit to power a high traffic horizontally scaled web service.
> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is.
I really don't see how you can read this as anything other than "SQLite is worse than Postgres". He basically says "sometimes I doubt if Postgres is the best database, but then I use SQLite, and it really is".
Take from it what you want, but he did not say that SQLite is worse, that's an inference. He just said Postgres is the best database in the world, and that's quite valid from the perspective of features, correctness, and performance.
SQLite is a tradeoff: It's very small and to make it that small, some traditional database aspects need to be discarded. That small size makes it useful when size is a factor.
1. Strictness by default and with no escape hatches;
2. Proper data types like all dates / times / datetimes / JSON etc.
3. In-process / embedded database. Almost all projects I worked on over the course of a 22 years of career did not need a separate node / VM / pod for a database.
PostgreSQL / MySQL and SQLite are not some ideal polar opposites. There is potential for a lot of cross-pollination of features and the ability to achieve the perfect DB.
I don't understand the comparison here at all.