Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phBpj-0001Oh-JY for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 16:06:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phBpi-0007Mq-5e for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 16:06:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phBph-0007Mh-O7 for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 16:06:01 +0000 Received: from mail-oi1-x22d.google.com ([2607:f8b0:4864:20::22d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phBpe-00008j-J6 for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 16:06:01 +0000 Received: by mail-oi1-x22d.google.com with SMTP id r14so3645382oiw.12 for ; Tue, 28 Mar 2023 09:05:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680019555; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=jqe0T6iEOI1o4lL7LjtpOueGS+AaiDyC+Em8CljTvHs=; b=O+i7tCVtNkWIILYevZzJIdez/6IU4n5PMYchtNo4k4y33Rmy1DeWAPdz9ZlP9+OLef cUOCaJSto+bqS4VRcw38KanMWpf++oOHg7O5v3RXEafO/GqCorZ13exZcJ9eC7CKLXC5 4Nz+mTUYkmw2hKKrZwzSPicnKzUEmo0m80hmJDXW4ilBvkb89Nf1xEvNBhMLkxvnrSrR 169+X9JUNY/SjyqB/knet2aH3EimnzViPeRdSdP6bKgxpWzJBiYZxBg2ajpReUtClKag rhcsskz/hPeaXWNMwjdb5B1NHDhe/tPmcKcoB7ZuPe7k+EZWVwYj4X+DSVi3e7FB8LPr RbVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680019555; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=jqe0T6iEOI1o4lL7LjtpOueGS+AaiDyC+Em8CljTvHs=; b=qK2luZWq4u7EhJym4YfNuboO40L5Ml5F2QL85kD/HnhFCGlCnw5niggVS+nO53QmpV fBXiUfuVTNxA6Kz5UR3NJYdiJbvInlncHMU0VQKyptKVwYZayHbsaLF1lVicLWiYynND TSIz1+nXnmSHs45WAObyq9rxGMsrmLNKLkST29aGzCjEnmgxQ9HPKp83hb02+musSHl1 jGCqnH3pR58ffjdi11XQTz7uamnlqD21RyD1TpkezATByen9YBfJuGNqd/31htP1tmD7 hEWQ8WUo3BMUKxP7/GwWKvULSsTgXtiz3pYg2NVVTfRDAb1lxVc5a4klq9Iper8RP/BH 294Q== X-Gm-Message-State: AO0yUKVv3GBHm2/vc5eP4QpzQSjcwm6+RyNMRS0U+YR5RwEb06ySzZ4I 4uXIwhIyc6ZZsXYoon1j/0DE6TwP8LM= X-Google-Smtp-Source: AK7set88nOagZQ0Xkt2Vppgn2njG5giI0Alvc/WU5kP+x0T39ktQyn9IJC8L1/TcHmbNqloy25KeGg== X-Received: by 2002:a54:4f8c:0:b0:384:9f4:dd3c with SMTP id g12-20020a544f8c000000b0038409f4dd3cmr7260658oiy.38.1680019555552; Tue, 28 Mar 2023 09:05:55 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:dab:6b8c:9338:df42? ([2600:1700:bd2c:b810:dab:6b8c:9338:df42]) by smtp.googlemail.com with ESMTPSA id r138-20020acaa890000000b00387367989d7sm7240779oie.23.2023.03.28.09.05.55 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 28 Mar 2023 09:05:55 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------vLNMGUGGLTwxfBjwIYdmgej7" Message-ID: Date: Tue, 28 Mar 2023 11:05:54 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: Cluster table based on grand parent? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------vLNMGUGGLTwxfBjwIYdmgej7 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------vLNMGUGGLTwxfBjwIYdmgej7 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.


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