Re: jsonb format is pessimal for toast compression

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 08/14/2014 04:02 PM, Tom Lane wrote:
>> It would be useful to see min/max/avg of pg_column_size() in both
>> these cases.

> Well, this is 9.4, so I can do better than that. How about quartiles?

> thetype | colsize_distribution
> ---------+----------------------------
> json | {1777,1803,1890,1940,4424}
> jsonb | {5902,5926,5978,6002,6208}

OK. That matches with the observation about being mostly toasted or not
--- the threshold for pushing out-of-line would be something a little
under 2KB depending on the other columns you had in the table.

What's more, it looks like the jsonb data is pretty much never getting
compressed --- the min is too high for that. So I'm guessing that this
example is mostly about the first_success_by threshold preventing any
compression from happening. Please, before looking at my other patch,
try this: in src/backend/utils/adt/pg_lzcompress.c, change line 221
thusly:

- 1024, /* Give up if no compression in the first 1KB */
+ INT_MAX, /* Give up if no compression in the first 1KB */

then reload the jsonb data and give us the same stats on that.

regards, tom lane