Re: jsonb format is pessimal for toast compression

So, I finally got time to test Tom's latest patch on this.

TLDR: we want to go with Tom's latest patch and release beta3.

Figures:

So I tested HEAD against the latest lengths patch. Per Arthur Silva, I
checked uncompressed times for JSONB against compressed times. This
changed the picture considerably.

TABLE SIZES
-----------

HEAD

?column? | pg_size_pretty
---------------------+----------------
json text format | 393 MB
jsonb: compressed | 1147 MB
jsonb: uncompressed | 1221 MB

PATCHED

?column? | pg_size_pretty
---------------------+----------------
json text format | 394 MB
jsonb: compressed | 525 MB
jsonb: uncompressed | 1200 MB

EXTRACTION TIMES
----------------

HEAD

Q1 (search via GIN index followed by extracting 100,000 values from rows):

jsonb compressed: 4000
jsonb uncompressed: 3250

Q2 (seq scan and extract 200,000 values from rows):

json: 11700
jsonb compressed: 3150
jsonb uncompressed: 2700

PATCHED

Q1:

jsonb compressed: 6750
jsonb uncompressed: 3350

Q2:

json: 11796
jsonb compressed: 4700
jsonb uncompressed: 2650

----------------------

Conclusion: with Tom's patch, compressed JSONB is 55% smaller when
compressed (EXTENDED). Extraction times are 50% to 70% slower, but this
appears to be almost entirely due to decompression overhead. When not
compressing (EXTERNAL), extraction times for patch versions are
statistically the same as HEAD, and file sizes are similar to HEAD.

USER REACTION
-------------

I polled at both PDXpgDay and at FOSS4G, asking some ~~ 80 Postgres
users how they would feel about a compression vs. extraction time
tradeoff. The audience was evenly split.

However, with the current patch, the user can choose. Users who know
enough for performance tuning can set JSONB columns to EXTERNAL, and the
the same performance as the unpatched version.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com