New PostgreSQL 9.6 Release: Postgres Professional Contribution
Today, September 29, 2016, a new release of PostgreSQL was released, received the number 9.6. It contains a lot of very useful features, and one can not help but talk about them, especially since the contribution of our company to this release is significant. Therefore, in this article we will talk about those Postgres Pro developments that are included in today's release.
Improving the presentation of pg_stat_activity in terms of information about pending processes (Amit Kapila, Ildus Kurbangaliev).
Previously, in pg_stat_activity, processes that were hung on heavyweight locks were considered to be waiting. Now, one can also see the expectations of lightweight locks and buffer-pins (how is it in Russian?). You can read about different types of locks in postgres in an article by Alexander Korotkov . The new information is visible in the wait_event_type and wait_event columns. Detailed information about this patch can be found in our blog and in the documentation .
Efficient memory usage when building GIN indexes. (Robert Abraham, Fedor Sigaev).
Now, when building GIN indexes, memory is used more efficiently if it is allocated (maintenance_work_mem) more than a gigabyte. Details are on the mailing list .
Immediate release of deleted GIN index pages (Jeff Janes, Fedor Sigaev).
Deleted pages now immediately fall into the list of free ones, which helps to reduce the database size. Useful for not too frequent vacuums. Details on the mailing list .
Effective processing of dead nodes in GiST indices (Anastasia Lubennikova).
If a dead table node is detected during an index scan, the corresponding index node will also be immediately marked as dead. When inserted into the index, the space it occupies will be used. Detailson the mailing list .
Replacing spinlocks with atomic operations (Alexander Korotkov, Andres Freund).
Increases vertical scalability due to more efficient implementation of locks. Details in the article by Alexander Korotkov .
Optimization of waiting for locks (Alexander Alekseev).
Changes to the lock wait algorithm, which make a significant contribution to multiprocessor servers. Details on the mailing list .
Improving the performance of ResourceOwner (Alexander Alekseev).
Linear search has been replaced with something more efficient. Details on the mailing list .
Calculation of expressions in SELECT after ORDER and LIMIT, if possible(Konstantin Knizhnik).
Now, if the results of the expression do not fall into the query and do not participate in the selection and grouping conditions, it will not be calculated. This reduces the number of function calls (possibly heavy). In addition, functions will be called in the order specified by ORDER BY, and sometimes this is important. Details on the mailing list .
Added selectivity assessment functions for contrib / intarray (Yuri Zhuravlev, Alexander Korotkov).
This improves the scheduler’s work with queries involving fields of the int [] type. Details on the mailing list .
Phrase search is a new full-text search feature (Fedor Sigaev, Oleg Bartunov, Dmitry Ivanov).
Details on slides and in full-text search documentation .
The full-text search parser now understands the leading numbers in e-mails and host names (Arthur Zakirov).
This helps to correctly index texts containing e-mails and urls. We need to rebuild the tsvectors generated using the previous version. Details on the patch are on the mailing list .
Support for Hunspell dictionaries and an increase in the number of supported languages (Arthur Zakirov).
Details on the mailing list and documentation.
New useful functions for working with tsvector (Stas Kelvich).
Details on the mailing list and in the documentation .
The functions ts_stat () and tsvector_update_trigger () now operate on data of binary compatible types (Fedor Sigaev).
Details on the mailing list .
An operator class has been added to SP-GiST for the box type (Alexander Lebedev)
Details on the mailing list .
Adding options to ALTER OPERATOR, allowing you to specify selectivity functions for operators (Yuri Zhuravlev).
Details on the mailing list .
The new CREATE ACCESS METHOD construct, which allows creating index access methods in PostgreSQL extensions (Alexander Korotkov, Petr Jelínek).
Details on the slides .
Simplification of the index access methods API (Alexander Korotkov, Andrew Gierth).
The index access methods API has been modified to better fit the concepts used in FDW and Tablesample. This simplifies C-code and makes it easy to create new methods for accessing installed extensions. The number of columns in the pg_am system table decreased, and new functions appeared to access the parameters of access methods from SQL. Details on the slides .
A generalized WAL recording interface has been added (Alexander Korotkov, Petr Jelínek, Markus Nullmeier).
This allows extensions to write to the WAL in a standardized way. This allows extensions to define their own types of indexes, which will automatically be supported by the WAL log mechanism, i.e., in particular, be replicated by streaming replication. Details on the slides .
Operator classes SP-GiST operator classes can now save some value (“traversal value”) during the index traversal (Alexander Lebedev, Fedor Sigaev).
Details on the mailing list .
The new contrib / bloom module implements the index access method based on Bloom filtering (Fedor Sigaev, Alexander Korotkov).
The module is written mainly to test new capabilities for defining access methods in extensions, but it can be useful in real multi-column queries. Details on the slides .
In the contrib / cube extension , a distance operator for cubes and support for kNN search for GiST indexes on columns of the cube type are introduced(Stas Kelvich)
Details on the mailing list .
In a section of the array can now be omitted left or right edge (Yuri Zhuravlev)
, for example,
Improvement pg_rewind : the ability to work with the modified target timeline (Alexander Korotkov)
This allows, for example, to roll back the former replica to the state of the old master. Details on the mailing list .
Improvements to the pageinspect module (Nikolay Shaplov)
The heap_page_items ( ) function of the contrib / pageinspect's module shows raw post data. The new tuple_data_split () and heap_page_item_attrs () functions allow you to look inside individual fields.
Details on the slides .
Added support for “word similarity” in the contrib / pg_trgm module (Alexander Korotkov, Arthur Zakirov)
You can measure the degree of similarity between a line and the word most similar to it on another line.
Details on the mailing list .
The configuration parameter pg_trgm.similarity_threshold (Arthur Zakirov) has been added to the contrib / pg_trgm module. The
similarity threshold can now be controlled through the configuration parameter. Previously, this was done only through the special functions set_limit () and show_limit (). Details on the mailing list .
If readers will be particularly interested in any of these points, we will be happy to write more.
Performance and Monitoring
Improving the presentation of pg_stat_activity in terms of information about pending processes (Amit Kapila, Ildus Kurbangaliev).
Previously, in pg_stat_activity, processes that were hung on heavyweight locks were considered to be waiting. Now, one can also see the expectations of lightweight locks and buffer-pins (how is it in Russian?). You can read about different types of locks in postgres in an article by Alexander Korotkov . The new information is visible in the wait_event_type and wait_event columns. Detailed information about this patch can be found in our blog and in the documentation .
Efficient memory usage when building GIN indexes. (Robert Abraham, Fedor Sigaev).
Now, when building GIN indexes, memory is used more efficiently if it is allocated (maintenance_work_mem) more than a gigabyte. Details are on the mailing list .
Immediate release of deleted GIN index pages (Jeff Janes, Fedor Sigaev).
Deleted pages now immediately fall into the list of free ones, which helps to reduce the database size. Useful for not too frequent vacuums. Details on the mailing list .
Effective processing of dead nodes in GiST indices (Anastasia Lubennikova).
If a dead table node is detected during an index scan, the corresponding index node will also be immediately marked as dead. When inserted into the index, the space it occupies will be used. Detailson the mailing list .
Replacing spinlocks with atomic operations (Alexander Korotkov, Andres Freund).
Increases vertical scalability due to more efficient implementation of locks. Details in the article by Alexander Korotkov .
Optimization of waiting for locks (Alexander Alekseev).
Changes to the lock wait algorithm, which make a significant contribution to multiprocessor servers. Details on the mailing list .
Improving the performance of ResourceOwner (Alexander Alekseev).
Linear search has been replaced with something more efficient. Details on the mailing list .
Calculation of expressions in SELECT after ORDER and LIMIT, if possible(Konstantin Knizhnik).
Now, if the results of the expression do not fall into the query and do not participate in the selection and grouping conditions, it will not be calculated. This reduces the number of function calls (possibly heavy). In addition, functions will be called in the order specified by ORDER BY, and sometimes this is important. Details on the mailing list .
Added selectivity assessment functions for contrib / intarray (Yuri Zhuravlev, Alexander Korotkov).
This improves the scheduler’s work with queries involving fields of the int [] type. Details on the mailing list .
Full Text Search
Phrase search is a new full-text search feature (Fedor Sigaev, Oleg Bartunov, Dmitry Ivanov).
Details on slides and in full-text search documentation .
The full-text search parser now understands the leading numbers in e-mails and host names (Arthur Zakirov).
This helps to correctly index texts containing e-mails and urls. We need to rebuild the tsvectors generated using the previous version. Details on the patch are on the mailing list .
Support for Hunspell dictionaries and an increase in the number of supported languages (Arthur Zakirov).
Details on the mailing list and documentation.
New useful functions for working with tsvector (Stas Kelvich).
Details on the mailing list and in the documentation .
The functions ts_stat () and tsvector_update_trigger () now operate on data of binary compatible types (Fedor Sigaev).
Details on the mailing list .
Extensibility and Extensions
An operator class has been added to SP-GiST for the box type (Alexander Lebedev)
Details on the mailing list .
Adding options to ALTER OPERATOR, allowing you to specify selectivity functions for operators (Yuri Zhuravlev).
Details on the mailing list .
The new CREATE ACCESS METHOD construct, which allows creating index access methods in PostgreSQL extensions (Alexander Korotkov, Petr Jelínek).
Details on the slides .
Simplification of the index access methods API (Alexander Korotkov, Andrew Gierth).
The index access methods API has been modified to better fit the concepts used in FDW and Tablesample. This simplifies C-code and makes it easy to create new methods for accessing installed extensions. The number of columns in the pg_am system table decreased, and new functions appeared to access the parameters of access methods from SQL. Details on the slides .
A generalized WAL recording interface has been added (Alexander Korotkov, Petr Jelínek, Markus Nullmeier).
This allows extensions to write to the WAL in a standardized way. This allows extensions to define their own types of indexes, which will automatically be supported by the WAL log mechanism, i.e., in particular, be replicated by streaming replication. Details on the slides .
Operator classes SP-GiST operator classes can now save some value (“traversal value”) during the index traversal (Alexander Lebedev, Fedor Sigaev).
Details on the mailing list .
The new contrib / bloom module implements the index access method based on Bloom filtering (Fedor Sigaev, Alexander Korotkov).
The module is written mainly to test new capabilities for defining access methods in extensions, but it can be useful in real multi-column queries. Details on the slides .
In the contrib / cube extension , a distance operator for cubes and support for kNN search for GiST indexes on columns of the cube type are introduced(Stas Kelvich)
Details on the mailing list .
Miscellaneous
In a section of the array can now be omitted left or right edge (Yuri Zhuravlev)
, for example,
array_col[3:]
. Details on the mailing list . Improvement pg_rewind : the ability to work with the modified target timeline (Alexander Korotkov)
This allows, for example, to roll back the former replica to the state of the old master. Details on the mailing list .
Improvements to the pageinspect module (Nikolay Shaplov)
The heap_page_items ( ) function of the contrib / pageinspect's module shows raw post data. The new tuple_data_split () and heap_page_item_attrs () functions allow you to look inside individual fields.
Details on the slides .
Added support for “word similarity” in the contrib / pg_trgm module (Alexander Korotkov, Arthur Zakirov)
You can measure the degree of similarity between a line and the word most similar to it on another line.
Details on the mailing list .
The configuration parameter pg_trgm.similarity_threshold (Arthur Zakirov) has been added to the contrib / pg_trgm module. The
similarity threshold can now be controlled through the configuration parameter. Previously, this was done only through the special functions set_limit () and show_limit (). Details on the mailing list .
If readers will be particularly interested in any of these points, we will be happy to write more.