Re: Avoiding unnecessary writes during relation drop and truncate

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> ISTM that buffers belonging to the victim relation would not necessarily
> stay in memory.

Right. They'd be unpinned and therefore candidates for being written
out and recycled. So we *might* write them before they are dropped.
That's still better than *definitely* writing them.

The particular case that annoyed me into thinking about this was
using strace to watch a backend manipulate a temp table, and seeing
it carefully write out a bunch of pages from local buffers just
before it dropped the temp table :-(. For local buffers there are
no checkpoints nor bgwriter and so accumulation of a lot of dirty
pages can be expected. It didn't matter all that much before
yesterday, with the size of the local buffer pool hardwired at 64,
but when there are hundreds or thousands of local buffers it'll
be important to suppress useless writes.

> Removing FlushRelationBuffers in those circumstances will save a scan of
> shared_buffers, but will it save I/O? Perhaps not, but I care more about
> the O(N) operation on shared_buffers than I do about the I/O.

Realistically, wasted I/O costs more. But yeah, saving one scan of the
buffer arena is a nice side benefit.

regards, tom lane