NoSQL with PostgreSQL 9.4 and JSONB

The introduction of the JSONB data type in PostgreSQL, definitely makes the “NoSQL” side of this relational DBMS come out: this introduction meets the requirements of all those who prefer a data structure in a “key-value” array, dictionary style (widely used in the field of development) and, at the same time, ensures all the advantages of a relational database.

PostgreSQL 9.2 already provided for the use of JSON type, maintening the compatibility of JSON data directly in a database. However, it was a text type data with the ability to validate JSON syntax. With this new type of JSONB data, information is stored in a dedicated binary format. So it is possible to benefit from specific algorithms which improve access performances and optimise storage on disk, which offer:

• advanced access and comparison operators: thanks to the specialised structure, JSONB has allowed the implementation of new operators that, in addition to offering more flexibility, allow the user to harness the full power of hash, btree, GIST and GIN indexes;
• reduced dimensions on disk: the space required to store documents of a complex structure with the JSONB data is smaller than disk requirements for the JSON format;
• internal organisation as a dictionary with a unique key: this means that access is extremely fast, but the key order of entry within the JSONB structure is not preserved. Furthermore, in the presence of duplicated keys, only the last entered value is maintained, unlike to what happened with JSON data:
  

  $ SELECT '{"a":1, "b":2}'::JSONB = '{"b":2, "a":1}'::JSONB ?column? -------- t (1 row) $ SELECT '{"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}'::JSON JSON ---------------------------------------------- {"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"} (1 row) $ SELECT '{"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}'::JSONB JSON ---------------------------------------------- {"a":"abc", "d":"overwritten","z":[1,2,3]} (1 row)

It is anyway necessary to point out that JSONB data is compatible with all functions introduced for the JSON data.

The effects of this possibility of indexing the JSONB type is translated into a better availability of data to be read, thus allowing an efficient access to the whole content of a JSONB field.

This makes it possible to efficiently use PostgreSQL in order to analyse data which does not have a predefined scheme, by moving it closer to the “NoSQL” world. In this regard, community member Thom Brown conducted some tests showing how an increase in reading performances (and a more reduced space occupied by indexes) was found, as compared to a JSON field, thus reaching reading performances even higher than those of typically NoSQL DBMS, such as MongoDB.

Conclusions

The introduction of +JSONB+ type definitely moves PostgreSQL closer to developers who normally use data in a +JSON+ format. An example is web developers who widely use JavaScript and have possibly already started working with PostgreSQL using the +JSON+ type for storing data. By evolving to +JSONB+, they will be able to use all the power of the PostgreSQL engine to develop that data easily and efficiently.