| PostgreSQL 9.6 Released. Postgres Pro Has Contributed to It.

New major release of PostgreSQL is approaching. PostgreSQL 9.6 is expected to be released later today. This is a great release which provides to users set of outstanding new features. We are especially happy that Postgres Professional did substantial contribution to this release. In particular, full-text search for phrases and scalability improvements are listed as major enhancements of this new PostgreSQL release.

The full list of Postgres Professional constributions including:

  • Improve the pg_stat_activity view’s information about what a process is waiting for (Amit Kapila, Ildus Kurbangaliev)

    Historically a process has only been shown as waiting if it was waiting for a heavyweight lock. Now waits for lightweight locks and buffer pins are also shown in pg_stat_activity. Also, the type of lock being waited for is now visible. These changes replace the waiting column with wait_event_type and wait_event.

    See blog post for details.

  • Fix the default text search parser to allow leading digits in email and host tokens (Artur Zakirov)

    In most cases this will result in few changes in the parsing of text. But if you have data where such addresses occur frequently, it may be worth rebuilding dependent tsvector columns and indexes so that addresses of this form will be found properly by text searches.

    See mailing list discussion for details.

  • Allow GIN index builds to make effective use of maintenance_work_mem settings larger than 1 GB (Robert Abraham, Teodor Sigaev)

    See mailing list discussion for details.

  • Add pages deleted from a GIN index’s pending list to the free space map immediately (Jeff Janes, Teodor Sigaev)

    This reduces bloat if the table is not vacuumed often.

    See mailing list discussion for details.

  • Improve handling of dead index tuples in GiST indexes (Anastasia Lubennikova)

    Dead index tuples are now marked as such when an index scan notices that the corresponding heap tuple is dead. When inserting tuples, marked-dead tuples will be removed if needed to make space on the page.

    See mailing list discussion for details.

  • Add an SP-GiST operator class for type box (Alexander Lebedev)

    See mailing list discussion for details.

  • Replace shared-buffer header spinlocks with atomic operations to improve scalability (Alexander Korotkov, Andres Freund)

    See blog post for details.

  • Partition the shared hash table freelist to reduce contention on multi-CPU-socket servers (Aleksander Alekseev)

    See mailing list discussion for details.

  • Improve performance of resource owners with many tracked objects (Aleksander Alekseev)

    See mailing list discussion for details.

  • When appropriate, postpone evaluation of SELECT output expressions until after an ORDER BY sort (Konstantin Knizhnik)

    This change ensures that volatile or expensive functions in the output list are executed in the order suggested by ORDER BY, and that they are not evaluated more times than required when there is a LIMIT clause. Previously, these properties held if the ordering was performed by an index scan or pre-merge-join sort, but not if it was performed by a top-level sort.

    See mailing list discussion for details.

  • Add options to ALTER OPERATOR to allow changing the selectivity functions associated with an existing operator (Yury Zhuravlev)

    See mailing list discussion for details.

  • Introduce CREATE ACCESS METHOD to allow extensions to create index access methods (Alexander Korotkov, Petr Jelínek)

    See slides for details.

  • Improve full-text search to support searching for phrases, that is, lexemes appearing adjacent to each other in a specific order, or with a specified distance between them (Teodor Sigaev, Oleg Bartunov, Dmitry Ivanov)

    See slides for details.

  • Allow omitting one or both boundaries in an array slice specifier, e.g. array_col[3:] (Yury Zhuravlev)

    See mailing list discussion for details.

  • Upgrade the ispell dictionary type to handle modern Hunspell files and support more languages (Artur Zakirov)

    See mailing list discussion for details.

  • Add new functions for tsvector data (Stas Kelvich)

    See mailing list discussion for details.

  • Allow ts_stat() and tsvector_update_trigger() to operate on values that are of types binary-compatible with the expected argument type, not only that argument type; for example allow citext where text is expected (Teodor Sigaev)

    See mailing list discussion for details.

  • Improve pg_rewind so that it can work when the target timeline changes (Alexander Korotkov)

    This allows, for example, rewinding a promoted standby back to some state of the old master’s timeline.

    See mailing list discussion for details.

  • Restructure index access method API to hide most of it at the C level (Alexander Korotkov, Andrew Gierth)

    This change modernizes the index AM API to look more like the designs we have adopted for foreign data wrappers and tablesample handlers. This simplifies the C code and makes it much more practical to define index access methods in installable extensions. A consequence is that most of the columns of the pg_am system catalog have disappeared. New inspection functions have been added to allow SQL queries to determine index AM properties that used to be discoverable from pg_am.

    See slides for details.

  • Add a generic interface for writing WAL records (Alexander Korotkov, Petr Jelínek, Markus Nullmeier)

    This change allows extensions to write WAL records for changes to pages using a standard layout. The problem of needing to replay WAL without access to the extension is solved by having generic replay code. This allows extensions to implement, for example, index access methods and have WAL support for them.

    See slides for details.

  • Allow SP-GiST operator classes to store an arbitrary “traversal value” while descending the index (Alexander Lebedev, Teodor Sigaev)

    This is somewhat like the “reconstructed value”, but it could be any arbitrary chunk of data, not necessarily of the same data type as the indexed column.

    See mailing list discussion for details.

  • Add contrib/bloom module that implements an index access method based on Bloom filtering (Teodor Sigaev, Alexander Korotkov)

    This is primarily a proof-of-concept for non-core index access methods, but it could be useful in its own right for queries that search many columns.

    See slides for details.

  • In contrib/cube, introduce distance operators for cubes, and support kNN-style searches in GiST indexes on cube columns (Stas Kelvich)

    See mailing list discussion for details.

  • Add selectivity estimation functions for contrib/intarray operators to improve plans for queries using those operators (Yury Zhuravlev, Alexander Korotkov)

    See mailing list discussion for details.

  • Make contrib/pageinspect’s heap_page_items() function show the raw data in each tuple, and add new functions tuple_data_split() and heap_page_item_attrs() for inspection of individual tuple fields (Nikolay Shaplov)

    See slides for details.

  • Add support for “word similarity” to contrib/pg_trgm (Alexander Korotkov, Artur Zakirov)

    These functions and operators measure the similarity between one string and the most similar single word of another string.

    See mailing list discussion for details.

  • Add configuration parameter pg_trgm.similarity_threshold for contrib/pg_trgm’s similarity threshold (Artur Zakirov)

    This threshold has always been configurable, but formerly it was controlled by special-purpose functions set_limit() and show_limit(). Those are now deprecated.

    See mailing list discussion for details.