Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News PostgreSQL advances in the JSON datastore landscape

PostgreSQL advances in the JSON datastore landscape

JSON based datastores have been gaining popularity in the past years. Following on the increasing popularity of MongoDB, CouchDB, RethinkDB, ToroDB and others are focusing on storing and retrieving JSON documents as seamlessly as possible.

On the other hand, relational databases are also evolving, with PostgreSQL getting more and more features inspired but the non relational world. Since version 9.2 Postgres supports the new JSON datatype and since 9.3 there were 12 new functions and dedicated JSON operators. Version 9.4 brought a binary JSON datatype allowing for mix and match between table and document data loads.

The latest 9.5 version, due to be released towards the end of this year, although not improving on JSON support, will bring parallel sequential scan support. This is a huge milestone for PostgreSQL as it means that fewer queries will be CPU constrained. This, together with the improvement in read scalability for the use case that the dataset can fit in memory, can expand the use cases where PostgreSQL is the preferred candidate.

Tackling on NoSQL and big proprietary RDBMS systems at the same time, PostgreSQL is positioning itself in the middle, offering the best of both worlds. As of the current version, it supports a JSON datatype, a binary JSON datatype, complex functions on them and even indexing subfields of a JSON document.

Is PostgreSQL going to be your next JSON database? It seems that, what PostgreSQL is good at, is storing the JSON document and retrieving it quickly. What it isn’t still good at, is updating the JSON document in place. If your dataset is in JSON and mutable, then maybe one is better using a dedicated JSON data store instead of PostgreSQL. If not, then PostgreSQL may be the best choice as it can combine both relational and non-relational workloads at the same time.

Rate this Article