PostgreSQL 18 arrived in September 2025 (with the latest patch release, 18.4, landing May 14, 2026), and it’s one of those releases that changes how you think about writing queries. Not because of one headline feature, but because a cluster of smaller improvements add up to a meaningfully different day-to-day experience.
This isn’t a comprehensive changelog — the full release notes span hundreds of entries. Instead, these are the features that will actually change the SQL you write, the indexes you create, and the constraints you rely on.
Skip Scan: Your Composite Indexes Just Got Smarter
If you’ve ever created a composite B-tree index on (tenant_id, status, created_at) and then watched the planner ignore it for a query that filters only on status, you’ve hit one of PostgreSQL’s longstanding limitations. The index requires a leading-column equality condition to be efficient — or at least, it used to.
PostgreSQL 18 introduces skip scan for B-tree indexes. The planner can now “skip” through the leading columns of a composite index to find distinct values, then search within each distinct value for matching rows on the later columns. This is the same technique that Oracle and SQLite have offered for years.
-- Composite index
CREATE INDEX idx_orders_tenant_status_date
ON orders (tenant_id, status, created_at);
-- This query now uses the index via skip scan
-- (no filter on tenant_id at all)
SELECT * FROM orders
WHERE status = 'pending'
AND created_at > now() - interval '7 days';
-- EXPLAIN output shows:
-- Index Scan using idx_orders_tenant_status_date
-- Index Cond: (status = 'pending'::text)
-- Skip scan: trueBefore PostgreSQL 18, this query would fall back to a sequential scan or require a separate index on (status, created_at). Skip scan is most effective when the leading column has low cardinality relative to the total row count — exactly the case with tenant IDs in multi-tenant systems, or status columns in workflow tables.
This doesn’t mean you should stop thinking about column order in your indexes. Skip scan has a cost — the planner has to probe for each distinct leading value. But it significantly reduces the number of “dead index” situations where a well-intentioned composite index goes unused.
Virtual Generated Columns: Computed on Read, Not on Write
PostgreSQL has supported generated columns since version 12, but they were always stored — the computed value was written to disk alongside the base columns. PostgreSQL 18 introduces virtual generated columns and makes them the new default.
CREATE TABLE products (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
price numeric(10, 2) NOT NULL,
tax_rate numeric(4, 4) NOT NULL DEFAULT 0.2400,
-- Virtual (computed on read, NOT stored on disk)
price_with_tax numeric(10, 2) GENERATED ALWAYS AS
(round(price * (1 + tax_rate), 2)),
-- Stored (computed on write, occupies disk space)
search_name text GENERATED ALWAYS AS (lower(name)) STORED
);The key difference: virtual generated columns cost zero storage. They’re computed at query time, which means they always reflect the current values of their source columns and don’t bloat your table. For expensive computations or columns you index frequently, STORED still makes sense. But for derived values you only occasionally read, virtual is the right default — and now PostgreSQL agrees.
If you have existing GENERATED ALWAYS AS ... STORED columns, nothing changes — your stored columns remain stored. But new generated columns without an explicit keyword default to virtual.
uuidv7(): Time-Ordered UUIDs, Finally Built In
UUIDv4 is random. That’s great for uniqueness but terrible for B-tree index locality — every insert goes to a random page, causing fragmentation and poor cache utilization. UUIDv7 fixes this by encoding a millisecond-precision timestamp in the first 48 bits, so new UUIDs sort after old ones.
-- Before: random UUIDv4 (poor index locality)
CREATE TABLE events (
id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);
-- After: time-ordered UUIDv7 (excellent index locality)
CREATE TABLE events (
id uuid PRIMARY KEY DEFAULT uuidv7()
);
-- You can also generate them explicitly
INSERT INTO events (id, payload)
VALUES (uuidv7(), '{"action": "click"}');
-- Explicit UUIDv4 still available via alias
SELECT uuidv4();If you’re currently using a timestamp + random approach or pulling in an extension like pg_uuidv7, the built-in uuidv7() eliminates that dependency entirely. For high-throughput insert workloads, switching from UUIDv4 to UUIDv7 can measurably reduce WAL volume and improve vacuum behavior because inserts cluster at the end of the index rather than scattering across it.
OLD and NEW in RETURNING: Compare Before and After
The RETURNING clause has always been useful for getting back the values that were actually written. But you couldn’t see what changed — only the result. PostgreSQL 18 adds OLD and NEW qualifiers to RETURNING, letting you compare before and after states in a single statement.
-- See what changed in an UPDATE
UPDATE accounts
SET balance = balance - 100
WHERE id = 'a1b2c3'
RETURNING
OLD.balance AS balance_before,
NEW.balance AS balance_after,
(OLD.balance - NEW.balance) AS amount_debited;
-- Capture deletions with full audit context
DELETE FROM sessions
WHERE last_active < now() - interval '30 days'
RETURNING
OLD.user_id,
OLD.session_token,
OLD.last_active;
-- MERGE with full visibility
MERGE INTO inventory AS target
USING staging AS source
ON target.sku = source.sku
WHEN MATCHED THEN
UPDATE SET quantity = source.quantity
WHEN NOT MATCHED THEN
INSERT (sku, quantity) VALUES (source.sku, source.quantity)
RETURNING
COALESCE(OLD.quantity, 0) AS previous_qty,
NEW.quantity AS new_qty,
CASE WHEN OLD IS NULL THEN 'inserted'
ELSE 'updated' END AS operation;This eliminates a common pattern where you'd wrap an UPDATE in a CTE that first SELECTs the old values. Fewer round trips, less boilerplate, and the semantics are atomic — you're guaranteed the OLD and NEW values come from the same row modification.
Temporal Constraints: Preventing Overlapping Ranges
If you've ever needed to enforce "no two subscriptions can overlap for the same user" or "an employee can't hold two positions simultaneously," you know this requires trigger gymnastics or application-level locking. PostgreSQL 18 adds WITHOUT OVERLAPS constraints, which let the database enforce temporal uniqueness natively.
CREATE TABLE subscriptions (
user_id int NOT NULL,
plan text NOT NULL,
period tstzrange NOT NULL,
-- No two subscriptions for the same user+plan can overlap
PRIMARY KEY (user_id, plan, period WITHOUT OVERLAPS)
);
-- This INSERT now fails:
INSERT INTO subscriptions VALUES
(1, 'pro', '[2026-01-01, 2026-06-01)'::tstzrange);
-- Duplicate key: user_id=1, plan='pro' overlaps existing periodForeign keys get a similar treatment with the PERIOD keyword, enabling temporal referential integrity — ensuring that a child record's time range falls within the parent's active period. This brings PostgreSQL closer to the SQL:2011 temporal table standard and is a significant simplification for any system that tracks validity periods, bitemporal data, or versioned records.
NOT ENFORCED Constraints: Safety Nets for Migrations
Sometimes you want the database to know about a constraint without immediately breaking your existing data. PostgreSQL 18 lets you create CHECK and foreign key constraints as NOT ENFORCED:
-- Document the rule without breaking existing violations
ALTER TABLE orders
ADD CONSTRAINT check_positive_total
CHECK (total >= 0) NOT ENFORCED;
-- The planner can still use this for query optimization
-- once you've cleaned up the data:
ALTER TABLE orders
ALTER CONSTRAINT check_positive_total ENFORCED;This is particularly useful during large-scale data migrations where you want to declare the target schema's integrity rules early, then progressively clean data and enable enforcement — without the constraint blocking your migration pipeline.
Under the Hood: AIO and Self-Join Elimination
Two performance features deserve a mention even though they require zero code changes. First, PostgreSQL 18 introduces an asynchronous I/O subsystem that allows backends to queue multiple read requests simultaneously, improving throughput for sequential scans, bitmap heap scans, and vacuum operations. It's controlled by the io_method setting and works transparently once enabled.
Second, the optimizer now performs self-join elimination. If your query joins a table to itself unnecessarily — a pattern that ORM-generated SQL frequently produces — the planner recognizes this and removes the redundant join. It's a silent win for anyone using query builders that aren't always efficient with their JOIN clauses.
Migration Notes
A few breaking changes to be aware of when upgrading:
- Data checksums are now enabled by default in
initdb. When upgrading withpg_upgrade, use--no-data-checksumsif your old cluster doesn't have checksums enabled. - MD5 password authentication is deprecated and will be removed in a future release. Migrate to SCRAM-SHA-256 before upgrading.
VACUUMandANALYZEnow process inheritance children by default. Use the newONLYkeyword to process just the parent table.pg_upgradenow preserves optimizer statistics (though extended statistics are not yet carried over), which means your query plans should be good immediately after upgrade — no need to wait for auto-analyze to repopulate most stats.
Wrapping Up
PostgreSQL 18 is a developer-focused release. Skip scan reduces the need for carefully curated index sets. Virtual generated columns and uuidv7() clean up common patterns. OLD/NEW in RETURNING eliminates boilerplate CTEs. Temporal constraints solve a class of problems that previously required triggers or application-level enforcement.
The full release notes are worth a read — there are improvements to logical replication, psql pipeline support, parallel GIN index builds, and per-backend I/O statistics that make production debugging significantly easier. Check the official PostgreSQL 18 release notes for the complete picture.