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.
On Thu, Jun 15, 2023 at 1:37 PM Vladimir Churyukin <vladimir@churyukin.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).