Re: Recovery performance of standby for multiple concurrent truncates on large tables

On 2018-07-19 00:53:14 +0000, Jamison, Kirk wrote:
> Hi,
> Thank you for your replies.
>
> On Tue, July 10, 2018 4:15 PM, Andres Freund wrote:
> >I think you'd run into a lot of very hairy details with this approach. Consider what happens if client processes need fresh buffers and need to write out a victim buffer. You'll need to know that the relevant buffer is actually invalid. Thus the knowledge about the "delayed" drops would need to be in shared buffers and scanned on every dirty buffer writeout.
>
> Yes, you're right. There are problems associated with checkpoint as you pointed out. I just thought of touching the involved process on writing dirty pages to the disk, which are both the functions of checkpoint and background writer.
> > How about using the background writer for this? ...
> > ...
> And now that I think about it, the suggestion of Ants above on "background writer" would seem to work better, as bg writer is more active and continuously write dirty pages but checkpointer only does it in interval. This seems to be a more viable solution and I'd appreciate advice. I'll explore around this idea, and if possible, I'd like to develop a solution for the next commitfest.

It'd have very similar problems as I pointed out above.

> > I personally think we should rather just work towards a ordered buffer mapping implementation.
>
> I understand that the main problem lies on shared_buffers scanning and that a buffer mapping implementation is underway(?) for PG12. I am not sure if the community has arrived at a consensus for that long-term fix. Would the community also welcome any minor/smaller-scale improvements (just for this particular problem on wal recovery for truncate table)? If yes, then I'd like to work on a possible solution.

I think this is a case where the potential work arounds are complex
enough to use significant resources to get right, and are likely to make
properly fixing the issue harder. I'm willing to comment on proposals
that claim not to be problmatic in those regards, but I have *SERIOUS*
doubts they exist.

Greetings,

Andres Freund