NoSQL Postgres is better than MongoDB?

In General, the management system relational database was conceived as a "one-size-fits-all solution for storing and retrieving data" for decades. But the growing need for scalability and new application requirements have created new challenges for traditional management systems RDBMS, including some dissatisfaction with the approach "one-size-fits-all" in some scalable applications.

The response to this was a new generation of lightweight, high-performance databases created in order to challenge the dominance of relational databases.

A big reason for the NoSQL movement was the fact that different implementations of web, enterprise and cloud applications have different requirements for their bases.

Example: for such big sites as eBay, Amazon, Twitter, or Facebook, scalability and high availability are essential requirements that cannot be compromised. For these applications, even the slightest outage can have significant financial consequences and impact customer trust.

Thus, ready-made solution database often must address not only transactional integrity, but more than that, higher volumes of data, increase speed and data throughput, and a growing variety of formats. There are new technologies that spetsializiruyutsya on optimizing one or two of the aspects mentioned above, sacrificing the other. Postgres JSON uses a more holistic approach to the needs of users, successfully solving most of the workloads NoSQL.

Comparison document-oriented/relational databases

Smart approach to new technology is based on a close evaluation of your needs with the tools available to achieve these needs. The following table compares the characteristics of the non-relational, document-oriented database (such as MongoDB) and features Postgres'ovsky relational/document-oriented database, in order to help You find the right solution for your needs.
the the the the the the the the the the the the the the the the the the the the the

Features	MongoDB	PostgreSQL
Start Open Source development	2009	1995
Scheme	Dynamic	Static and dynamic
hierarchical data	Yes	(2012)
Support "key-event" data	Yes	(2006)
Support for relational data / normalized form	No	Yes
Restrictions on data	No	Yes
Combining data and foreign keys	No	Yes
Powerful query language	No	Yes
transaction Support and Management of competitive access using versioning	No	Yes
Atomic transaction	Within the document	entire database
Supported languages web development	JavaScript, Python, Ruby, and other...	JavaScript, Python, Ruby, and other...
Support of common data formats	JSON (Document), Key-Value, XML	JSON (Document), Key-Value, XML
Support for spatial data	Yes	Yes
the easiest way to scale	Horizontal scaling	Vertical masshtabnaya
Sharding	Simple	Complex
Programming on the server side	No	Many procedural languages such as Python, JavaScript, C,C++, Tcl, Perl, and many others
Easy integration with other data sources	No	External data collectors from Oracle, MySQL, MongoDB, CouchDB, Redis, Neo4j, Twitter, LDAP, File, Hadoop, and other...
Business logic	Distributed client applications	Centrally with triggers and stored procedures, or into client applications
the Availability of free educational resources	Hard to find	Easy to find
first use	Big data (billions of records) with lots of concurrent updates, where is the integrity and consistency of data is required.	Transactional and operational applications, benefits of which in normalized form, associations, constraints, data and support transactions.

Source: EnterpriseDB website.

The document in MongoDB is automatically supplied field _id if it is not present. When You want to get this document, You can use _id — it behaves exactly like a primary key in relational databases. PostgreSQL stores data in fields of tables, MongoDB stores it as JSON documents. On the one hand, MongoDB looks like a great solution, since you can have all the different data from multiple tables in PostgreSQL in a single JSON document. This flexibility is achieved with no restrictions on data structure, which can be really attractive at first, and really devastating on a large database in which some records have incorrect values, or empty fields.

PostgreSQL 9.3 comes with a great feature that allows you to turn it into a NoSQL database, with full support for transactions and storing JSON documents with constraints on the fields with data.

Simple example

I'll show you how to do this using a very simple example table Employees. Every Employee has a name, a description, an id number, and salary.

PostgreSQL

A simple table in PostgreSQL might look like the following:

the

CREATE TABLE emp (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
salary DECIMAL(10,2)
);

This table allows us to add employees so:

the

INSERT INTO emp (name, description, salary) VALUES ('raju', 'HR', 25000.00);

Alas, the above table allows you to add a blank line without some important values:

the

INSERT INTO emp (name, description, salary) VALUES (null, -34, 'sdad');

This can be avoided by adding constraints to the database. Suppose that we always want to have non-empty unique name, a non-empty description, not a negative salary. This table will look like:

the

CREATE TABLE emp (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
description TEXT NOT NULL,
salary DECIMAL(10,2) NOT NULL,
CHECK (length(name) > 0),
CHECK (description IS NOT NULL AND length(description) > 0),
CHECK (salary >= 0.0)
);

Now all operations such as adding, or updating records that contradict some of these restrictions will fall off with an error. Let's see:

the

INSERT INTO emp (name, description, salary) VALUES ('raju', 'HR', 25000.00);
--INSERT 0 1
INSERT INTO emp (name, description, salary) VALUES ('raju', 'HR', -1);
--ERROR: new row for relation "emp" violates check constraint "emp_salary_check"
--DETAIL: Failing row contains (2, raju, HR -1).

NoSQL version

In MongoDB, the entry from the table above will look like the following JSON document:

the

{
"id": 1,
"name": "raju",
"description": "HR
salary: 25000.00
}

similarly, in PostgreSQL we can save this entry as a row in the emp table:

the

CREATE TABLE emp (
data TEXT
);

It works in most non-relational databases, no checks, no errors with bad fields. As a result, you can transform the data as you want, the problems begin when your application expects that the salary is a number, it is either a string or no.

Checking JSON

In PostgreSQL 9.2 for these purposes, there is a good data type, it is called JSON. This type can store only valid JSON before conversion to that type is checked for validity.
Let's change the table description:

the

CREATE TABLE emp (
JSON data
);

We can add some valid JSON in this table:

the

INSERT INTO emp(data) VALUES('{
"id": 1,
"name": "raju",
"description": "HR",
salary: 25000.00
}');
--INSERT 0 1
SELECT * FROM emp;
{ +
"id": 1, +
"name": "raju", +
"description": "HR",+
salary: 25000.00 +
}
--(1 row)

It works, but adding incorrect JSONa will fail:

the

INSERT INTO emp(data) VALUES('{
"id": 1,
"name": "raju",
"description": "HR",
"price": 25000.00,
}');
--ERROR: invalid input syntax for type json

The problem with formatting can be hard visible (I added a comma in the last line, JSONу don't like it).

Checking the boxes

So we have a solution which looks almost like the first pure PostgreSQL solution: we have data that are validated. This does not mean that the data is meaningful. Let's add a validation for data validation. In PostgreSQL 9.3 has a new powerful functionality to manage JSON objects. There are certain operators for the JSON type, which will give You easy access to fields and values. I will only use "->>", but You can find more information Postgres documentation.

In addition, I need to test the types of fields, including the id field. Is that Postgres just checks for data type definitions. I will use a different syntax for tests as I want to give it a name. It will be much easier to look for a problem in a particular field and not around the huge JSON document.

Table limits will look like the following:

the

CREATE TABLE emp (
data JSON
CONSTRAINT validate_id CHECK ((data->>'id')::integer >= 1 AND (data->>'id') IS NOT NULL ),
Validate_name CONSTRAINT CHECK (length(data->> name) > 0 AND (data->>'name') IS NOT NULL )
);

The operator "->>" allows me to extract the value from the desired fields JSON'a, check whether it exists and its validity.

Let's add a JSON without the of the description:

the

INSERT INTO emp(data) VALUES('{
"id": 1,
"name": "", 
"salary": 1.0
}');

--ERROR: new row for relation "emp" violates check constraint "validate_name"

We still have one more problem. Fields name and id must be unique. This can easily be achieved as follows:

the

CREATE a UNIQUE INDEX ON emp ui_emp_id((data->>'id'));
Ui_emp_name CREATE a UNIQUE INDEX ON emp((data->>'name'));

Now, if you try to add a JSON document in the database whose id is already contained in the database, you receive the following error:

the

--ERROR: duplicate key value violates unique constraint "ui_emp_id"
--DETAIL: Key ((data ->> 'id'::text))=(1) already exists.
--ERROR: current transaction is aborted, commands  ignored  until end of transaction block

Performance

PostgreSQL handles the most demanding requests of the largest insurance companies, banks, brokerages, government agencies, and defense contractors in the world, and have done for many years. Improve the performance of PostgreSQL continuous annual release and includes improvements and unstructured data types including.

Source: EnterpriseDB White Paper: Using the NoSQL capabilities in Postgres

To personally experience the performance of NoSQL in PostgreSQL, download the pg_nosql_benchmark from GitHub.

Article based on information from habrahabr.ru