On Tue, Mar 28, 2023 at 5:08 PM Peter J. Holzer <hjp-pgsql@xxxxxx> 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.
