Welcome PostgreSQL 18: A New Era of Performance on Aiven

Aiven is proud to launch the newest version of PostgreSQL, version 18, alongside with the open source community as the first managed PostgreSQL provider to support the latest version. This year we had three Aiveners join in on contributing to this major release, a trend which we hope to only see increase. Congrats to Patrick Stählin, Ronan Dunklau, and Thomas Krennwallner for your contributions to the codebase.

With Aiven for PostgreSQL, you can now enjoy the following newest innovations from the PostgreSQL community as a fully managed service with Aiven.

Turbocharge Your Throughput with an Asynchronous I/O Subsystem

PostgreSQL 18 introduces a game-changing asynchronous I/O (AIO) subsystem designed to dramatically boost I/O throughput and minimize latency. This is particularly beneficial for operations like sequential scans, bitmap heap scans, and vacuums. By allowing the database to queue multiple read requests without waiting for each to complete, AIO can lead to significant performance improvements for read-heavy workloads. Initial testing has seen up to 3x performance improvements in read-heavy situations.

Experience a Boost in JOINs and GROUP BY Operations

Every new PostgreSQL release brings enhancements to the query planner, and version 18 is no exception. Users can expect improved performance and lower memory consumption for common yet resource-intensive JOIN and GROUP BY operations. While not a single marquee feature, these under-the-hood optimizations contribute to overall faster query execution and a more efficient database.

Accelerate Index Maintenance with Parallel GIN Indexes

Creating indexes on large tables can be a time-consuming process. PostgreSQL 18 addresses this by introducing the ability to create GIN (Generalized Inverted Index) indexes in parallel. This can dramatically reduce the time required for index creation, a significant win for maintaining large-scale databases.

Greater Flexibility in Schema Evolution with unenforced Constraints

A new level of flexibility is coming to schema management. PostgreSQL 18 will allow the addition of CHECK and foreign key constraints without them being immediately enforced on existing data. This is particularly useful during data migrations and complex schema changes, allowing for a more phased and controlled approach to data validation. This builds on the NOT VALID functionality for NOT NULL constraints, which allows for adding constraints without an immediate, potentially locking, full-table scan.

Smarter Indexing with Skip Scans for B-tree Indexes

PostgreSQL 18 gets smarter about how it uses multi-column indexes. The introduction of skip scans for B-tree indexes allows the query planner to efficiently use an index even if the leading column of the index is not present in the WHERE clause. This can lead to significant performance gains for certain queries by allowing the index to skip over irrelevant data, reducing the need for full table scans.

Enhanced Concurrency with Improved Locking Performance

High-contention databases will benefit from improved locking mechanisms in PostgreSQL 18. Enhancements have been made to reduce lock contention, especially in scenarios with many concurrent read operations on tables with numerous partitions or indexes. These improvements lead to better overall performance and scalability for demanding workloads.

Making Virtual Generated Columns the default

PostgreSQL 18 simplifies schema design by making virtual generated columns the default. Previously, generated columns had to be STORED, taking up disk space. Virtual generated columns compute their values at runtime when you run a query.

We should note that this involves a trade-off: Virtual columns trade storage space for computation time. If you need to create indexes on calculated values, or if calculations are computationally expensive, you should use stored generated columns.

Want to learn more?

It’s just day one and we’re still waiting on a few extensions to add their support before we make it the default version. While we wait, we’re going to be exploring and learning more about how to make the most out of this update. Stay tuned to our events page, and our social media for more blog posts and workshop announcements.

Get Started Today!

If you’re coming from an older version of PostgreSQL, there have been a lot of innovations over the last 5 years and PostgreSQL 18 looks like an excellent addition.

Try out the newest PostgreSQL version at aiven.io and get a free 30-day trial on us. Make sure the version is set to 18 to get the latest features.