Digest news from the world of PostgreSQL. Issue number 12

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


    PostgreSQL 11.1
    10.6, 9.6.11, 9.5.15, 9.4.20 and 9.3.25 were also released with this version (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,,, Postgres Pro Enterprise 10.6.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, in Enterprise 10.6.1 and support for Astra Linux operating system Smolensk 1.6 and improved 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 ...)

    Binaries for Windowsand macOS, as well as for Python Wheel, Dockercontainer and tarball with source code can be downloaded from here . RPMYou and we DEBshould appear in the repositories postgresql.orgin 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

    Released PipelineDB 1.0. 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

    2ndQuadranthas 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.

    The 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, it works automatically failoverandswitchover. You can recover in PITR mode. Some (rather sketchy) description is here .

    Foreign Data Wrapper for SQLite 1.1.0

    In this version, a lot has been completed, started 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

    In the new version, bugs were fixed, 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;
    • mergenow belongs to the category of stable operations (the command mergeupdates the full parent backup using the specified incremental backups)
    • in this version, a more conservative approach to file validation: during the validation process, pg_probackupchecks the checksums of the blocks, validates the block headers and checks whether 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 LSNas 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

    This version adds support for PostgreSQL 11. Bucardo is a daemon written in Perl, which does trigger-based replication. You can configure replication at the table level.

    pg_back 1.7

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

    dbForge Data Compare for PostgreSQL 3.1

    In this versionsupport 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

    This version of the database schema manager adds support for procedures, which appeared in PostgreSQL 11. You can learn about new features and fixes here . You can download from here .

    Cybertec PostgreSQL Configurator

    In this tuning utility , support for PostgreSQL 11 also appeared.

    V1.5 of pgmetrics

    In this release, support for SCRAM-SHA-256 authentication has been added . Also added the ability to collect information only about the bases from the specified list. Github release. Details here .

    pglogical 2.2.1

    2ndQuadrant informs that the new version has 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, and here 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 adapter for Python. 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


    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 ); o pg_probachup, backup and verification with it ( Grigori 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) held 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 JSON/JSONBand 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).



    Clickable banner, details on the link) PGConf.ASIA 2018
    is 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

    P2D2 2019 will be held simultaneously in Prague (February 13-14). It is expected that the conference will be focused 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 sitewhich will still be filled with information as we get closer to the date.


    PostgreSQL. The basics of the SQL language The

    textbook was published by the BHV-Petersburg publishing house 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 .

    Tips Bruce

    great and not terrible Bruce Momjian replaced Role soothsayer on 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.


    Care To Know Clauses: All About SELECT , FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT

    Joshua Otuel (Joshua Otwell) shows the difference between how and what is written in the request, and what and in what order to actually performed.

    When Databases Meet FPGA: Achieving 1 Million TPS With X-DB Heterogeneous Computing
    You don’t often 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 DBMSX-DBown storage engine 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 (Denish Patel) explains what the problem with sectioning were finally resolved in PostgreSQL 11. There is a matrix functionality of 9.6 / 10/11.

    A little about configuring PostgreSQL

    This article by Pavel Fomin is remarkable, perhaps not by itself, but by the fact that it appeared on InfoStartwas at least an attempt to seriously talk about configuring PostgreSQL and 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 and prices. There are winners and losers.

    fsync () and OS

    Viktor Yegorov drew the attention of the members of the PostgreSQL in Russia group to facebookthe fact that the following minor releases will be with altered behaviors for errorsfsync()calls. "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

    Also popular now: