New PostgreSQL 9.6 Release: Postgres Professional Contributions
Today, September 29, 2016, PostgreSQL is released, with the number 9.6. It contains many very useful features, and it is impossible not to 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 on pending processes (Amit Kapila, Ildus Kurbangaliev).
Earlier in pg_stat_activity, processes hanging on heavy (heavyweight) locks were considered to be pending. Now, there are also visible expectations of light locks (lightweight locks) and buffer-pins (how is it in Russian?). On different types of locks in the postgres can be found in the article by Alexander Korotkov . 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 use of memory when building GIN-indices. (Robert Abraham, Fedor Sigaev).
Now, when building GIN-indices, memory is used more efficiently if its allocation (maintenance_work_mem) is more than a gigabyte. Details are on the mailing list .
Immediate release of deleted GIN-index pages (Jeff Janes, Fedor Sigaev).
The deleted pages now immediately fall into the free list, which helps to reduce the volume of the database. Useful when the vacuum is not too frequent. Details on the mailing list .
Efficient processing of dead nodes in GiST indices (Anastasia Lubennikova).
If the dead table node is detected during the index scan, then the corresponding index node will also be immediately marked as dead. When inserted into an index, the space occupied by it will be used. Details on the mailing list .
')
Replacing spinlock for 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 lock waiting (Alexander Alekseev).
Changes in the algorithm for waiting for locks, making a significant contribution to multiprocessor servers. Details on the mailing list .
Improving the performance of ResourceOwner (Alexander Alekseev).
Linear search replaced by 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 issuance of the query and do not participate in the conditions of sampling and grouping, 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 .
Selectivity evaluation functions for contrib / intarray (Yury Zhuravlev, Alexander Korotkov) have been added .
This improves the work of the scheduler with queries that involve fields of type int []. Details on the mailing list .
Phrase search - a new full-text search feature (Fedor Sigaev, Oleg Bartunov, Dmitry Ivanov).
Details on slides and in full text search documentation.
The full-text 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. It is necessary to rebuild the tsvectors, generated using the previous version. Details of the patch are on the mailing list .
Support for Hunspell dictionaries and an increase in the number of languages ​​supported (Arthur Zakirov).
Details on the mailing list and in the documentation .
New useful features 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 .
Added operator class to SP-GiST for box type (Alexander Lebedev)
Details on the mailing list .
Adding options to ALTER OPERATOR, allowing you to specify the selectivity functions for operators (Yuri Zhuravlev).
Details on the mailing list .
New design CREATE ACCESS METHOD, which allows you to create index access methods in PostgreSQL extensions (Alexander Korotkov, Petr JelĂnek).
Details on the slides .
Simplify the API index access methods (Alexander Korotkov, Andrew Gierth).
The index access method API is modified to more closely match the concepts used in FDW and Tablesample. This simplifies the C-shny code and makes it easier to create new access methods to installed extensions. The number of columns in the pg_am system table has decreased, and new functions have appeared for accessing parameters of access methods from SQL. Details on the slides .
Added a generic interface for recording WAL (Alexander Korotkov, Petr JelĂnek, Markus Nullmeier).
This allows extensions to make entries in a standardized way in WAL. This allows extensions to define their own index types, which will automatically be supported by the WAL logs mechanism, that is, in particular, replicated by stream replication. Details on the slides .
The classes of SP-GiST operator classes can now retain some value (“traversal value”) during the index crawling process (Alexander Lebedev, Fedor Sigaev).
Details on the mailing list .
The new contrib / bloom module implements an 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 may be useful in real multi-column queries. Details on the slides .
The contrib / cube extension introduced the distance operator for cubes and kNN search support for GiST indexes in cube columns (Stas Kelvich)
Details on the mailing list .
In the array section, you can now omit the left or right border (Yuri Zhuravlev)
For example,
Improved pg_rewind : ability to work with changed target time line (Alexander Korotkov)
This allows, for example, to roll back the former replica to the state of the old master. Details on the mailing list .
Pageinspect module improvements (Nikolai Shaplov)
The contrib / pageinspect's module's heap_page_items () function displays raw entry data. The new functions tuple_data_split () and heap_page_item_attrs () allow you to look inside individual fields.
Details on the slides .
Added support for “word similarity” in contrib / pg_trgm module (Alexander Korotkov, Artur Zakirov)
You can measure the degree of similarity between a line and the most similar word from another line.
Details on the mailing list .
In the module contrib / pg_trgm added the pg_trgm.similarity_threshold configuration parameter (Arthur Zakirov)
The similarity threshold can now be controlled via the configuration parameter. Previously, this was done only through the special functions set_limit () and show_limit (). Details on the mailing list .
If the readers are particularly interested in any of these points, we are happy to write more.
Performance and monitoring
Improving the presentation of pg_stat_activity in terms of information on pending processes (Amit Kapila, Ildus Kurbangaliev).
Earlier in pg_stat_activity, processes hanging on heavy (heavyweight) locks were considered to be pending. Now, there are also visible expectations of light locks (lightweight locks) and buffer-pins (how is it in Russian?). On different types of locks in the postgres can be found in the article by Alexander Korotkov . 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 use of memory when building GIN-indices. (Robert Abraham, Fedor Sigaev).
Now, when building GIN-indices, memory is used more efficiently if its allocation (maintenance_work_mem) is more than a gigabyte. Details are on the mailing list .
Immediate release of deleted GIN-index pages (Jeff Janes, Fedor Sigaev).
The deleted pages now immediately fall into the free list, which helps to reduce the volume of the database. Useful when the vacuum is not too frequent. Details on the mailing list .
Efficient processing of dead nodes in GiST indices (Anastasia Lubennikova).
If the dead table node is detected during the index scan, then the corresponding index node will also be immediately marked as dead. When inserted into an index, the space occupied by it will be used. Details on the mailing list .
')
Replacing spinlock for 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 lock waiting (Alexander Alekseev).
Changes in the algorithm for waiting for locks, making a significant contribution to multiprocessor servers. Details on the mailing list .
Improving the performance of ResourceOwner (Alexander Alekseev).
Linear search replaced by 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 issuance of the query and do not participate in the conditions of sampling and grouping, 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 .
Selectivity evaluation functions for contrib / intarray (Yury Zhuravlev, Alexander Korotkov) have been added .
This improves the work of the scheduler with queries that involve fields of type int []. Details on the mailing list .
Full text search
Phrase search - a new full-text search feature (Fedor Sigaev, Oleg Bartunov, Dmitry Ivanov).
Details on slides and in full text search documentation.
The full-text 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. It is necessary to rebuild the tsvectors, generated using the previous version. Details of the patch are on the mailing list .
Support for Hunspell dictionaries and an increase in the number of languages ​​supported (Arthur Zakirov).
Details on the mailing list and in the documentation .
New useful features 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
Added operator class to SP-GiST for box type (Alexander Lebedev)
Details on the mailing list .
Adding options to ALTER OPERATOR, allowing you to specify the selectivity functions for operators (Yuri Zhuravlev).
Details on the mailing list .
New design CREATE ACCESS METHOD, which allows you to create index access methods in PostgreSQL extensions (Alexander Korotkov, Petr JelĂnek).
Details on the slides .
Simplify the API index access methods (Alexander Korotkov, Andrew Gierth).
The index access method API is modified to more closely match the concepts used in FDW and Tablesample. This simplifies the C-shny code and makes it easier to create new access methods to installed extensions. The number of columns in the pg_am system table has decreased, and new functions have appeared for accessing parameters of access methods from SQL. Details on the slides .
Added a generic interface for recording WAL (Alexander Korotkov, Petr JelĂnek, Markus Nullmeier).
This allows extensions to make entries in a standardized way in WAL. This allows extensions to define their own index types, which will automatically be supported by the WAL logs mechanism, that is, in particular, replicated by stream replication. Details on the slides .
The classes of SP-GiST operator classes can now retain some value (“traversal value”) during the index crawling process (Alexander Lebedev, Fedor Sigaev).
Details on the mailing list .
The new contrib / bloom module implements an 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 may be useful in real multi-column queries. Details on the slides .
The contrib / cube extension introduced the distance operator for cubes and kNN search support for GiST indexes in cube columns (Stas Kelvich)
Details on the mailing list .
miscellanea
In the array section, you can now omit the left or right border (Yuri Zhuravlev)
For example,
array_col[3:] . Details on the mailing list .Improved pg_rewind : ability to work with changed target time line (Alexander Korotkov)
This allows, for example, to roll back the former replica to the state of the old master. Details on the mailing list .
Pageinspect module improvements (Nikolai Shaplov)
The contrib / pageinspect's module's heap_page_items () function displays raw entry data. The new functions tuple_data_split () and heap_page_item_attrs () allow you to look inside individual fields.
Details on the slides .
Added support for “word similarity” in contrib / pg_trgm module (Alexander Korotkov, Artur Zakirov)
You can measure the degree of similarity between a line and the most similar word from another line.
Details on the mailing list .
In the module contrib / pg_trgm added the pg_trgm.similarity_threshold configuration parameter (Arthur Zakirov)
The similarity threshold can now be controlled via the configuration parameter. Previously, this was done only through the special functions set_limit () and show_limit (). Details on the mailing list .
If the readers are particularly interested in any of these points, we are happy to write more.
Source: https://habr.com/ru/post/311352/
All Articles