Re: Add pg_walinspect function with block info columns

On Fri, Mar 17, 2023 at 5:51 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> Yes. The CPU cost is one thing, but I am also worrying about the
> I/O cost with a tuplestore spilling to disk a large number of FPIs,
> and some workloads can generate WAL so as FPIs is what makes for most
> of the contents stored in the WAL. (wal_compression is very effective
> in such cases, for example.)
>
> It is true that it is possible to tweak SQLs that exactly do that with
> a large amount of data materialized, or just eat so much CPU that they
> basically DoS the backend. Still I'd rather keep a minimalistic
> design for each function with block_info having only one field able to
> track back to which record a block information refers to, and I'd like
> to think one able to look at WAL internals will be smart enough to
> write SQL in such a way that they avoid that on a production machine.
> The current design allows to do that in this view, but that's just one
> way I see how to represent structures at SQL level.

Not really. It has nothing to do with some abstract ideal about how
the data should be logically structured. It is about how the actual
underlying physical data structures work, and are accessed in
practice, during query execution. And its about the constraints placed
on us by the laws of physics. Some ways of doing this are measurably,
provably much faster than other ways. It's very much not like we're
querying tables whose general structure is under our control, via
schema design, where the optimizer could reasonably be expected to
make better choices as the data distribution changes. So why treat it
like that?

Right now, you're still basically standing by a design that is
*fundamentally* less efficient for certain types of queries -- queries
that I am very interested in, that I'm sure many of us will be
interested in. It's not a matter of opinion. It is very much in
evidence from Bharath's analysis. If a similar analysis reached the
opposite conclusion, then you would be right and I would be wrong. It
really is that simple.

> This said, your point about having rec_blk_ref reported as an empty
> string rather than NULL if there are no block references does not feel
> natural to me, either.. Reporting NULL would be better.

You have it backwards. It outputs an empty string right now. I want to
change that, so that it outputs NULLs instead.

--
Peter Geoghegan