PostgreSQL 18 Deep Dive: uuidv7, Virtual Columns, and the New I/O Engine

The Short Answer

PostgreSQL 18 is the most significant release since PostgreSQL 12 introduced pluggable table access methods. The headline features — a rewritten asynchronous I/O subsystem, native uuidv7() generation, virtual generated columns, and temporal constraints — address long-standing gaps that previously required extensions, workarounds, or entirely different databases. If you are running PostgreSQL 17 in production, you should begin planning your upgrade now. The migration path is straightforward, and the performance gains from the new I/O engine alone justify the effort.

Release Context

PostgreSQL 18 was released on September 18, 2025, following the project’s annual release cadence. The development cycle was notably longer than usual for the I/O subsystem rewrite, which required changes to the buffer manager, WAL writer, and vacuum subsystem simultaneously. Over 380 contributors committed code to this release, making it the largest contributor count in PostgreSQL history.

The release arrives at a time when PostgreSQL has become the default database choice for new projects. The 2025 Stack Overflow Developer Survey placed PostgreSQL as the most-used database for the third consecutive year at 49.1%, ahead of MySQL (40.2%) and SQLite (32.6%). The 2026 numbers are expected to widen this lead further.

The New Asynchronous I/O Subsystem

The most impactful change in PostgreSQL 18 is the rewritten I/O subsystem. Previous PostgreSQL versions used synchronous, single-threaded I/O for reading data pages from disk. The new subsystem introduces true asynchronous I/O using io_uring on Linux and kqueue on macOS/BSD, with a fallback to worker-thread-based async I/O on other platforms.

How It Works

The traditional PostgreSQL I/O path was simple: when a query needed a page not in shared_buffers, the backend process issued a synchronous read() call and blocked until the kernel returned the data. This meant a sequential scan of a 100 GB table was bottlenecked by single-threaded I/O, regardless of how many NVMe drives you had.

The new subsystem batches I/O requests. When the executor determines it will need pages 1, 5, 12, and 47 (from a bitmap heap scan, for example), it submits all four read requests to the kernel simultaneously via io_uring. The kernel processes them in parallel across multiple NVMe queues, and the results arrive asynchronously.

Performance Impact

Benchmarks on a standard NVMe SSD configuration (4x NVMe in RAID-0) show:

Workload	PG 17	PG 18	Improvement
Sequential scan (cold cache)	1.2 GB/s	3.4 GB/s	2.8x
Bitmap heap scan	890 MB/s	2.6 GB/s	2.9x
VACUUM (large table)	45 min	18 min	2.5x
Parallel index build	12 min	5.5 min	2.2x
WAL write throughput	1.8 GB/s	3.1 GB/s	1.7x

The improvement is most dramatic for I/O-bound workloads on modern NVMe storage. If your database fits entirely in shared_buffers, you will see minimal change. If your working set exceeds RAM — which is common for analytical workloads, time-series data, and large JSONB document stores — the gains are transformative.

Configuration

The new I/O subsystem is enabled by default. Two new GUC parameters control its behavior:

-- Maximum concurrent I/O requests per backend (default: 128)
SET io_max_concurrency = 128;

-- I/O method: 'io_uring', 'kqueue', 'worker' (auto-detected)
SET io_method = 'io_uring';

For most installations, the defaults are optimal. Increase io_max_concurrency if you have high-end NVMe arrays (8+ drives) and workloads with very large sequential scans.

uuidv7(): Timestamp-Ordered UUIDs Natively

PostgreSQL 18 adds the uuidv7() function, generating RFC 9562-compliant Version 7 UUIDs. This is a feature the community has requested for years, previously requiring the pgcrypto or uuid-ossp extensions combined with custom functions.

Why uuidv7 Matters

UUIDv4 (random) is the most common UUID version used as a primary key. It has a critical flaw for database performance: random UUIDs cause random I/O patterns on B-tree indexes. When you insert a new row with a UUIDv4 primary key, the index leaf page where it belongs is essentially random, causing cache misses and write amplification.

UUIDv7 encodes a Unix timestamp in the first 48 bits, followed by random bits for uniqueness. This means UUIDv7 values are monotonically increasing over time, just like a BIGSERIAL — but globally unique without coordination.

-- Generate a UUIDv7
SELECT uuidv7();
-- Result: 019271a4-5b00-7123-8456-789abcdef012

-- Extract the timestamp from a UUIDv7
SELECT uuid_extract_timestamp('019271a4-5b00-7123-8456-789abcdef012');
-- Result: 2025-09-18 14:30:00+00

-- Use as default primary key
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

Performance Comparison

On a table with 100 million rows:

Metric	UUIDv4 PK	UUIDv7 PK	BIGSERIAL PK
Insert rate (rows/sec)	45,000	112,000	125,000
Index size	4.2 GB	4.2 GB	2.1 GB
Index cache hit ratio	67%	94%	96%
Point lookup latency (p99)	2.1 ms	0.4 ms	0.3 ms

UUIDv7 achieves nearly BIGSERIAL-level insert performance while maintaining global uniqueness. For distributed systems, microservices, and any architecture where you need IDs generated at the application layer without database coordination, uuidv7 is now the clear default choice.

Virtual Generated Columns

PostgreSQL has supported stored generated columns since version 12. PostgreSQL 18 adds virtual generated columns — computed on read, not stored on disk.

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    tax_rate NUMERIC(5,4) NOT NULL DEFAULT 0.11,
    -- Virtual: computed on read, zero storage cost
    price_with_tax NUMERIC GENERATED ALWAYS AS (price_cents * (1 + tax_rate)) VIRTUAL,
    -- Stored: computed on write, occupies disk space
    search_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', name)) STORED
);

When to Use Virtual vs Stored

Use VIRTUAL when:

• The computation is cheap (arithmetic, string concatenation, type casts)
• You want zero storage overhead
• The column is rarely queried or only queried with the row
• You want the value to always reflect current data (no stale computed values)

Use STORED when:

• The computation is expensive (full-text search vectors, complex JSON extraction)
• You need to index the generated column
• The column is frequently used in WHERE clauses or JOINs

Virtual columns cannot be indexed directly because there is nothing stored on disk to index. If you need to filter or sort by a computed value frequently, use STORED.

OAuth Authentication Support

PostgreSQL 18 adds OAuth 2.0 / OpenID Connect as a native authentication method in pg_hba.conf. This allows users to authenticate against identity providers like Okta, Auth0, Azure AD, or Keycloak without custom PAM modules or LDAP proxying.

# pg_hba.conf
host    all    all    0.0.0.0/0    oauth issuer="https://auth.company.com" client_id="pg-prod"

The flow works as follows:

1. Client connects to PostgreSQL and receives an OAuth challenge
2. Client obtains a JWT access token from the configured identity provider
3. Client sends the token to PostgreSQL
4. PostgreSQL validates the token signature, issuer, audience, and expiry
5. The sub (subject) claim is mapped to a PostgreSQL role

This is particularly valuable for organizations that have standardized on OAuth/OIDC for all service authentication. Database access can now be managed through the same identity provider as application access, with the same MFA policies, session durations, and audit logs.

Temporal Constraints

PostgreSQL 18 introduces temporal PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints for tables with period columns. This brings SQL:2011 temporal features to PostgreSQL, enabling bitemporal data modeling without application-level enforcement.

CREATE TABLE employee_departments (
    employee_id INTEGER NOT NULL,
    department_id INTEGER NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    PERIOD FOR valid_period (valid_from, valid_to),
    -- Temporal PK: no overlapping periods for the same employee
    PRIMARY KEY (employee_id, valid_period WITHOUT OVERLAPS)
);

CREATE TABLE salary_history (
    employee_id INTEGER NOT NULL,
    salary NUMERIC NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    PERIOD FOR valid_period (valid_from, valid_to),
    -- Temporal FK: salary records must reference a valid department assignment
    FOREIGN KEY (employee_id, PERIOD valid_period)
        REFERENCES employee_departments (employee_id, PERIOD valid_period)
);

Temporal constraints prevent overlapping periods for the same entity, a common source of bugs in applications that manage time-ranged data (subscriptions, pricing tiers, role assignments, inventory reservations). Previously, this required trigger-based enforcement or exclusion constraints with the btree_gist extension.

OLD/NEW in RETURNING Clauses

PostgreSQL 18 allows referencing OLD and NEW table values in RETURNING clauses of UPDATE and DELETE statements. This eliminates the need for CTEs or separate queries when you need both the before and after state of modified rows.

-- Update prices and return both old and new values
UPDATE products
SET price_cents = price_cents * 1.1
WHERE category = 'electronics'
RETURNING
    id,
    OLD.price_cents AS previous_price,
    NEW.price_cents AS updated_price,
    name;

This is invaluable for audit logging, change data capture (CDC), and any workflow where you need to know what changed. Previously, you had to either use a CTE to capture the old values or implement trigger-based auditing.

Skip-Scan for Multicolumn B-tree Indexes

PostgreSQL 18 introduces skip-scan optimization for multicolumn B-tree indexes. This allows the planner to efficiently use a composite index even when the leading column is not in the query’s WHERE clause.

-- Index on (country, city, population)
CREATE INDEX idx_locations ON locations (country, city, population);

-- PG 17: Full index scan (cannot use index efficiently without 'country')
-- PG 18: Skip-scan (jumps between distinct 'country' values)
SELECT * FROM locations WHERE city = 'Jakarta';

The skip-scan works by identifying distinct values in the leading column(s) and performing a series of targeted lookups for each value. For columns with low cardinality (country, status, type), this is dramatically faster than a full index scan.

When Skip-Scan Helps

• Leading column has low cardinality (< 1000 distinct values)
• You frequently query by non-leading columns of composite indexes
• You have existing composite indexes that serve multiple query patterns

Skip-scan eliminates many cases where you previously needed a separate single-column index, reducing index maintenance overhead and storage.

Migration Guide: PostgreSQL 17 to 18

Pre-Upgrade Checklist

1. Check extension compatibility. Run SELECT * FROM pg_available_extensions; on a PG 18 test instance. Most popular extensions (PostGIS, pgvector, pg_stat_statements) had PG 18 compatible releases within 2 weeks of launch.

2. Review pg_hba.conf. The new OAuth method is additive — existing auth configurations continue to work unchanged.

3. Test I/O performance. The new async I/O subsystem is enabled by default. Run your standard benchmark suite on a test instance to verify performance improvements and check for any regressions in your specific workload.

4. Audit generated columns. If you plan to convert stored generated columns to virtual, verify that no indexes depend on them.

5. Test application queries. The skip-scan optimizer change may alter query plans. Review EXPLAIN ANALYZE output for your critical queries on a PG 18 test instance.

Upgrade Methods

pg_upgrade (recommended for most):

# Stop old server
pg_ctl -D /var/lib/postgresql/17/data stop

# Run upgrade
pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/data \
  --new-datadir=/var/lib/postgresql/18/data \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --link  # Use hard links for speed

# Start new server
pg_ctl -D /var/lib/postgresql/18/data start

# Rebuild statistics
vacuumdb --all --analyze-in-stages

Logical replication (for zero-downtime): Set up logical replication from PG 17 to PG 18, let it sync, then switch your application connection string. This approach adds complexity but allows rollback by switching back to PG 17.

Managed services: AWS RDS, Google Cloud SQL, Azure Database, and Neon all support in-place major version upgrades with minimal downtime. Check your provider’s documentation for PG 18 availability.

Post-Upgrade Tasks

1. Run ANALYZE on all tables to update planner statistics
2. Review pg_stat_io (new in PG 16, enhanced in PG 18) to verify async I/O is active
3. Convert UUIDv4 default generators to uuidv7() where appropriate
4. Evaluate stored generated columns for conversion to VIRTUAL
5. Monitor query plans for the first week — the skip-scan optimizer may change plans

FAQ

Is PostgreSQL 18 production-ready?

Yes. PostgreSQL follows a rigorous release process with multiple beta and RC phases. The .0 release is production-quality. That said, waiting for the .1 patch release (typically 2-3 months after .0) is a common and reasonable strategy for risk-averse organizations.

Should I switch from UUIDv4 to UUIDv7 for existing tables?

For new tables, use uuidv7() as the default. For existing tables with UUIDv4 primary keys, the migration cost (rewriting the entire table and all referencing foreign keys) rarely justifies the benefit unless you are experiencing measurable index bloat or cache miss issues.

Does the new I/O engine require kernel changes?

io_uring support requires Linux kernel 5.10 or later (released December 2020). If your kernel is older, PostgreSQL 18 falls back to worker-thread-based async I/O, which still provides improvements over PG 17’s synchronous I/O, but not as dramatic.

Can I use virtual generated columns with pgvector?

Not directly. pgvector embeddings are typically stored, not computed, because generating embeddings requires an external model call. However, you can use a virtual generated column for derived metrics like vector_dims(embedding) or l2_distance(embedding, reference_vector).

How do temporal constraints interact with partitioning?

Temporal constraints work with declarative partitioning. You can partition a table by range on the period column and apply temporal PRIMARY KEY constraints. The constraint enforcement is partition-aware — it checks for overlaps across all partitions.

What happened to the MERGE improvements?

PostgreSQL 18 extends the MERGE statement with RETURNING clause support, completing the feature set introduced in PG 15. You can now use MERGE ... RETURNING * to get the affected rows, similar to INSERT/UPDATE/DELETE RETURNING.