BUG #11109: No Toast compression on JSON, JSONB columns

From: ljw1001(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11109: No Toast compression on JSON, JSONB columns
Date: 2014-08-01 18:21:02
Message-ID: 20140801182102.2696.87926@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11109
Logged by: Larry White
Email address: ljw1001(at)gmail(dot)com
PostgreSQL version: 9.4beta2
Operating system: OSX Mavericks
Description:

if PG can, after compression, put an entire JSON or JSONB 'document' into
one row/page in the toast table it does. However, if the document is too big
to fit in one row after compression, it does no compression at all.

I created a test Json file (in part) with this loop:

for (int j = 0; j < 110; j++) {
mediumPayload.getJunk().add("124245etweetwet345gwtretwt43 qwrqwq
qwre qw rsdflkas");
mediumPayload.getJunk().add("q4535 wqrqwrqwrqw2 wrqwrqwrq32232w
kswe sfasrs sdfsd");
}

if the loop runs 110 times as shown, it compresses.
if the loop runs 111 times, it does not:

With 110 iterations:
Extended 8192 bytes (one page)
External 66 MB

With 111 iterations:
Extended 69 MB
External 69 MB

This is not the behavior observed with Text compression in Toast. For very
large files Text compression works correctly. I got these results running
similar tests on larger json files:

Column Type - Storage - TOAST table size
JSONB - EXTERNAL - 2448 MB
JSONB - EXTENDED - 2448 MB
JSON - EXTENDED - 2504 MB
TEXT - EXTERNAL - 2409 MB
TEXT - EXTENDED - 40 MB

To Recreate:
Take a json file that is too large to compress into a single page. insert it
into a table and check the size of the Toast table. Repeat with a file that
is small enough to fit into a single page when compressed (but bigger than
the Toast minimum size). The first file will have no compression and the
second file will compress correctly.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Fleming 2014-08-01 19:55:07 Re: BUG #11028: psql doesn't use 0x01 and 0x02 to inform readline of non-printable PROMPT1 chars
Previous Message danghuu.ken 2014-08-01 18:20:10 BUG #11108: Missing Binaries