PostgreSQL 11 released



    POSTGRESSO Special Edition, dedicated to the release of the official release of version 11.

    Outdoor PostgreSQL holiday. After four beta, PostgreSQL 11 General Availability was released , that is, the official version. In the announcement, there is even a welcome word from Bruce Momdjan : “In preparing this release, the community especially took care of adding the functionality necessary to work with very large databases. It has been proven that PostgreSQL works well with transactional workloads, and now the new version, PostgreSQL 11, will also make it easier for developers to create applications for Big Data. ”

    In release notes , they highlight

    • partitioning:
      • added hash partitioning;
      • PRIMARY KEY, FOREIGN KEY, indexes (see below for this topic) and triggers;
      • the default section for entries that go beyond the boundaries of the created sections;
      • UPDATE by partitioning key can now automatically move the entry to the corresponding section;
      • PostgreSQL has learned to exclude unnecessary sections (partition pruning)during execution of SELECT queries;
    • parallelization:
      • Now you can create an index in parallel in the case of B-tree;
      • with CREATE TABLE ... AS, CREATE MATERIALIZED VIEW and in some cases queries with UNION;
      • improved performance in parallel HASH JOIN and SEQUENTIAL SCAN;
    • new stored procedures have appeared, and transaction management is possible in them;
    • JIT compilation of query fragments, gain on the calculation of expressions;
    • window functions now support all frame options of the SQL: 2011 standard, including RANGE distance from PRECEDING / FOLLOWING, GROUPS mode, the ability to exclude lines from the frame;
    • appeared covering indexes [not covering, but inclusive, strictly speaking - approx. POSTGRESSO] using the INCLUDE clause with CREATE INDEX;
    • from the “miscellaneous” section: ALTER TABLE ... ADD COLUMN with NOT NULL values ​​by default: this variant of the command now does not overwrite all the rows in the table and, therefore, works quickly.


    An impressive list, although not sensational: by definition, Feature freeze fixes functionality, and it was still in mid-April.

    Postgressist from HPE regularly publish a detailed summary of the functionality. After the April Feature freeze, it was like this (in this PDF there is a brief summary of the innovations, and details, and examples).

    Not quite clear, nevertheless, are the super-boring raptures (see below the reaction of Lucas Fitt) about this version. A lot of useful things have been done, but very important things have yet to be completed, but there are directions in which it’s not that the horse doesn’t roll, but this is only the light at the beginning of the tunnel. For example, much has been done in partitioning, no doubt, but it is still impossible to refer to a partitioned table using FOREIGN KEY. You can create a FOREIGN KEY, but in the partitioned table itself. There are no global indexes.

    JIT/LLVMappeared, but immediately disappeared from the default configuration: by default it is turned off. And this is no accident, because in some cases it does not accelerate, but introduces unnecessary overheads.

    Not included in the new version of the big patches with functions to work with JSON/JSONB.

    For the connected storage engines (pluggable storage) and, in particular, zheap(that is, an Oracle-like UNDO that works without VACUUM), many follow with a sinking heart. They are in an embryonic state, they have not even yet resolved the API. An overview of the possibilities in this direction is here .

    But it was just a reminder of what remains to be. Work on the new and unfinished old is in full swing, the direction of development is understandable.

    Articles


    New in Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures
    Lucas Fitl (Lukas Fittl) not only lists the most important innovations , but also results in performance evaluations, listings plans. Lucas explains that the new in the extension for pre-heating the base(pg_prewarm) , recalls the features of the stored procedures, tests JIT. The conclusion is ultra-optimistic: it looks like it will be the best release of PostgreSQL .

    Postgres 11 - a First Look
    Craig Kirstins (Craig Kerstiens) draws attention to features that are not painted above, recalls changes in statistics, for example. Or about ... see below.

    Adding new table columns with default values ​​in PostgreSQL 11
    An article about a curious author patch, Andrew Dunstan (Andrew Dunstan) from 2ndQuadrant . Now, for example, not only static values ​​can be specified in the default column, but also CURRENT_TIMESTAMP or random ().

    At conferences and webinars on PostgreSQL 11


    Peter Eizentraut (Peter Eisentraut) from 2ndQuadrant conducted a webinar on PostgreSQL 11 innovations. The record is supplemented with answers to questions that were not answered at the webinar.

    On PGCONF.EU in Lisbon (this program ) is planned, of course, the overview report: Magnus Hagandera (Magnus Hagander)
    for What's new in the PostgreSQL 11? and to him a pair of
    What is old in PostgreSQL 11? Devrim Gunduz (Devrim Gündüz).
    Towards more efficient query plans: PostgreSQL 11 and beyondAlexandra Kuzmenkova (Postgres Professional), in which not only (and even not so much) closed features, but also what is still in the work will be mentioned.
    Also in the program there are reports by
    PostgreSQL worst practices of Ilya Kosmodemyansky ( Data Egret )
    Do you need a full text search in PostgreSQL? Oleg Bartunova (Postgres Professional) ,
    Advanced PostgreSQL Backup and Recovery methods by Anastasia Lubennikova (Postgres Professional)


    Subscribe to the postgresso channel !

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

    Also popular now: