After a year of development, a new stable branch of the PostgreSQL 14 DBMS has been published. Updates for the new branch will be released over five years until November 2026.
Main innovations:
- Added support for accessing JSON data using array-like expressions: SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release']; SELECT * FROM test WHERE details['attributes']['size'] = '"medium"';
A similar syntax is implemented for the key/value data provided by the hstore type. This syntax was initially implemented using a universal framework, which in the future may be used for other types. Example for 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 “multirange” types, which allow you to specify ordered lists of non-overlapping ranges of values. In addition to each existing range type, its own multirange type is proposed, for example, the type “int4range” corresponds to “int4multirange”, and the type “daterange” corresponds to “datemultirange”. The use of new types simplifies the design of queries that manipulate 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 processing a large number of connections. In some tests, a twofold increase in performance is observed.
- The efficiency of B-tree indexes has been improved and the problem with index growth when tables are frequently updated has been resolved.
- Added support for the client-side (implemented at the libpq level) mode of pipeline transmission of requests, which allows you to significantly speed up database scenarios associated with performing a large number of small write operations (INSERT/UPDATE/DELETE) by sending the next request without waiting the result of the previous one. The mode also helps speed up work on connections with long packet delivery delays.
- Enhanced capabilities for distributed configurations involving multiple PostgreSQL servers. In the implementation of logical replication, it now becomes possible to send in streaming mode transactions that are in the process of execution, which can significantly improve the performance of replication of large transactions. In addition, the logical decoding of data received during logical replication has been optimized.
- The mechanism for connecting external tables Foreign Data Wrapper (postgres_fdw) has added support for parallel query processing, which is currently only applicable when connecting to other PostgreSQL servers. postgres_fdw also adds support for adding data to external tables in batch mode and the ability to import partitioned tables by specifying the “IMPORT FOREIGN SCHEMA” directive.
- Optimizations have been made to the implementation of the VACUUM operation (garbage collection and packaging of disk storage). An emergency cleanup mode has been added that skips non-essential cleanup operations if transaction ID wraparound conditions are created. Reduced overhead when processing indexes in the B-Tree format. The execution of the “ANALYZE” operation, which collects statistics about the operation of the database, has been significantly accelerated.
- Added the ability to configure the compression method used in the TOAST system, which is responsible for storing large data, such as blocks of text or geometric information. In addition to the pglz compression method, TOAST can now use the LZ4 algorithm.
- The tools for monitoring the operation of the DBMS have been expanded. Added views to track the progress of COPY commands (pg_stat_progress_copy), statistics about replication slots (pg_stat_replication_slots) and activity related to the WAL transaction log (pg_stat_wal). The compute_query_id function has been added, which enables various subsystems, such as pg_stat_activity and EXPLAIN VERBOSE, to track requests by assigning a unique identifier for each request.
- Optimizations have been added to the query planner to improve parallel processing of queries and improve the performance of simultaneous execution of sequential record scan operations, parallel execution of queries in PL/pgSQL using the “RETURN QUERY” command, and parallel execution of queries in “REFRESH MATERIALIZED VIEW”. To improve the performance of cyclic nested merges (join), support for additional caching has been implemented.
- Advanced statistics can now be used to optimize expressions, and incremental sorting can now be used to optimize window functions.
- Stored procedures that allow you to manage transactions in blocks of code now support defining return data using "OUT" parameters.
- Added date_bin function to round 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
- Added SEARCH and CYCLE expressions defined in the SQL standard to make it easier to order and identify 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, the autocompletion of commands with tabs has been improved, the ability to display function arguments has been added to the “\df” command, and the displayed statistics have been expanded to the “\dX” command.
- It is possible to assign read-only or write-only privileges to users. Privileges can be set on individual tables, views, and schemas using the pg_read_all_data and pg_write_all_data predefined roles. GRANT pg_read_all_data TO user1;
- New installations default to password authentication using SCRAM-SHA-256 instead of md5 (the "password_encryption" parameter when generating postgresql.conf is now set to 'scram-sha-256').
Source: opennet.ru