Re: Relpartbound, toasting and pg_class

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Relpartbound, toasting and pg_class
Date: 2017-06-12 23:00:02
Message-ID: 32739.1497308402@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2017-06-12 18:10:52 -0400, Tom Lane wrote:
>> it'd be worthwhile checking some actual examples rather than guessing.

> It's indeed not very compact. E.g a simple example with small types:

> CREATE TABLE partitioned(a int, b int, c timestamptz, data text) PARTITION BY RANGE (a, b, c);
> CREATE TABLE partitioned_child1 PARTITION OF partitioned FOR VALUES FROM (1, 1, '2017-01-01') TO (1, 1, '2017-02-01');

> postgres[6961][1]=# SELECT relname, pg_column_size(relpartbound), length(relpartbound), pg_column_size(pg_class) FROM pg_class WHERE relpartbound IS NOT NULL;
> ┌───────────────────────────────┬────────────────┬────────┬────────────────┐
> │ relname │ pg_column_size │ length │ pg_column_size │
> ├───────────────────────────────┼────────────────┼────────┼────────────────┤
> │ partitioned_child1 │ 1355 │ 1351 │ 1523 │
> │ partitioneded_list_committers │ 1130 │ 8049 │ 1298 │
> └───────────────────────────────┴────────────────┴────────┴────────────────┘

So, counting on my fingers, you'd need something like twenty partitioning
columns before you hit trouble with the RANGE syntax. I'm willing to live
with that, especially since that's *before* compression. (Your example
does not show that compression was ineffective; more likely it wasn't
tried, since the pg_class tuple was under 2K.)

The LIST case might be more of a problem, but I'm not sure. It looks like
that eats circa 150 bytes per value in outfuncs.c format, but they're
*very* repetitive and compress really well. I get about 16 stored bytes
per value with a long list of integer keys, so it looks like you could
approach 500 values in the LIST before hitting trouble. Maybe a few less
with wider datatypes.

On the whole, I'm inclined to agree with Peter and Alvaro that this is
fine, at least for the short term. Even in the long term, I doubt we
need toastability, just a more compact representation than an expression
tree. bytea storage of an array, perhaps? Or maybe better, use anyarray
like we do in pg_statistic, so that it prints legibly.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-06-12 23:17:34 Re: Relpartbound, toasting and pg_class
Previous Message Alvaro Herrera 2017-06-12 22:49:25 Re: Re: BUG #14680: startup process on standby encounter a deadlock of TwoPhaseStateLock when redo 2PC xlog