Released PostgreSQL 9.5: UPSERT, RLS and Big Data



/ > Today the PostgreSQL Global Development Group announced the launch PostgreSQL 9.5. Among other innovations can be noted the function UPSERT, security, row-level (Row Level Security, RLS) and a few functions work with Big Data. According to the developers, the new features make PostgreSQL a better option among all possible for startups, large corporations and government organisations.

Learn more about the new features under the cut.

the

UPSERT


The most popular function among application developers, UPSERT is short for "INSERT ON CONFLICT UPDATE". New feature allows to handle a situation of inability to add data through INSERT, for example, due to violations of the conditions of the uniqueness or inadmissibility of the value of one of the fields, and instead of the error output to ignore the execution of a statement or to change key field data (i.e. if the entry already exists, instead of INSERT to perform the UPDATE).

According to representatives of the team, UPSERT simplifies the development of web and mobile applications. The condition ON CONFLICT allows us to ignore new data or update the various columns or communications so that the company supported ETL (Extract, Transform, Load) sets of compilers to download the datasets. A new feature is fully compatible with all other features of PostgreSQL, including Replication Logical.

the

Row-Level Security (RLS)


This feature is the result of work for a period of five years. RLS provides the ability to create security policies that restrict user access to information in the database. Security policies allow either "close" information, fully or partially, or to allow performance of certain actions with the information.

the

Big Data


Here the developers have added several new features, including a Block Range Indexes (BRIN), which provides a faster search for certain types of data. On the search for such data, according to representatives of the team will leave only 5% of the time required to search a B-tree.

BRIN-indices. This new type of index allows you to create a tiny but efficient indexes for very large tables, the data in which the "naturally ordered". For example, tables containing data of system logs with billions of rows can be indexed and scanned for only 5% of the time it takes for a standard BTree indexes.

Express sorting. Now PostgreSQL sorts textual data and the data of NUMERIC type faster using the algorithm "abbreviated keys". This speeds up some queries that require sorting large amounts of data, from 2 to 12 times and can speed up the index creation is up to 20 times.
CUBE, ROLLUP, and GROUPING SETS. These new proposals the standard SQL language allows users to create reports with multiple levels of summarizing in a single query. Offer CUBE also allows you to closely integrate PostgreSQL with a large number of tools to create OLAP-reports (Online Analytic Processing) — such as Tableau.

PostgreSQL's foreign data (Foreign Data Wrappers, FDW). This functionality previously enabled to use PostgreSQL as the environment for requests to other "Big Data"systems like Hadoop and Cassandra. In version 9.5 added IMPORT FOREIGN SCHEMA and JOIN-displacement (JOIN pushdown), making queries to external databases as easier to install and more effective.

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

Overall, the 9.5 release a lot of new, really useful for developers. It should be remembered that when upgrading some old DB may have problems, this is worth remembering.

More detailed information about the innovations in version 9.5 (Wiki developers) available here or here (Russian press release).
Article based on information from habrahabr.ru

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

Approval of WSUS updates: import, export, copy

Изображение
the Backstory Until recently, I worked as edikasyon a major Russian company that has many offices throughout the country. In my jurisdiction there were eleven sites, located in different cities of the Far East (this is important). In each of these offices had its own, not connected with other network infrastructure — your domain AD own subnet, etc. Once the leadership gave me the task to organize the automatic updating of the OS and programs from MS and allowed to expand on accountable sites WSUS . Problem After WSUS was deployed, binding policy, computers to WSUS groups are configured, the directory synchronization content server MS held, etc., the question arose: who and how will approve the update? Imagine 11 cities that are so "fast" and "stable", that rare moment when latency of ICMP replies are only 800 MS, are perceived as unheard of luck. And in each you test all updates before deploying. The access to the servers at the sites was only v...
Далее...

Kaspersky Security Center — the fight for automation

oddly enough, I found habré only one article on the subject — the sandbox much unfinished actually contains a piece of the slightly redesigned the product help. Yes, and Google query klakaut silent. I'm not going to tell you how to administer the hierarchy of Kaspersky Security Center (hereinafter KSC) from the command line — I do not yet need never. Just want to share some thoughts about automation tools to those who need it and disassemble one case with which I had to face. If you, %habrauser%, this topic will be interesting — welcome under kat. Historically, the anti-virus protection on the job, I prefer products of Kaspersky Lab (hereafter LK). Causes and other Holy war of personal opinions, I'll leave behind the scenes. Naturally, I would like to centrally deploy, protect, to protect and to enforce the law to draw beautiful graphics to integrate into existing monitoring systems and to do other work shifting the blame to a healthy server. And if you deplo...
Далее...

The Hilbert curve vs. Z-order

Изображение
Repeatedly heard the opinion that of all swept curves . it the Hilbert curve the most promising for spatial indexing. The explanation given is that it does not contain discontinuities and therefore in some sense “well-arranged”. Whether so it actually and what is the spatial indexing, we will understand under the cut. The concluding article of the series: the the Why doesn't the straightforward approach to spatial indexing swept curves the Presentation the 3D and obvious optimization the 8D and perspectives OLAP the 3D field as it looks the 3D field benchmarks the How about indexing non-point objects? The Hilbert curve has the remarkable property — the distance between two successive points is always equal to one (step current of detail, to be precise). Therefore, data is not much different Hilbert curve values are close to each other (the inverse, incidentally, is wrong — adjacent points may be very different in meaning). For this property it is us...
Далее...