A long time ago I wrote a critical opinion of SQLite. I had the opinion that SQLite basically sucks and I would rather use Postgres even for projects that didn't need most of Postgres's features. Lately SQLite's philosophy is growing on me.
Postgres has its whole giant built-in system of account and permission management, completely independent of the operating system or your app. When does this make sense? Permission management belongs in your app, not your database.
An SQLite database on the other hand is subject to filesystem permissions on the database file, and other than that, permissions are your application's job.
Postgres violates the self-containedness of your app. Different databases, even ones from different apps, live together in a centralization location on the host system. Postgres is run as a service your apps talk to, and so it has to have all these messy concepts like connection pooling... damn.
SQLite is just a library your process uses to access a file. It's so much more elegant.
I used to love PostgreSQL for having arrays. Not so much anymore. I had my epiphany about why arrays shouldn't be necessary in a relational database; foreign keys are the idiomatic solution, which allow querying members of the "array" without a slew of dedicated features and special syntax for arrays like Postgres has.
One of the biggest things I hated on about SQLite when I wrote that article was not enforcing foreign keys. What the hell, how could they do that? But I'm now starting to wonder if even foreign key handling isn't so useful, because maybe that too belongs in my app anyway. After all, if something can't be deleted because something else depends on it, I don't want to get an SQL error and have to tell the user "something went wrong". I want to be able to show them "can't delete that because ...".
I'm not trying to say none of the things I'm criticizing about Postgres are ever useful. But on the whole they seem like violations of the proper separation of responsibilities.
I just counted the source code lines with tokei. SQLite is 300k and Postgres is 1m. So those questionable features and design choices cost over a 3x increase in the weight of the database software itself. Maybe that stuff matters to me more than it should, but personally I loathe the feeling of millions of lines of code underneath me.
The major gripes I still have with SQLite are the lack of type enforcing and the lack of timestamp types. Those are the two I'm sure I'll never come around to agreeing with. But hey, at least it means SQLite doesn't have to care about timezones or bullshit like that. Postgres's
timestamp with time zone have certainly confused me before.