Re: Cluster table based on grand parent?

On Tue, Mar 28, 2023 at 5:08 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2023-03-28 07:26:47 -0700, Adrian Klaver wrote:
> > On 3/28/23 06:17, Dominique Devienne wrote:
> > > PS: At this point, I don't even know how much cluster affects
> performance.
>
> I think that this depends a lot on your access patterns

As I wrote, per-parent access to child and grandchild rows is typical.
So w/o parent-based clustering of grandchild table(s), access those rows
could potential seek to several (~50, see below) smaller clusters with
arbitrary gaps.

Cardinality is a few to ~20K on parent, x10-x50 on child, x20 - x100 on
grandchild.
So total row count rarely exceeds the 1M - 10M range. But there are
LOBs/BYTEa...

> (especially on
> how much you update the grandchild table and whether those updates can
> be HOT), so you will probably have to measure it yourself with a
> realistic work load.
>

In this particular case, there aren't much UPDATEs, because of a deficiency
of the client applications, which mostly do DELETE+INSERT instead of
UPDATEs.
Although we have to cascade modified dates up the parent hierarchy,
so some UPDATEs do occur, but mostly on the less numerous child and parent
tables.

> (Personally I doubt the impact is large, but I don't know your data or
> your access patterns.)
>

OK.

> > > But because it can affect the schema structure (by denormalizing),
> > > i'd rather know early.
> >
> > You will need to explain to me how it denormalizes? It reorders rows by
> > index definition and does not maintain that order over updates and
> inserts.
>
> I think he means that in order to cluster the grandchild table by the
> parent.id
> he would have to denormalize the table.
>

exactly.

Thanks for your input.