The following bug has been logged on the website:
Bug reference: 11109
Logged by: Larry White
Email address: ljw1001@gmail.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.