8 Enhancing Features in PostgreSQL 18

PostgreSQL 18 is here, and it brings a powerful mix of performance boosts, developer-friendly enhancements, and modern security upgrades. Each release of PostgreSQL cements its reputation as the most advanced open-source relational database, and version 18 continues that legacy.

In this blog, we’ll look at 8 key enhancements in PostgreSQL 18 and why they matter for DBAs, developers, and organizations.

1. Asynchronous I/O (AIO)

PostgreSQL 18 introduces an asynchronous I/O subsystem that accelerates operations such as:

• Sequential scans
• Bitmap heap scans
• VACUUM processes

Instead of waiting for disk I/O, PostgreSQL can now process requests in a non-blocking way, improving throughput and reducing query latency.

Impact: Faster queries and better performance for large datasets, analytics, and OLTP workloads.

2. Skip Scan Lookups – Smarter Multicolumn Indexing

Previously, multicolumn B-tree indexes weren’t fully utilized unless the query filtered on the leading column. PostgreSQL 18 changes that with skip scan lookups, allowing the engine to skip over unused index portions.

• More efficient index usage
• Fewer full table scans
• Reduced need for redundant indexes

--- Index with 3 columns
miru_sports=# CREATE INDEX idx_sports_stats ON sports_stats (league, team, match_date);
CREATE INDEX

--- filter is ommitted but the index still works
miru_sports=# EXPLAIN SELECT team FROM sports_stats WHERE team = ‘Team 5’ AND match_date BETWEEN '2025-06-01' AND '2025-08-31';
QUERY PLAN 
--------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_sports_stats on sports_stats (cost=0.29..27.17 rows=635 width=7) (actual time=0.088..0.203 rows=616.00 loops=1)
Index Cond: ((team = 'Team 5'::text) AND (match_date >= '2025-06-01'::date) AND (match_date <= '2025-08-31'::date))

//In the above explain plan we could see the query planner is choosing the index even when the first column is not part of the filter//

This is a big win for applications with complex composite indexes.

3. UUIDv7() Support

UUIDs are widely used, but random UUIDs (uuidv4) can cause fragmentation in indexes. PostgreSQL 18 introduces uuidv7(), which generates timestamp-ordered UUIDs.

mafiree=# CREATE TABLE events (
mafiree(# id UUID PRIMARY KEY DEFAULT uuidv7(),
mafiree(# event_time TIMESTAMPTZ DEFAULT now(),
mafiree(# payload TEXT
mafiree(# );

-- Inspect the timestamp

postgres=# SELECT id,event_time,payload,uuid_extract_timestamp(id) from events;
id                                    | event_time                    | payload          | uuid_extract_timestamp 
--------------------------------------+-------------------------------+------------------+----------------------------
019984f4-f912-7359-a149-cc772e50f682  | 2025-09-26 07:37:51.122+00    | user_login       | 2025-09-26 07:37:51.122+00
019984f4-f912-7896-a803-7fd9494e3d76  | 2025-09-26 07:37:51.122+00    | order_created    | 2025-09-26 07:37:51.122+00
019984f4-f912-78d5-8639-09d8e418a6cd  | 2025-09-26 07:37:51.122+00    | payment_received | 2025-09-26 07:37:51.122+00
019984f4-f912-78ef-a145-d0251338fec6  | 2025-09-26 07:37:51.122+00    | order_shipped    | 2025-09-26 07:37:51.122+00

Impact: Improves index locality and performance

4. Expanded RETURNING Support – With OLD and NEW

In version 18, the RETURNING clause has been enhanced. You can now use OLD and NEW row references in INSERT, UPDATE, DELETE, and MERGE commands.

miru_sports=# UPDATE deliveries SET status = 'Delivered' WHERE delivery_id = 1 RETURNING OLD.*, NEW.*;
delivery_id  | order_id | delivery_date | status  | delivery_id | order_id | delivery_date | status 
-------------+----------+---------------+---------+-------------+----------+---------------+-----------
1            | 1        | 2025-09-28    | Pending | 1           | 1        | 2025-09-28    | Delivered
(1 row)
UPDATE 1

Impact :

• More flexible query result
• Cleaner ways to return row states before and after changes
• Useful for auditing and application logic

5. Virtual Generated Columns – Efficiency by Default

Generated columns are now virtual by default, meaning their values are computed on read rather than stored.

--- Creating a table with generated columns
miru_sports=# CREATE TABLE shop_orders (
miru_sports(# order_id SERIAL PRIMARY KEY,
miru_sports(# product_name VARCHAR(50),
miru_sports(# unit_price NUMERIC(7,2),
miru_sports(# quantity INT,
miru_sports(# total_price NUMERIC(9,2) GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL
miru_sports(# );
CREATE TABLE

--- Insert Sample Data
miru_sports=# INSERT INTO shop_orders (product_name, unit_price, quantity) VALUES
miru_sports-# ('Football', 1500.00, 2),
miru_sports-# ('Cricket Bat', 2500.50, 1),
miru_sports-# ('Basketball', 1800.25, 3);
INSERT 0 3

--- Query the Table
miru_sports=# SELECT order_id, product_name, unit_price, quantity, total_price FROM shop_orders;
order_id  | product_name | unit_price | quantity | total_price 
----------+--------------+------------+----------+-------------
1         | Football     | 1500.00    | 2        | 3000.00
2         | Cricket Bat  | 2500.50    | 1        | 2500.50
3         | Basketball  | 1800.25     | 3        | 5400.75
(3 rows)

--- Updating a record
miru_sports=# UPDATE shop_orders SET quantity = 4 WHERE product_name = 'Cricket Bat';
UPDATE 1

--- Always Up-to-Date
miru_sports=# SELECT order_id, product_name, unit_price, quantity, total_price
FROM shop_orders;
order_id  | product_name | unit_price | quantity | total_price 
----------+--------------+------------+----------+-------------
1         | Football     | 1500.00    | 2        | 3000.00
3         | Basketball   | 1800.25    | 3        | 5400.75
2         | Cricket Bat  | 2500.50    | 4        | 10002.00
(3 rows)

Impact :

• Saves storage space
• Ensures always up-to-date derived values
• Flexible for attributes like total_price = price * quantity

6. OAuth Authentication – Modern Security

PostgreSQL 18 adds OAuth authentication support, allowing integration with identity providers (IdPs) for token-based authentication.

Impacts :

• Centralized authentication management
• Easier integration with cloud-native and enterprise systems
• Stronger, modern access control

7. Temporal Constraints

PostgreSQL 18 adds temporal constraints for PRIMARY KEY, UNIQUE, and FOREIGN KEY. This means constraints can now apply over time ranges. Before PostgreSQL 18, preventing overlapping time periods required additional application logic, custom triggers.

--- create a table employee to demonstrate temporal constraints

CREATE TABLE employee (
emp_id INTEGER,
emp_name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
valid_period tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
PRIMARY KEY (emp_id, valid_period WITHOUT OVERLAPS)
);

--- Insert data into the table
mafiree=# INSERT INTO employee (emp_id, emp_name, department, position, salary, valid_period)
VALUES
(1, 'Alice Johnson', 'Engineering', 'Software Engineer', 75000,
tstzrange('2024-01-01', '2025-01-01', '[)')),
(1, 'Alice Johnson', 'Engineering', 'Senior Software Engineer', 85000,
tstzrange('2025-01-01', 'infinity', '[)')),
(2, 'Bob Wilson', 'Marketing', 'Marketing Specialist', 60000,
tstzrange('2024-06-01', 'infinity', '[)'));
INSERT 0 3

// The temporal primary key (emp_id, valid_period WITHOUT OVERLAPS) allows multiple rows for the same employee (emp_id = 1) as long as their time periods don't overlap. This enables you to maintain a complete history of changes while ensuring data integrity. //

Impacts :

• Stronger data integrity in temporal databases
• Ideal for financial systems, HR applications, and time-based data models
• Prevents invalid overlaps in range-based keys

8. pg_upgrade Retains Optimizer Statistics

Upgrading PostgreSQL often required rebuilding optimizer statistics, which could be time-consuming. Now, pg_upgrade retains statistics.

Impacts :

• Faster upgrades
• Reduced downtime
• Immediate post-upgrade performance stability

A huge plus for teams maintaining large production databases.

PostgreSQL 18 is a landmark release that focuses not only on performance improvements but also on developer productivity, data integrity, and modern security. Features like skip scan lookups, UUIDv7, virtual generated columns, temporal constraints, OAuth authentication, and retained optimizer statistics in pg_upgrade all reduce complexity while boosting efficiency.

If you have any questions or require assistance with your PostgreSQL upgrade, feel free to reach out to us at sales@mafiree.com for dedicated PostgreSQL support. Together, let’s unlock the full potential of your data with PostgreSQL 18