I've built and deployed specifically small applications using sqlite and yeah I agree with everything, but especially the migration pains. Any change becomes difficult and bringing another developer onto a project just slows it to a crawl when db changes are needed. If that can be resolved I could be convinced, but until them postgres4lyf
I don't think you need to have the largest following to have great value, even lemmy as it is right now feels great. I'll actually want to dive into comment sections compared to the endless scrolling on reddit.
As long as there's enough people using a platform for a variety of ideas and experience in topics, I think that's good enough for me.