Re: Bypassing shared_buffers

Do you foresee any difficulties in implementation of the "unwarm"
operation? It requires a cache flush operation,
so I'm curious how complicated that is (probably there is a reason this is
not supported by Postgres by now? mssql and oracle support stuff like that
for a long time)
Cluster restart is not an option for us unfortunately, as it will be
required for each query pretty much, and there are a lot of them.
An ideal solution would be, if it's possible, to test it in parallel with
other activities...
Evicting all the other stuff using pg_prewarm is an interesting idea though
(if a large prewarm operation really evicts all the previously stored data
reliably).
It's a bit hacky, but thanks, I think it's possible to make this work with
some effort.
It will require exclusive access just for that testing, which is not ideal
but may work for us.

-Vladimir )churyukin

On Wed, Jun 14, 2023 at 7:29 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Thu, Jun 15, 2023 at 1:37 PM Vladimir Churyukin
> <vladimir(at)churyukin(dot)com> wrote:
> > Ok, got it, thanks.
> > Is there any alternative approach to measuring the performance as if the
> cache was empty?
>
> There are two levels of cache. If you're on Linux you can ask it to
> drop its caches by writing certain values to /proc/sys/vm/drop_caches.
> For PostgreSQL's own buffer pool, it would be nice if someone would
> extend the pg_prewarm extension to have a similar 'unwarm' operation,
> for testing like that. But one thing you can do is just restart the
> database cluster, or use pg_prewarm to fill its buffer pool up with
> other stuff (and thus kick out the stuff you didn't want in there).
>