pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Cluster table based on grand parent?
Date: Tue, 28 Mar 2023 17:08:09 +0200
Message-ID: <20230328150809.myepvgkbvytazk2d@hjp.at> (raw)
In-Reply-To: <28cfc94c-f778-548d-1159-e33627e44eec@aklaver.com>
References: <CAFCRh-_MVYOEVoX9Y8neu_ZmSCvr6iFCSMQQxVW6veoPg-8g0w@mail.gmail.com>
	<28cfc94c-f778-548d-1159-e33627e44eec@aklaver.com>

On 2023-03-28 07:26:47 -0700, Adrian Klaver wrote:
> On 3/28/23 06:17, Dominique Devienne wrote:
> > PS: At this point, I don't even know how much cluster affects performance.

I think that this depends a lot on your access patterns (especially on
how much you update the grandchild table and whether those updates can
be HOT), so you will probably have to measure it yourself with a
realistic work load.

(Personally I doubt the impact is large, but I don't know your data or
your access patterns.)

> >      But because it can affect the schema structure (by denormalizing),
> > i'd rather know early.
> 
> You will need to explain to me how it denormalizes? It reorders rows by
> index definition and does not maintain that order over updates and inserts.

I think he means that in order to cluster the grandchild table by the parent.id
he would have to denormalize the table. I.e. instead of like this:

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

The last create statement would have to be like this:

create table grandchild (
    id int generated always as identity primary key,
    parent int not null references child(id) on delete cascade,
    grandparent int not null references parent(id) on delete cascade,
    name text not null,
    unique(grandparent, parent, name)
);

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (8+ messages)  latest in thread

Message-ID: <20230328150809.myepvgkbvytazk2d@hjp.at>
Permalink:  ../20230328150809.myepvgkbvytazk2d@hjp.at/
Also on:    postgresql.org/message-id/20230328150809.myepvgkbvytazk2d@hjp.at

 · 

reply

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: hjp-pgsql@hjp.at
  Subject: Re: Cluster table based on grand parent?
  In-Reply-To: <20230328150809.myepvgkbvytazk2d@hjp.at>
  References: <CAFCRh-_MVYOEVoX9Y8neu_ZmSCvr6iFCSMQQxVW6veoPg-8g0w@mail.gmail.com> <28cfc94c-f778-548d-1159-e33627e44eec@aklaver.com> <20230328150809.myepvgkbvytazk2d@hjp.at>

* 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