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
```
