{"id":119954,"date":"2024-09-26T21:43:01","date_gmt":"2024-09-26T19:43:01","guid":{"rendered":"https:\/\/prohoster.info\/blog\/novosti-interneta\/reliz-subd-postgresql-17"},"modified":"2024-09-26T21:43:01","modified_gmt":"2024-09-26T19:43:01","slug":"reliz-subd-postgresql-17","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/news\/reliz-subd-postgresql-17","title":{"rendered":"Release of PostgreSQL 17","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"
After a year of development, a new stable branch of the PostgreSQL database management system 17 has been released. Updates for this new branch will be provided for five years until November 2029. Support for PostgreSQL 12.x, the oldest supported branch, will end on November 14. <\/p>\n
Key innovations: <\/p>\n
\n
When performing the VACUUM operation (garbage collection, cleaning, and packing of disk storage), a new data structure is used that consumes up to 20 times less memory. The transition to this new structure has accelerated the execution of VACUUM operations and reduced the consumption of shared resources.\n
Input\/output performance optimization has continued. Improvements in the code for handling the Write-Ahead Logging (WAL) have doubled the write operation throughput in systems with a high number of concurrently executing queries. A new streaming I\/O interface has been proposed, allowing for faster sequential data scanning in tables and speeding up statistics updates for the query planner after running the ANALYZE operation.\n
Query performance with the 'IN' expression has been improved when using indexes based on the B-tree structure. The ability to build BRIN (Block Range Index) indexes in parallel has been implemented. The query planner optimizes 'NOT NULL' checks and speeds up the processing of Common Table Expressions (CTE) defined using the SQL command 'WITH'. Vector processor instructions (SIMD) are now utilized to accelerate certain calculations; for example, the bit_count function is enhanced by AVX-512 instructions.\n
Support for new features for working with JSON format, defined in the SQL\/JSON standard, has been implemented. A JSON-TABLE function has been added to convert JSON data into standard PostgreSQL tables, along with new jsonpath operators for transforming JSON data into native PostgreSQL data types. New functions for creating (JSON, JSON_SCALAR, and JSON_SERIALIZE) and querying (JSON_EXISTS, JSON_QUERY, and JSON_VALUE) JSON data have been proposed.\n
The capabilities of the 'MERGE' SQL command have been expanded, allowing for the creation of conditional SQL expressions that combine INSERT, UPDATE, and DELETE operations in a single statement. The RETURNING expression has been added to calculate and return values based on inserted, updated, or deleted rows. Support for updating views has also been implemented.\n
The data loading and export capabilities in batch mode have been enhanced. The COPY command's export speed for large rows has been increased by up to two times, copying has been optimized when the source and target encodings match, and the ON_ERROR option has been added, allowing the import to continue after errors occur during data insertion.\n
For partitioned tables, the use of columns with unique identifiers has been added, as well as support for the constraint exclusion optimization technique.\n