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 MBjsonb: compressed | 1147 MBjsonb: uncompressed |
1221MB
PATCHED
?column? | pg_size_pretty
---------------------+----------------json text format | 394 MBjsonb: compressed | 525 MBjsonb: uncompressed |
1200MB
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
