Change JSONB's on-disk format for improved performance

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 29 September 2014 11:29 UTC

The original design used an array of offsets into the variable-length portion of a JSONB container. However, such an array is basically uncompressible by simple compression techniques such as TOAST's LZ compressor. That's bad enough, but because the offset array is at the front, it tended to trigger the give-up-after-1KB heuristic in the TOAST code, so that the entire JSONB object was stored uncompressed; which was the root cause of bug #11109 from Larry White.

To fix without losing the ability to extract a random array element in O(1) time, change this scheme so that most of the JEntry array elements hold lengths rather than offsets. With data that's compressible at all, there tend to be fewer distinct element lengths, so that there is scope for compression of the JEntry array. Every N'th entry is still an offset. To determine the length or offset of any specific element, we might have to examine up to N preceding JEntrys, but that's still O(1) so far as the total container size is concerned. Testing shows that this cost is negligible compared to other costs of accessing a JSONB field, and that the method does largely fix the incompressible-data problem.

While at it, rearrange the order of elements in a JSONB object so that it's "all the keys, then all the values" not alternating keys and values. This doesn't really make much difference right at the moment, but it will allow providing a fast path for extracting individual object fields from large JSONB values stored EXTERNAL (ie, uncompressed), analogously to the existing optimization for substring extraction from large EXTERNAL text
values.

Bump catversion to denote the incompatibility in on-disk format. We will need to fix pg_upgrade to disallow upgrading jsonb data stored with 9.4 betas 1 and 2.

Heikki Linnakangas and Tom Lane

def4c28 Change JSONB's on-disk format for improved performance.
src/backend/utils/adt/jsonb.c | 4 +-
src/backend/utils/adt/jsonb_util.c | 383 +++++++++++++++++++++++++-----------
src/include/catalog/catversion.h | 2 +-
src/include/utils/jsonb.h | 125 +++++++-----
4 files changed, 357 insertions(+), 157 deletions(-)

Upstream: git.postgresql.org

  • Share