Digest news from the world of PostgreSQL. Issue number 7

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

Releases

As promised, the PostgreSQL 11 beta was released on May 25th. Its innovations in relation to 10.x are mainly already listed in the summary that they froze on feature freeze .

Postgres Pro 10.4.1
Postgres Professional has released new releases of Postgres Pro Standard / Enterprise 10.4.1, based on PostgreSQL 10.4 and Postgres Pro 10.3.2. They include all the improvements to PostgreSQL 10.4, as well as a number of important improvements:

• Updated jsquery module, responsible for queries to the jsonb data type;
• The pg_probackup utility for managing backups and restoring database clusters has been updated to version 2.0.17 and has been enhanced with new features; for example, the DELTA mode has been added, in which all data files in the directory are read and an incremental copy is created for pages that have been modified since the previous copy;
• pg_pathman module, which is responsible for the partitioning of large and distributed databases, has been updated to version 1.4.11, in which a number of errors have been fixed;
• Corrected the sorting of the letters "E" and "D" in the module mchar, providing additional data types for compatibility with Ms SQL Server;
• Improved version for Windows.

Odyssey
Yandex introduced the 1st open source version of the multi-threaded Odyssey puller for PostgreSQL. It is designed for high loads in multi-core systems: more than 10k TLS connections and more than 100k TPS per server with a much smaller number of open connections with PostgreSQL than with several pgbouncer ones.
Odyssey can:

• linearly scaled by core;
• work in different modes of pooling, with custom authentication, restrictions;
• automatically roll back transactions and kill server processes when connection is lost;
• correctly forward errors.

PostGIS-2.5.0alfa
This newest version is still under development. It will work with PostgreSQL 9.4 and younger, but it will reveal all the features with just released PostgreSQL 11beta.

Developments

The list of PostgreSQL committers (22 people) is extended to 7 people, including Alexander Korotkov , Postgres Professional (a note about this in TAdviser ). Prior to that, the only representative of Russia in this list of people endowed with the power to approve sent patches was Fedor Sigaev (also Postgres Professional).

Microsoft acquired GitHub
The purchase cost the company $ 7.5 billion. About motives can be read, for example, here .

Education

The e-book Basics of Database Technology, Part 1 has been published .
B. A. Novikov, E. A. Gorshkova, N. G. Grafeeva ; by ed. E. V. Rogova ; Postgres Professional.

The 2-week tour of Vladivostok-Khabarovsk by Postgres Professional teachers Egor Rogov and Pavel Luzanov was successfully completed. In Vladivostok, classes were held in such a nice new building (FEFU, Building A, Level 8, “Boiling Point”):

Postgres moral

It is proposed and actively discussed in the community Moral Code of the post-regress . An appropriate committee should appear soon.

Articles

PostgreSQL Standard SQL Gap Analysis
Markus Winand (Markus Winand), teacher and consultant, author of the book SQL Performance Explained , promises to tell the details of his tests PostgreSQL to check their compliance with the latest standards ( SQL-2016 ) against the other 5 DBMS. This time about what PostgreSQL has not yet implemented from SQL, or the implementation is incomplete. Speech in this case about MERGE, JSON, EXTRACT, listagg , etc.
You can read about the history of the MERGE patch here , and about the thorny path of JSON patches here . Markus Vinand is not at all an enemy of PostgreSQL: before that he gave a talk on Standard SQL Features Where PostgreSQL Beats its Competitors

Built-in Sharding for PostgreSQL
In this article, Robert Haas does not so much explain the work of sharding in PostgreSQL, but rather examines the problems that have yet to be solved:

• The PostgreSQL Executor does not know how to simultaneously perform queries for different foreign partitions.
• We must learn to give more work to third-party resources (more pushdown).
• ACID. Now it is possible that a transaction is fixed on some nodes and not fixed on others. Haas mentions Stas Kelvich (Postgres Professional), who suggested a realistic way using global snapshots.
• Automation of installation and support sharding. Too much is done manually.

Recovery use cases for Logical Replication in PostgreSQL 10
Konstantin Evteev (Avito) analyzes the possibilities of transferring recovery tools from Londiste to logical replication in PostgreSQL 10 . On examples of configurations in your company

Solving Data Science tasks with Greenplum DB
This article describes how the real analytical problems of Big data (using model training, MapReduce and processing non-formalized text) are solved on Greenplum , without Hadoop , etc. from the gentleman's Big data set.
On the example of a system of 3 nodes in virtual machines.

We monitor active PostgreSQL 10 sessions, as in Oracle
Dmitry Tsvetkov rewrote the ASH-Viewer for Oracle monitoring utility for PostgreSQL and demonstrates its capabilities.

Battle of MERGE. Chronicle with conclusions and morality
The story of the life of the community with an excursion into the syntax and variations of the command MERGE .

Pacemaker / corosync cluster without validol
An article by Igor Kosenkov (Postgres Professional) with practical tips for setting up a pacemaker/corosync .

Past conferences and meetings

BDR 3.0 Webinar
2ndQuadrant held this webinar for everyone, partly compensating for the paucity of materials on BDR 3.0, which is of great interest. The main part was followed by offline answers to questions ( Follow up ). However, the topic requires further clarification.

PGCon 2018 in Ottawa
Sasha Alexeev's report on the PGCon 2018 conference in Ottawa with a bias in reports from Russia. The conference also extensively discussed issues of plaggable storage (including API), undo log, JIT, and, of course, fsgate .
It was decided in this cycle to hold 5 komitfests instead of the usual 4. The program and slides of some reports can be searched here . There is also a transcript of the discussion of organizational issues.

Future conferences

PGDay.IT 2018
Held in Lazise on the shores of Lake Garda, near Verona on June 29th.

Swiss PGDay 2018
The end of registration (June 28) is nearing a conference in Rapperswil (near Zurich). It will take place on the 29th of June.

PGConf.Brazil 2018
Should be held in Brazilian São Paulo on August 3-4.

PG Day France 2018
Expected in Marseille on the 26th of June.

The Portland PostgreSQL Users Group
PgConf Silicon Valley 2018
Will be held in San Francisco on September 5-7. Until the end of registration (12th of June), there are very few.

PostgresConf South Africa 2018
To be held in Johannesburg on October 9th. Registration will last the whole of June.

PGconf.ASIA 2018
It will be December 10-12 in Tokyo.

Subscribe to the postgresso channel!

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