{"id":144010,"date":"2025-09-25T17:11:55","date_gmt":"2025-09-25T15:11:55","guid":{"rendered":"https:\/\/prohoster.info\/blog\/novosti-interneta\/reliz-subd-postgresql-18"},"modified":"2025-09-25T17:11:55","modified_gmt":"2025-09-25T15:11:55","slug":"reliz-subd-postgresql-18","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/news\/reliz-subd-postgresql-18","title":{"rendered":"Release of Database Management System PostgreSQL 18","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"
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. <\/p>\n
Key innovations: <\/p>\n
\n
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.\n
Optimization of the 'skip scan' feature has been implemented in multi-column 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, previously, when creating a B-tree index on the columns '(status, date)', the index was only applied to queries checking the 'status' field or both 'status' and 'date' fields, while checking only the 'date' field would cause a scan of the table's contents. The 'skip scan' mode allows, in certain situations, scanning the index for queries on just the 'date' field. This mode only applies to 'B-tree' indexes when using the conditional operator '=' on the indexed field, specifically in situations where the skipped field has a small number of distinct values (for example, the optimization will be effective if the 'status' field has a few fixed values).\n
Optimizations have been added that more efficiently utilize indexes for queries containing 'OR' and 'IN (...)' constructs in the 'WHERE' block, as well as enhancing the performance of join planning and execution (for example, the hash merge code has been accelerated and incremental sorting has been allowed during table merging).\n
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.\n
The ability to create materialized views and keys for partitioned tables with 'unique' index structures that do not use B-tree has been introduced.\n
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.\n
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.\n
The ability to save the query planner's statistics after updates between significant releases of PostgreSQL has been implemented. This change helps to avoid executing the resource-intensive 'ANALYZE' operation after launching a new version, during which a performance drop of the DBMS can be observed.\n
The performance of the pg_upgrade utility, used to automate the transition to a significant new release of PostgreSQL, has been improved. The optimizations are especially noticeable when upgrading databases that contain a large number of objects, such as tables and sequences. To speed up pg_upgrade, a flag «—jobs N» has also been added for parallelizing checks across N threads, and a flag «—swap» for completely swapping data directories without creating links, cloning, or copying files.\n
Support for virtual generated columns has been added, where the values are computed on the fly during query execution, without being saved to disk. If the expression «CREATE TABLE…» specifies only the keyword «GENERATED» for generated columns without specifying the type (STORED or VIRTUAL), the new variant is applied 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. A drawback of virtual generated columns is that they cannot be used in indexes, while an advantage is the ability to normalize and modify data on the fly (relevant when working with JSON data). As for classical stored generated columns, support for logical replication has been provided in the new release.\n
In INSERT, UPDATE, DELETE, and MERGE commands, the ability to return previous (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.*.»\n
The uuidv7() function has been added for generating random unique identifiers in the UUIDv7 format. Unlike the old function for generating UUIDs (gen_random_uuid), which is now also available under the name uuidv4(), UUIDv7 includes the time of generation along with the random value. The presence of ordered parts in the UUID value (the first 12 characters represent epoch time, while the subsequent 18 are random values) improves sorting and indexing efficiency, which is particularly relevant since UUIDs are commonly used as primary keys (for example, keys created close in time are placed next to each other in the index).\n
The 'LIKE' operation has been enhanced to support text matching that uses non-deterministic locale properties 'collation', allowing for matches that consider the meaning of characters (for example, accent marks may be disregarded in comparisons). A CASEFOLD function has been added to change the case of characters with respect to 'collation' properties (for instance, some characters have more than two lowercase variants or may require conversion to uppercase instead of lowercase during comparisons).\n
The ability to use temporal constraints has been added. In the values 'PRIMARY KEY' and 'UNIQUE', the expression 'WITHOUT OVERLAPS' should be used to add temporal constraints, while in the value 'FOREIGN KEY' \u2014 the expression PERIOD. For example, when defining primary keys, keys with overlapping time intervals can be restricted.\n
A command 'CREATE FOREIGN TABLE ... LIKE command' has been added to create a foreign table schema based on the definition of a local table.\n
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).\n
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.\n
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.\n
When executing the 'EXPLAIN ANALYZE' operation, information on the number of index search operations during index scanning and the number of buffer accesses during query execution is provided. The 'EXPLAIN ANALYZE VERBOSE' output includes statistics on CPU usage, WAL logs, and the intensity of read operations. The pg_stat_all_tables table has been updated with information about the time spent on the VACUUM operation and table analysis. Statistics on input\/output intensity and WAL log load by individual connections have been provided. Information on diagnosing conflicts during write operations in logical replication has been added to pg_stat_subscription_stats and logs.\n
In new installations, checksums for data integrity verification are enabled by default. To disable this behavior when starting initdb, the '--no-data-checksums' option should be specified.\n
A flag '--all' has been added to the pg_createsubscriber utility to create logical replicas for all databases in one command.\n
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. <\/ul>\n