PostgreSQL: compress JSON column

Question

I have a table with a JSON type column and I have 1 row in the table. Following request show me result 20761 bytes:

SELECT pg_column_size(test_column) FROM test_table;

The value from test_column has size 45888 bytes so it means that PostgreSQL compressed this data, but it compressed 45888/20761=~2.1 times. How can I do compression of JSON type more than existing value?

What exactly is your question? How to improve the built-in compression algorithm of Postgres? That's only possible by changing the Postgres source code? — user330315
– user330315, Commented Jul 17, 2017 at 11:57
You can't change the built-in compression algorithm. You might want to have a look in to the extension zson which is a dictionary based compression tailored for JSONB: github.com/postgrespro/zson — user330315
– user330315, Commented Jul 17, 2017 at 12:23
@JohnChristopherJones is that really true? In some cases I've found JSONB size to be 130% of the JSON string size. There's also parse and encode time with JSONB. I'm curious how you reconcile these differences but still conclude with "basically no advantage"? — bradley.ayers
– bradley.ayers, Commented Nov 14, 2017 at 22:03
In many scenarios JSONB could be a better option than JSON though definitely not in all cases, e.g., see heapanalytics.com/blog/engineering/… (there are many other articles on the topic). I'd consider saying that there is no advantage to be fairly misleading, indeed these are not advantages but disadvantages of JSONB ;-) — jso
– jso, Commented Mar 12, 2019 at 15:23

Sander Toonen · Accepted Answer · 2019-06-20 12:24:23Z

8

Changing the type to jsonb does not make it use less disk space, it might in some cases even use more. Take a look at ZSON. It is a PostgreSQL extension that compresses the JSON data by creating a lookup table for the most common data, most likely the json-keys, and it claims to be able to save up to half of the needed disk space.

answered Jun 20, 2019 at 12:24

Sander Toonen

3,6131 gold badge38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: compress JSON column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related