Re: Cluster table based on grand parent?

[Ron <ronljohnsonjr@xxxxxxxxx>]

    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.