After a year of development, the new stable branch of Database Management System PostgreSQL 18 has been released. Updates for this new branch will continue for five years until November 2030. Support for PostgreSQL 13.x, the oldest of the supported branches, will end on November 13.
Key innovations:
- An asynchronous input/output subsystem has been added, which allows increasing the throughput of input/output and eliminating delays. In addition to the universal implementation of AIO (io_method=worker) available on all platforms, which is based on running several worker processes (default 3), the io_uring asynchronous input/output interface (io_method=io_uring) can be used on Linux, supported from kernel version 5.1. Asynchronous input/output is currently employed only to accelerate certain operations related to reading data from the file system, such as sequential scans, index bitmap scans, and vacuuming. In some tests, the use of AIO has resulted in performance improvements of 2-3 times. Write operations continue to be executed synchronously to meet ACID requirements.
- The "skip scan" optimization has been implemented in multicolumn indexes, allowing the index to be used not only for checking the first indexed column and the full combination of columns but also for processing other indexed columns separately. For example, when creating a B-tree index on columns "(status, date)," the index was previously utilized only for queries checking the "status" field or both the "status" and "date" fields, whereas checking only the "date" field in a query required scanning the table contents. The "skip scan" mode allows indexing in certain situations when querying only the "date" field. This mode applies only to "B-tree" indexes when using a conditional operator "=" on the indexed field in situations where the skipped field has a small number of distinct values (for example, the optimization will work if the "status" field has a few fixed values).
- Optimizations have been added that use indexes more efficiently for queries containing "OR" and "IN (...)" constructs in the "WHERE" clause, as well as enhancing the performance of planning and executing table joins (for example, the hash merge code has been accelerated, and incremental sorting can now be used during table merges).
- Support for parallelized building of GIN (Generalized Inverted Index) indexes has been added, which are used for indexing composite values such as arrays and for organizing searches on full-text data or JSON structures.
- The capability to create materialized views and keys for partitioning tables with unique indexes that do not use a B-tree structure has been added.
- Overall lock performance has been improved for queries operating with a large number of tables, and enhancements have been made to the processing of queries to partitioned tables, speeding up the elimination of unused partitions and join operations.
- Text operations have been accelerated, such as functions for converting to upper/lower case. A PG_UNICODE_FAST mode has been added to speed up the handling of Unicode character locale properties.
- The capability to save query planner statistics after updates between significant PostgreSQL releases has been implemented. This change allows avoiding expensive "ANALYZE" operations after launching a new version, during which a drop in DBMS performance is observed.
- The performance of the pg_upgrade utility, used to automate the transition to a new major PostgreSQL release, has been enhanced. Optimizations are particularly noticeable when upgrading databases containing a large number of objects, such as tables and sequences. Additionally, a "--jobs N" flag has been added for parallelizing checks in N threads, and a "--swap" flag for replacing data directories entirely without linking, cloning, or copying files.
- Support has been added for virtual generated columns, whose values are computed on-the-fly during query execution without being saved to disk. If the expression 'CREATE TABLE...' for generated columns specifies only the keyword 'GENERATED' without clarifying the type (STORED or VIRTUAL), the new variant applies by default instead of the old implementation. In the old implementation, values were generated during 'INSERT' or 'UPDATE' operations and saved to disk for later use. The drawback of virtual generated columns is that they cannot be used in indexes, while the advantage is the ability to normalize and modify data on-the-fly (which is relevant when working with JSON data). As for classical stored generated columns, support for logical replication has been provided in the new release.
- In the INSERT, UPDATE, DELETE, and MERGE commands, the ability to output past (OLD) and current (CURRENT) values in the RETURNING expression has been implemented. For example, 'UPDATE… RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.'
- A function uuidv7() has been added for generating random unique identifiers in UUIDv7 format. Unlike the old function for generating UUIDs (gen_random_uuid), which is now also available under the name uuidv4(), uuidv7 includes the generation time as well as a random value. The presence of ordered parts in the UUID value (the first 12 characters represent epoch time, while the following 18 are random values) enhances sorting and indexing efficiency, which is crucial since UUIDs are typically used for primary keys (for instance, keys created close in time are placed next to each other in the index).
- In the 'LIKE' operation, support has been implemented for text matching that uses nondeterministic collation properties, allowing for comparisons that consider the meaning of characters (for example, diacritical marks may be ignored during comparison). A CASEFOLD function has been added to change the case of characters considering collation properties (for example, some characters have more than two lowercase variants or require conversion to uppercase rather than lowercase during comparison).
- The ability to use temporal constraints has been added. For the values 'PRIMARY KEY' and 'UNIQUE', the expression 'WITHOUT OVERLAPS' should be used to add temporal constraints, while for 'FOREIGN KEY', the expression PERIOD should be used. For example, when defining primary keys, keys with overlapping time intervals can be restricted.
- A command 'CREATE FOREIGN TABLE … LIKE command' has been added for creating an external table schema based on the definition of a local table.
- Support for connecting to the DBMS using OAUTH 2.0-based authentication with an access token instead of a password has been added. Using OAUTH allows for not storing passwords in the DB, identifying users via external services, and utilizing features such as two-factor authentication and single sign-on (SSO).
- The function ssl_tls13_ciphers() has been added, which allows you to define the list of encryption algorithms permitted when connecting using the TLSv1.3 protocol.
- Support for MD5 algorithm for password hashing has been marked as deprecated and planned for removal. Instead of MD5, it is recommended to use the SCRAM algorithm (SCRAM-SHA-256), which appeared in PostgreSQL 10. Additionally, support for SCRAM-based authentication forwarding via postgres_fdw and dblink to external PostgreSQL servers has been implemented.
- When executing the 'EXPLAIN ANALYZE' operation, information about the number of index search operations during index scanning and the number of buffer accesses during query execution is provided. The output for 'EXPLAIN ANALYZE VERBOSE' includes statistics on CPU, WAL logs, and read operation intensity. The pg_stat_all_tables table has been updated with information on the time spent on VACUUM operations and table analysis. Statistics on I/O intensity and WAL log load have been provided in terms of individual connections. Diagnostic information about conflicts during write operations in logical replication has been added to pg_stat_subscription_stats and logs.
- In new installations, checksum usage for verifying the integrity of stored data is enabled by default. To disable this behavior when running initdb, the option '--no-data-checksums' should be specified.
- The pg_createsubscriber utility now includes the flag "--all" for creating logical replicas with a single command for all databases.
- A new version (3.2) of the protocol used for interaction between external utilities and the DBMS has been implemented in the libpq library. The last protocol update was done in PostgreSQL 7.4 (2003). Version 3.0 continues to be the default in the libpq library.
Source: opennet.ru
