ThePostgreSQL Global Development Grouptoday announced the release ofPostgreSQL 18,the latest version of the world's most advanced open source database. Translations of this press release are available in thePostgreSQL 18 press kit.
PostgreSQL 18 improves performance for workloads of all sizes through a new I/Osubsystem that has demonstrated up to 3× performance improvements when readingfrom storage, and also increases the number of queries that can use indexes.This release makes major-version upgrades less disruptive, accelerating upgradetimes and reducing the time required to reach expected performance after anupgrade completes. Developers also benefit from PostgreSQL 18 features,including virtual generated columns that compute values at query time, and thedatabase-friendlyuuidv7() function that provides better indexing and readperformance for UUIDs. PostgreSQL 18 makes it easier to integrate with single-signon (SSO) systems with support for OAuth 2.0 authentication.
"The efforts of the global open source community shape every PostgreSQL releaseand help deliver features that meet users where their data resides," saidJonathan Katz, a member of the PostgreSQL core team. "PostgreSQL 18 builds on theproject's long, rich history of delivering a reliable and robust data managementexperience, while continuing to expand the workloads it can support."
PostgreSQL, an innovative data management system known for its reliability,robustness, and extensibility, benefits from nearly 30 years of open sourcedevelopment from a global developer community and has become the preferred opensource relational database for organizations of all sizes.
PostgreSQL previously relied on operating system readahead mechanisms toaccelerate data retrieval. However, because operating systems lack insight intodatabase-specific access patterns, they cannot always anticipate what data willbe required, leading to suboptimal performance in many workloads.
PostgreSQL 18 introduces a new asynchronous I/O (AIO) subsystem designed toaddress this limitation. AIO lets PostgreSQL issue multiple I/O requestsconcurrently instead of waiting for each to finish in sequence. This expandsexisting readahead and improves overall throughput. AIO operations supported inPostgreSQL 18 include sequential scans, bitmap heap scans, and vacuum.Benchmarking has demonstrated performance gains of up to 3x in certain scenarios.
The newio_methodsetting lets you toggle between the AIO methods, includingworker andio_uring,or you can choose to maintain the current PostgreSQL behavior with thesyncsetting. There are now more parameters to consider tuning with AIO, which youcanlearn more about in the documentation.
A key PostgreSQL feature is the generation and storage ofstatistics that helpPostgreSQL select the most efficient query plan. Before PostgreSQL 18, thesestatistics didn't carry over on amajor version upgrade,which could cause significant query performance degradations on busy systemsuntil theANALYZEfinished running. PostgreSQL 18 introduces the ability to keep plannerstatistics through a major version upgrade, which helps an upgraded clusterreach expected performance more quickly after the upgrade.
Additionally,pg_upgrade,a utility that performs major version upgrades, includes several enhancements inPostgreSQL 18, such as faster upgrades when a database contains many objectslike tables and sequences. This release also letspg_upgrade process itschecks in parallel based on the settings of the--jobs flag, and adds the--swap flag that swaps upgrade directories instead of copying, cloning, orlinking files.
PostgreSQL 18 further accelerates query performance with features thatautomatically make your workloads faster. This release introduces "skip scan"lookups onmulticolumn B-tree indexesthat improve execution time for queries that omit an= condition on one ormore prefix index columns. It can also optimize queries that useOR conditionsin aWHERE to use an index, leading to significantly faster execution. Thereare also numerous improvements for how PostgreSQL plans and executes table joins,from boosting the performance of hash joins to allowing merge joins to useincremental sorts. PostgreSQL 18 also supports parallel builds forGIN indexes, joining B-tree andBRIN indexes in supportingthis capability.
This release also builds on PostgreSQL support for hardware acceleration,including support for ARM NEON and SVE CPU intrinsics for thepopcountfunction, which is used by thebit_countand other internal capabilities.
PostgreSQL 18 introducesvirtual generated columnsthat compute values at query time instead of storing them. This is now thedefault option for generated columns. Additionally, stored generated columns cannow be logically replicated.
This release adds the capability to access both the previous (OLD) and current(NEW) values in theRETURNING clauseforINSERT,UPDATE,DELETE andMERGE commands. PostgreSQL 18 also addsUUIDv7 generation through theuuidv7()function, letting you generate random UUIDs that are timestamp-ordered tosupport better caching strategies. PostgreSQL 18 includesuuidv4()as an alias forgen_random_uuid().
PostgreSQL 18 addstemporal constraints-- constraints over ranges -- for bothPRIMARY KEY andUNIQUE constraintsusing theWITHOUT OVERLAPS clause, and onFOREIGN KEY constraints using thePERIOD clause.
Finally, PostgreSQL 18 makes it easier to create the schema definition of aforeign table using the definition of a local table with theCREATE FOREIGN TABLE ... LIKEcommand.
PostgreSQL 18 makes text processing easier and faster with several newenhancements. This release adds thePG_UNICODE_FASTcollation, which provides full Unicode semantics for case transformations whilehelping to accelerate many comparisons. This includes theupper andlowerstring comparison functions and the newcasefoldfunction for case-insensitive comparisons. Additionally, PostgreSQL 18 nowsupports makingLIKE comparisons over text that uses anondeterministic collation,simplifying how you can perform more complex pattern matching. This release alsochangesfull text searchto use the default collation provider of a cluster instead of always using libc,which may require you to reindex allfull text searchandpg_trgmindexes after runningpg_upgrade.
PostgreSQL 18 introducesoauth authentication,which lets users authenticate using OAuth 2.0 mechanisms supported throughPostgreSQL extensions. Additionally, PostgreSQL 18 includes validation forFIPS mode,and adds thessl_tls13_ciphersparameter for configuring server-side TLS v1.3 cipher suites.
This release deprecatesmd5 password authentication, which will be removed ina future release. If you require PostgreSQL password-based authentication, useSCRAM authentication.PostgreSQL 18 also supports SCRAM passthrough authentication with bothpostgres_fdw anddblink for authenticating toremote PostgreSQL instances. Additionally,pgcryptonow supportsSHA-2 encryption for password hashing.
PostgreSQL 18 supports reporting logical replication write conflicts in logs andin thepg_stat_subscription_stats view. Additionally,CREATE SUBSCRIPTIONnow defaults to using parallel streaming for applying transactions, which canhelp improve performance. Thepg_createsubscriberutility now has an--all flag so you can create logical replicas for alldatabases in an instance with a single command. PostgreSQL 18 also lets youautomaticallydrop idle replication slots to help prevent storing too manywrite-ahead log files on a publisher.
PostgreSQL 18 improves itsvacuumstrategy by proactively freezing more pages during regular vacuums, reducingoverhead and helping in situations that require aggressive vacuums.
PostgreSQL 18 adds more details toEXPLAIN,which provides information about query plan execution, and as of this releasenow automatically shows how many buffers (the fundamental unit of data storage)are accessed when executingEXPLAIN ANALYZE. Additionally,EXPLAIN ANALYZEnow shows how many index lookups occur during an index scan, andEXPLAIN ANALYZE VERBOSEincludes CPU, WAL, and average read statistics. PostgreSQL 18 includes more infoinpg_stat_all_tableson time spent on vacuum and related operations, as well as per-connectionstatistics on I/O and WAL utilization.
Databases initialized with PostgreSQL 18initdbnow have page checksums enabled by default. This can affect upgrades fromnon-checksum enabled clusters, which would require you to create a newPostgreSQL 18 cluster with the--no-data-checksums option when usingpg_upgrade.
PostgreSQL 18 also introduces a new version (3.2) of the PostgreSQL wireprotocol, the first new protocol version since PostgreSQL 7.4 (2003).libpq still usesversion 3.0 by default while clients (e.g., drivers, poolers, proxies) addsupport for the new protocol version.
Many other new features and improvements have been added to PostgreSQL 18 thatmay also be helpful for your use cases. Please see therelease notes for acomplete list of new and changed features.
PostgreSQL is the world's most advanced opensource database, with a global community of thousands of users, contributors,companies and organizations. Since its beginnings at the University ofCalifornia, Berkeley over 40 years ago, PostgreSQL has continued with anunmatched pace of development. PostgreSQL's mature feature set not only matchestop proprietary database systems, but exceeds them in advanced databasefeatures, extensibility, security, and stability.