I debated running my own Postgres cluster for a side project, but ended up paying for Digitalocean’s managed offering. I’d like to hear from anyone running their own Postgres. Why did you choose to do that vs pay for a managed instance? How much of a hassle has it been? Is it something you really need significant Postgres experience to do well (to achieve security, backups, high availability, etc)? Biggest gotchas to…
The primary reason was the desire to use extensions; I'm now able to use Foreign Data Wrappers with great effect to turn the Postgres databases in an enterprise data integration platform/data warehouse.
Additional benefits include a staggering 50x performance increase* , a much finer grained user authorization strategy (by being able to (automatically) alter grants and alter default privileges of other users with superuser permissions). Also, my business users can now leverage NFS or sFTP shares to load very large datasets direct from the server and we can actually shut down unused environments outside of business hours (Azure's DBaaS can be scaled down, but not shut down).
The costs of this setup is actually ~20% lower compared to Azure's managed offering (for same core count, could be even lower if you add the enormous performance gains to the comparison). This included cost of developers/operators! (we're finding the operational effort is more or less equal to a managed offering; almost non-existant). The only thing that really caused some discomfort in the beginning was our setup of pgBouncer.
Off course, There're some caveats; we run an OLAP workload, not business critical and therefor haven't really put a lot of effort into a HA setup. Still, the advantages of an "real" Postgres server cannot be overstated! To be able to use extensions alone has brought the time to market of new features from weeks to days.
* Geometric mean of TPC-DS benchmark suite. On Azure's Ls_v2 machines, leveraging the NVMe disk with bcache. Compared to a tuned Azure's "Postres as a Service" with equal core count.