PostgreSQL 18: New Features, Performance Improvements & What's New for Data Engineers

In September, the PostgreSQL Global Development Group announced the release of Postgres 18, marking another milestone in the evolution of the world's most advanced open-source database. We’re big fans of Postgres at Matia, and this release helped drive that home. The new and improved Postgres brings performance improvements, developer-friendly features, and enhanced operational capabilities that address both modern cloud workloads and traditional database challenges.

Read on to learn what’s new with the latest Postgres release, and how data engineers are leveraging it.

The Game-Changer: Asynchronous I/O

The most significant advancement in Postgres 18 is the new asynchronous I/O subsystem, which has demonstrated up to 3x performance improvements when reading from storage. Previously, Postgres’ synchronous I/O forced backends to wait for each operation to complete before requesting the next one, creating a bottleneck for data-intensive workloads.

The new AIO subsystem lets Postgres issue multiple I/O requests concurrently instead of waiting for each to finish in sequence, dramatically improving throughput for sequential scans, bitmap heap scans, and vacuum operations.

You can configure this feature using the new io_method setting:

-- Enable asynchronous I/O with io_uring (Linux)
ALTER SYSTEM SET io_method = 'io_uring';

-- Or use worker-based approach
ALTER SYSTEM SET io_method = 'worker';

-- Revert to traditional synchronous behavior if needed
ALTER SYSTEM SET io_method = 'sync';

-- Fine-tune I/O combining behavior
ALTER SYSTEM SET io_combine_limit = 128;

‍

This foundational improvement means your database can handle more parallel work while I/O operations complete, freeing CPU cycles for other tasks and making the entire system more responsive.

UUIDv7: The Primary Key Debate Gets a New Champion

Postgres 18 introduces the database-friendly uuidv7() function that provides better indexing and read performance for UUIDs. This native support nearly settles the long-standing debate about whether to use SERIAL/IDENTITY types or UUIDs as primary keys.

Unlike traditional UUIDv4 values that are completely random and cause poor B-tree locality, UUIDv7 combines global uniqueness with timestamp ordering, delivering the best of both worlds:

-- Create a table using UUIDv7 as primary key
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id INT,
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Generate a UUIDv7
SELECT uuidv7();
-- Output: 018c7f4e-3b2a-7000-8000-000000000001

The timestamp-ordered nature means sequential inserts result in better data locality, reduced page splits, and improved caching behavior compared to random UUIDs.

Virtual Generated Columns: Compute on Demand

Developers benefit from virtual generated columns that compute values at query time, providing flexibility without the storage overhead of stored generated columns. This is now the default behavior for generated columns in Postgres 18.

Query Optimizer Enhancements

Postgres 18 introduces "skip scan" lookups on multicolumn B-tree indexes that improve execution time for queries that omit an equality condition on one or more prefix index columns. 

For data engineers, this solves a long-standing indexing issue. Previously, if you created a composite index on country, city and then queried only by city, Postgres would completely ignore the index and perform a full table scan, potentially scanning millions of rows. This forced the creation of redundant single-column indexes, increasing storage costs, slowing down queries, and complicating indexes.

With skip scan, Postgres 18 can now efficiently use composite indexes even when you skip the leading columns. The optimizer scans through the distinct values of the leading column and performs index lookups for each combo. This is particularly effective when the leading column has relatively few distinct values. For example, querying cities across 10 countries is better than the entire database.

This optimization automatically makes queries faster without requiring schema changes:

-- Create a multicolumn index
CREATE INDEX idx_users_country_city 
    ON users(country, city);

-- Previously inefficient, now optimized with skip scan
SELECT * FROM users WHERE city = 'San Francisco';
-- Postgres 18 can now use the index efficiently
-- even though 'country' prefix is missing

The performance impact is most dramatic in multi-tenant applications and time-series data where you frequently query by secondary dimensions. Instead of maintaining duplicate indexes for every query pattern, you can now rely on well-designed composite indexes to serve multiple use cases efficiently.

Additionally, queries using OR conditions can now leverage indexes more effectively, leading to significantly faster execution times.

Enhanced OLD and NEW References

Postgres 18 supports OLD and NEW references in RETURNING clauses for INSERT, UPDATE, DELETE, and MERGE commands, making change tracking and audit trails simpler:

-- Track changes during upsert operations
INSERT INTO products (name, price) 
VALUES ('Widget', 25.00)
ON CONFLICT (name) DO UPDATE 
SET price = EXCLUDED.price
RETURNING 
    name,
    old.price AS previous_price,
    new.price AS current_price,
    (old.price IS NULL) AS is_new_record;

-- Track deleted records
DELETE FROM products WHERE price < 10.00
RETURNING 
    old.name AS deleted_product,
    old.price AS deleted_price;

Temporal Constraints Without Overlaps

Postgres 18 introduces temporal constraints for PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints, enabling better support for time-series and temporal data:

-- Prevent double-booking scenarios
CREATE TABLE room_bookings (
    room_id INT,
    booking_period TSRANGE,
    PRIMARY KEY (room_id, booking_period WITHOUT OVERLAPS)
);

-- This ensures the same room cannot be booked
-- for overlapping time periods

OAuth 2.0 Authentication

Postgres 18 makes it easier to integrate with single-sign-on systems(SSO) like Okta with support for OAuth 2.0 authentication, enabling modern identity provider integration and reducing reliance on shared passwords for better security posture.

‍

Improved Upgrade Experience

This release makes major-version upgrades less disruptive, accelerating upgrade times and reducing the time required to reach expected performance after an upgrade completes. The pg_upgrade tool now preserves planner statistics, meaning your newly upgraded cluster reaches optimal performance immediately rather than waiting for ANALYZE to complete.

Impact on Data Warehousing and ETL Pipelines

For organizations running data warehouse syncs to platforms like Snowflake, Redshift, or BigQuery, Postgres 18's performance improvements translate directly to faster and more efficient data movement. The asynchronous I/O enhancements significantly accelerate bulk reads during extraction phases, reducing the time required to pull large datasets from your operational database.

The improved query optimizer with skip scan capabilities means your ETL queries can retrieve data more efficiently, even when dealing with complex filtering conditions. This is particularly valuable for incremental sync patterns where you're querying on timestamp ranges or change data capture (CDC) scenarios:

-- Incremental sync query that benefits from skip scan
CREATE INDEX idx_events_tenant_timestamp 
    ON events(tenant_id, created_at);

-- Query omits tenant_id but still uses index efficiently
SELECT * FROM events 
WHERE created_at > '2025-11-01'
AND event_type = 'purchase';

Virtual generated columns also streamline data preparation before syncing to analytical platforms, allowing you to materialize transformations at query time rather than storing pre-computed values:

-- Compute analytics-friendly fields on-the-fly
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    amount DECIMAL(10,2),
    created_at TIMESTAMP,
    year INT GENERATED ALWAYS AS 
        (EXTRACT(YEAR FROM created_at)) VIRTUAL,
    quarter INT GENERATED ALWAYS AS 
        (EXTRACT(QUARTER FROM created_at)) VIRTUAL
);

-- ETL extracts computed dimensions without storage overhead
SELECT id, amount, year, quarter 
FROM transactions 
WHERE created_at > (NOW() - INTERVAL '1 day');

The faster vacuum operations enabled by AIO also reduce maintenance windows, ensuring your source database stays healthy and responsive even under heavy replication and sync workloads.

Replicate Postgres Faster with Matia

While Postgres 18 brings tremendous improvements, replicating a Postgres instance still requires significant expertise and time. That's where Matia comes in.

Matia provides a fully managed unified dataops experience that lets you leverage the latest Postgres capabilities immediately, without the operational overhead.

With Matia, you get the following benefits:

• Up to 10x refresh speed with parallel sync  drastically reduce sync times compared to Fivetran and other tools. For example, customers have reduced syncs from 3 days to 24 hours for over 8TB Postgres databases. Think of it as opening a multilane highway vs a single lane road.
• Automated backups and point-in-time recovery so you can focus on building, not babysitting infrastructure
• Seamless scaling as your data and query load grows
• Built in connectors for fast, reliable syncs to Snowflake and other warehouses and datalakes. Postgres can also be a destination. You can also send data to Snowflake or another warehouse to your Postgres instance.

Whether you're migrating from an older Postgres version or starting fresh, Matia accelerates your time-to-value by handling the complexity of database operations while you focus on leveraging Postgres 18's powerful new capabilities for your applications and analytics workflows.

Postgres 18 represents a significant leap forward with architectural improvements like asynchronous I/O, practical developer features like UUIDv7 and virtual generated columns, and enhanced operational capabilities. Whether you're running transactional workloads, analytical queries, or hybrid systems, Postgres 18 delivers measurable performance gains and quality-of-life improvements that make it a compelling upgrade target.

For data teams managing warehouse syncs and ETL pipelines, the performance improvements translate to faster data movement, reduced extraction times, and more efficient ingestion workflows. Combined with a managed platform like Matia, you can harness these benefits immediately without the operational complexity.

‍