Re: Cluster table based on grand parent?

On 3/28/23 08:17, Dominique Devienne wrote:
> Hi again,
>
> I just sent a question regarding parent/child and cascading FKs.
> But in reality, our schema has not 2 but 3 "layers",
> with an additional grandchild "leaf" table (see below).
>
> Given that many acces patterns are parent-based, i.e. get all
> child of given parent, or get all grandchild of given child, I can
> use [CLUSTER][1] leveraging the natural-key (parent, name) constraint/index.
>
> But for grandchild rows, doesn't mean the rows for a given (grand)parent
> won't be fully clustered? Yes, our software often accesses rows in child
> and grandchild
> for a given parent row.
>
> So can grandchild table(s) be "fully" clustered per-(grand)parent?
> Would that require denormalizing, and adding an extra grandparent column
> FK in grandchild, to achieve that?
> And if that's the case, then there are two "paths" to CASCADE a delete
> from parent; Would that be a problem? (w.r.t. performance or otherwise?)
>
> Finally, does cluster affect associated toast tables too? (the doc doesn't
> say)
>
> Thanks for any insights. --DD
>
> PS: At this point, I don't even know how much cluster affects performance.
>     But because it can affect the schema structure (by denormalizing), i'd
> rather know early.
>
> [1]: https://www.postgresql.org/docs/current/sql-cluster.html
>
> ```
> dd=> create table parent (id int generated always as identity primary key,
> name text not null unique);
> CREATE TABLE
>
> dd=> create table child (id int generated always as identity primary key,
> parent int not null references parent(id) on delete cascade, name text not
> null, unique(parent, name));
> CREATE TABLE
>
> dd=> create table grandchild (id int generated always as identity primary
> key, parent int not null references child(id) on delete cascade, name text
> not null, unique(parent, name));
> CREATE TABLE
> ```

You can only get from parent to grandchild via//child.id to
grandchild.parent, so why not cluster grandchild on grandchild.parent?

--
Born in Arizona, moved to Babylonia.