PostgreSQL 18 released with major I/O and performance overhauls

The PostgreSQL Global Development Group today released PostgreSQL 18.0, the annual major feature release for the widely used open-source SQL database. This release focuses on I/O and query performance while adding several developer-facing features and security improvements.

Let’s take a dive.

Big wins for I/O and throughput

PostgreSQL 18 introduces a new asynchronous I/O (AIO) subsystem that lets the server issue multiple I/O requests concurrently. Benchmarks in the announcement show up to 3× faster reads from storage in certain workloads. The new io_method setting lets administrators choose between worker AIO, io_uring on Linux, or maintain the legacy synchronous behavior.

AIO accelerates sequential scans, bitmap heap scans, and vacuum operations. Other low-level gains include initial NUMA awareness and AVX-512 usage for CRC32C computations, plus SIMD-optimized JSON escaping.

Faster upgrades and steadier post-upgrade performance

Major upgrades often caused performance dips while statistics rebuilt. PostgreSQL 18 lets clusters preserve planner statistics across major-version upgrades, helping upgraded systems reach expected performance much faster.

pg_upgrade itself sees speedups for databases with many objects. It can now run checks in parallel using --jobs and offers a --swap option to swap upgrade directories instead of copying files.

Smarter query planning and index use

• The optimizer in PostgreSQL 18 can make more queries use indexes.
• New “skip scan” lookups speed queries that omit equality conditions on index prefixes.
• Queries with OR conditions can now often benefit from index access.
• Join performance sees multiple improvements: hash joins are faster, merge joins can use incremental sorts, and several join planning tweaks reduce execution time.
• Indexing also benefits from parallel GIN index builds and improved cooperation between B-tree and BRIN indexes.

Developer features and data model enhancements

Developers get virtual generated columns as the default for generated values, computing values at query time rather than storing them. Stored generated columns can still be used and now support logical replication.

PostgreSQL 18 adds uuidv7() for timestamp-ordered UUIDs, improving index locality and caching strategies; uuidv4() is provided as an alias for gen_random_uuid(). The RETURNING clause now allows access to both OLD and NEW values for INSERT, UPDATE, DELETE, and MERGE.

New temporal constraints enable range-aware PRIMARY KEY, UNIQUE, and FOREIGN KEY semantics using WITHOUT OVERLAPS and PERIOD clauses.

Text processing, collations, and full text search

• Text handling is improved with a new PG_UNICODE_FAST collation. This option gives full Unicode case-transformation semantics while improving comparison speed.
• A new casefold function aids case-insensitive comparisons.
• LIKE comparisons over nondeterministic collations are now supported.
• Full text search now uses the cluster’s default collation provider instead of always using libc; administrators may need to reindex certain full text and pg_trgm indexes after upgrade.

Security, authentication, and replication

PostgreSQL 18 adds OAuth 2.0 authentication support through extensions, making SSO integration easier.
md5 password authentication is deprecated; SCRAM should be used for password-based auth going forward. pgcrypto gains SHA-2 hashing support, and the release adds FIPS mode validation plus a TLS 1.3 cipher configuration parameter.
Replication gains include reporting logical replication write conflicts in logs and pg_stat_subscription_stats. Logical replication now defaults to parallel streaming when applying transactions, and tools like pg_createsubscriber gain an --all flag. Idle replication slots can be automatically dropped to avoid excessive WAL retention.

Observability and maintenance

• EXPLAIN and EXPLAIN ANALYZE gain more detailed statistics.
• Plans now report buffer accesses, index lookup counts during scans, and average read metrics, among others.
• pg_stat_all_tables tracks vacuum time and related operations better.
• Vacuum strategy improves by proactively freezing more pages during routine vacuums, reducing future vacuum overhead.

Other notable changes

initdb will enable page checksums by default for new clusters. This may affect upgrade paths from older, non-checksummed clusters. A new PostgreSQL wire protocol version (3.2) is introduced, the first protocol update since 2003; libpq continues to use 3.0 by default while clients add support for the new protocol.

For administrators and developers evaluating PostgreSQL 18, the release emphasizes tangible gains for I/O-bound and index-heavy workloads, faster post-upgrade stabilization, and improved observability. The release notes list many additional improvements and migration considerations for production environments. Translations of the official announcement are available in the PostgreSQL 18 press kit.

Source: postgresql