The PostgreSQL development group released the second Beta version of PostgreSQL 18 in July; the GA version is expected later in 2025 (around the September/October timeframe). The PostgreSQL development group and its community is very dedicated and ensures several minor releases during the year and major releases every year.

Why You Should Perform a Major Version Upgrade

Every major PostgreSQL release comes with significant new features that improve the stability, performance, and usability of PostgreSQL as well as the user experience. Each new release brings critical security patches, performance improvements, and new SQL features that can simplify development and reduce technical debt.

Upgrading ensures continued community and vendor support, compatibility with evolving infrastructure and libraries, and access to enhancements in scalability, monitoring, and disaster recovery. Staying current also reduces the risk and cost of future migrations, as skipping multiple versions makes upgrades more complex and disruptive. In short, regular major upgrades keep PostgreSQL stable, fast, secure, and ready for future growth.

The global and vibrant PostgreSQL community is contributing to PostgreSQL success, diligently ensuring that all changes are carefully scrutinized and reviewed before they are added to the project source code. It is also very encouraging to see big technology names like Microsoft, Google, Apple, and others investing in Postgres by developing in-house expertise and giving back to the open source community.

Upgrading to PostgreSQL 18 delivers significant benefits in performance, security, and ease of management, making it a smart move for both technical and business reasons. The new asynchronous I/O engine and smarter indexing features speed up queries and maintenance tasks, while improved pg_upgrade with preserved planner statistics ensures faster, low‑risk version upgrades. Developers gain productivity with virtual generated columns, enhanced RETURNING support, and built‑in uuidv7() for better indexing, while enterprises benefit from OAuth 2.0 authentication, stronger encryption, and data checksums enabled by default for higher reliability. Combined with improved observability and flexible schema changes that minimize downtime, PostgreSQL 18 is a future‑ready release that enhances performance, security, and operational efficiency.

Performance & Query Optimization

Every major PostgreSQL release comes with features in different categories (i.e. performance, logical replication, monitoring, developer experience, security etc.). In this blog I will be going over the key performance features added to the PostgreSQL 18 release. Like I have done previously, this blog will be followed by at-least 2 blogs in which I will delve into more features in other categories with details and practical examples on usage.

Adding an Asynchronous I/O Subsystem

The ability to add an asynchronous I/O subsystem is a major performance feature added to the PostgreSQL 18 release. The Asynchronous I/O (AIO) feature is introduced to boost I/O throughput, especially for sequential scans, bitmap heap scans, and VACUUM operations. On Linux with io_uring, this can offer 2–3× performance improvements by overlapping disk access with processing.

The main motivations behind adding AIO (Asynchronous I/O) to PostgreSQL are:

• Reduce the time spent waiting for IO by issuing IO sufficiently early. Historically, PostgreSQL relied heavily on blocking I/O for reads and writes. That meant a backend process would issue one I/O call and then sit idle, waiting for the OS or disk to respond before doing anything else. The idea here is to avoid that idle time by starting I/O operations well before the data is actually needed—allowing PostgreSQL to overlap I/O with useful work (e.g., computation or other I/O requests).

• Allow the use of Direct I/0 (DIO). Direct I/O refers to bypassing the OS kernel’s page cache and performing I/O operations straight between the application and storage device. DIO can offload most of the work for IO to hardware and thus increase throughput / decrease CPU utilization, as well as reduce latency. This will also enable PostgreSQL to configure DIO with a GUC setting; the GUC “io_method” is explained later in the blog.

The AIO infrastructure allows the implementation of AIO using different methods. The feature has introduced a new GUC named  “io_method” which controls the choice of method that will be used. The choice of the AIO method is controlled by the new io_method GUC; it can be set in postgresql.conf at the server start. The io_method cannot be changed without the server restart, the server will return the following error if you try to change it with an alter system command.

parameter "io_method" cannot be changed without restarting the server

The parameter can be set to the following possible values:

sync:  This was the setting of the parameter at the initial commit when the infrastructure of the AIO was added. This is the traditional synchronous I/O with blocking I/O operations basically giving the same behavior at PG-17. Setting the value to sync doesn’t implement any AIO operations, it just ensures that new code added for this feature is bypassed.

worker:  This is the default setting in PostgreSQL 18.  This setting uses the background I/O worker processes, the backend processes queue I/O requests while the background worker processes handle read/write operations asynchronously. The number of background I/O worker processes is controlled by the io_worker GUC. The background worker processes will be seen as dedicated processes in the OS process list.

postgres=# SHOW io_method;
 io_method 
-----------
 worker
(1 row)

The number of background I/O worker processes is controlled by the io_worker GUC. The background worker processes will be seen as dedicated processes in the OS process list.

pgedge    352900  352899  0 16:53 ?        00:00:01 postgres: io worker 0
pgedge    352901  352899  0 16:53 ?        00:00:00 postgres: io worker 1
pgedge    352902  352899  0 16:53 ?        00:00:00 postgres: io worker 2

postgres=# SHOW io_workers;
 io_workers 
------------
 3
(1 row)

Io_uring:  io_uring is a Linux specific modern high performance interface for true asynchronous I/O. This method requires Postgres to be built with --with-liburing and runs on compatible kernels/filesystems. The io_uring method eliminates the worker processes and uses a shared ring buffer between Postgres and the kernel to enqueue/distribute I/O requests efficiently.

This AIO method offers low syscall overhead and can significantly improve performance especially on high latency storage systems. This method gives the fastest performance on linux.

The io_uring method requires the following kernel setting.

echo 0 | sudo tee /proc/sys/kernel/io_uring_disabled

I have carried out a test for AIO with all 3 supported io_methods (sync, worker, and io_uring), and shared the results of the test below. There is clear performance improvement when you go from sync to worker to io_uring as your io_method.

The example below shows the use of the AIO feature with the three supported io_methods.

-- Create test table with data

postgres=# CREATE TABLE aio_test AS
SELECT generate_series(1, 10000000) AS id,
       repeat('x', 100) AS filler;
postgres-# postgres-# 
SELECT 10000000

-- Create an index

CREATE INDEX ON aio_test(id);
CREATE INDEX

postgres=# ALTER SYSTEM SET track_io_timing = on;

-- Test with sync io_method

postgres=# SHOW io_method;
 io_method 
-----------
 sync
(1 row)

postgres=#  EXPLAIN (ANALYZE, TIMING) SELECT count(*) FROM aio_test;
                                                                    QUERY PLAN                                                             
        
-------------------------------------------------------------------------------------------------------------------------------------------
--------
 Finalize Aggregate  (cost=225499.55..225499.56 rows=1 width=8) (actual time=573.824..577.110 rows=1.00 loops=1)
   Buffers: shared hit=15927 read=156489
   ->  Gather  (cost=225499.33..225499.54 rows=2 width=8) (actual time=573.674..577.104 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=15927 read=156489
->  Partial Aggregate  (cost=224499.33..224499.34 rows=1 width=8) (actual time=568.825..568.825 rows=1.00 loops=3)
               Buffers: shared hit=15927 read=156489
               ->  Parallel Seq Scan on aio_test  (cost=0.00..214082.67 rows=4166667 width=0) (actual time=0.092..444.480 rows=3333333.33 l
oops=3)
                     Buffers: shared hit=15927 read=156489
 Planning:
   Buffers: shared hit=16 read=6
 Planning Time: 2.263 ms
 Execution Time: 577.863 ms

-- Test with worker io_method

postgres=# SHOW io_method;
 io_method 
-----------
 worker
(1 row)

postgres=#  EXPLAIN (ANALYZE, TIMING) SELECT count(*) FROM aio_test;
                                                                                   QUERY PLAN                                              
                                     
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------
 Finalize Aggregate  (cost=212777.59..212777.60 rows=1 width=8) (actual time=431.819..431.967 rows=1.00 loops=1)
   Buffers: shared hit=12 read=27331 written=1
   I/O Timings: shared read=120.329 write=0.017
   ->  Gather  (cost=212777.37..212777.58 rows=2 width=8) (actual time=431.814..431.963 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=12 read=27331 written=1
         I/O Timings: shared read=120.329 write=0.017
         ->  Partial Aggregate  (cost=211777.37..211777.38 rows=1 width=8) (actual time=426.209..426.209 rows=1.00 loops=3)
               Buffers: shared hit=12 read=27331 written=1
               I/O Timings: shared read=120.329 write=0.017
               ->  Parallel Index Only Scan using aio_test_id_idx on aio_test  (cost=0.43..201360.64 rows=4166691 width=0) (actual time=0.3
96..333.324 rows=3333333.33 loops=3)
                     Heap Fetches: 0
                    Index Searches: 1
                     Buffers: shared hit=12 read=27331 written=1
                     I/O Timings: shared read=120.329 write=0.017
 Planning:
   Buffers: shared hit=36 read=20 dirtied=1
   I/O Timings: shared read=1.706
 Planning Time: 2.186 ms
 Execution Time: 432.178 ms

-- Test with io_uring io_method

postgres=# SHOW io_method;
 io_method 
-----------
 io_uring
(1 row)

postgres=#  EXPLAIN (ANALYZE, TIMING) SELECT count(*) FROM aio_test;
                                                                                   QUERY PLAN                                              
                                     
-------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------
 Finalize Aggregate  (cost=212777.59..212777.60 rows=1 width=8) (actual time=389.182..389.555 rows=1.00 loops=1)
   Buffers: shared hit=13 read=27331
   I/O Timings: shared read=86.715
   ->  Gather  (cost=212777.37..212777.58 rows=2 width=8) (actual time=388.204..389.549 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=13 read=27331
         I/O Timings: shared read=86.715
         ->  Partial Aggregate  (cost=211777.37..211777.38 rows=1 width=8) (actual time=377.915..377.916 rows=1.00 loops=3)
               Buffers: shared hit=13 read=27331
               I/O Timings: shared read=86.715
               ->  Parallel Index Only Scan using aio_test_id_idx on aio_test  (cost=0.43..201360.64 rows=4166691 width=0) (actual time=0.0
83..276.593 rows=3333333.33 loops=3)
                     Heap Fetches: 0
                     Index Searches: 1
                     Buffers: shared hit=13 read=27331
                     I/O Timings: shared read=86.715
Planning:
   Buffers: shared hit=36 read=20
   I/O Timings: shared read=0.352
 Planning Time: 0.840 ms
 Execution Time: 389.683 ms

The "I/O Timings: shared in the explain plan shows that AIO was triggered as part of this query plan.