PostgreSQL 9.5 Released: UPSERT, RLS and Big Data

Today, the PostgreSQL Global Development Group announced the release of PostgreSQL 9.5. Among other innovations, one can mention the UPSERT function, row level security (Row Level Security, RLS), and several Big Data functions. According to the developers, the new features make PostgreSQL the best option among all possible startups, large corporations, and government organizations.

In more detail about new functions - under a cat.

UPSERT

The most requested feature among application developers, UPSERT is short for INSERT, ON CONFLICT UPDATE. The new feature allows you to handle the situation of the impossibility of adding data through the “INSERT”, for example, because of the violation of the conditions of uniqueness or the inadmissibility of the value of one of the fields, and instead of displaying an error, ignore the operator’s performance or change the data associated with the key field exists, instead of INSERT execute UPDATE).
')
According to representatives of the team, UPSERT simplifies the development of web and mobile applications. The ON CONFLICT condition allows you to ignore new data, or update various columns or relationships so that ETL (Extract, Transform, Load) compiler sets are supported for loading arrays of data. The new feature is fully compatible with all other PostgreSQL features, including Logical Replication.

Row-Level Security (RLS)

This feature is the result of a five-year job. RLS provides the ability to create security policies that restrict users' access to information in the database. Security policies allow you to either “close” information in whole or in part, or to allow certain actions to be performed on the information.

Big data

Here, developers have added several new features at once, including Block Range Indexes (BRIN), which provides a faster search for some data types. According to representatives of the team, it will take only 5% of the time required to search for B-tree to search for such data.

BRIN indices . This new type of index allows you to create tiny but effective indexes for very large tables, the data in which are "naturally ordered." For example, tables containing syslog data with billions of rows can be indexed and scanned in just 5% of the time required for standard BTree indices.

Accelerated sorting . PostgreSQL now sorts text data and NUMERIC type data faster using the "reduced keys" algorithm. This speeds up some queries that require sorting large amounts of data from 2 to 12 times and can speed up the creation of indexes up to 20 times.
CUBE, ROLLUP and GROUPING SETS . These new standard SQL language sentences allow users to create reports with multiple levels of summarization in a single query. The CUBE offer also allows you to tightly integrate PostgreSQL with more OLAP reporting tools (Online Analytic Processing), such as Tableau.

Foreign Data Wrappers (FDW) . This functionality previously allowed PostgreSQL to be used as an environment for querying other “Big Data” systems — for example, Hadoop and Cassandra. In version 9.5, the IMPORT FOREIGN SCHEMA and JOIN-push (JOIN pushdown) commands are added, which makes requests to external databases both easier to install and more efficient.

TABLESAMPLE . This SQL statement allows you to quickly get a statistical sample for a huge table without the need for resource-intensive sorting.

In general, in version 9.5 there are a lot of new, really useful for developers. It should be remembered that when updating some old databases, problems may arise, it is worth remembering.

Even more detailed information on innovations in version 9.5 (Wiki from the developers) can be available here or here (Russian press release).