My experience with this is that all of the projects that are worthwhile, continue to grow and then at some point some kind of bottleneck is hit. Migrations become slow or overly complicated, lookups start taking forever, etc and the app just starts to slowly become unusable the more you try to use it.

You end up in a situation where you are following some complex guide (hopefully written by the actual developer) where you make a huge backup, have to tear everything down, and then hope the migration from SQLite or MariaDB doesn't hit any issues. Postgres is heavier but at the scales of self hosting it should not matter on any modern system. Multiple docker containers even on a Pi isn't something you have to worry about. Solutions like Immich are usually run on a NAS where there would never be an issue

Also you don't always know who your users are gonna be. You could create a cool personal photo backup app for your <20k library, then get people with a dozen terabytes of photos wondering why is it so slow?