PostgreSQL 18: New Features, Performance Improvements, and Migration Guide

PostgreSQL 18: What Developers Need to Know

The PostgreSQL 18 new features bring the most significant performance improvements in years. The headline features — native async I/O, virtual generated columns, and improved JSON path operations — address long-standing pain points for both application developers and database administrators. For the first time, the engine reaches beyond incremental query-planner tuning and changes how it talks to the storage layer underneath.

This guide covers the features that matter most for production workloads, with representative benchmarks showing the performance impact. Whether you are planning a migration from PostgreSQL 17 or evaluating Postgres for a new project, this breakdown will help you understand what has changed and why it matters. Importantly, most of these gains require no application rewrite — they arrive simply by upgrading the binary and tuning a handful of configuration parameters.

PostgreSQL 18 New Features: Async I/O Leads the Way

PostgreSQL has historically used synchronous I/O, meaning each backend process waits for disk operations to complete before proceeding. PostgreSQL 18 introduces native async I/O using io_uring on Linux, allowing multiple I/O operations to be submitted and completed in parallel. As a result, a single backend can keep many in-flight reads outstanding instead of stalling on each block fault.

-- Enable async I/O (postgresql.conf)
-- io_method = 'io_uring'  (Linux only, default on supported kernels)
-- io_workers = 4           (number of I/O worker processes)

-- Benchmark: Sequential scan on 10GB table
-- PostgreSQL 17: 8.2 seconds
-- PostgreSQL 18: 3.1 seconds (2.6x faster, representative figures)

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount) as total
FROM transactions
WHERE transaction_date >= '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 10000
ORDER BY total DESC;

-- PostgreSQL 18 output shows:
-- I/O Prefetch Blocks: 128000 (async prefetching active)
-- Shared Read I/O: 2.1s (was 6.8s in PG17)

The impact is most dramatic for analytical queries that scan large tables. OLTP workloads see a more modest 15-30% improvement due to more efficient buffer pool management. Moreover, the improvement is automatic — no query changes needed. Benchmarks published by the community show the largest wins on cold caches, where the old synchronous path spent most of its time waiting on the storage device rather than the CPU.

Tuning io_method and io_workers Correctly

The new io_method parameter accepts three values: sync (the legacy behaviour), worker (a portable thread-pool approach that runs everywhere), and io_uring (the high-performance Linux path). On older kernels or inside restricted containers where io_uring is disabled by seccomp, the engine falls back gracefully, so it pays to confirm which path is actually active rather than assuming.

-- Confirm the active I/O method and worker pool at runtime
SHOW io_method;
SHOW io_workers;

-- Inspect async activity per backend
SELECT backend_type, io_object, reads, writes, extends
FROM pg_stat_io
WHERE reads > 0
ORDER BY reads DESC
LIMIT 10;

A common pitfall is over-provisioning io_workers. Because each worker competes for the same storage queue depth, setting it far above the number of physical cores or the device’s effective queue depth yields diminishing returns and can increase context-switching overhead. In production teams typically start at four workers and scale only after observing saturation in pg_stat_io. Conversely, on NVMe-backed instances with deep queues, raising the value modestly can unlock additional parallelism.

Virtual Generated Columns

PostgreSQL has supported stored generated columns since version 12, but they consume disk space. PostgreSQL 18 adds virtual generated columns that are computed on read, saving storage while maintaining query convenience. This is particularly valuable for derived values that are cheap to compute but expensive to materialize across billions of rows.

-- Virtual generated columns (computed on read, no storage)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    tax_rate NUMERIC(5,4) DEFAULT 0.08,

    -- Virtual: computed on every read, uses no disk space
    price_dollars NUMERIC GENERATED ALWAYS AS (price_cents / 100.0) VIRTUAL,
    price_with_tax NUMERIC GENERATED ALWAYS AS (
        price_cents / 100.0 * (1 + tax_rate)
    ) VIRTUAL,

    -- Stored: precomputed, uses disk space (existing behavior)
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', name)
    ) STORED
);

-- Virtual columns work in queries like regular columns
SELECT name, price_dollars, price_with_tax
FROM products
WHERE price_with_tax > 100
ORDER BY price_with_tax DESC;

-- You can index virtual columns for performance
CREATE INDEX idx_products_price_tax ON products (price_with_tax);

The trade-off is straightforward: virtual columns trade storage for CPU. Each read recomputes the expression, so a column read inside a tight loop over millions of rows can cost more than the disk it saves. The practical rule is to keep cheap arithmetic and formatting expressions virtual, and reserve the STORED variant for anything involving full-text vectors, JSON extraction, or function calls that the planner cannot inline. When in doubt, benchmark both forms against your real query mix rather than guessing.

JSON Path Improvements

JSON handling gets more powerful with enhanced path expressions and better performance for JSONB operations. The flagship addition is JSON_TABLE, the SQL/JSON standard construct that turns a JSON document into a relational result set without a single line of procedural code:

-- New JSON path functions in PostgreSQL 18
-- JSON_TABLE: Convert JSON to relational rows (SQL/JSON standard)
SELECT jt.*
FROM api_responses,
  JSON_TABLE(
    response_body,
    '$.data[*]' COLUMNS (
      user_id INTEGER PATH '$.id',
      username TEXT PATH '$.name',
      email TEXT PATH '$.contact.email',
      signup_date DATE PATH '$.created_at',
      is_active BOOLEAN PATH '$.status'
        DEFAULT true ON EMPTY
    )
  ) AS jt
WHERE jt.is_active = true;

-- JSON_SERIALIZE and JSON_PARSE for type-safe conversions
SELECT JSON_SERIALIZE(config ORDER BY key)
FROM app_settings;

-- Improved JSONB containment queries (around 30% faster in PG18)
SELECT * FROM events
WHERE metadata @> '{"type": "purchase", "source": "mobile"}'::jsonb;

Before this release, the same transformation required nested jsonb_array_elements calls and a tangle of ->> extractions that the planner struggled to optimize. With JSON_TABLE, the document is unnested once, columns are typed explicitly, and the DEFAULT ... ON EMPTY and ON ERROR clauses give you predictable behaviour for missing or malformed keys. For teams ingesting semi-structured API payloads, this alone removes a great deal of fragile glue code.

Other Notable Features

Improved COPY Performance

-- COPY now supports parallel workers
COPY large_table FROM '/data/import.csv'
WITH (FORMAT csv, HEADER true, PARALLEL 4);

-- 3-4x faster bulk imports on multi-core systems (representative)
-- PostgreSQL 17: 45 seconds for 10M rows
-- PostgreSQL 18: 12 seconds for 10M rows (4 workers)

OAuth/OIDC Authentication

# pg_hba.conf — OAuth authentication support
# Connect with OAuth tokens from identity providers
host  all  all  0.0.0.0/0  oauth  issuer="https://auth.example.com"
                                    audience="postgresql-prod"

Native OAuth/OIDC support is more consequential than it first appears. Previously, integrating Postgres with an enterprise identity provider meant standing up an external proxy such as PgBouncer with custom auth, or relying on Kerberos. Now the database can validate bearer tokens directly against your issuer, which slots cleanly into zero-trust architectures where short-lived, audience-scoped tokens replace long-lived passwords.

Incremental Backup Improvements

# Faster incremental backups with block-level tracking
pg_basebackup -D /backups/full --checkpoint=fast
pg_basebackup -D /backups/incr1 --incremental=/backups/full/backup_manifest

# Combine incrementals for restore
pg_combinebackup /backups/full /backups/incr1 -o /backups/combined

When NOT to Rush the Upgrade — Trade-offs

Despite the headline numbers, PostgreSQL 18 is not a drop-in win for every shop. The async I/O path is newest and least battle-tested precisely where it matters most, so latency-sensitive OLTP systems should soak it in staging under realistic concurrency before promoting it. If your workload is already CPU-bound rather than I/O-bound, the storage improvements will barely register, and you are taking on upgrade risk for little reward.

There are also ecosystem considerations. Extensions compiled against the PostgreSQL 17 ABI must be rebuilt, and a small number of monitoring tools may not yet understand the new pg_stat_io columns or the io_uring telemetry. Furthermore, managed providers often lag the community release by months, so cloud-hosted databases may simply not offer version 18 on your preferred timeline. In short, plan the upgrade deliberately, but do not let a major-version bump outrun your testing discipline. For deeper background on what changed before this release, the PostgreSQL 17 features guide remains a useful companion.

Migration from PostgreSQL 17

Upgrading from PostgreSQL 17 to 18 follows the standard pg_upgrade path. Additionally, test your application thoroughly with the new async I/O behavior, since query plans and I/O timing can shift enough to surface latent assumptions in your code:

# Step 1: Install PostgreSQL 18
sudo apt install postgresql-18

# Step 2: Stop both clusters
sudo systemctl stop postgresql@17-main
sudo systemctl stop postgresql@18-main

# Step 3: Run pg_upgrade
sudo -u postgres pg_upgrade   --old-datadir=/var/lib/postgresql/17/main   --new-datadir=/var/lib/postgresql/18/main   --old-bindir=/usr/lib/postgresql/17/bin   --new-bindir=/usr/lib/postgresql/18/bin   --link  # Use hard links (faster, less disk space)

# Step 4: Start PostgreSQL 18 and run post-upgrade
sudo systemctl start postgresql@18-main
sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb   --all --analyze-in-stages

The --link mode is the fastest because it hard-links data files instead of copying them, but it makes the upgrade irreversible once the new cluster starts — keep a verified backup first. Running vacuumdb --analyze-in-stages afterwards is not optional; fresh statistics let the planner take advantage of the new I/O behaviour immediately rather than producing surprising plans on the first day in production.

Key Takeaways

PostgreSQL 18 delivers meaningful performance improvements that most applications will benefit from automatically. Async I/O is the headline feature, but virtual generated columns and improved JSON handling will simplify application code in ways that pay dividends long after the upgrade. As a result, plan your upgrade deliberately — the performance gains alone justify the migration effort for most production databases, provided you validate the new I/O path under load first.

Related Reading

• PostgreSQL 17 Features & Production Guide
• SQL Query Optimization for PostgreSQL
• Database Observability & Query Performance

External Resources

• PostgreSQL 18 Official Documentation
• PostgreSQL Wiki — What’s New in 18

In conclusion, the PostgreSQL 18 new features represent an essential topic for modern software development. By applying the patterns and practices covered in this guide, you can build more robust, scalable, and maintainable systems. Start with the fundamentals, measure the async I/O impact on your own workload, and iterate on your configuration to ensure you are getting the most value from these approaches.