From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Cluster table based on grand parent?
Date: Tue, 28 Mar 2023 11:05:54 -0500
Message-ID: <a46c2fb9-e77c-12fe-c18d-1c43dcc54ec5@gmail.com> (raw)
In-Reply-To: <CAFCRh-_MVYOEVoX9Y8neu_ZmSCvr6iFCSMQQxVW6veoPg-8g0w@mail.gmail.com>
References: <CAFCRh-_MVYOEVoX9Y8neu_ZmSCvr6iFCSMQQxVW6veoPg-8g0w@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: Cluster table based on grand parent?
In-Reply-To: <a46c2fb9-e77c-12fe-c18d-1c43dcc54ec5@gmail.com>
References: <CAFCRh-_MVYOEVoX9Y8neu_ZmSCvr6iFCSMQQxVW6veoPg-8g0w@mail.gmail.com> <a46c2fb9-e77c-12fe-c18d-1c43dcc54ec5@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox