Thread: Get IEEE754 bytes of real and double types for msgpack encoding

Get IEEE754 bytes of real and double types for msgpack encoding

From
Tom Kazimiers
Date:
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