Re: jsonb format is pessimal for toast compression

Josh Berkus <josh@agliodbs.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