PostgreSQL 18 Features: 25 Amazing Performance Improvements Every DBA Should Know

PostgreSQL 18 Features introduce the biggest database performance improvements in recent years. Released on September 25, 2025, PostgreSQL 18 adds asynchronous I/O, parallel GIN index creation, advanced query optimization, UUIDv7 support, virtual generated columns, enhanced monitoring, improved logical replication, and stronger security. This guide explores the most important PostgreSQL 18 Features with practical SQL examples for DBAs,, developers, and database architects.

PostgreSQL 18 Features for Database Performance

Table of Contents

Asynchronous I/O Subsystem: The Most Significant Addition

PostgreSQL 18 introduces a powerful new asynchronous I/O (AIO) subsystem, allowing parallel data reads and reducing wait times. This marks a major shift from the previous synchronous I/O, significantly improving performance

Key Benefits of Asynchronous I/O:

- Increased I/O Throughput: Multiple read requests can be queued and processed simultaneously
- Reduced Latency: I/O operations no longer block query execution
- Better Resource Utilization: More efficient use of storage subsystems
- Enhanced Performance: Particularly beneficial for sequential scans, bitmap heap scans, and vacuum operations

Configuration Options:

- io_method: Controls the I/O method (enables AIO)
- io_combine_limit and io_max_combine_limit: Control I/O operation combining
- pg_aios: New system view to monitor asynchronous I/O file handles

Asynchronous I/O Configuration in PostgreSQL 18

Check the current I/O method

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

Enable asynchronous I/O

testdb=# SHOW io_combine_limit;
io_combine_limit
------------------
128kB
(1 row)
testdb=# SHOW io_max_combine_limit;
io_max_combine_limit
----------------------
128kB
(1 row)

Monitor asynchronous I/O handles

testdb=# SELECT * FROM pg_aios LIMIT 5;
pid | io_id | io_generation | state | operation | off | length | target | handle_data_len | raw_result | result | target_desc | f_sync | f_localmem | f_buffer
ed
-----+-------+---------------+-------+-----------+-----+--------+--------+-----------------+------------+--------+-------------+--------+------------+---------
---
(0 rows)

Parallel GIN Index Building:

PostgreSQL 18 adds parallel builds for GIN indexes, speeding up index creation for JSON and full-text search data.

- JSON and JSONB data
- Full-text search columns
- Array data types
- Other complex data structure

Parallel GIN Index Building

testdb=# CREATE TABLE docs(id serial, content jsonb);
CREATE TABLE
testdb=# INSERT INTO docs (content)
testdb-# VALUES ('{"key": "value", "count": 10}'::jsonb);
INSERT 0 1
testdb=# SELECT jsonb_build_object('text', md5(random()::text)) FROM generate_series(1, 50000);
              jsonb_build_object
----------------------------------------------
{"text": "75ea8a8788ab2f5817cb68499ae581f0"}
{"text": "46932c038a966472df32fa4937e8aa65"}
{"text": "d479ddb89fd7d1d9355bc723632350f1"}
{"text": "722227f05df276d241849e376f473b77"}

Parallel GIN index creation

Set the Session Parallelism

testdb=# SET max_parallel_maintenance_workers = 4;
SET

Create Parallel GIN index

testdb=# CREATE INDEX gin_docs_idx ON docs USING gin (content jsonb_path_ops);
CREATE INDEX

– Verify in pg_stat_progress_create_index

testdb=# SELECT * FROM pg_stat_progress_create_index WHERE command = 'CREATE INDEX';
pid | datid | datname | relid | index_relid | command | phase | lockers_total | lockers_done | current_locker_pid | blocks_total | blocks_done | tuples_total
| tuples_done | partitions_total | partitions_done
-----+-------+---------+-------+-------------+---------+-------+---------------+--------------+--------------------+--------------+-------------+--------------
+-------------+------------------+-----------------
(0 rows)

PostgreSQL 18 Features for Query Optimization

- Self-Join Elimination: PostgreSQL 18 can automatically remove unnecessary table self-joins, simplifying query execution plans and improving performance.
- Enhanced OR-Clause Processing: PostgreSQL 18’s optimizer now turns OR clauses into arrays, making index use faster and queries more efficient.
- Right Semi Join Support: PostgreSQL 18 adds Hash Right Semi Join, letting the planner choose which table to hash based on size, improving semi-join performance.
- Skip Scans for B-tree Indexes: Multi-column B-tree indexes can now be used by queries that only reference the second or later indexed columns through skip scan functionality.

Self-Join Elimination Example

testdb=# INSERT INTO employees VALUES (1, NULL, 'CEO'), (2, 1, 'Manager'), (3, 2, 'Developer'); 
INSERT 0 3 
testdb=# EXPLAIN SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;                                 
QUERY PLAN 
----------------------------------------------------------------------------- 
Merge Join  (cost=166.75..280.75 rows=7200 width=64)   
Merge Cond: (e1.manager_id = e2.id)  
 ->  Sort  (cost=83.37..86.37 rows=1200 width=36)         Sort Key: e1.manager_id         
->  Seq Scan on employees e1  (cost=0.00..22.00 rows=1200 width=36)   
->  Sort  (cost=83.37..86.37 rows=1200 width=36)         Sort Key: e2.id         
->  Seq Scan on employees e2  (cost=0.00..22.00 rows=1200 width=36) 
(8 rows)

Skip Scan on Multi-column Index

testdb=# CREATE TABLE orders(order_id int, customer_id int, order_date date); 
CREATE TABLE 
testdb=# CREATE INDEX orders_idx ON orders(customer_id, order_date); 
CREATE INDEX

Query using skip scan (only order_date)

testdb=# EXPLAIN SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days';
                                QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on orders  (cost=19.63..41.53 rows=680 width=12)
  Recheck Cond: (order_date > (CURRENT_DATE - '30 days'::interval))
  ->  Bitmap Index Scan on orders_idx  (cost=0.00..19.46 rows=680 width=0)
        Index Cond: (order_date > (CURRENT_DATE - '30 days'::interval))
(4 rows)

Advanced Data Management Features

Virtual Generated Columns:

PostgreSQL 18 introduces virtual generated columns as the default behavior, representing a significant shift from the previous stored-only approach:

Virtual vs. Stored Generated Columns:

- Virtual: Values are computed when the column is read (default in PostgreSQL 18)
- Stored: Values are computed and stored when the row is written (available via STORED option)

Benefits of Virtual Generated Columns:

- Reduced storage requirements
- Always up-to-date values without maintenance overhead
- Better performance for columns that are read infrequently

Virtual Generated Columns

testdb=# CREATE TABLE sales (
testdb(#   id serial,
testdb(#   price numeric,
testdb(#   quantity int,
testdb(#   total numeric GENERATED ALWAYS AS (price * quantity) VIRTUAL
testdb(# );
CREATE TABLE
testdb=# INSERT INTO sales (price, quantity) VALUES (100, 5), (200, 2);
INSERT 0 2
testdb=# SELECT * FROM sales;
id | price | quantity | total
----+-------+----------+-------
  1 |   100 |        5 |   500
  2 |   200 |        2 |   400
(2 rows)

Enhanced UUID Support:

UUIDv7 Generation: PostgreSQL 18introduces uuidv7(), a new time-sortable UUID version that provides globally unique, non-predictable IDs ideal for ordered data storage and retrieval:

- Time-series data
- Distributed systems requiring ordered identifiers
- Primary keys where chronological ordering is beneficial

Time-sortable UUID:

Time-sortable UUIDs: It include a time-based component, allowing efficient sorting and improving database performance compared to random UUIDs like UUIDv4

uuidv7() example

testdb=# SELECT uuidv7();
                uuidv7
--------------------------------------
0199ca8c-692d-769d-812c-5f9054168fa1
(1 row)

Time-sortable UUID example
```
testdb=# SELECT uuidv7() AS time_sortable_uuid;
          time_sortable_uuid
--------------------------------------
0199ca8d-cbd0-7c4a-9afb-97a5266c2505
(1 row)
```
Explicit UUIDv4 Generation: The UUIDv4 is a “purely random” 128-bit identifier, mostly generated from high-quality randomness, with a few bits set by the RFC 9562 standard to define version and variant.

testdb=# SELECT uuidv4() AS random_uuid;
            random_uuid
--------------------------------------
9945163f-616f-467e-9ece-fb110b2cb6e0
(1 row)

Improved NOT NULL Constraint Management:

Improved NOT NULL constraint management is active in recent PostgreSQL versions (particularly since PostgreSQL 11, with further enhancements in 17 and 18), focusing on two main areas:

1. Zero-downtime schema changes for large tables.
2. Query performance optimization by leveraging constraint knowledge.

NOT NULL Constraint Improvements

testdb=# CREATE TABLE customers (
testdb(#   id serial PRIMARY KEY,
testdb(#   name text,
testdb(#   email text
testdb(# );
CREATE TABLE

Named NOT NULL Constraints: You can now assign names to NOT NULL constraints, improving error messages and constraint management.

NOT VALID Support: NOT NULL constraints can be marked as NOT VALID, allowing for non-blocking constraint addition on large tables.
Inheritance Control: NOT NULL constraints can now be made inheritable or non-inheritable on partitioned tables.

Add NOT NULL constraint with a name and NOT VALID option

testdb=# ALTER TABLE customers ADD CONSTRAINT nn_email CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE

Validate constraint later

testdb=# ALTER TABLE customers VALIDATE CONSTRAINT nn_email;
ALTER TABLE

Enhanced Developer Experience in PostgreSQL 18

RETURNING Clause Enhancements

The RETURNING clause now supports OLD/NEW syntax, allowing developers to access both old and new values in DML operations:

This feature is particularly useful for:

- Aduit trails
- Change tracking
- Data migration scenarios

RETURNING Clause with OLD/NEW

testdb=# CREATE TABLE employees_audit(id serial, name text, salary numeric);
CREATE TABLE
testdb=# INSERT INTO employees_audit(name, salary) VALUES ('Alice', 50000);
INSERT 0 1
testdb=#
testdb=# UPDATE employees_audit
testdb-# SET salary = salary * 1.1
testdb-# RETURNING old.salary AS old_salary, new.salary AS new_salary;
old_salary | new_salary
------------+------------
      50000 |    55000.0
(1 row)

Advanced JSON Functionality

- Improved NULL Handling: JSONB null values can now be cast to scalar types as NULL, improving flexibility in JSON data processing.
- Enhanced Array Processing: New optional parameters in json{b}_strip_nulls() allow removal of null array elements.

JSON Enhancements

testdb=# CREATE TABLE json_test(data jsonb);
CREATE TABLE
testdb=# INSERT INTO json_test VALUES ('{"a":1,"b":null,"c":[1,null,3]}');
INSERT 0

Remove null array elements

testdb=# SELECT jsonb_strip_nulls(data, true) FROM json_test;
  jsonb_strip_nulls
-----------------------
{"a": 1, "c": [1, 3]}
(1 row)

NULL casting example
```
testdb=# SELECT (data->>'b')::int IS NULL AS null_cast_check FROM json_test;
null_cast_check
-----------------
t
(1 row)
```
New Array Functions:
- array_sort(): Sorts an array’s first dimension
- array_reverse(): Reverses an array’s first dimension
- reverse(): Reverses bytea bytes

New Array Functions

testdb=# SELECT array_sort(ARRAY[5,2,9,1]) AS sorted,
      array_reverse(ARRAY[1,2,3,4]) AS reversed;
  sorted   | reversed
-----------+-----------
{1,2,5,9} | {4,3,2,1}
(1 row)

testdb=# SELECT reverse('PostgreSQL18'::bytea);
          reverse
----------------------------
\x38314c515365726774736f50
(1 row)

Case Folding Support: The new casefold() function enables advanced, language-aware case-insensitive text matching beyond simple lower/upper conversions.

Case Folding

testdb=# SELECT lower('Straße') AS folded_german,
testdb-#        lower('İstanbul') AS folded_turkish;
folded_german | folded_turkish
---------------+----------------
straße        | istanbul
(1 row)

Time: 17.053 ms

PostgreSQL 18 Security Features

OAuth (open-authorization) Authentication Support:

PostgreSQL 18 introduces built-in Open-Authorization authentication support, modernizing authentication capabilities:

- OAuth authentication method in pg_hba.conf
- libpq OAuth connection options
- Integration with modern identity providers

Enhanced SSL/TLS Configuration

- TLS 1.3 Cipher Suites: The New ssl_tls13_ciphers parameter allows specification of multiple TLS 1.3 cipher suites.
- Extended Curve Support: The ssl_groups parameter (renamed from ssl_ecdh_curve) now supports multiple elliptic curves, including X25519.
- Security Key Improvements: Cancel request keys are now 256 bits, enhancing security when using wire protocol version 3.2.

Deprecation of MD5 Passwords

PostgreSQL 18 officially deprecates MD5 password authentication:

- CREATE ROLE and ALTER ROLE now emit warnings for MD5 passwords
- MD5 support will be removed in a future major version
- Organizations should migrate to SCRAM-SHA-256 authentication

PostgreSQL 18 Monitoring Features

Enhanced Statistics Collection:
- Detailed I/O Statistics: New columns in pg_stat_io report I/O activity in bytes (read_bytes, write_bytes, extend_bytes).
- WAL I/O Monitoring: WAL receiver activity is now tracked in pg_stat_io with dedicated wait events.
- Parallel Worker Statistics: New columns in pg_stat_database track parallel worker launch and execution metrics.
Enhanced I/O Stats

testdb=# SELECT backend_type,
testdb-#        object,
testdb-#        read_bytes,
testdb-#        write_bytes
testdb-# FROM pg_stat_io
testdb-# WHERE read_bytes > 0;
  backend_type    |  object  | read_bytes | write_bytes
-------------------+----------+------------+-------------
client backend    | relation |    5505024 |           0
autovacuum worker | relation |     106496 |           0
startup           | wal      |      32768 |           0
(3 rows)

Time: 65.021 ms

WAL I/O Monitoring

testdb=# SELECT *
testdb-# FROM pg_stat_io
testdb-# WHERE object = 'WAL';
backend_type | object | context | reads | read_bytes | read_time | writes | write_bytes | write_time | writebacks | writeback_time | extends | extend_bytes |
extend_time | hits | evictions | reuses | fsyncs | fsync_time | stats_reset
--------------+--------+---------+-------+------------+-----------+--------+-------------+------------+------------+----------------+---------+--------------+-
------------+------+-----------+--------+--------+------------+-------------
(0 rows)

Time: 7.194 ms
testdb=# SELECT DISTINCT object FROM pg_stat_io;
    object
---------------
wal
temp relation
relation
(3 rows)

Improved EXPLAIN Functionality:

- Automatic BUFFERS Output: EXPLAIN ANALYZE now automatically includes buffer usage information.
- Enhanced Node Details: Additional memory and disk usage details for Material, Window Aggregate, and CTE nodes.
- Fractional Row Counts: EXPLAIN now outputs fractional row counts for more accurate cost estimates.

EXPLAIN Enhancements

testdb=# EXPLAIN (ANALYZE, BUFFERS)
testdb-# SELECT * FROM sales WHERE total > 100;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on sales  (cost=0.00..25.53 rows=270 width=72) (actual time=4.675..4.701 rows=2.00 loops=1)
  Filter: ((price * (quantity)::numeric) > '100'::numeric)
  Buffers: shared hit=1
Planning Time: 7.938 ms
Execution Time: 8.154 ms
(5 rows)

Time: 39.339 ms

Replication and High Availability in PostgreSQL 18

Generated Column Replication

PostgreSQL 18 allows generated column values to be logically replicated, controlled by the new publish_generated_columns option..

Generated Column Replication

Enhanced Subscription Management:

- Default Streaming Mode: CREATE SUBSCRIPTION now defaults to parallel streaming mode instead of off.
- Two-Phase Commit Flexibility: ALTER SUBSCRIPTION can now modify replication slot two-phase commit behavior.

Improved Conflict Resolution

Enhanced logging and monitoring of conflicts during logical replication, with new columns in pg_stat_subscription_stats for detailed conflict tracking.

Administrative and Operational Improvements in PostgreSQL 18

Vacuum and Maintenance Enhancements:

- Eager Page Freezing: VACUUM can now freeze some all-visible pages even during normal operations, controlled by vacuum_max_eager_freeze_failure_rate.
- Truncation Control: New vacuum_truncate server variable controls file truncation during VACUUM operations.
- Enhanced Vacuum Statistics: Detailed timing information for vacuum and analysis operations, including delay time reporting.

VACUUM Enhancements

testdb=# VACUUM (VERBOSE) sales;
INFO:  vacuuming "testdb.public.sales"
INFO:  finished vacuuming "testdb.public.sales": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 2 remain, 0 are dead but not yet removable
removable cutoff: 807, which was 0 XIDs old when operation ended
new relfrozenxid: 789, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.986 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 5 records, 6 full page images, 38403 bytes, 0 buffers full
system usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s
INFO:  vacuuming "testdb.pg_toast.pg_toast_24665"
INFO:  finished vacuuming "testdb.pg_toast.pg_toast_24665": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 807, which was 0 XIDs old when operation ended
new relfrozenxid: 807, which is 19 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 0.727 MB/s, avg write rate: 0.727 MB/s
buffer usage: 25 hits, 1 reads, 1 dirtied
WAL usage: 2 records, 1 full page images, 7495 bytes, 0 buffers full
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s
VACUUM
Time: 75.587 ms

Adjust eager freeze rate

testdb=# SET vacuum_max_eager_freeze_failure_rate = 0.05;
SET
Time: 6.117 ms
testdb=# SET vacuum_truncate = on;
SET
Time: 0.230 ms

Flexible Autovacuum Configuration:

- Worker Slot Management: New autovacuum_worker_slots parameter allows dynamic adjustment of autovacuum workers without server restart.
- Threshold Configuration: autovacuum_vacuum_max_threshold allows specification of fixed dead tuple counts for triggering autovacuum.

Autovacuum Worker Slots

testdb=# SHOW autovacuum_worker_slots;
autovacuum_worker_slots
-------------------------
16
(1 row)

Time: 7.151 ms

Backup and Recovery Improvements:

- pg_combinebackup Enhancements: New –link option enables hard linking for faster backup combinations.
- Tar Format Verification: pg_verifybackup now supports verification of tar-format backups.
- Enhanced pg_upgrade: Statistics preservation during major version upgrades, parallel database checks, and new –swap mode for potentially faster upgrades.

Client and Tool Enhancements

psql Improvements

- Pipeline Query Support: New commands for issuing pipeline queries (\startpipeline, \syncpipeline, etc.).
- Enhanced Information Display: Improved \conninfo output with tabular format and additional connection details.
- Expanded Mode for Lists: All list commands can now use expanded mode with the x suffix. (\x) command for expanded enable or disable.

Connection and Protocol Enhancements

- Protocol Version Control: New libpq parameters to specify minimum and maximum acceptable protocol versions.
- Search Path Reporting: Changes to search_path are now reported to clients.
- SSL Key Logging: New sslkeylogfile parameter for debugging SSL connections.

Infrastructure and Platform Updates

Build System and Dependencies:

Meson on Windows: Meson builds are no longer experimental on Windows platforms.

Updated Requirements:

- Minimum Python version: 3.6.8
- OpenSSL: Version 1.1.1 or later required
- LLVM: Version 14 or later if enabled

NUMA (Non-Uniform Memory Access) Awareness:

New-with-libnuma configure option enables NUMA awareness, with supporting functions: It’s introduced on PostgreSQL version 18, and it is valid for later versions.

- pg_numa_available(): Reports NUMA availability
- pg_shmem_allocations_numa and pg_buffercache_numa: NUMA-aware system views

Migration and Compatibility Considerations

Breaking Changes

- Data Checksums Default: initdb now enables data checksums by default. Use –no-data-checksums to disable.
- Time Zone Handling: Session time zone abbreviations are now favored over the server timezone_abbreviations setting.
- VACUUM Behavior: VACUUM and ANALYZE now process inheritance children by default. Use the new ONLY option for previous behavior.

Upgrade Path

- Statistics Preservation: pg_upgrade can now preserve optimizer statistics, reducing post-upgrade analysis time.
- Parallel Checks: Database checks during upgrade can now run in parallel, significantly reducing upgrade time.
- Checksum Compatibility: pg_upgrade requires matching checksum settings between clusters.

Performance Impact and Real-World Benefits in PostgreSQL 18

Expected Performance Improvements

- I/O-Bound Workloads: Applications with heavy read patterns will see the most dramatic improvements from asynchronous I/O.
- Index-Heavy Operations: Parallel GIN index builds and enhanced B-tree skip scans will significantly benefit search-intensive applications.
- Large Table Maintenance: Improved vacuum strategies and parallel operations reduce maintenance windows.

Industry-Specific Benefits

- Analytics and Reporting: Enhanced query optimization and parallel processing benefit OLAP workloads.
- Full-Text Search: Parallel GIN index building and improved JSON handling enhance search applications.
- Time-Series Data: UUIDv7 support and virtual generated columns optimize time-series workloads.

Looking Forward

PostgreSQL 18 marks a key shift toward more efficient, scalable operations, with async I/O boosting its competitiveness while keeping reliability and standards intact.

Key areas where PostgreSQL 18 excels:

- Performance: Asynchronous I/O and query optimization improvements
- Scalability: Enhanced parallel processing and better resource utilization
- Developer Experience: Improved SQL features and better tooling
- Operations: Enhanced monitoring and flexible maintenance options
- Security: Modern authentication methods and improved SSL/TLS support

Conclusion

PostgreSQL 18 delivers on the promise of modern database performance while maintaining PostgreSQL’s commitment to reliability and standards compliance. The introduction of asynchronous I/O, enhanced query optimization, and improved developer features make this release a compelling upgrade for organizations of all sizes.