Re: Very Limited Toast Compression on JSONB (9.4 beta 2)

Yes. It was EXTENDED.

As a further test, I dropped the table and rebuilt it, explicitly changing
the EXTENDED designation to EXTERNAL and got exactly the same size TOAST
table. So there was no compression at all with storage set to EXTENDED.

On Thu, Jul 31, 2014 at 11:51 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 07/31/2014 01:44 PM, Larry White wrote:
>
>> Hi,
>>
>> I'm running an experiment on 9.4 beta 2.
>>
>> I put 275,000 identical JSON files into a table using JSONB (one per
>> row). Each raw text file is 251K in size, so the total uncompressed is
>> 69GB. The column storage is set to EXTENDED. There are other toastable
>> columns in the table, but none have more than 36 bytes of data in them.
>>
>> My Toast table is 66GB. I would have expected to get that much (or more)
>> compression just from JSONB being a binary format.
>>
>> If I compress one of these JSON files outside of Postgres, it goes from
>> 251K to 1K. So each file should theoretically fit on a single row in the
>> toast table. In total, the amount well under a GB when compressed
>> outside of PG.
>>
>> Any guesses as to why there is so little compression of this data or how
>> I might remedy the situation?
>>
>
> Are you sure the column storage is EXTENDED and not EXTERNAL?
>
>
>
>> Thanks much for your help.
>>
>>
>> Larry
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>