Hi,
I am currently investigating whether I could have Postgres return its
results in the msgpack [1] format and it seems someone already did most
of the work: Patrik Simek came up with encoder and decoder functions
[2]. Unfortunately it is lacking support for numbers in real and double
format. To add support for this, real and double numbers need to be
represented as their big-endian IEEE754 bytes [3]. Is there any way in
Postgres to obtain these relatively quickly?
As an example, this is what [2] does to represent a 16bit integers (here
available as variable "_numeric"):
_pack = E'\\315'::bytea
|| set_byte(E' '::bytea, 0, (_numeric::integer >> 8) & 255)
|| set_byte(E' '::bytea, 0, _numeric::integer & 255);
Using the msgpack binary format would help me improve performance of
some of my heavier back-end calls: I have a aggregation table which acts
more or less as a materialized view, where each data entry is currently
stored as jsonb. Per client request I currently look up ~5 MB of data
(mainly lists of numbers) and send it to the user's browser front-end.
At the moment I am encoding msgpack from Python (which I use for my
back-end), but some preliminary tests show that I could speed things up
quite a bit by preventing the Postgres driver (psycopg2) to parse the
JSON result and me converting to msgpack in Python.
Thanks,
Tom
[1] https://msgpack.org
[2] https://github.com/patriksimek/msgpack-postgres
[3] https://github.com/msgpack/msgpack/blob/master/spec.md#float-format-family