Do you manage your own Postgres or use a DBaaS?

I read here and there some people saying how they want to use a hosted DB service for they Postgres database instead of hosting it on their own VPS.

I did some research and found interesting solutions such as Heroku Postgres, Google Cloud SQL, Amazon RDS, ElephantSQL, Compose, etc.

However, I quickly understood that anything above the free/dev tier could have costs escalate quite quickly.

So, what to do? is it that hard/problematic to host my own Postgres?
Do you use DBaaS? If so, what do you recommend?

Thanks!!
Greetings from Berlin!

Comments

[Michiel Hendriks]

We host our own postgresql on VMs running on our own hardware using storage from the SAN of our ISP.
We ran a vanilla postgresql 9.3 for years. That means, the default configuration you get when downloading the official postgresql package.
For one of our customers the total database size had grown to about 1.5 TiB, and growing about 2GiB a day (note: vanilla configuration).
At some point I finally convinced management to spend some money to have an expert check our setup and give some recommendations. I knew beforehand that postgresql had a bunch of interesting knobs to fiddle with, and that the default set up was probably not the best for our use-case.
One of the recommendations was to increase the shared_buffers from 128MiB to 8GiB (the server had 64GiB of RAM anyway). And there were a few other things including changing some parameters for query planning as disk access times for random seek or sequential seek as basically similar when your storage is a SAN. And a whole bunch of other things.
I do not have clear stats on performance improvements of our platform. But being able to tune various things around the setup of the database server can have significant benefits.
Most of the time is spend in our application. Peak database TPS was near 400. pgbench for the platform was about 1600 iirc. (pgbench on a similar setup with 9.5 resulted in 2400). The actual performance issues we suffered were often related to IO load on the SAN. Proper configuration of your database can ensure that pages and buffers are more properly used, and thus can depend much less on SAN load.
Note that postgresql makes a lot of use of OS features and does not try to be smart an do its own thing. So tuning postgresql is not just changing the postresql.conf file, but also tuning the OS. Something which you cannot do in a DBaaS setup.

As we were able to handle the peak business load of an enterprise business with ease on a vanilla postgresql setup, I'm confident that starting with a DBaaS is a good option. But once things start to become serious I would really switch to a IaaS (and running your own postgresql) where you get more control, and thus more responsibility.
Also, I can really recommend to reach out one of the many companies which commercially support PostgreSQL to do "health check" at some point in time when you are in proper business. A lot of them also offer DBA services for when it is needed/requested. So you have the benefit of a platform under your control, with the expertise within reach when needed.

[Daniel da Rocha]

that sounds like a good strategy. Start with a DBaaS to avoid common hiccups that newbies might encounter. thanks for the detailed reply!

[Dian Fay]

The real selling point for managed database solutions is that they take care of backups and the like for you. Local databases are still perfectly sufficient for non-prod environments, and if you need specialized tuning or use foreign data wrappers extensively managed isn't an option. But if you just need A Database Somewhere the cloud is usually as good a place to put it as any. Money spent there is money/time you're not spending on routine care and feeding.

The only vendor I've worked with is Amazon. I can't give them an unqualified recommendation; the AWS interface is obtuse and their modifications to the Postgres core sometimes cause problems.

[Daniel da Rocha]

I was actually going the route of having a DO droplet where I have my node app and its postgres db all in. And that would be already my production environment.

So as long as I am willing to invest "routine care and feeding", it should be ok?

[Dian Fay]

I don't know anything about DigitalOcean specifically, but in principle yes. If it's a container or vm, make sure your data directory is in a permanent volume instead of ephemeral storage.

[Adrian B.G.]

I will use them and recommend managed hostings for

• Teams with low funds and small servers
• Lack of sys admins
• Prototypes and mvps

One more thing that people negliject when comparing costs they forget to include:

• Cost of maintenance the service, OS, libs and hardware
• Cost of know how, a good comparable sys admin will cost
• Cost of working backups, test that a restore works
• Cost of security patches

When I say costs I mean all resources: time, money, time that is Not spent building features. Cost of business losees if someone forgets to update a critical component.

All these costs probably will triple when you add replication. Ah and I forget to mention the networking between them?

[Daniel da Rocha]

makes sense.
which ones did you use already? any recommendation?
what about costs, how much are you investing on DBaaS per month, for what size of applications?

thanks!!

[Adrian B.G.]

I don't use Postgres so I don't know the specifics.

For my own learning projects I pay 0$ because there are too few users so I'm in the free tier.

You don't really have a separate choice for the database, you have to choose a provider for the entire app, you do not want to have a cross-datacenter connection between your app and your database, the latency will kill you. So if you host on google cloud use their postgress, and so on.

[rhymes]

I have some experience with Heroku Postgres.

It's more or less a normal PostgreSQL 10 database in the cloud. They manage backups, patches and you can set up a read replica in a few clicks.

This is the list of extensions they have enabled: devcenter.heroku.com/articles/hero...

You can easily export data and migrate away if you need to.

[Daniel da Rocha]

I also found this, and one person pointed this out:

If your application is the source of the service for which people pay money and the database is pretty "dumb" (select, insert, delete) then I'd say yes. If your database has extensions, functions, that are unique and valuable, then I'd say you would be better off hosting your own.

My case is definitely the latter. My Postgres is not "dumb" at all, as I stored the business logic in there, with extensions and functions.

Is the user's statement still true?

[Daniel da Rocha]

Answering my own question:

It seems some providers support extensions and procedural languages, which would still allow my db to stay "smart"...

I guess I just have to try them out and see which one sticks....

[rhymes]

Yeah, check if they have all the extensions you need.