8 Enhancing Features in PostgreSQL 18

What’s New in PostgreSQL 18?

PostgreSQL 18 continues the database’s reputation as one of the most advanced open-source relational database systems available today. This release improves performance for both OLTP and analytics workloads while simplifying application development and database administration.

Why PostgreSQL 18 Is a Significant Release

The focus areas of PostgreSQL 18 include:

• Improved storage and query performance
• Better indexing intelligence
• Enhanced upgrade experience
• Modern authentication standards
• Stronger temporal data integrity
• Developer-friendly SQL enhancement

PostgreSQL 18 Async I/O — Faster Disk Reads Up to 3x

One of the most important PostgreSQL 18 features is the introduction of Asynchronous I/O (AIO).

PostgreSQL can now perform non-blocking disk operations for:

• Sequential scans
• Bitmap heap scans
• VACUUM operations

Instead of waiting for disk reads to complete, PostgreSQL processes requests asynchronously, significantly improving throughput and reducing latency.

How to Configure io_method in postgresql.conf

PostgreSQL 18 introduces new asynchronous I/O settings that can be configured directly inside postgresql.conf.

io_method = worker

Depending on the operating system and workload, administrators can tune I/O behavior for better concurrency and performance.

Index Skip Scan Improvements

Another major enhancement is PostgreSQL 18 Skip Scan, which improves how PostgreSQL uses multicolumn B-tree indexes.

Previously, composite indexes were only fully useful when queries filtered on the leading column. PostgreSQL 18 now allows the planner to intelligently skip unused portions of an index.

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

--- filter is omitted 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)
 )

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

Even without filtering on league, PostgreSQL can still efficiently use the 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))

How Skip Scan Reduces Query Cost on Composite Indexes

Benefits include:

• Better utilization of existing indexes
• Reduced need for duplicate indexes
• Lower storage overhead
• Fewer full table scans
• Faster query execution plans

This is especially beneficial for applications with large and complex indexing strategies.

Native UUIDv7 Support in PostgreSQL 18

PostgreSQL UUIDv7 support is now built directly into PostgreSQL 18.

Traditional random UUIDs (UUIDv4) often cause index fragmentation because inserted values are randomly distributed. UUIDv7 solves this by generating time-ordered UUIDs.

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

-- 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
(4 rows)

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

-- 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
(4 rows)

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

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

UUIDv7 vs UUIDv4: Why the Switch Matters

UUIDv7 offers several advantages over UUIDv4:

• Better index locality
• Reduced page splits
• Improved insert performance
• Better cache efficiency
• More predictable storage behavior

Expanded RETURNING Support — With OLD and NEW

PostgreSQL 18 enhances the RETURNING clause by allowing direct access to OLD and NEW row values during:

• INSERT
• UPDATE
• DELETE
• MERGE

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

• Cleaner auditing logic
• Easier change tracking
• Simplified application workflows
• Reduced trigger complexity

Virtual Generated Columns

Generated columns in PostgreSQL 18 are now virtual by default, meaning values are computed during reads rather than stored physically.

CREATE TABLE shop_orders (
order_id SERIAL PRIMARY KEY,
product_name VARCHAR(50),
unit_price NUMERIC(7,2),
quantity INT,
total_price NUMERIC(9,2)
GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL
);

Insert Sample Data:

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

Query example:

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)

Benefits of Virtual Generated Columns

• Reduced storage usage
• Always up-to-date computed values
• Simplified schema design
• Useful for derived business attributes

Ideal Use Cases

• Pricing calculations
• Tax computations
• Inventory summaries
• Derived reporting fields

OAuth 2.0 Authentication in PostgreSQL 18

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

Benefits

• Centralized authentication management
• Improved security controls
• Easier compliance management
• Better cloud-native integration

Impact

Organizations using modern infrastructure can now standardize database authentication alongside application authentication workflows.

Temporal Constraints

PostgreSQL 18 introduces native temporal constraints for:

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY

This allows constraints to enforce validity across time ranges.

Example:-

--- 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. //

Benefits of Temporal Constraints

• Prevents overlapping time ranges
• Maintains historical integrity
• Simplifies application logic
• Reduces dependency on triggers

Ideal Use Cases

• HR systems
• Financial applications
• Subscription management
• Insurance platforms
• Audit history tracking

pg_upgrade Retains Optimizer Statistics

A major operational improvement in PostgreSQL 18 upgrade workflows is that pg_upgrade now retains optimizer statistics.

Previously, DBAs often needed to rebuild statistics after upgrades, which could impact performance immediately after migration.

Benefits

• Faster upgrade completion
• Reduced downtime
• Stable post-upgrade performance
• Faster production cutovers

This is especially valuable for large enterprise databases where ANALYZE operations can take significant time.

Should You Upgrade to PostgreSQL 18? A DBA’s Checklist

For most organizations, PostgreSQL 18 offers meaningful benefits in:

• Performance
• Scalability
• Security
• Operational simplicity

Breaking Changes to Review Before Upgrading

Before upgrading:

• Review extension compatibility
• Validate replication configurations
• Test authentication changes
• Benchmark application workloads
• Review deprecated configuration parameters

Recommended Upgrade Path

Recommended best practices:

1. Test upgrades in staging environments
2. Validate backup and rollback plans
3. Benchmark critical queries
4. Use pg_upgrade for minimal downtime migrations
5. Monitor workloads post-upgrade