Re: Add pg_walinspect function with block info columns

On Wed, Mar 22, 2023 at 8:35 AM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> After reading what you said, I was interested to see how substantial the
> I/O cost with non-compressed FPI would be.
>
> Using a patch with a parameter to pg_get_wal_block_info() to skip
> outputting FPI, I found that on a fast local nvme ssd, the timing
> difference between doing so and not still isn't huge -- 9 seconds when
> outputting the FPI vs 8.5 seconds when skipping outputting FPI. (with
> ~50,000 records all with non-compressed FPIs).
>
> However, perhaps obviously, the I/O cost is worse.
> Doing nothing but
>
> SELECT * FROM pg_get_wal_block_info(:start_lsn, :end_lsn, true)
> where fpi is not null;
>
> per iostat, the write latency was double for the query which output fpi
> from the one that didn't and the wkB/s was much higher.

I think that we should also have something like the patch that you
wrote to skip FPIs. It's not something that I feel as strongly about
as the main point about including all the fields from
pg_get_wal_records_info. but it does seem worth doing.

> I have had use for block info without seeing the FPIs, personally.

I'd go further than that myself: I haven't had any use for FPIs at
all. If I was going to do something with FPIs then I'd just use
pg_waldump, since I'd likely want to get them onto the filesystem for
analysis anyway. (Just my experience.)

--
Peter Geoghegan