One TOAST fits ALL: JSONB TOASTER
We have submitted TOAST API for PG15.
Below is a performance analysis of JSONB, which uses JSONB TOASTER implemented using TOAST API.
Each jsonb looks like: key1, looong key2[], key3, loong key4[]. Check slide #10 of our talk at pgvision 2021 for description of data and queries used in benchmarks.
The problem of jsonb scaling is clearly seen on pictures below.
We implemented custom JSONB TOASTER (as an extension!) using proposed TOAST API and present the results of different optimizations in regard to jsonb size (inline, compressed inline, toasted) and key position. We see (the first picture), that all optimizations (+all) result in two orders of magnitude increase in performance.
Other pictures show performance of key access for different keys and jsonb size.
Notes:
TOAST API is the further extension of postgres extensibility, it allows per data type custom storage for long values (can be implemented as an extension). Our plan is to have the API in PG15 , plus GSON - Generic JSON API (http://www.sai.msu.su/~megera/postgres/talks/json-build-2020.pdf), so we can publish later jsonb_toaster as an extension. Since we decided to concentrate on JSONB only, we have removed JSON support in GSON (it can be restored later) to make the patch smaller.
See
Understanding jsonb performance.
Below is a performance analysis of JSONB, which uses JSONB TOASTER implemented using TOAST API.
Each jsonb looks like: key1, looong key2[], key3, loong key4[]. Check slide #10 of our talk at pgvision 2021 for description of data and queries used in benchmarks.
The problem of jsonb scaling is clearly seen on pictures below.
We implemented custom JSONB TOASTER (as an extension!) using proposed TOAST API and present the results of different optimizations in regard to jsonb size (inline, compressed inline, toasted) and key position. We see (the first picture), that all optimizations (+all) result in two orders of magnitude increase in performance.
Other pictures show performance of key access for different keys and jsonb size.
Notes:
TOAST API is the further extension of postgres extensibility, it allows per data type custom storage for long values (can be implemented as an extension). Our plan is to have the API in PG15 , plus GSON - Generic JSON API (http://www.sai.msu.su/~megera/postgres/talks/json-build-2020.pdf), so we can publish later jsonb_toaster as an extension. Since we decided to concentrate on JSONB only, we have removed JSON support in GSON (it can be restored later) to make the patch smaller.
See
Understanding jsonb performance.