Digest news from the world of PostgreSQL. Issue number 12

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

Releases

PostgreSQL 11.1
Together with this version came out also 10.6, 9.6.11, 9.5.15, 9.4.20 and 9.3.25 (this is the latest release of 9.3). This version has security related changes. Corrected the detected errors. You can read about releases here , and download here .
')

Postgres Pro Standard 11.1.1, 10.6.1, 9.6.11.1, 9.5.15.1, Postgres Pro Enterprise 10.6.1, 9.6.11.1

These releases are based on the corresponding vanilla versions of PostgreSQL and on previous versions of Standard and Enterprise. In the "Release Notes" section in the appropriate version of the documentation you can read about differences from the previous version of the family. For example, Enterprise 10.6.1 and 9.6.11.1 added support for the Astra Linux operating system “Smolensk” 1.6 and improved the stability of autonomous transactions.

pgAdmin 4 version 3.5.

In this release, not only fixed bugs (11 pieces), but also added new features . Among them:

• Migrating from Bootstrap 3 to Bootstrap 4
• You can stop service processes (Backup / Restore / Maintenance ...)

You can download binaries for Windows and macOS , as well as for Python Wheel , Docker containers and tarballs with source files from here . RPM and DEB should appear in the postgresql.org repositories in the coming days.

TimescaleDB 1.0

The announcement says that this version is suitable for use in industrial mode. This time-oriented database is designed as an extension to PostgreSQL. You can run different versions of TimescaleDB with the same PostgreSQL instance. The core of the development team is Princeton University graduates.

PipelineDB 1.0

PipelineDB 1.0 released. This DBMS too, as well as TimescaleDB, is focused on work with time series. But PipelineDB is optimized for aggregation and does not store large amounts of raw data, it is designed for analytics in real time. This DBMS is the successor to TelegraphCQ (streaming DBMS on PostgreSQL), hereinafter StreamDB (its commercial version). Now it works as a PostgreSQL extension under the Apache 2.0 license.


Postgres-XL 10R1

2ndQuadrant announced the release of Postgres-XL 10R1. It supports most of the major new features from PostgreSQL’s recent past, such as declarative partitioning in PostgreSQL 10 and query parallelization, which appeared in 9.6. In 10R1, a full-text phrasal search appeared. Specific features of XL: The distributed query scheduler has been improved. Details here , and you can download here.

GDS

2ndQuadrant reports that their Global Database as a Service (GDS) now supports PostgreSQL 11. It is designed for clouds , supports BDR, including in a geographically distributed environment. You can backup from any node to any and from any restore, automatic failover and switchover . You can recover in PITR mode. Some (rather sketchy) description is here .

Foreign Data Wrapper for SQLite 1.1.0

In this version, much has been completed, begun in 1.0.0 released shortly before. As a result, it has:

• PostgreSQL 11 support;
• push-down expressions WHERE;
• push-down aggregation;
• transaction support;
• on external tables, you can perform SELECT, INSERT, UPDATE, and DELETE.

Develop Foreign Data Wrapper for SQLite at Toshiba Software Engineering & Technology Center . Sources can be found in the repository .

pg_probackup 2.0.24

The new version fixes bugs, and among the innovations regarding version 2.0.17:

• Starting with version PG 9.6, when backing up a replica, no connection to the master is required;
• merge now belongs to the category of stable operations (the merge command updates the full parent backup using the specified incremental backups)
• in this version, a more conservative approach to file validation: during the validation process, pg_probackup checks the checksums of the blocks, validates the block headers and checks if there are any blocks “from the future”. This is the default behavior and can be undone;
• in PAGE mode, you can now create a map of changed pages in parallel;
• Starting from version 10, you can set LSN as a restore point.

To see other innovations and fixes, you can look here . You can download from here . The Windows version is now in beta status.

Bucardo 5.5.0

PostgreSQL 11 support has been added to this release . Bucardo is a Perl daemon written for trigger-based replication. You can configure replication at the table level.

pg_back 1.7

This is a new version of the script for backup. The script can save roles and table spaces.

dbForge Data Compare for PostgreSQL 3.1

This version adds support for connections to PostgreSQL 10.x and 11.x. The utility Data Compare allows you to compare databases and individual tables in them, helps to write scripts to synchronize databases.

pgCodeKeeper 5.2.0

In this version of the database schema manager, support has been added for procedures that appeared in PostgreSQL 11. You can learn about new features and fixes here . You can download from here .

Cybertec PostgreSQL Configurator

PostgreSQL 11 support has also appeared in this tuning utility .

v1.5 of pgmetrics

This release adds support for SCRAM-SHA-256 authentication. Also added the ability to collect information only about the bases from the specified list. Release on githaba . Details here .

pglogical 2.2.1

2ndQuadrant informs that in the new version full support for PostgreSQL 11.

pgpool-II 4.0.2

In addition to this version, Pgpool-II 4.0.2, 3.7.7, 3.6.14, 3.5.18, 3.4.21 are available, but versions up to 3.6 are no longer distributed in source codes. About the release here . Sources and RPMs here .

psycopg 2.7.6

This is a popular Python adapter. It has several extensions to facilitate access to PostgreSQL from Python. In this version, they limited themselves to correcting errors and remapping error codes for PostgreSQL 11. Sources here .

psqlODBC 11.00.0000

About changes in this version (4 zeros - this is not a typo) can be found here .

Conferences and meetings

Past

PGDay Krasnoyarsk

The conference was held on November 12-13. From various reports it was possible to learn, for example, about the experience of working in the PostgreSQL + 1C bundle ( Mikhail Fiskov ); pg_probachup , backup and verification with it ( Gregory Smolkin ); about sharding and sectioning with a billion records ( Alexey Fadeev ). And the famous Bruce Momdjan talked about whether Postgres is eternal. You can read about the conference in the Postgres Professional blog , and here you can read the presentation slides.

Egor Rogov and Pavel Luzanov ( Postgres Professional training department) conducted courses on basic PostgreSQL administration skills ( DBA1 )

Mitap iCluster in Nizhny Novgorod

This PostgreSQL mitap was held on November 16 in the Ankudinovka Technopark. Bruce Momdjan again acted as a foreign star. Oleg Bartunov and Ivan Panchenko , who represented Postgres Professional at the highest level, explained the possibilities of JSON/JSONB and the principles of creating high-performance (and not just high-loaded) applications, respectively. Postgres specialists communicated with city authorities and local business representatives. Report cnews journalist can be seen here .

Five Cool Things I Learned at the PostgreSQL Conference Europe 2018

Achilles Mantzios about what he saw and heard in Lisbon. Topics that impressed the author (there are not quite 5 of them):

• logical replication;
• zheap;
• parallel queries;
• setting shared_buffers;
• Little by little about everything (including JIT and improved full-text search).

Future

(Clickable banner, details on the link)

PGConf.ASIA 2018
expected December 10-12 in Tokyo.

PGConf India 2019

An Indian user group will host PGconf in Bangalore from February 13 to 15. Since quite a few key PG developers are associated with this subcontinent, this can be interesting. Conference site .

Prague PostgreSQL Developer Day 2019

Synchronously (February 13-14), P2D2 2019 will be held in Prague. It is expected that the conference will focus on the problems of users and application developers. Apply here .

PostgreSQL Conference Europe 2019, Milan

This conference will be held October 15-18. It is proposed to follow the news on the site , which will still be filled with information as it approaches the date.

Education

PostgreSQL. Basics of SQL

The textbook was published by the publishing house BHV-Petersburg with the assistance of Postgres Professional and is already being sold in bookstores "Biblio-Globus", Ozon.ru, "Labyrinth", "Moscow House of the Book", "Young Guard" and others. The author of the textbook E.P. Morgunov .

Bruce's Tips

The great and not at all terrible Bruce Momdzhan changed the role of the soothsayer to the role of an ordinary sensei. Here are tips for those who want to participate as a speaker at international PG conferences. Preferred topics:

• new functionality (features) in PG;
• user experience (case studies);
• (internal device PG (Internals);
• new types of loads (workloads);
• performance
• Application Development

It is better to submit applications for several topics at once: the competition is great, and at least one of them can pass ... And if you have accepted the application, start sculpting the slides a few weeks, and not 30 minutes before the report.

Articles

All About SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT

Joshua Otwell shows the difference between how and what is written in the request, and what and in what order is actually executed.

When Databases Meet FPGA: Achieving 1 Million TPS With X-DB Heterogeneous Computing
Not so often you hear about GPUs that successfully accelerated PostgreSQL, and FPGA / FPGAs are completely exotic. And here is an article about processing 1 million transactions per second, partly due to FPGA. They have already been assigned acceleration of queries with SELECT and WHERE, on the GROUP BY queue. This solution is more surprising because Alibaba has an OLTP load, not OLAP, and a serious share of pushing transactions. In a distributed DBMS X-DB its own X-Engine storage X-Engine . The article describes the specific architecture and modifications necessary for working in such an extreme and exotic environment.

Postgres 11 partitioning

Denish Patel clarifies which partitioning problems were finally solved in PostgreSQL 11. There is a 9.6 / 10/11 functionality matrix.

A little about configuring PostgreSQL

This article by Pavel Fomin is remarkable, perhaps not by itself, but by the fact that its appearance on the InfoStart website was at least an attempt at a serious discussion about configuring PostgreSQL and about the technologies of this DBMS. It is written, to put it mildly, not yesterday, but comments continue to arrive to this day.

Express performance review of PostgreSQL 10.5 in the latest cloud services of Yandex. Oblak

In this review there are numbers, performance graphs, prices. There are winners and losers.

fsync () and OS

Victor Yegorov drew the attention of the members of the group “PostgreSQL in Russia” on facebook to the fact that the following minor releases will be with altered behaviors to fsync() call errors. "On a number of OSs (Linux in the first rows), the kernel no longer attempts to write from its buffer to the file system (which the application sees as an fsync error), and when the application is called again by fsync, it simply returns SUCCESS (the kernel simply drops its write -back cache if something went wrong). As a result, it was decided in such cases PANIC, i.e. the base will be folded. The added data_sync_retry torsion, which, when changed, causes the database not to fold, but only to generate an error in the log and continue trying fsync blocks. True, products do not recommend this option. ” Discussed commit and discussion details .

---

Subscribe to the postgresso channel!

Send your ideas and wishes to email: news_channel@postgrespro.ru
Previous issues: # 11 (special) , # 10 ,
# 9 , # 8 , # 7 , # 6 , # 5 , # 4 , # 3 , # 2 , # 1