Re: Using JSONB directly from application

> On 26 February 2018 at 04:05, Anthony Communier <anthony(dot)communier(at)gmail(dot)com> wrote:

> 

> > It would be nice if application connected to a Postrgesql database could

> > send and receive JSONB in binary. It could save some useless text

> > conversion. All works could be done on application side which are often

> > more scalable than database itself.

>

> To support this, you'd need to extract PostgreSQL's jsonb support into a C

> library that could be used independently of backend server infrastructure

> like 'palloc' and memory contexts, ereport(), etc. Or write a parallel

> implementation.

At Shift, we also have use cases that would likely be sped up quite a bit if we could avoid the conversion from JSONB to JSON, and instead pass binary JSONB to the application side and parse it there (in Go).  I doubt we'd want to reuse any of Postgres's C code, and would instead go with your "parallel implementation" idea; I can't imagine it being particularly difficult to implement a JSONB parser from scratch.

All we need, I think, is a Postgres function raw_jsonb(jsonb) that returns bytea but is the identity function at the byte level.  (Or allow a cast from jsonb to bytea.)

Our Go code would then send queries like SELECT col1, col2, raw_jsonb(col3) FROM table1 WHERE ...; I haven't thought in depth about how we'd parse the JSONB in Go, but perhaps we can synthesize a stream of JSON tokens from the binary JSONB (one token at a time, to avoid copies and allocations) and adapt the streaming parser https://github.com/json-iterator/go to turn it into Go values.

Sending raw JSONB to Postgres might also be interesting, but I'd start with receiving.

Would implementing raw_jsonb be as trivial as it sounds?  What about usages like SELECT raw_jsonb(col3->'foo'); does the subobject returned by '->' share structure with the containing object, making the conversion to a self-contained JSONB value less direct?

Can these conversions be implemented without copying the bytes?

An open question about the API contract would be how raw_jsonb would be affected if Postgres introduces a version 2 of JSONB encoding.  My intuition is to punt that problem to the application, and define that raw_jsonb returns whatever version of JSONB is most convenient for Postgres for that particular datum; this minimizes conversion work on the Postgres side, which is the purpose of the mechanism.  Applications that want a stable format can use the conventional textual JSON format.  But I could see a case for making the function raw_jsonb(int, jsonb) and allowing the caller to specify what (maximum?) version of JSONB they want.