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.
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_buffersfrom 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.
that sounds like a good strategy. Start with a DBaaS to avoid common hiccups that newbies might encounter. thanks for the detailed reply!