When to use unstructured data types in PostgreSQL? Comparison * PostgreSQL hstore vs. JSON vs. JSONB

Since PostgreSQL has started supporting NoSQL (via * PostgreSQL hstore, JSON and JSONB), the question of when to use a PostgreSQL relational mode, and any mode of NoSQL, began to rise quite often. Will you have to completely abandon the traditional table structures and work with representations of documents in the future? To mix both approaches? The answer to this question is not surprising — it all depends on many factors. Each new model of data storage including * PostgreSQL hstore, JSON and JSONB has its ideal applications. Then we dig deeper and learn about the features of each of them and view when to use



* PostgreSQL hstore


excluding the XML * PostgreSQL hstore was the first truly unstructured data type, added in PostgreSQL. * PostgreSQL hstore was added quite a long time in Postgres 8.3 to upsert, streaming replication, and to window functions. * PostgreSQL hstore is basically a repository of key/value directly in PostgreSQL. * PostgreSQL hstore using you limited in the choice of the used data type. In fact you have only strings. You don't even have nesting data; in Short, this single-level data type key/value.


* PostgreSQL hstore Advantage is that you do not have to define the keys (unlike columns) in advance. You can just insert a record, and it will store all necessary data. Let's say you have a sample script to create the table:


the 
CREATE TABLE products (
id serial PRIMARY KEY,
name varchar,
* PostgreSQL hstore attributes
);

With the * PostgreSQL hstore you can insert anything you want in the attribute column. In this case, the request to add these keys and values will look as follows:


the 
INSERT INTO products (name, attributes) VALUES (
'Geek Love: A Novel',
'author = > "Katherine Dunn",
pages = > 368,
category => fiction'
);

a select Query will be:


the 
SELECT name, attributes->'author' as author
FROM products
WHERE attributes->'category' = 'fiction'

the Obvious advantage of this approach is flexibility, but that's where it really manifests itself fully, so it is possible to use different types of indexes. In particular, GIN or GiST index would be to index each key and value within a * PostgreSQL hstore. I.e., when filtering is used-added code in case if required by the scheduler PostgreSQL.


Because * PostgreSQL hstore is not a full equivalent document, it is important to understand is it profitable to use it as such.
If you have relational data and also some data, which may not always exist in the column, this approach can be a great solution. For example, in most cases, the attributes of the product catalogs can be a great example for this type of data. Then, for some products, such as books (which you store in a separate table named “Products”) can be defined such as genre, year of publication. In another case for products such as clothing, which you also store in that same table can be defined already by other parameters — the size and color. Add the same column to the products table for each possible parameter redundantly and unnecessarily.


JSON


Starting with version 9.2 of Postgres supports JSON. Now, PostgreSQL can compete with MongoDB. (Although the functionality of JSON in PostgreSQL 9.2, of course, a bit exaggerated. More on that below.)


the data Type as JSON in Postgres, if you look at pretty much just the text box. All you get with JSON type of data is the validation of values when you insert. Postgres enforces the JSON format. One small potential advantage of JSONB (which we will discuss next) is that the JSON preserves the indentation of the data that enters the database. So if you are very picky about the formatting of your data, or you need to save a record in a particular structure, JSON can be useful.


JSONB


Finally, in Postgres 9.4 we have received a true and correct JSON support in the form of JSONB. B means “better” (Better). JSONB is a binary representation of the data in the JSON format. This means that the data is compressed and more efficient for storage than text. In addition, under the hood he has a mechanism similar * PostgreSQL hstore. Technically, once in the development was almost completed Hstore2 type and separate type JSON and subsequently they were United in JSONB, as he is now.


the JSONB Type is largely what you might expect from the type of JSON data. It allows you to implement a nested structure, use the basic data types, and also has a number of built-in functions to work with it. The best part of this similarity with the * PostgreSQL hstore is indexing. Create GIN index on a JSONB column will create an index for each key and value within a JSON document. The possibility of indexing and nesting of data within the document indicate that JSONB * PostgreSQL hstore is superior in most cases.


Although still a small question about in what cases it is necessary to use only JSONB. Let's say you create a document database and from all variants choose Postgres. With a package like MassiveJS it can be quite convenient.


the Most common examples of use:


    the
  1. event Tracking data by adding a changing payload of the event.
    2. the
  2. Storing the game data is quite common, especially where you have single player and changing the data schema on the basis of the user state.
    3. the
  3. Tools that integrate multiple data sources, the example here can be the tool that integrates multiple customer databases to Salesforce, Zendesk or something else. The combination of the circuits makes it more painful than it should be.

Let's look at another example of JSONB. The script creates a table and inserts some sample data:


the 
CREATE TABLE integrations (id UUID, data JSONB);

INSERT INTO integrations VALUES (
uuid_generate_v4(),
'{
"service": "salesforce",
"id": "AC347D212341XR",
"email": "craig@citusdata.com",
"occurred_at": "8/14/16 11:00:00",
"added": {
"lead_score": 50
},
"updated": {
"updated_at": "8/14/16 11:00:00"
}
}');

INSERT INTO integrations (
uuid_generate_v4 (),
'{
"service": "zendesk",
"email": "craig@citusdata.com",
"occurred_at": "8/14/16 10:50:00",
"ticket_opened": {
"ticket_id": 1234,
"ticket_priority": "high"
}
}');

In the above case, you can easily find all events that have occurred c by the user with email craig@citusdata.com and then do some action. For example, you can conduct some form of behavioral analysts, and to calculate the users who did foo and then bar, or do a simple report.
Adding a Gin index all the data within my JSONB field index automatically:


the 
CREATE INDEX idx_integrations_data ON integrations USING gin(data);

Conclusion


In most cases JSONB this is probably just what you are looking for when you are planning to use nonrelational data type. For * PostgreSQL hstore and JSON, you can also find a good application, though in more rare cases. JSONB does not always fit into the data model. In case you can normalize the schema, you will have the advantage, but if the schema, a large number of optional columns (e.g., data about the events) or a scheme very different from another, that JSONB is much better.


in summary, the algorithm for choosing decision:


JSONB — IN most cases
JSON — If you are processing the logs, you do not often need to request data or not to use them as something more than for the tasks of logging.
* PostgreSQL hstore — works fine with text data based on the submission of key-value, but in General JSONB also copes with this task.

Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Integration of PostgreSQL with MS SQL Server for those who want faster and deeper

Изображение
Recently habré was already published integration PostgreSQL and MSSQL. But, items there is categorically not enough. Therefore, the objectives of this publication are the following: the the to broaden and deepen publicly available information about FDW for MSSQL-called tds_fdw to tell the difference in major versions and describe basic compatibility issues; the to talk about the possibilities of optimization of queries that use the foreign table; the to address the subject of caching external data in a materialized view; the to say a few words about the exotic ways to integrate PostgreSQL and MSSQL. the Installing and configuring TDS FDW The guys from PostgresPro already sufficiently said about this process, I will not repeat. Will leave a few links to the official PostgreSQL documentation and examples from tds_fdw: the the creating an extension (extension) in PG: off.dokumentacija ; the server: off.documentation , example of tds_fdw ; the creating ...
Далее...

Custom database queries in MODx Revolution

This topic will certainly be useful for those who have quite a lot of projects on MODx Revolution, as with the usual sites of business cards is enough and the standard working methods of a La $modx- > getObject() , $modx->getCollection() , etc., and these methods are inherently not just a job database, but also with the objects of MODx. When working with large projects methods a La $modx- > getCollection() for us is not the best solution for two reasons: 1. The overexpenditures. These methods are not just getting data from DB, but also create instance of the retrieved objects. In this case, getting information about 10000 documents, we get 10000 objects of modResource that it is not very cool. 2. The task is complicated by the count of records. In addition to the direct difficulties of counting even at the query level, even if you get 10 records of the same document (for example), MODx will return as a result only one object modResource. Although often that will su...
Далее...

Google Web Mercator: a mixed coordinate system

Изображение
the First of October 2014, the us National Geospatial-Intelligence Agency ( NGA ) has published report , which contained criticism of the coordinate system of the Web Mercator used in the web mapping services. The document was accompanied by a detailed explanation of the problem and recommendations for partners NGA. The document received a great response, but not all articles based on this report, very accurate and literate presentation. This applies, for example, articles on the website of the GIS Association, which, because of gross errors in terminology, can be considered illiterate. Because this coordinate system the developers of web services face more often, I think it makes sense to understand the problem. what is it? For a start, a couple definitions, without which some of the details may not be clear. It is important to understand that Web Mercator is the coordinate system, not only the projection, although its name is similar to many well-known Mercator projection...
Далее...