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.
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 |