Re: Conundrum with scaling out of bottleneck with hot standby, PgPool-II, etc.

On Wed, Dec 23, 2020 at 07:34:01PM +0100, Marco Colli wrote:
>  Hello,
>
> I asked the same question to myself in the past years.
>
> I think that the question boils down to: 
> How can I achieve unlimited database scalability? 
> Is it possible to have linear scalability (i.e. throughput increases
> proportionally to the number of nodes)?
>
> The answer is "sharding". It can be a custom solution or a database that
> supports it automatically. In this way you can actually split data across
> multiple nodes and the client contacts only the relevant servers for that data
> (based on a shard key). See also https://kubernetes-rails.com/#conclusion about
> database. Take a look at how Cassandra, MongoDB, CouchDB and Redis Cluster work
> for example: however there are huge limitations / drawbacks that come along
> with their unlimited-scalability strategies.
>
> For hot standbys, those are only useful if you have a relatively small number
> of writes compared to reads. With that slave nodes you only scale the *read*
> throughput.

Agreed. There are really two parameters:

1. percentage of reads vs writes
2. number of standbys

If you have a high value for #1, it makes sense to use pgpool, but
having only one standby doesn't buy you much; add three, and you will
see an impact. Second, if writes are high, only scaling up the primary
or adding sharding will help you. It is kind of an odd calculus, but it
makes sense.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee