What's New in PostgreSQL 18

Short version: PostgreSQL 18 is worth upgrading for six things you will actually use. uuidv7() gives you time-sortable UUIDs natively, so your primary keys stop causing index page splits. Generated columns can be VIRTUAL and now are by default, so a computed column costs no disk and no write overhead. OLD and NEW work in RETURNING, which deletes the extra SELECT around every mutation. WITHOUT OVERLAPS makes non-overlapping time ranges a declarative constraint. NOT NULL ... NOT VALID finally gets the two-phase treatment CHECK and FOREIGN KEY already had. And B-tree skip scan lets a composite index serve queries that do not filter on its leading column.

PostgreSQL 18 dropped in September 2025, and it's one of the most feature-rich major releases in years. I'm not going to list every change from the release notes. Instead, here are the features with the most practical impact for application developers, with SQL examples you can run yourself.

Set Up PostgreSQL 18

Every example runs on a stock PostgreSQL 18 install, no extensions. The quickest managed option is Layerbase Cloud: create a PostgreSQL 18 instance and you get TLS, backups, and connection details in the Quick Connect panel, with the same stock PostgreSQL behavior these examples use.

If you'd rather run it on your own machine, the Layerbase CLI (formerly SpinDB) is a CLI for running databases locally. (What is the Layerbase CLI?)

# Install the Layerbase CLI
npm i -g layerbase

# Create a PostgreSQL instance named "pg18", start it, and connect
lbase create pg18 --start --connect

No Homebrew formulas, no Docker, no manual configuration. The Layerbase CLI downloads the correct binary for your platform, initializes a data directory, starts the server, and drops you into psql. Paste away.

If you get disconnected or close your terminal, reconnect any time:

lbase connect pg18

Native UUIDv7

The headliner. PostgreSQL 18 adds a built-in uuidv7() function that generates UUID version 7 values. UUIDv7 embeds a timestamp in the first 48 bits, so these IDs are globally unique and chronologically sortable.

SELECT uuidv7();
-- 01936f6e-8b50-7cc2-a174-1dd6b0f37b8f

Use it as a default primary key:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_name TEXT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer_name, total) VALUES ('Alice', 99.99);
INSERT INTO orders (customer_name, total) VALUES ('Bob', 149.50);
INSERT INTO orders (customer_name, total) VALUES ('Charlie', 75.00);

SELECT id, customer_name, total FROM orders ORDER BY id;

Because UUIDv7 values sort by creation time, ORDER BY id gives you insertion order without a separate timestamp column. B-tree indexes stay efficient because new values cluster at the end, avoiding the page splits that random UUIDv4 causes.

PostgreSQL has had gen_random_uuid() (v4) since version 13, and uuid-ossp offers time-based v1, but neither produces time-sorted, globally unique IDs. Before v18, UUIDv7 required application-level generation. Native uuidv7() gives you UUID uniqueness with sequential index performance.

Virtual Generated Columns

Generated columns have existed since version 12, but they were always STORED: the computed value written to disk on every insert and update. PostgreSQL 18 adds VIRTUAL generated columns and makes them the default.

Virtual columns compute at read time. No disk space, no write overhead.

CREATE TABLE line_items (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    product_name TEXT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price)
);

INSERT INTO line_items (product_name, quantity, unit_price) VALUES
    ('Widget', 5, 12.99),
    ('Gadget', 2, 49.99),
    ('Sprocket', 10, 3.50);

SELECT product_name, quantity, unit_price, total FROM line_items;

 product_name | quantity | unit_price | total
--------------+----------+------------+--------
 Widget       |        5 |      12.99 |  64.95
 Gadget       |        2 |      49.99 |  99.98
 Sprocket     |       10 |       3.50 |  35.00

total is never stored on disk. It's computed from quantity * unit_price on every read. You can still use STORED explicitly, but for most computed columns, virtual is the better default.

OLD/NEW in RETURNING

This eliminates the common pattern of running a separate SELECT before or after a mutation to capture previous values. PostgreSQL 18 lets you reference OLD (pre-mutation) and NEW (post-mutation) directly in the RETURNING clause.

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    name TEXT UNIQUE NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Keyboard', 79.99),
    ('Mouse', 29.99),
    ('Monitor', 349.99);

-- Apply a 10% price increase and see old vs. new in one statement
UPDATE products
SET price = price * 1.10
RETURNING
    name,
    OLD.price AS old_price,
    NEW.price AS new_price;

   name   | old_price | new_price
----------+-----------+-----------
 Keyboard |     79.99 |     87.99
 Mouse    |     29.99 |     32.99
 Monitor  |    349.99 |    384.99

This also works with INSERT ... ON CONFLICT (upserts), where OLD values are NULL for newly inserted rows:

INSERT INTO products (name, price) VALUES ('Keyboard', 89.99)
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 was_inserted;

No more CTEs or before/after queries. Audit logging, CDC, and undo features all get simpler.

Temporal Constraints with WITHOUT OVERLAPS

Anyone who's built a booking system knows the pain of enforcing non-overlapping time ranges in application code or trigger logic. PostgreSQL 18 makes this a declarative constraint.

CREATE TABLE room_bookings (
    room_id INT,
    booking_period TSTZRANGE NOT NULL,
    guest_name TEXT NOT NULL,
    PRIMARY KEY (room_id, booking_period WITHOUT OVERLAPS)
);

-- Alice books room 101 for March 1-5
INSERT INTO room_bookings VALUES
    (101, '[2026-03-01, 2026-03-05)', 'Alice');

-- Bob books room 102 for the same dates (different room, no conflict)
INSERT INTO room_bookings VALUES
    (102, '[2026-03-01, 2026-03-05)', 'Bob');

-- Charlie tries to book room 101 for March 3-7 (overlaps Alice)
INSERT INTO room_bookings VALUES
    (101, '[2026-03-03, 2026-03-07)', 'Charlie');
-- ERROR: conflicting key value violates exclusion constraint

The database enforces non-overlapping ranges per room via a GiST index. No triggers, no application-side validation, no race conditions. Works with any range type: TSTZRANGE, DATERANGE, INT4RANGE, etc.

Zero-Downtime NOT NULL with NOT VALID

Adding NOT NULL to an existing column on a large table used to require a full table scan under an AccessExclusiveLock, blocking everything. PostgreSQL 18 lets you split it into two steps:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    name TEXT NOT NULL,
    email TEXT
);

INSERT INTO users (name) VALUES ('Legacy User');

-- Step 1: Add the constraint instantly (no table scan, brief lock)
ALTER TABLE users
    ADD CONSTRAINT users_email_not_null
    NOT NULL email NOT VALID;

-- New inserts must satisfy the constraint immediately
INSERT INTO users (name) VALUES ('Another User');
-- ERROR: null value in column "email" violates not-null constraint

-- But existing rows with NULL are not checked yet.
-- Step 2: Clean up existing NULLs at your own pace
UPDATE users SET email = name || '@unknown.com' WHERE email IS NULL;

-- Step 3: Validate the constraint (table scan, but with a weaker lock)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

Same two-phase pattern that's existed for CHECK and FOREIGN KEY, but NOT NULL didn't support it until now. If you run zero-downtime deployments, this is a big deal.

B-tree Skip Scan

Subtle but impactful. Previously, a composite B-tree index only helped if your query filtered on the leading column(s). PostgreSQL 18 can now skip through distinct values of the leading column to reach what you're actually filtering on.

CREATE TABLE sales (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    region TEXT NOT NULL,
    category TEXT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

CREATE INDEX idx_sales_region_cat_date
    ON sales (region, category, sale_date);

-- In PG 17, this query could NOT use the index (no filter on 'region')
-- In PG 18, skip scan makes this efficient:
SELECT * FROM sales
WHERE category = 'electronics'
    AND sale_date > '2026-01-01';

Most effective when the leading column has low cardinality (few distinct values). In practice, you maintain fewer indexes. One composite index now serves more query patterns than before.

Enhanced EXPLAIN

Small but welcome: EXPLAIN ANALYZE now includes buffer usage statistics by default. No more remembering to write EXPLAIN (ANALYZE, BUFFERS) to see shared buffer hits vs. disk reads.

-- PG 17: EXPLAIN ANALYZE omits buffer stats unless you add BUFFERS
-- PG 18: buffer stats are included by default
EXPLAIN ANALYZE SELECT * FROM sales WHERE category = 'electronics';

The output now includes lines like:

Buffers: shared hit=128 read=45
I/O Timings: shared read=12.34

Buffer usage is the single most useful metric for query performance. Whether a query reads from shared buffers (fast) or disk (slow) tells you more than row counts or cost estimates ever will.

Asynchronous I/O

PostgreSQL 18 introduces an async I/O subsystem that queues multiple read requests instead of waiting for each sequentially. Up to 3x speedups for I/O-bound workloads: large sequential scans, bitmap heap scans, and VACUUM.

-- Check the I/O method
SHOW io_method;
-- sync (traditional), worker (thread pool), io_uring (Linux, fastest)

On Linux, io_uring gives the best performance. On macOS, worker uses a thread pool as a portable alternative. No application changes needed.

What Else Changed

A few more worth mentioning:

• OAuth 2.0 authentication: native OAUTHBEARER support in pg_hba.conf, so clients can authenticate with bearer tokens from identity providers like Okta or Azure AD instead of passwords
• Logical replication improvements: parallel streaming is now the default for new subscriptions, generated columns can be replicated, and conflict logging gives visibility into replication issues
• pg_upgrade --swap and --jobs: parallel consistency checks and directory swapping significantly reduce upgrade downtime
• pg_stat_io reports in bytes: I/O statistics now include byte counts in addition to page counts, with per-backend granularity

FAQ

Do I still need to generate UUIDv7 in my application?

No. uuidv7() is built in as of 18, so DEFAULT uuidv7() on a primary key is all it takes. The value embeds a timestamp in its first 48 bits, which is what makes it globally unique and chronologically sortable at the same time. gen_random_uuid() has been there since 13 but produces v4, which is random and therefore causes the index page splits UUIDv7 avoids.

What is the difference between a virtual and a stored generated column?

A stored column writes the computed value to disk on every insert and update. A virtual column computes at read time, so it costs no disk space and no write overhead. Virtual is the default in 18; STORED is still there when you want the value materialized, for example to index it.

How do I get the previous value of a row I just updated?

Reference OLD in the RETURNING clause. Before 18 that meant a separate SELECT before or after the mutation, with the race condition that implies. NEW gives you the post-mutation value in the same statement.

Can I enforce non-overlapping bookings without a trigger?

Yes, with PRIMARY KEY (room_id, booking_period WITHOUT OVERLAPS). The database enforces it through a GiST index, so there is no trigger, no application-side validation, and no race window. Any range type works.

How do I add NOT NULL to a big table without locking it?

In two phases. ADD CONSTRAINT ... NOT NULL <col> NOT VALID is instant and applies to new writes immediately, then you clean up the existing NULLs at your own pace, then VALIDATE CONSTRAINT does the scan under a weaker lock. This is the same pattern CHECK and FOREIGN KEY have had for years; NOT NULL just did not support it until 18.

What does B-tree skip scan change about my indexes?

It means a composite index can serve a query that does not filter on the leading column, by skipping through that column's distinct values. It works best when the leading column has low cardinality. Practically, one composite index now covers more query patterns, so you maintain fewer of them.

Do I need to change anything to benefit from asynchronous I/O?

No application changes. It queues multiple read requests instead of waiting on each in turn, which is worth up to 3x on I/O-bound work like large sequential scans, bitmap heap scans, and VACUUM. SHOW io_method tells you which mode you are in: io_uring is the fastest on Linux, and worker is the portable thread-pool fallback.

Connect From Your Application

Once you've tried the examples, connect your application to the same instance. The Layerbase CLI gives you a connection string:

lbase url pg18
# postgresql://postgres:postgres@localhost:5432/postgres

Use that URL with any PostgreSQL client library: pg for Node.js, psycopg for Python, pgx for Go, or any ORM that supports PostgreSQL.

Stop when you're done, start when you need it again:

lbase stop pg18
lbase start pg18

The Layerbase CLI manages PostgreSQL and 20 other database engines with the same commands. Need Redis, MongoDB, or DuckDB alongside PostgreSQL? Same CLI. For a hosted PostgreSQL 18 endpoint, use Layerbase Cloud.

For the full list of PostgreSQL 18 changes, see the official release notes.