PostgreSQL 18 to boost OLTP performance, but misses AI readiness

The upcoming version of PostgreSQL 18, the latest release of the popular open-source database due later this month, will introduce performance-enhancing features, promising significant gains for online transactional processing (OLTP) workloads.

However, according to industry experts, PostgreSQL 18 falls short in AI readiness despite rapid proliferation.

PostgreSQL has become a go-to database for developers, especially for building modern applications in a cost-effective manner, as it offers a rich feature set, extensibility, and a permissive license that supports enterprise adoption.

However, PostgreSQL has one Achilles’ heel — it is not effective for online analytical processing (OLAP), which impedes AI readiness as enterprises increasingly adopt hybrid transactional/analytical processing (HTAP) architectures to support AI workloads, specifically agentic AI.

HTAP, which merges OLTP and OLAP capabilities in a single system, is essential for agentic systems as it provides the agent more context for better decision-making by providing real-time analytics on live transactional and operational data.

“…there are no features in PostgreSQL 18 which specifically benefit analytical and AI workloads,” Alastair Turner, technology evangelist at Percona, a provider of PostgreSQL and other databases.

The lack of features for OLAP performance is so evident that PlanetScale co-founder and CEO Sam Lambert suggests that “it’s best to separate concerns and choose a dedicated OLTP database (PostgreSQL) and add on analytics databases like ClickHouse, BigQuery, etc, as needed.”

“Having separated compute and storage resources dedicated to each purpose reduces the risk of contention (competition of workloads for shared resources, such as CPU) being a problem and negatively impacting application performance,” Lambert explained.

PlanetScale, too, offers a managed version of PostgreSQL.

However, in contrast to Turner and Lambert, PostgreSQL-based database provider firm EDB’s SVP of database servers and tools, Tom Kincaid, is a bit more optimistic about PostgreSQL 18’s OLAP capabilities, as he said that every release expands its range of analytics use cases.

“This release (PostgreSQL 18), for example, supports asynchronous data reads and many optimizer enhancements. These improvements accelerate performance for large data sets and queries that involve many tables — exactly the kinds of workloads frequently involved in OLAP,” Kincaid said.

Earlier releases, according to Kincaid, have added features such as Foreign Data Wrappers (FDWs) and Table Access Method (TAM) to enable more analytical workloads.

While FDWs act as a bridge that enables PostgreSQL to send queries to a remote data source and receive the result, TAM is a feature that allows users to change data access settings and configure it for OLAP: read-heavy and often involving complex aggregations over large datasets.

Performance gains for OLTP

PostgreSQL 18, though lacking in AI readiness, introduces a few features that experts expect will result in significant performance gains for OLTP workloads.

One such feature, according to Lambert, is Asynchronous I/O or AIO in short.

“The use of Asynchronous I/O via ‘io_uring’ Linux interface should really help improve IO performance, leading to lower latencies. This is a big win for OLTP, where time is everything… We will likely see especially good gains here in environments with network-attached storage,” Lambert said.

Asynchronous IO, according to Percona’s Turner, allows database workers to issue multiple IO instructions without waiting for earlier instructions to complete, and this delivers performance gains by not blocking processing while waiting for subsequent IO operations to start.

However, AIO for now supports disk-heavy reads and not writes, experts pointed out, adding that work is underway on improving bulk writes and checkpoint writes. Examples of write-heavy OLTP workloads are vehicle telemetry, social media, and online gaming platforms.

Another feature that will help PostgreSQL 18’s performance in indexing and caching is the upgrade of universally unique identifier (UUID) from version 4 to 7.

UUID is a 128-bit value used in PostgreSQL to provide a globally unique identifier for records, and UUID 7 differs from UUID 4 in the way it stores the 128-bit value, according to Tuner.

Unlike UUID 4, which is fully random, UUID 7 starts with a timestamp, and this means recent UUID 7 values are close together in sort order, so they’re stored on fewer, consecutive index pages, Turner said, adding that this improves cache efficiency, since active data is more likely to stay in memory.

“In contrast, UUID 4 scatters recent values across many pages, making caching less effective.”

Other notable features

PostgreSQL also comes with other notable features, such as improved Explain and OAuth.

The improved Explain command, according to EDB’s Kincaid, should assist DBAs and developers in identifying situations where a query needs to be tuned and how it should be tuned, resulting in faster troubleshooting, optimized systems, and better performance.

“The more information you have regarding how the query planner executed or will execute your query, the more information you have to make adjustments to the statistics, indexes, configuration settings, and the query itself,” Kincaid said.

The OAuth feature, on the other hand, will significantly improve security, according to experts.

“It should enable Postgres to integrate much more easily with corporate identity management systems and will help eliminate a lot of duplication of effort and potentially error-prone processes while also strengthening security by aligning Postgres with the same identity and access controls used across the enterprise,” Kincaid said.

A complete list of changes made in PostgreSQL can be found in its release notes.