Comment by TheAceOfHearts
9 hours ago
I'll take it one step further and say you should always ask yourself if the application or project even needs a beefy database like Postgres or if you can get by with using SQLite. For example, I've found a few self-hosted services that just overcomplicated their setup and deployment because they picked Postgres or MariaDB over SQLite, despite it being a much better self-contained solution.
I find that if I want to use JSON storage I'm somewhat stuck choosing my DB stack. If I want to use JSON, and change my database from SQLite to Postgres I have to substantially change my interface to the DB. If I use only SQLite, or only Postgres it's not so bad, but the transition cost to "efficient" JSON use in Postgres from a small demo in SQLite is kind of high compared to just starting with an extra docker run (for a Postgres server) / docker compose / k8s yaml / ... that has my code + a Postgres database.
I really like having some JSON storage because I don't know my schema up front all the time, and just shoving every possible piece of potentially useful metadata in there has (generally) not bit me, but not having that critical piece of metadata has been annoying (that field that should be NOT NULL is NULL because I can't populated it after the fact).
SQLite is great until you try to do any kind of multi-writer stuff. Theres no SELECT FOR UPDATE locking and no parallel write support, if any of your writes take more than a few ms you end up having to manage queueing at the application layer, which means you end up having to build your own concurrent-safe multi-writer queue anyway.