PostgreSQL 18 has been officially released, showcasing a host of enhancements aimed at boosting performance, introducing new features, and reinforcing security within the open-source database ecosystem. This upgrade, dubbed PostgreSQL 18, focuses on addressing limitations and elevating functionality to better serve a diverse range of organizational needs.
At the core of PostgreSQL 18 is a groundbreaking I/O subsystem that leverages asynchronous operations, significantly improving throughput and enabling faster data retrieval. This enhancement has shown potential performance gains of up to three times in specific use cases. Major-version upgrades have also been streamlined, retaining planner statistics to allow upgraded clusters to achieve optimal performance more quickly. The pg_upgrade utility has seen improvements that facilitate quicker transitions, especially for databases with a high number of objects.
Performance optimizations extend to query execution, with new "skip scan" capabilities on multicolumn B-tree indexes allowing for faster query responses, even when certain index columns are not used in equality conditions. Enhancements in query planning for table joins, including optimizing hash joins and enabling merge joins to leverage incremental sorting, further bolster performance. Additionally, the release introduces support for advanced hardware features, enhancing the processing capabilities of PostgreSQL.
Developers will appreciate the introduction of virtually generated columns that compute values dynamically at query time rather than storing them, streamlining data management. The RETURNING clause in various data manipulation commands now allows access to both previous and current values, improving developer efficiency. Temporal constraints have also been added, enabling more complex data integrity rules, while the CREATE FOREIGN TABLE... LIKE command simplifies foreign table schema definitions.
Text processing capabilities have been upgraded as well. The introduction of the PG_UNICODE_FAST collation allows for full Unicode semantics in case transformations, enhancing text comparison operations. This version also enables LIKE comparisons with nondeterministic collations, streamlining complex pattern matching processes.
In the realm of security, PostgreSQL 18 enhances authentication mechanisms by adding OAuth 2.0 support and validating for FIPS mode compliance. The deprecation of MD5 password authentication in favor of SCRAM authentication marks a significant shift towards more secure practices. Additionally, the inclusion of SHA-2 support in pgcrypto for password hashing enhances security protocols further.
Replication features have also been improved, with the ability to log logical replication write conflicts and a shift to parallel streaming for transaction applications, which could lead to notable performance gains. The pg_createsubscriber utility now allows for the creation of logical replicas across multiple databases in a single operation.
Maintenance and observability enhancements are also notable. PostgreSQL 18 optimizes regular vacuum processes, proactively freezing more pages to minimize overhead. The EXPLAIN command has been refined to offer deeper insights into query plans, including buffer access details, while EXPLAIN ANALYZE VERBOSE now provides comprehensive statistics on CPU, WAL, and average read operations.
PostgreSQL 18 also introduces a new wire protocol version (3.2), the first update since 2003, while maintaining backward compatibility. The libpq client still defaults to the older version, but this new protocol promises improved functionality for future client developments.
In summary, PostgreSQL 18 marks a substantial advancement in database technology, positioning itself to meet the evolving demands of organizations globally. With a wealth of new features and performance enhancements, PostgreSQL solidifies its reputation as the leading open-source relational database suitable for various applications.
For more detailed insights, please refer to the comprehensive release notes. To download the latest version, visit the designated download page
At the core of PostgreSQL 18 is a groundbreaking I/O subsystem that leverages asynchronous operations, significantly improving throughput and enabling faster data retrieval. This enhancement has shown potential performance gains of up to three times in specific use cases. Major-version upgrades have also been streamlined, retaining planner statistics to allow upgraded clusters to achieve optimal performance more quickly. The pg_upgrade utility has seen improvements that facilitate quicker transitions, especially for databases with a high number of objects.
Performance optimizations extend to query execution, with new "skip scan" capabilities on multicolumn B-tree indexes allowing for faster query responses, even when certain index columns are not used in equality conditions. Enhancements in query planning for table joins, including optimizing hash joins and enabling merge joins to leverage incremental sorting, further bolster performance. Additionally, the release introduces support for advanced hardware features, enhancing the processing capabilities of PostgreSQL.
Developers will appreciate the introduction of virtually generated columns that compute values dynamically at query time rather than storing them, streamlining data management. The RETURNING clause in various data manipulation commands now allows access to both previous and current values, improving developer efficiency. Temporal constraints have also been added, enabling more complex data integrity rules, while the CREATE FOREIGN TABLE... LIKE command simplifies foreign table schema definitions.
Text processing capabilities have been upgraded as well. The introduction of the PG_UNICODE_FAST collation allows for full Unicode semantics in case transformations, enhancing text comparison operations. This version also enables LIKE comparisons with nondeterministic collations, streamlining complex pattern matching processes.
In the realm of security, PostgreSQL 18 enhances authentication mechanisms by adding OAuth 2.0 support and validating for FIPS mode compliance. The deprecation of MD5 password authentication in favor of SCRAM authentication marks a significant shift towards more secure practices. Additionally, the inclusion of SHA-2 support in pgcrypto for password hashing enhances security protocols further.
Replication features have also been improved, with the ability to log logical replication write conflicts and a shift to parallel streaming for transaction applications, which could lead to notable performance gains. The pg_createsubscriber utility now allows for the creation of logical replicas across multiple databases in a single operation.
Maintenance and observability enhancements are also notable. PostgreSQL 18 optimizes regular vacuum processes, proactively freezing more pages to minimize overhead. The EXPLAIN command has been refined to offer deeper insights into query plans, including buffer access details, while EXPLAIN ANALYZE VERBOSE now provides comprehensive statistics on CPU, WAL, and average read operations.
PostgreSQL 18 also introduces a new wire protocol version (3.2), the first update since 2003, while maintaining backward compatibility. The libpq client still defaults to the older version, but this new protocol promises improved functionality for future client developments.
In summary, PostgreSQL 18 marks a substantial advancement in database technology, positioning itself to meet the evolving demands of organizations globally. With a wealth of new features and performance enhancements, PostgreSQL solidifies its reputation as the leading open-source relational database suitable for various applications.
For more detailed insights, please refer to the comprehensive release notes. To download the latest version, visit the designated download page
PostgreSQL 18 released
PostgreSQL 18 has been released, bringing significant performance improvements, new features, and enhanced security measures to the world's most advanced open-source database. Key enhancements include a novel I/O subsystem with asynchronous operations that boost overall throughput, faster major-version upgrades, and improved query and general performance through optimizations for indexing and table joins. The release also introduces virtually generated columns, temporal constraints, and improved text processing capabilities, as well as enhanced authentication and security features, including OAuth 2.0 support and validation for FIPS mode.
