Re: Bypassing shared_buffers

On Thu, Jun 15, 2023 at 2:51 PM Vladimir Churyukin
<vladimir(at)churyukin(dot)com> wrote:
> 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)

If they have a way to kick individual relations out of the buffer
pool, then I suspect they have an efficient way to find the relevant
buffers. We'd have to scan the entire buffer pool, or (for small
relations), probe for blocks 0..n (when we know that n isn't too
high). We'll probably eventually get something tree-based, like
operating system kernels and perhaps those other databases use for
their own buffer pools, which is useful for I/O merging and for faster
DROP, but until then you'll face the same problem while implementing
unwarm, and you'd probably have to understand a lot of details about
bufmgr.c and add some new interfaces.

As Tom says, in the end it's going to work out much like restarting,
which requires a pleasing zero lines of new code, perhaps explaining
why no one has tried this before... Though of course you can be more
selective about which tables are zapped.

> 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.

You can use pg_buffercache to check the current contents of the buffer
pool, to confirm that a relation you're interested in is gone.

https://www.postgresql.org/docs/current/pgbuffercache.html#PGBUFFERCACHE-COLUMNS

I guess another approach if you really want to write code to do this
would be to introduce a function that takes a buffer ID and
invalidates it, and then you could use queries of pg_buffercache to
drive it. It would simplify things greatly if you only supported
invalidating clean buffers, and then you could query pg_buffercache to
see if any dirty buffers are left and if so run a checkpoint and try
again or something like that...

Another thing I have wondered about while hacking on I/O code is
whether pg_prewarm should also have an unwarm-the-kernel-cache thing.
There is that drop_cache thing, but that's holus bolus and Linux-only.
Perhaps POSIX_FADV_WONTNEED could be used for this, though that would
seem to require a double decker bus-sized layering violation.