Re: Would PostgreSQL adapt to a HYPOTHETICAL hardware that grows and shrinks as load increases?

That makes a lot of sense now.

Thanks for explaining.

Em ter., 21 de dez. de 2021 11:16, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
escreveu:

> On Mon, 2021-12-20 at 15:33 -0300, Jean Baro wrote:
> > Let's imagine there is a type of Server(Physical, Unikernel, or
> Hypervisor,
> > doesn't matter here) that could allocate more RAM, IO bus, Network
> bandwidth,
> > or CPU power through API calls. The increase or decrease would take a few
> > milliseconds to effectively take place.
> >
> > This hypothetical VM would receive commands from external monitoring
> tools
> > that detect load and complex queries (through statistics and ML) and
> would
> > decide to adjust the VM size accordingly,
> >
> > I have a rough idea how complex (or impossible) it would be for ANY
> modern
> > database to rely on an always-changing "hardware". As there are many CORE
> > datasets in memory and statistics around the hardware, so changing this
> > every now and then seems to require a completely different strategy.
> >
> > I am not an expert but as I see more and more Serverless solutions
> tackling
> > RDBMS, and PostgreSQL being the leader in the cloud world, I was
> wondering
> > if one day PostgreSQL could take advantage of these scalable VMs.
> > * It seems to me that scaling up could be less complex, as PG wouldn't
> > have to select what to invalidate from its cache layers and
> statistics.
> > * Statistics would be affected every time the size of the instance
> changes
> > * The instance should probably have a minimum size to avoid cold starts.
> > So scaling to zero wouldn't be possible.
> > * If the server where the VM/Unikernel is running has no more available
> > resources to grow, what should be done (maybe this machine should
> share
> > resources with SPOT only, so it could forcibly deallocate the spot
> > instances before increasing the size of the DB VM?)
>
> PostgreSQL does not examine the operating system resources. Doing so
> would be
> difficult, because that would have to support all (or at least most of the)
> operating systems supported by PostgreSQL, and the differences are
> substantial.
>
> On the other hand, there are a couple of parameters, like
> "effective_cache_size",
> that tell PostgreSQL something about the operating system resources, and
> other
> parameters like "work_mem" that might change with the available resources
> are configurable at runtime.
>
> So while I don't see PostgreSQL "self-tuning", it should be possible to
> write
> a piece of software that monitors the resources on a given system and
> adjusts PostgreSQL accordingly. This will of course never be a "one size
> fits all",
> because the correct configuration depends a lot on the workload.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>