PostgreSQL 18 released
The latest version of the advanced open-source database, PostgreSQL, has been released. This significant upgrade, dubbed PostgreSQL 18, promises to bring substantial performance improvements, new features, and enhanced security measures.
At the heart of this release lies a novel I/O subsystem designed to tackle the limitations imposed by readahead mechanisms in operating systems. By using asynchronous I/O (AIO) operations, PostgreSQL can now make several requests at the same time, which improves its ability to read ahead and increases overall AIO has been demonstrated to deliver performance gains up to three times in certain scenarios.
Major-version upgrades are also set to become less disruptive thanks to the new release. By preserving planner statistics during an upgrade, upgraded clusters will reach expected performance more quickly after the process is complete. Furthermore, pg_upgrade, the utility responsible for major version upgrades, has been enhanced with features such as faster upgrades when dealing with numerous database objects and parallel processing of checks based on user settings.
Query and general performance have also seen significant enhancements. The new "skip scan" lookups on multicolumn B-tree indexes allow queries that don't use = conditions on some of the index columns to run faster, and improvements for queries with OR conditions in the WHERE clause can now use indexing for much quicker results. Additionally, improvements have been made to how PostgreSQL plans and executes table joins, including boosting the performance of hash joins and allowing merge joins to utilize incremental sorting.
PostgreSQL 18 now supports faster processing by using hardware features like ARM NEON and SVE CPU instructions for the popcount function, which is used by bit_count and other internal functions.
The developer experience has been further enhanced with the introduction of virtually generated columns that compute values at query time instead of storing them. This is now the default option for generated columns, while stored generated columns can be logically replicated. The RETURNING clause in INSERT, UPDATE, DELETE, and MERGE commands has also gained the ability to access both previous (OLD) and current (NEW) values.
Temporal constraints, which are constraints over ranges using the WITHOUT OVERLAPS clause, have been added for PRIMARY KEY and UNIQUE constraints, as well as FOREIGN KEY constraints using the PERIOD clause. Moreover, creating a foreign table schema definition has become easier with the introduction of the CREATE FOREIGN TABLE ... LIKE command.
Text processing has also received enhancements in PostgreSQL 18. The PG_UNICODE_FAST collation provides full Unicode semantics for case transformations while accelerating many comparisons. Additionally, support has been added for making LIKE comparisons over text that uses a nondeterministic collation, simplifying complex pattern matching. Full-text search now uses the default collation provider of a cluster instead of always relying on libc.
PostgreSQL 18 significantly improves the authentication and security features. OAuth authentication allows users to authenticate using OAuth 2.0 mechanisms supported through PostgreSQL extensions. Validation for FIPS mode has also been added, along with an ssl_tls13_ciphers parameter for configuring server-side TLS v1.3 cipher suites.
This release deprecates MD5 password authentication, which will be removed in a future version. Instead, developers are advised to use SCRAM authentication for PostgreSQL password-based authentication. Additionally, pgcrypto now supports SHA-2 encryption for password hashing.
Replication has been enhanced with the ability to report logical replication write conflicts in logs and in the pg_stat_subscription_stats view. The default setting for CREATE SUBSCRIPTION is now parallel streaming for applying transactions, which can enhance performance. The pg_createsubscriber utility also features an --all flag that enables creating logical replicas for all databases in an instance with a single command.
Maintenance and observability have seen improvements as well. PostgreSQL 18 proactively freezes more pages during regular vacuums, reducing overhead and helping in situations requiring aggressive vacuums. EXPLAIN has been enhanced to provide more details about query plan execution, including automatically showing buffer accesses when executing EXPLAIN ANALYZE. Additionally, EXPLAIN ANALYZE VERBOSE now includes CPU, WAL, and average read statistics.
Other notable changes include enabling page checksums by default for databases initialized with PostgreSQL 18 initdb, which can affect upgrades from non-checksum-enabled clusters. These upgrades would require creating a new PostgreSQL 18 cluster with the --no-data-checksums option when using pg_upgrade.
Lastly, PostgreSQL 18 introduces a new version (3.2) of the PostgreSQL wire protocol, which is the first new protocol version since PostgreSQL 7.4 in 2003. The libpq still uses version 3.0 by default while clients add support for the new protocol version.
In conclusion, PostgreSQL 18 represents a significant leap forward in terms of performance, features, and security. With its array of improvements and additions, this release is set to meet the growing demands of organizations worldwide, solidifying PostgreSQL's position as the preferred open-source relational database for all sizes and types of organizations.
Please refer to the comprehensive release notes for further details. To access the latest version for download, visit the designated download page.
