Re: Cluster table based on grand parent?

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"