Developers Club geek daily blog

2 years, 10 months ago
There was PostgreSQL 9.5: UPSERT, RLS and Big Data

Today PostgreSQL Global Development Group declared PostgreSQL 9.5 output. Among other innovations it is possible to note the UPSERT function, safety at the level of lines (Row Level Security, RLS) and several functions of work with Big Data. According to developers, new functions do PostgreSQL by the best option among all possible for startups, big corporations, the government organizations.

In more detail about new functions — under a cat.


The most demanded function among application developers, UPSERT is reduction for "INSERT, ON CONFLICT UPDATE". A new opportunity allows to process a situation of impossibility of adding of data through "INSERT", for example, because of violation of conditions of uniqueness or inadmissibility of value of one of fields, and instead of an output of an error to ignore execution of the operator or to change connected with the key Data field (i.e. if record already exists, instead of INSERT to execute UPDATE).

According to representatives of command, UPSERT the web and mobile applications simplifies development. The condition of ON CONFLICT allows to ignore new data, or to update different columns or communications so that support of ETL (Extract, Transform, Load) sets of compilers for loading of data arrays was performed. New function is completely compatible to all other opportunities of PostgreSQL, including Logical Replication.

Row-Level Security (RLS)

This function — result of work lasting five years. RLS gives an opportunity of creation of security policies which limit access to users to information to a DB. Security policies allow or "to close" information in whole or in part, or to permit execution of certain actions with information.

Big Data

Here developers added several new functions, including Block Range Indexes (BRIN) providing faster search of some data types at once. On search of such data, according to the statement of representatives of command, only 5% of time demanded for implementation of search of B-tree will leave.

In general, in version 9.5 there is a lot of new, really useful to developers. At the same time it is worth remembering that when updating some old DB there can be problems, it is worth remembering it.

Even more detailed information on innovations in version 9.5 (Wiki from developers) it is possible it is available here.

This article is a translation of the original post at
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here:

We believe that the knowledge, which is available at the most popular Russian IT blog, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.
Best wishes.

comments powered by Disqus