Talk "NoSQL The SQL Way" at PostgreSQL Meetup Group Berlin in March 2017

On 30th of March 2017 I gave a talk at PostgreSQL Meetup Group Berlin about JSON in PostgreSQL

You could find my slides and the SQL code on GitHub. In addition to the talk, we had some discussions about JSON extension, especially about JsQuery .
The extension is not available on PGXN, which does mean you have to have installed make to get installed on your server.

In addition to that, I stumbled upon another extension, ZSON, which is an extension to compress JSONB. It might be very useful, if your JSONB documents are slightly bigger and if you access only several paths inside that document, as you have to train ZSON. You have to compile this extension, too.

For whatever reason, I always thought, that JSONB documents would be smaller, than JSON documents in PostgreSQL. Yesterday I learned, thanks to Josh Berkus, that I was wrong. Today I compared the size of the Amazon book reviews data, used during the talk, in JSONB and JSON:

-- Create a JSONB table
CREATE TABLE reviews_jsonb(review_jsonb jsonb);

-- Import customer reviews from a file into JSONB
COPY reviews
    FROM '/var/tmp/customer_reviews_nested_1998.json';

-- Create a JSON table
CREATE TABLE reviews_json(review_json json);

-- Import customer reviews from a file into JSON
COPY reviews_json
    FROM '/var/tmp/customer_reviews_nested_1998.json';

SELECT pg_table_size('reviews') AS size_jsonb
    , pg_table_size('reviews_json') AS size_json;

The result is:

To summarize, if you don't process any JSON data in PostgreSQL itself, use JSON as datatype, your data will be consuming less disc space.

While I gave my talk in Berlin, there was another one given by Oleg Bartunev at PGConf US 2017 about the future of JSON in PostgreSQL. I really recommend to read the slides. As there is no a JSON ISO standard for SQL since December 2016, there will be a subset of this standard implemented into PostgreSQL. As the patch was to big for the coming version 10.0, it will probably be integrated into the version afterwards.