vvek

Four Postgres 18 features you can use in your next migration: uuidv7(), RETURNING OLD/NEW, skip scan and virtual columns

A practical look at four Postgres 18 additions: time-ordered UUIDs, old and new values in RETURNING, skip scans on multicolumn indexes and virtual generated columns.

Postgres 18 came out in September 2025, and a year on, many teams are only now upgrading. If you are one of them, the release notes are long, so here are four additions that remove code you would otherwise write yourself. (For the newest major version, see our look at the PostgreSQL 19 release candidate.)

uuidv7(): IDs that sort by time

Random UUIDs are convenient, but they scatter inserts across the whole primary key index. Every insert lands in a different page, so the index stays "warm" only if it fits in memory. A UUID version 7 starts with a timestamp, so new values land near the end of the index, much like a serial number would.

Postgres 18 adds a built-in function for it, and an explicit uuidv4() alias for the old random kind:

CREATE TABLE events (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  kind text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (kind) VALUES ('signup') RETURNING id;

What you gain: better locality for inserts, and ids that roughly sort by creation time, which makes "latest first" pagination cheaper. What you give up: the id now leaks an approximate creation time. If that matters for a public identifier, keep a random id for outside use.

Note

Existing rows keep their old ids. Changing the default only affects new rows, so you do not need to rewrite anything.

RETURNING OLD and NEW

RETURNING has long been useful, but it gave you the new row for inserts and updates and the old row for deletes. If you wanted both sides of an update, you needed a trigger or a second query.

In 18, you can name old and new directly. The documentation's own example shows a price change:

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, old.price AS old_price, new.price AS new_price,
            new.price - old.price AS price_change;

The same words work in INSERT, DELETE and MERGE. For an insert, the old values are normally NULL; for a delete, the new ones are. And for an upsert (INSERT ... ON CONFLICT DO UPDATE), the old values are non-NULL exactly when a conflict happened, which gives you a neat way to tell "created" from "updated" in one round trip:

INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value
RETURNING old.value IS NULL AS created, new.value;

Use cases that come to mind: writing an audit row with before and after values in the same statement, returning a diff to an API client, and checking that an update changed what you expected. If your application already writes audit records, this can simplify them; just keep the write in the same transaction.

Skip scan on multicolumn indexes

Take an index on (region, created_at). A query that filters on created_at alone used to be a poor fit for it, because the index is sorted by region first. Many teams ended up with a second index just for that.

Postgres 18 allows the planner to "skip" through the index: it can look at each distinct value of the leading column and do a narrow lookup on the later column in each one. The notes describe it as allowing multicolumn B-tree indexes to be used more often when early columns have no restriction, or only non-equality ones, and there are useful restrictions on later columns.

You do not switch anything on. What you do is check:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at >= now() - interval '1 day';

In 18, EXPLAIN ANALYZE reports the number of index lookups per index scan node, so you can see how many probes a skip scan made. If you are new to reading plans, our guide to reading EXPLAIN ANALYZE covers the basics.

A word of caution, from general reasoning rather than the notes: skip scan should pay off when the leading column has few distinct values. With thousands of distinct leading values, the planner will have to do thousands of small lookups, and a dedicated index may still win. Measure before dropping an index you rely on. Our post on indexes and their write cost explains why removing one is not free to undo either.

Virtual generated columns

Generated columns used to be stored: the value was computed on write and took disk space. In 18 you can make them virtual, and virtual is now the default. A virtual column computes its value when it is read.

CREATE TABLE invoices (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  net_minor bigint NOT NULL,
  tax_minor bigint NOT NULL,
  total_minor bigint GENERATED ALWAYS AS (net_minor + tax_minor) -- virtual
);

-- the old behaviour, if you want it
ALTER TABLE invoices ADD COLUMN total_stored bigint
  GENERATED ALWAYS AS (net_minor + tax_minor) STORED;

Virtual columns cost no storage and no write time; you pay a little on each read. Cheap expressions like the sum above are a good fit. For an expensive expression you read often, or one you want to index, STORED is still available. The release notes do not list the limits of virtual columns, so check the "generated columns" section of the CREATE TABLE page before you rely on them, for example in an index or a constraint.

Upgrade notes worth a minute

  • Statistics survive pg_upgrade. The notes say optimizer statistics are now preserved, which avoids the slow period of poor plans after an upgrade. Extended statistics are not preserved, so run ANALYZE for tables that use them. A --no-statistics option turns the behaviour off.
  • Checksums are on by default for new clusters made with initdb. When upgrading a cluster that has none, the old and new clusters must match, so initdb --no-data-checksums is the way to build the target.
  • Dump and restore, pg_upgrade or logical replication is still required to move from an earlier major version.

If you also change schemas as part of the move, keep the lock rules in mind; our guide to schema changes without downtime covers them.

A small adoption plan

  1. Change new tables to DEFAULT uuidv7() and leave old tables alone.
  2. Replace one trigger or follow-up query with RETURNING old.*, new.*-style columns where you need before and after values.
  3. After upgrading, run EXPLAIN ANALYZE on your slowest multicolumn-index queries and look for the new lookup counts.
  4. Use virtual columns for cheap derived values; keep STORED where reads are hot.

None of these needs a rewrite. They are the kind of change you make one table at a time, as you touch it.

Sources

Keep reading

A short email when we publish. Confirm once, unsubscribe any time.