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 was especially concerned about 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 eliminate unnecessary partitions (partition pruning) during the 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/LLVM appeared, 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 large patches with functions for working with JSON/JSONB .



zheap plug-in storage engines ( pluggable storage ), including zheap (i.e., Oracle-like UNDO that works without VACUUM), are monitored 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

Lukas Fittl (Lukas Fittl) not only lists the most important innovations , but also leads to 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 Kerstiens (Craig Kerstiens) also 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.



At PGCONF.EU in Lisbon (here is the program ), of course, a review report is scheduled: Magnus Hagander

What's new in PostgreSQL 11? and to him a couple

What is old in PostgreSQL 11? Devrim Gunduz (Devrim Gündüz).

Towards more efficient query plans: PostgreSQL 11 and beyond Alexandra Kuzmenkova (Postgres Professional), which will mention not only (and even not so much) the embedded features, but also what else is in the work.

Also in the program there are reports

PostgreSQL worst practices by Ilya Kosmodemyansky ( Data Egret )

Do you need a Full-Text Search in PostgreSQL? Oleg Bartunov (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