Postgres + JSON = NoSQL Functionality

September 10, 2015

It doesn’t take a specialized solution to create an unstructured database to support workloads for emerging mobile, social and machine data. Certainly, NoSQL-only solutions have a place in the data center to meet the needs of specific applications. But Postgres has emerged with the capabilities to support most applications today’s enterprises deploy that require semi-structured or unstructured data. With JSON for document databases and the HSTORE data type for key/value pairs, Postgres supports unstructured and semi-structured data alongside relational data.   

Postgres was originally architected to be an object-relational database designed specifically to enable extensibility. It supports objects, classes, custom data types and methods. In the early years of the Postgres project this was problematic as it slowed down development cycles because new code had to be fully integrated so everything would work with everything else. However, as Postgres has become more feature rich over the past 15 years, that original design hurdle has turned into a unique advantage. The fact that Postgres is an object-relational database means new capabilities can be developed and plugged into the database as needs evolve.

Using this level of extensibility, Postgres developers have expanded the database to include new features and capabilities as new workloads requiring greater flexibility in the data model emerged. The most relevant examples in the NoSQL discussion are JSON and HSTORE. With JSON and HSTORE, Postgres can support applications that require a great deal of flexibility in the data model.

     Learn how to use the NoSQL capabilities in Postgres by watching the webcast, NoSQL on Acid – Meet Unstructured Postgres, recorded on Sept. 23.

One of Postgres’ key strengths is the easy integration of conventional SQL statements, for ANSI SQL tables and records, with JSON and HSTORE references pointing documents and key-value pairs. Because JSON and HSTORE are extensions of the underlying Postgres model, the queries use the same syntax, run in the same ACID transactional environment, and rely on the same query planner, optimizer and indexing technologies as conventional SQL-only queries.

Postgres provides a number of functions to bridge between JSON and ANSI SQL. This is an important capability when applications and data models mature, and designers start to recognize emerging data structures and relationships. Postgres can create a bridge between ANSI SQL and JSON, for example by making an ANSI SQL table look like a JSON data set. This capability allows developers and DBAs to start with an unstructured data set, and as the project progresses, adjust the balance between structured and unstructured data.

The ability of Postgres to support key-value stores and documents within the same database empowers users to address expanding demands using proven, best-in-class open source technologies. Utilizing NoSQL capabilities within Postgres to address more data problems instead of turning immediately to a NoSQL solution ultimately means lower costs, less risk and less complexity while delivering enterprise-class workloads with ACID compliance and ensuring the long-term viability of enterprise data.

If you’d like more information on using NoSQL in Postgres, contact us directly.

Jason Davis is Senior Director, Product Management at EnterpriseDB. 

Share this

Relevant Blogs

pgAdmin User Management in Server Mode

pgAdmin can be deployed as a web application by configuring the app to run in server mode. One can check out server deployment on how to run pgAdmin in...
August 24, 2023

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023

More Blogs

Highlights from the PostgreSQL 16 Beta Release

The PostgreSQL Global Development Group released PostgreSQL 16 Beta 1 on May 25, 2023. PostgreSQL 16 improves logical replication by enabling replication from standbys as well as the ability to...
June 02, 2023