NoSQL for the Enterprise

EDB Relational QT

What is Postgres NoSQL for the Enterprise?

Postgres NoSQL is the powerful combination of unstructured and relational database technologies in a single enterprise database management system. Postgres NoSQL provides the freedom, flexibility and performance of handling unstructured and semi-structured data while preserving its long-term viability as enterprise information.

Postgres document and key-value store technologies provide the advantages of new NoSQL technologies plus the ability to integrate them into traditional corporate data infrastructures without data silos, operational complexity, loss of data integrity, and risk.

 

The Complete Document Database

Postgres has combined the leading JSON implementation with its proven ACID transaction engine. This unique combination gives users industry leading workload flexibility on the most cost effective and industry proven platform.

A complete database solution today often must address not only transactional integrity but also higher orders of data volume, increased data velocity and throughput speed, and a growing variety of data formats. New technologies have emerged that specialize in optimizing against one or two if these aspects while sacrificing the others.

Postgres with JSON takes a more holistic approach to user needs, more successfully addressing the majority of NoSQL workloads. For more details read the white paper PostgreSQL Advances to Meet NoSQL Challenges.

 

ACID or BASE?

The standards for Atomicity, Consistency, Isolation, and Durability (ACID) that have guided database technology for decades means an organization’s most valuable resource—its data—is valid, consistent and protected. Stand-alone NoSQL technologies do not meet ACID standards because they sacrifice critical data protections in favor of high throughput performance for unstructured applications. They achieve Basic Availability, Soft State and Eventual Consistency (BASE), which may be acceptable for use cases with little risk, such as a lost text message or the move of a token in a multi-player game.

While NoSQL solutions can promise Atomicity within a single document, this is inadequate for most enterprise systems with important mission critical relationships between multiple sets of dis-aggregated data whether structured or unstructured. With Postgres' NoSQL capabilities it is easy to integrate critical portions of your unstructured data with your traditionally governed data and maintain the performance, agile and unstructured benefits enjoyed by NoSQL tools.

 

Simplicity

Writing apps for the social or mobile web is a snap with Postgres' support for Javascript, Python, Ruby, node.js, JSON, Key-Values, geo-spatial and XML.

Using their favorite agile tools, developers can easily improve customer experience, accelerate time to market, and reduce costs.

 

Scalability

The explosive growth in Postgres users has been partially fueled by a proven track record for supporting high throughput systems with thousands of users processing large amounts of data. In all but the most extreme volume situations, Postgres easliy handles large amounts of data for large numbers of concurrent users. 

  • xDB Replication Server from EDB provides a non-virtualized read scale out solution. xDB in multi-master configurations provides read scalability and write availability to one of the largest telephone companies in America. 
  • EDB Failover Manager creates fault tolerant database clusters to minimize downtime when a master database fails by keeping data online in high 9s availability configurations. 
  • Large-scale Postgres configurations and solutions can be professionally managed, monitored, and tuned using EDB Postgres Enterprise Manager complete with performance dashboards, critical condition alerts, capacity planning, log analyzers, and much more.

 

Performance

Postgres today handles the most demanding needs of the largest insurance companies, banks, brokerages, government agencies, and defense contractors in the world today and has done so for years.  Performance improvements for Postgres are continuous with each yearly release and include great performance for its unstructured data types as well.

 

EDB encourages you to experience Postgres' NoSQL performance for yourself. Download the pg_nosql_benchmark  at GitHub let us know what you think.

Document/Relational Database Comparison

A smart approach to new technology demands a close evaluation of your needs and the tools available to meet those needs. The table below compares NoSQL Document oriented database (such as MongoDB) characteristics and Postgres' Relational / Document database characteristics to help you navigate matching the right solution to your requirements.

Features MongoDB PostgreSQL
Open Source Development Start 2009 1995
Schemas Dynamic Static and Dynamic
Supports Hierarchical Document Data Yes Yes (since 2012)
Supports Key-Value Data Yes Yes (since 2006)
Supports Relational Data / Normalized Form Storage No Yes
Data Constraints No Yes
Maximum Document Size 16 MB 1,000 MB
Joining of Data Extremely Difficult Extremely Easy
Powerful Standardized Query Language No Yes
Transaction Support and Multi Version Concurrency Controls No Yes
Atomic Transactions Within a Document Across the Database
Supported Web Development Languages JavaScript, Python, Ruby, more… JavaScript, Python, Ruby, more…
Common Web Data Format Support JSON (Document), Key-Value, XML JSON (Document), Key-Value, XML
Geo-spatial Support Yes Yes
Easiest path to Scaling Horizontal Scale Out Vertical Scale Up
Sharding Easy Complex
Server Side Programming None Many Procedural Languages like Python, JavaScript, C,C++, Tcl, Perl and many more
Easy Integration with Other Data Stores No, creates data silos Yes, Foreign Data Wrappers to Oracle, MySQL, MongoDB, CouchDB, Redis, Neo4j, Twitter, LDAP, File, Hadoop and more…
Business Logic Embedded in and Distributed Across Client Applications Centralized with Triggers & Stored Procedures or DIstributed across Client Applications
Skilled Resources Availability Difficult to Find Easy to Find
Primary Uses Big Data (billions of records) with high concurrency updates where data integrity and consistency are not required. Transactional & Operational Applications that benefit from normalized form, joins, data constraints and transactional support.

 

For more information about the JSON capabilities in Postgres...

Postgres Supports Structured and Unstructured Data

NoSQL technologies, such as document stores and key-value stores, support incremental development methodologies where data models emerge as the application goes through cycles of agile development, instead of being carefully crafted upfront using ER-diagramming, normal form analysis, and conceptual/logical/physical design frameworks.

NoSQL and ACID Together

Postgres has introduced JSON (2012) and HSTORE (2006) to provide solution architects and developers a schema-less data management option that is fully integrated with Postgres’ robust ACID (Atomic, Consistent, Isolation and Durable) model.

 

JSON Datatype Support

Postgres offers robust support for inserting JSON data complete with a validating parser, storage, and a wide variety of functions for extracting elements from within JSON documents.

 

JSON In and JSON Out

Postgres also offers the ability to easily encode query result sets into JSON which means application developers that prefer to work natively with JSON can easily obtain their data from Postgres in its original format.

 

Document and Key-Value Data

Postgres has also offered HSTORE for key-value support since 2006 but unlike other NoSQL-only solutions, a key-value store created in Postgres is ACID compliant. Just like JSON, HSTORE can be used to emulate a schema-less database when that is desirable. There are also functions that convert Postgres-maintained key-value data to JSON formatted data, which increases the flexibility and scope of NoSQL-like applications that can be addressed by Postgres.

 

JSON and Javascript

Postgres provides Javascript capabilities right in the database, allowing web developers to write centralized database logic using the same JavaScript engine that powers their web clients. Developed by Google for Chrome, V8 is designed to work on the client and the server and is also at the heart of Node.js. V8 is available as the Postgres add-on PL/V8.  With Postgres, your developers can work with their web data format and web application language in the database too.

 

Postgres for When Precision Counts

Postgres 9.4 adds JSONB, a second JSON type with a binary storage format that is significantly different than BSON, which is used by one of the larger document-only database providers. BSON stands for Binary JSON, but in fact not all JSON values can be represented using BSON. For example, BSON cannot represent an integer or floating-point number with more than 64 bits of precision, whereas JSONB can represent any arbitrary JSON values.

For more information about the JSON capabilities in Postgres...

Postgres JSON/JSONB Operators and Functions

The tables below show the wealth of operators and functions available for storing, retrieving, converting, manipulating and creating JSON data in a Postgres relational environment. When these features are used with Postgres' Key-Value format (HSTORE), full text search, trigram search, nearest neighbor search, GIN and GiST indexing, Unlogged Tables, XML support, foreign data wrappers (e.g. MongoDB, CouchDB and Redis), and asynchronous commit settings for 'eventual consistency' you will find you don't need another specialized database to meet your unstructured data needs.

Postgres' JSON data types are for storing JSON (JavaScript Object Notation) data, as specified in RFC 7159. The JSON data type stores an exact copy of the input text, which processing functions must reparse on each execution; while JSONB data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. JSONB also supports indexing, which can be a significant advantage.

 

The table below shows operators that are available for use with the two JSON datatypes: JSON and JSONB.

JSON and JSONB Operators
Operator Right Operand Type Description
–> int Get JSON array element (indexed from zero,negative integers count from the end)
–> text Get JSON object field by key
–>> int Get JSON array element as text
–>> text Get JSON object field as text
#> text[ ] Get JSON object at specified path
#> text[ ] Get JSON object at specified path as text

 

Additional operators exist only for JSONB, the binary storage format for JSON.

Additional JSONB Operators
Operator Right Operand Type Description
@> jsonb Does the left JSON value contain the right JSON path/value entries at the top level?
<@ jsonb Are the left JSON path/value entries contained at the top level within the right JSON value?
? text Does the string exist as a top-level key within the JSON value?
?| text[ ] Do any of these array strings exist as top-level keys?
?& text[ ] Do all of these array strings exist as top-level keys?
| | jsonb Concatenate two jsonb values into a new jsonb value.
- text Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.
- integer Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.
#- text[] Delete the field or element with specified path (for JSON arrays, negative integers count from the end).

 

This table shows the functions that are available for creating JSON values. Currently, there are no equivalent functions for JSONB, but you can cast the result of one of these functions to JSON.

JSON Creation Functions
Function Description
to_json(anyelement)

to_jsonb(anyelement)

Returns the value as json or jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.
array_to_json(anyarray [, pretty_bool]) Returns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension-1 elements if pretty_bool is true.
row_to_json(record [, pretty_bool]) Returns the row as a JSON object. Line feeds will be added between level-1 elements if pretty_bool is true.
json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list.
json_build_object(VARIADIC "any")

jsonb_build_object(VARIADIC "any")

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.
json_object(text[ ])

jsonb_object(text[ ])

Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.
json_object(keys text[ ], values text[ ])

jsonb_object(keys text[ ], values text[ ])

This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.

 

The final table shows the functions that are available for processing JSON and JSONB values.

JSON Processing Functions
Function Return Type Description
json_array_length(json)

jsonb_array_length(jsonb)

int Returns the number of elements in the outermost JSON array.
json_each(json)

jsonb_each(jsonb)

setof key text, value json

setof key text, value jsonb

Expands the outermost JSON object into a set of key/value pairs.
json_each_text(json)

jsonb_each_text(jsonb)

setof key text,

value text

Expands the outermost JSON object into a set of key/value pairs. The returned values will be of type text.
json_extract_path(from_json json, VARIADIC path_elems text[ ])

jsonb_extract_path(from_jsonb jsonb, VARIADIC path_elems text[ ])

json

jsonb

Returns JSON value pointed to by path_elems (equivalent to #> operator).
json_extract_path_text(from_json json, VARIADIC path_elems text[ ])

jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[ ])

test Returns JSON value pointed to by path_elems as text (equivalent to #>> operator).
json_object_keys(json)

jsonb_object_keys(jsonb)

setof text Returns set of keys in the outermost JSON object.
json_populate_record(base anyelement, from_json json)

jsonb_populate_record(base anyelement, from_json jsonb)

anyelement Expands the object in from_json to a row whose columns match the record type defined by base (see note below).
json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

setof anyelement Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base (see note below).
json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

Expands a JSON array to a set of JSON values.
json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof text Expands a JSON array to a set of text values.
json_typeof(json)

jsonb_typeof(jsonb)

text Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.
json_to_record(json)

jsonb_to_record(jsonb)

record Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.
json_to_recordset(json)

jsonb_to_recordset(jsonb)

setof record Builds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.
json_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)

json

jsonb

Returns from_json with all object fields that have null values omitted. Other null values are untouched.
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) jsonb Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.
jsonb_pretty(from_json jsonb) text Returns from_json as indented JSON text.

 

Click here for the full JSON Functions and Operator Reference with Examples and Example Results.

 

For more information about the JSON capabilities in Postgres...

Tell Us What You Think!

We want to improve this website, and we need your help! Please tell us how well this website meets your needs:

Begin Survey >>

PG XDK Tutorial

The PostgreSQL XDK allows you to explore the NoSQL capabilities of PostgreSQL. 

Learn how to manipulate your JSON data with PostgreSQL 9.4 through this free guided tutorial on Amazon Web Services.

PG XDK Quick Start Guide

Download PosgreSQL

 

On Demand Webcast

NoSQL Applications - Doing More with Postgres

Learn how NoSQL capabilities in Postgres are opening up new avenues for solving enterprise challenges without bringing risk and instability to data management.
 

Benchmark NoSQL

See for yourself how Postgres performs with unstructured data against MongoDB.

Download the Postgres NoSQL Benchmark and run your own tests!

Download PosgreSQL