0

JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string:

{"0.000295":1544.2,"0.000324":1050,"0.000325":40.1,"0.000348":0}

Question: Is it possible to store it in a format that consumes lesser space? Using some CPU cycles to serialize/deserialize the JSON string is acceptable as a compromise for using lesser storage space. The JSON data does not need to be searchable. The JSON object keys are almost always different in different rows.

I am particularly interested in using JSON encoding/compression algorithms like MessagePack with zlib, but how can we use this when inserting the record into the PostgreSQL table?

Note: Also using Node.js with Knex.js to communicate with PostgreSQL. Currently converting JSON objects to strings using Node's JSON.stringify function.

  • 1
    text values (and jsonb which you should use) are automatically compressed if they are longer that approx. 2000 characters – a_horse_with_no_name Apr 6 at 5:06
1

Individual PostgreSQL fields over 2000 bytes are automatically compressed, for TOAST storage. However, this compression is not very good, and is local to just that one PostgreSQL value, so can't compress out repetition that occurs between values (like the same keys being used repeatedly, but only once in each JSON structure). You might want to look at ZSON for compression that uses the whole column for compression context (I've never use it myself).

I am particularly interested in using JSON encoding/compression algorithms like MessagePack with zlib, but how can we use this when inserting the record into the PostgreSQL table?

You could always compress it on the client, then pass it to and from the database as bytea. Of course then you can't do JSON manipulation inside the database. Also working with bytea is generally unpleasant.

If you can find an implementation of MessagePack in Perl or Python, you could use those pl languages to allow compression and decompression inside the database.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.