Digest news from the world of PostgreSQL. Issue number 3

We continue to introduce you to the most interesting news on PostgreSQL.

Releases

PostgreSQL 10.2 released
In the list of fixes dozens of items. For example: eliminating failures of parallel processes when using more than one Gather node ( Thomas Munro ) or Changing the behavior of the cube ~> int operator in the contrib / cube extension to ensure its compatibility with the kNN search ( Alexander Korotkov ). At the same time, fixes for versions 9.x. We remind you that in the new numbering 10.2 this is a minor release.
Postgres Pro 10.2.1 Standard
Available on the Postgres Professional website. The release includes all the new features introduced in PostgreSQL 10, as well as bug fixes included in PostgreSQL 10.2. Among the fixes already from Postgres Professional:

• The pg_probackup utility has been updated to version 2.0.14 (fixed ptrack and page backup behavior in race conditions; fixed parallel ptrack backup interrupt ); A new backup status (ORPHAN) has been added, indicating that one of the previous backups has been corrupted.
• Updated pg_variables module.
• Module pg_pathman updated to version 1.4.9. This version fixes ONLY processing in all types of requests.

Details here .
2ndQuadrant Announces OmniDB 2.5 Version
The new version of the PostgrSQL graphical client is close to the stated goal: to become a universal database client. Announced that the new version has basic support for Oracle: you can manage, connect and interact with Oracle databases, using most of the functionality available to PostgreSQL users. A new DDL panel has appeared. Release 2.5 was accompanied by a series of articles, for example: Oracle with OmniDB key developer William Ivanski . Citus 7.2 released.
Distributed SQL queries (including complex SELECTs) can now be run on a cluster. New:

• CTE;
• Complex queries;
• Set operations, such as UNION, INTERSECT, etc .;
• JOIN local and distributed tables using CTE;
• JOIN using inequality expressions;
• pg_partman-based section control automation.

The new version can be downloaded or tested in the cloud of Citus Cloud .

Patches

JIT compilation with LLVM
The patch proposed by Anders Freund on a JIT compilation with LLVM is overgrown with patch additions. The author has already talked about the importance of JIT at several conferences, for example, there is a 50-minute speech at FOSDEM 2018, which is described below. There is still a lot of work to do, but as they say, the union of the JIT libraries LLVM and PostgreSQL can already be considered complete. Current goals: use LLVM for JIT compilation for:

• calculating expressions (WHERE expressions, aggregates, GROUP BY expressions, etc.);
• for tuple deforming - efficient reading from the disk of field values ​​of records and convenient representation in memory.

Support parallel btree index builds. Patch with support for parallel building btree-indexes. Testing shows that you can achieve acceleration by 2-3 times compared with the sequential construction. So far, the model for choosing the number of background processes is primitive, but it can be improved further.

Conferences

future

PGConf APAC 2018
The Asia-Pacific Post-aggressors Conference will be held in Singapore from March 22-23. The program includes reports by Oleg Bartunov, Anton Bykov, Alexander Kukushkin and others.
German-speaking PostgreSQL Conference 2018
should pass on April 13th in Berlin.
PostgresConf US 2018
Held in Jersey City on April 16-20.
PGConfNepal 2018
scheduled for May 4-5 at the University of Kathmandu, in the mountains, in the city of Dulichel, 30 km. from the capital of Nepal.

past

PgConf.Russia 2018 was held on February 5-7 in Moscow.
Home for data - Dmitry Volkov (“Open Systems”) told about his impressions of the conference on the pages of Computerworld magazine.
“According to the speakers at PgConf.Russia, despite sometimes performance problems and a shortage of qualified PostgreSQL specialists in the country, any project may well start using this DBMS, but the choice of a specific DBMS is largely determined by the habits of the developers.” Video reports will appear on website organizer ( Postgres Professional ) soon.
FOSDEM 2018 Video from the conference, held February 3-4 in Brussels.
Security Technologies-2018
The conference was held FSTEC om 14.02. This time it was devoted mainly to the critical information infrastructure. For example, topics were discussed:

• critical information infrastructure and FZ-187 with by-laws;
• change of certification rules for safety requirements.

Alexey Lukatsky in his blog talks about the conference in detail.

Articles

DO or UNDO - there is no VACUUM
A conceptual article by Robert Haas on the need to develop an alternative storage (reminiscent of Oracle architecture) in order to get rid of problems with VACUUM, inherent in the MVCC PostgreSQL architecture. The development is called zheap, but Haas does not disclose the details yet.
A PostgreSQL Row Level Security Primer + Creating Large Policies
Useful article on Jonathan Katz's Crunchy Data website. Understand the use of RLS policies in the case of TOAST.

Blogs

Whats your favorite hidden gem?
An interesting free discussion of the treasures of PostgreSQL. Readers are invited to add to their list, which they happily do.
Machine learning for better query optimization
Oleg Ivanov examines in detail the possibilities of using AI in a DBMS. Author Summary:

• Considered the mechanism of operation of the PostgreSQL query optimizer;
• problems of algorithms that are currently used are noted;
• showed how to use machine learning methods to evaluate selectivity;
• It has been experimentally established that the use of machine learning improves the work of the optimizer and, consequently, improves the performance of the DBMS.

Waiting for PostgreSQL 11 - Support parallel btree index builds.
In the blog Depesz . “The model by which it is now decided how many background processes to use is rather primitive. But we will improve it in the future. ”

Courses

Administering PostgreSQL 10. Basic Course
These courses will be held in Nizhny Novgorod from 02.22 to 03.03.2018. Read Pavel Luzanov and Egor Rogov . Registration page.

Books

A new edition of “Baby”, PostgreSQL for beginners, was released from the printing house (authors Pavel Luzanov, Egor Rogov and Igor Levshin ). In him:

• PostgreSQL 10 is considered;
• Added materials about backing up and setting permissions for connections;
• pgAdmin 3 replaced by new version 4
• updated information about training courses.

Anyone can download the book.

Send your ideas and wishes to email: news_channel@postgrespro.ru
Previous issues: # 2 , # 1