Subject: Re: Bypassing shared_buffers

From: Andrey M. Borodin <[email-masked]>
Date: Mon, 19 Jun 2023 12:47:40 +0300

Hi!

> On 15 Jun 2023, at 03:57, Vladimir Churyukin <[email-masked]> wrote:
> 
> Hello,
> 
> There is often a need to test particular queries executed in the worst-case scenario, i.e. right after a server restart or with no or minimal amount of data in shared buffers. In Postgres it's currently hard to achieve (other than to restart the server completely to run a single query, which is not practical). Is there a simple way to introduce a GUC variable that makes queries bypass shared_buffers and always read from storage? It would make testing like that orders of magnitude simpler. I mean, are there serious technical obstacles or any other objections to that idea in principle? 

Few months ago I implemented "drop of caches" to demonstrate basic structure of shared buffers [0]. The patch is very unsafe in the form is was implemented, but if you think that functionality is really useful (it was not intended to be) I can try to do the same as extension.

it worked like "SELECT FlushAllBuffers();" and what is done resembles checkpoint, but evicts every buffer that can be evicted. Obviously, emptied buffers would be immediately reused by concurrent sessions.

Best regards, Andrey Borodin.

[0] https://www.youtube.com/watch?v=u8BAOqeKnwY