I've self-hosted postgres a few times, mostly to cut costs on dev. Two production use-cases I've come across is using custom extensions and deploying to a bare-metal compute provider.