Re: Relpartbound, toasting and pg_class

On 2017-06-12 18:10:52 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > Tom Lane wrote:
> >> How about gathering some actual evidence on the point --- ie, how big
> >> a partition expression do you need to make it fall over?
>
> > You'd need a 2kB expression (after compression) in
> > relpartbound before you hit a problem here. I wouldn't worry about it
> > at this stage ...
>
> Actually, as long as the expression was less than ~8KB after compression,
> it'd work. But I don't have a clear idea of complex an expression that
> really is --- we've never made much of an effort to make the outfuncs.c
> representation compact, so maybe there's an issue here? As I said,
> 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');

And with LIST style partitioning it'd be quite reasonable to have
significantly longer IN() lists, no? Compression will save us to some
degree here, but it's not going super far, especially with pglz. I
think we have some hope of compressing out some of the serialization
overhead, but not more.

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 │
└───────────────────────────────┴────────────────┴────────┴────────────────┘

We can see in the latter, which just is a LIST partition with every
committers name & username, that compression helps, but in the earlier
example from above it doesn't.

> > Not on point, but this conversation reminded me of
> > https://www.commandprompt.com/blog/grant_schema_usage_to_2500_users_no_can_do/
> > wherein you needed 2500 roles in an ACL column before it became a
> > problem -- and the project's stance is not to bother supporting that
> > case.
>
> Quite on point really. But there we knew how many entries it took to
> break it, and we also knew that good practice wouldn't hit the problem
> because you'd use groups instead of a lot of individual ACL entries.
> I don't think we're in a position yet to just dismiss this question.

Yea, I don't think those are entirely comparable. I'm also not sure
it was actually the right decision back then.

Greetings,

Andres Freund