Developers have been really excited about the addition of JSON support starting PostgreSQL v9.2. They feel they now have the flexibility to work with a schema-less unstructured dataset while staying within a relational DBMS. So what’s the buzz all about? Let’s explore below …

Why is NoSQL so attractive?

Rapid turnaround time … it is as simple as that. With the push to decrease time-to-market, developers are under constant pressure to turn POCs around very quickly. It is actually not just POCs, marketable products are increasingly getting the same treatment. The attitude is, “If I don’t get it out, someone else will.”.

Any decent sized application will need to store data somewhere. Rather than going through the pains of designing schemas and the debates on whether to normalize or not, developers just want to get to the next step. That’s how databases like MongoDB gained such tremendous popularity. They allow for schema-less, unstructured data to be inserted in document form and the developers find it easy to convert class objects within their code into that document directly.

There is a trade-off, however. The document (and key/value store) databases are very unfriendly to relations. While retrieving data, you will have a very hard time cross referencing between different tables making analytics nearly impossible. And, nightmare of nightmares for mission critical applications, these databases are not ACID compliant.

In walks PostgreSQL with JSON and HSTORE support.

NoSQL in PostgreSQL

While the HSTORE contrib module has been providing key/value data types in standard PostgreSQL table columns since v8.2, the introduction of native JSON support in v9.2 paves way for the true power of NoSQL within PostgreSQL.

Starting v9.3, not only do you have the ability to declare JSON data types in standard tables, you now have functions to encode data to JSON format and also to extract data elements from a JSON column. What’s more, you can also interchange data between JSON and HSTORE using simple & intuitive functions.

… and this is all ACID compliant!

The Power

Talk about bringing the best of both worlds together, the power that NoSQL capabilities bring to a traditional relational database is amazing. Developers now have the ability to kick-start their application development without any database bottlenecks using unstructured data. At the stage where analytics are required, they can be gradually structured to accommodate enterprise requirements within the same PostgreSQL database without the need for expensive migrations.