Repository files navigation

ZSON is a PostgreSQL extension for transparent JSONB compression. Compression isbased on a shared dictionary of strings most frequently used in specific JSONBdocuments (not only keys, but also values, array elements, etc).

In some cases ZSON can save half of your disk space and give you about 10% moreTPS. Memory is saved as well. Seedocs/benchmark.md.Everything depends on your data and workload, though. Don't believe anybenchmarks, re-check everything on your data, configuration, hardware, workloadand PostgreSQL version.

ZSON was originally created in 2016 byPostgres Professional team:researched and coded byAleksander Alekseev; ideas, code review, testing,etc byAlexander Korotkov andTeodor Sigaev.

See also discussions onpgsql-general@,Hacker News,RedditandHabraHabr.

Build and install ZSON:

cd /path/to/zson/source/codemakesudo make install

Run tests:

make installcheck

Connect to PostgreSQL:

psql my_database

Enable extension:

create extension zson;

Disable extension:

drop extension zson;

Uninstall ZSON:

cd /path/to/zson/source/codesudo make uninstall

First ZSON should betrained on common data using zson_learn procedure:

zson_learn(    tables_and_columns text[][],    max_examples int default 10000,    min_length int default 2,    max_length int default 128,    min_count int default 2)

Example:

select zson_learn('{{"table1", "col1"}, {"table2", "col2"}}');

You can create a temporary table and write some common JSONB documents into itmanually or use the existing tables. The idea is to provide a subset of realdata. Let's say some documenttype is twice as frequent as another documenttype. ZSON expects that there will be twice as many documents of the first typeas those of the second one in a learning set.

Resulting dictionary could be examined using this query:

select * from zson_dict;

Now ZSON type could be used as a complete and transparent replacement of JSONBtype:

zson_test=# create table zson_example(x zson);CREATE TABLEzson_test=# insert into zson_example values ('{"aaa": 123}');INSERT 0 1zson_test=# select x -> 'aaa' from zson_example;-[ RECORD 1 ]-?column? | 123

When a schema of JSONB documents evolves ZSON could bere-learned:

select zson_learn('{{"table1", "col1"}, {"table2", "col2"}}');

This timesecond dictionary will be created. Dictionaries are cached in memoryso it will take about a minute before ZSON realizes that there is a newdictionary. After that old documents will be decompressed using the olddictionary and new documents will be compressed and decompressed using the newdictionary.

To find out which dictionary is used for a given ZSON document use zson_infoprocedure:

zson_test=# select zson_info(x) from test_compress where id = 1;-[ RECORD 1 ]---------------------------------------------------zson_info | zson version = 0, dict version = 1, ...zson_test=# select zson_info(x) from test_compress where id = 2;-[ RECORD 1 ]---------------------------------------------------zson_info | zson version = 0, dict version = 0, ...

Ifall ZSON documents are migrated to the new dictionary the old one couldbe safely removed:

delete from zson_dict where dict_id = 0;

In general, it's safer to keep old dictionaries just in case. Gaining a few KBof disk space is not worth the risk of losing data.

Unfortunately, it's hard to recommend a general approach.

A good heuristic could be:

select pg_table_size('tt') / (select count(*) from tt)

... i.e. average document size. When it suddenly starts to grow it's time tore-learn.

However, developers usually know when they change a schema significantly. It'salso easy to re-check whether the current schema differs a lot from the originalone using zson_dict table.

Intalling ZSON in a schema other thanpublic is not supported (i.e.CREATE EXTENSION zson WITH SCHEMA ...).