After a year of development, the new stable branch of the PostgreSQL DBMS 14 has been released. Updates for this new branch will be released for five years until November 2026.
Key innovations:
- Support has been added for accessing JSON data using expressions resembling array operations: SELECT (‘{ "postgres": { "release": 14 }}’::jsonb)[‘postgres’][‘release’]; SELECT * FROM test WHERE details[‘attributes’][‘size’] = ‘"medium"‘;
Similar syntax has been implemented for key/value format data provided by the hstore type. This syntax was originally implemented using a universal framework, which may also be utilized for other types in the future. Example for the hstore type: INSERT INTO mytable VALUES (‘a=>b, c=>d’); SELECT h[‘a’] FROM mytable; UPDATE mytable SET h[‘c’] = ‘new’;
- The family of types for defining ranges has been expanded with new types called "multirange", which allow the specification of ordered lists of non-overlapping value ranges. For each existing range type, a corresponding multirange type has been proposed; for example, "int4range" corresponds to "int4multirange", and "daterange" corresponds to "datemultirange". The use of the new types simplifies query formation when dealing with complex sequences of ranges. SELECT ‘{[3,7), [8,9)}’::int4multirange; SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
- Optimizations have been made to improve the performance of high-load systems handling a large number of connections. Some tests observe a twofold increase in performance.
- The efficiency of B-tree indexes has been improved, and the issue of index bloat from frequent table updates has been resolved.
- Client-side support has been added for the pipeline mode (implemented at the libpq level), which significantly accelerates database operation scenarios involving a large number of small write operations (INSERT/UPDATE/DELETE) by allowing the next query to be sent without waiting for the previous one to complete. This mode also helps speed up operations with connections that have high latency.
- Capabilities have been expanded for distributed configurations involving multiple servers PostgreSQL. The implementation of logical replication has introduced the ability to stream transactions that are currently in progress, significantly improving the performance of replicating large transactions. Additionally, logical decoding of data received during logical replication has been optimized.
- The Foreign Data Wrapper (postgres_fdw) mechanism for connecting external tables now supports parallel query processing, applicable only when connecting to other PostgreSQL servers. Support for batch data insertion into external tables and importing partitioned tables via the ‘IMPORT FOREIGN SCHEMA’ directive has also been added to postgres_fdw.
- Optimizations have been made to the VACUUM operation (garbage collection and disk storage packing). An emergency cleanup mode has been introduced, skipping non-essential cleanup operations if conditions lead to transaction ID wraparound. Overhead has been reduced when processing B-Tree indexes. The ANALYZE operation, which collects statistics about database performance, has been significantly accelerated.
- A new option to configure the compression method used in the TOAST system, responsible for storing large data like text blocks or geometric information, has been added. In addition to the pglz compression method, the LZ4 algorithm can now also be used in TOAST.
- Monitoring tools for the DBMS have been expanded. Views have been added to track the progress of ‘COPY’ commands (pg_stat_progress_copy), statistics on replication slots (pg_stat_replication_slots), and activities related to WAL transaction logs (pg_stat_wal). A compute_query_id function has been introduced, allowing for tracking queries with a unique identifier assigned to each query across different subsystems such as pg_stat_activity and EXPLAIN VERBOSE.
- The query planner has added optimizations that improve parallel query processing and enhance the performance of executing simultaneous operations, including sequential record scanning, parallel execution of queries in PL/pgSQL using the ‘RETURN QUERY’ command, and parallel execution of queries in ‘REFRESH MATERIALIZED VIEW’. To boost the performance of cyclic nested joins, support for additional caching has been implemented.
- For expression optimization, extended statistics can now be used, and for optimizing window functions, incremental sorting is available.
- In stored procedures that manage transactions within code blocks, support for defining return data using ‘OUT’ parameters has been implemented.
- The date_bin function has been added for rounding timestamp values according to a specified interval. SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); 2020-02-11 15:30:00
- The SQL standard expressions SEARCH and CYCLE have been added, simplifying ordering and identifying cycles in recursive common table expressions (CTE). WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH DEPTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol;
- In the psql utility, command autocompletion has been improved with tab completion, the ‘\df’ command now displays function arguments, and the ‘\dX’ command has expanded output statistics.
- Users can now be granted privileges that allow for read-only or write-only access. These privileges can be defined for specific tables, views, and schemas using the predefined roles pg_read_all_data and pg_write_all_data. GRANT pg_read_all_data TO user1;
- In new installations, password authentication is now applied by default using the SCRAM-SHA-256 method instead of md5 (the ‘password_encryption’ parameter in postgresql.conf generation is now set to 'scram-sha-256').
Source: opennet.ru
