Lesson 19 intermediate

What’s New in PostgreSQL 18

Every important change in PostgreSQL 18 in one place, tried on Orbit: uuidv7, virtual generated columns, OLD and NEW in RETURNING, temporal keys with WITHOUT OVERLAPS, NOT ENFORCED and NOT VALID constraints, asynchronous I/O, B-tree skip scan, OAuth login, faster upgrades that keep statistics, data checksums by default and new psql features.

PostgreSQL 18 was released on 25 September 2025 and is the current major version. Earlier lessons already used some of its features where they fit; this lesson collects all the changes worth knowing, grouped by who benefits, with examples on the Orbit database.

New SQL features

uuidv7()

Time-ordered UUIDs are now built in. They keep the advantages of UUIDs (generated anywhere, hard to guess the next one) while inserting into B-tree indexes almost as efficiently as an identity column (lesson 5):

CREATE TABLE api_tokens (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id    bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now()
);
SELECT uuid_extract_version(uuidv7()) AS version;
CREATE TABLE
 version
---------
       7
(1 row)

uuid_extract_timestamp(id) recovers the moment a version-7 id was created. uuidv4() is a new, explicit name for gen_random_uuid().

Virtual generated columns

Generated columns can now be virtual: computed when you read them, stored nowhere. Virtual is the new default when you leave out STORED, and adding one is instant because nothing is written to the table:

ALTER TABLE notes ADD COLUMN title_length int GENERATED ALWAYS AS (char_length(title)) VIRTUAL;
SELECT id, title, title_length FROM notes WHERE id <= 3 ORDER BY id;
ALTER TABLE
 id |      title       | title_length
----+------------------+--------------
  1 | Shopping list    |           13
  2 | Deploy checklist |           16
  3 | Book ideas       |           10
(3 rows)

Use STORED, as lesson 13 did for the search vector, when the value is expensive to compute or must be indexed.

OLD and NEW in RETURNING

RETURNING can now show values from before and after the change in INSERT, UPDATE, DELETE and MERGE. Perfect for audit messages and APIs that report what changed. Ana upgrades her plan:

UPDATE users SET plan = 'pro' WHERE id = 1
RETURNING old.plan AS old_plan, new.plan AS new_plan;
 old_plan | new_plan
----------+----------
 free     | pro
(1 row)

UPDATE 1

In an INSERT ... ON CONFLICT DO UPDATE, old is NULL for rows that were newly inserted, so one statement tells you which rows were created and which were updated.

Temporal keys: WITHOUT OVERLAPS and PERIOD

Primary and unique keys can now include a range that must not overlap for the same key, which models history directly. Orbit keeps a history of each user’s plan:

CREATE TABLE plan_history (
  user_id      bigint NOT NULL REFERENCES users (id),
  valid_during tstzrange NOT NULL,
  plan         plan NOT NULL,
  PRIMARY KEY (user_id, valid_during WITHOUT OVERLAPS)
);

The same user may appear many times, but never with overlapping periods. It needs the btree_gist extension installed in lesson 10. Foreign keys can reference such keys over time with PERIOD, ensuring a child row’s period is covered by its parent’s.

More flexible constraints

-- documented but not checked by the server
ALTER TABLE notes ADD CONSTRAINT notes_body_not_huge CHECK (char_length(body) < 1000000) NOT ENFORCED;

-- add a NOT NULL rule without scanning the table, validate later
ALTER TABLE users ADD COLUMN locale text;
UPDATE users SET locale = 'en';
ALTER TABLE users ADD CONSTRAINT users_locale_not_null NOT NULL locale NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_locale_not_null;

NOT NULL constraints are now real, named entries in pg_constraint, so they can be added NOT VALID and validated later, just like the check and foreign key constraints in lesson 10.

Smaller additions

SELECT array_sort(ARRAY[3, 1, 2]) AS sorted, array_reverse(ARRAY[1, 2, 3]) AS reversed;
 sorted  | reversed
---------+----------
 {1,2,3} | {3,2,1}
(1 row)
  • casefold() performs full Unicode case folding for case-insensitive comparisons, and the new builtin collation PG_UNICODE_FAST gives fast, version-stable Unicode sorting.
  • crc32() and crc32c() compute checksums; min() and max() work on arrays and composite types.
  • COPY ... WITH (ON_ERROR ignore, REJECT_LIMIT 100) skips bad rows but fails if there are too many.
  • VACUUM ONLY and ANALYZE ONLY process a partitioned or inheritance parent without its children.

Performance

Asynchronous I/O

The biggest internal change in years: Postgres can now issue many reads at once instead of waiting for each one. Sequential scans, bitmap heap scans and vacuum benefit most, especially on cloud storage with high latency:

SHOW io_method;
 io_method
-----------
 worker
(1 row)

worker (the default) uses background I/O worker processes and works everywhere; io_uring uses the Linux kernel interface directly; sync restores the old behaviour. The pg_aios view shows I/O in flight.

B-tree skip scan

A multicolumn index can now help queries that do not filter on its first column. Orbit’s unique index on (user_id, title) from lesson 10 can serve WHERE title = 'Book ideas': Postgres skips through the few distinct user_id values instead of reading the whole index. It works best when the leading column has few distinct values.

Smarter planning and maintenance

  • OR conditions and IN lists use indexes more effectively, and unneeded self-joins are removed.
  • Autovacuum freezes pages more eagerly during normal vacuums, which smooths out the big anti-wraparound runs, and autovacuum_vacuum_max_threshold caps how many dead rows a huge table may collect.
  • EXPLAIN ANALYZE now shows buffer usage by default, plus index lookup counts per scan.

Security

  • OAuth 2.0 authentication: a new oauth method in pg_hba.conf lets users log in with tokens from an identity provider, through a validator module configured with oauth_validator_libraries.
  • MD5 passwords are deprecated: CREATE ROLE and ALTER ROLE warn when an MD5 password is set. Move every role to SCRAM (lesson 15) before a future release removes MD5.
  • Data checksums are on by default in new clusters created by initdb, so storage corruption is detected instead of silently returned.
  • New connection options for TLS 1.3 cipher suites (ssl_tls13_ciphers), and cancel keys are longer.

Operations and upgrades

  • pg_upgrade keeps planner statistics, so an upgraded cluster has good plans immediately instead of after a full ANALYZE. It also checks databases in parallel with --jobs and offers --swap, which moves data directories for the fastest switch.
  • vacuumdb --missing-stats-only analyzes only what has no statistics, the natural follow-up to an upgrade.
  • pg_dump and pg_restore gain --no-policies, --statistics-only, --no-statistics, --no-schema and --no-data.
  • idle_replication_slot_timeout invalidates slots that stay idle too long, protecting the disk (lesson 18).
  • log_lock_failures logs NOWAIT lock failures, and log_connections can log each connection step separately.
  • Monitoring views report more: I/O in bytes in pg_stat_io, vacuum and analyze time per table in pg_stat_all_tables, and per-backend I/O and WAL statistics.

Tip: Because initdb now enables checksums, pg_upgrade from an older cluster without checksums needs the new cluster created with --no-data-checksums. The --check run tells you.

psql

  • \conninfo prints a table with much more detail: server version, protocol, TLS settings and more.
  • Pipelining: \startpipeline, \sendpipeline, \syncpipeline and \endpipeline send several queries without waiting for each result, cutting round trips on slow links.
  • \dx shows each extension’s default version, so you can see which ones need ALTER EXTENSION ... UPDATE.
  • WATCH_INTERVAL sets the default delay for \watch, and %P in the prompt shows the pipeline status.

Should you upgrade?

For new projects, start on 18. For existing clusters on 13 or 14, plan the move now: version 13 is out of support and 14 reaches end of life in November 2026. From 15, 16 or 17, the upgrade is low risk with pg_upgrade --check first and a tested backup (lessons 16 and 18). Always read the “Migration to Version 18” section of the release notes for behaviour changes; the most visible ones are the checksum default, the MD5 deprecation and VACUUM/ANALYZE now processing inheritance children unless you say ONLY.

Quick reference

FeatureExample
Time-ordered UUIDDEFAULT uuidv7()
Virtual columnGENERATED ALWAYS AS (expr) VIRTUAL
Before and after valuesRETURNING old.col, new.col
No overlapping periodsPRIMARY KEY (id, during WITHOUT OVERLAPS)
Unchecked constraintCHECK (...) NOT ENFORCED
NOT NULL without a scanADD CONSTRAINT c NOT NULL col NOT VALID
Async I/Oio_method = worker / io_uring / sync
Upgrade keeps statspg_upgrade ... --swap --jobs 8
Fill missing statsvacuumdb --all --missing-stats-only --analyze-in-stages
Sort an arrayarray_sort(arr)

Exercise

You add a computed column for a value that is cheap to calculate and never filtered on. Which PostgreSQL 18 option avoids storing it?

  1. GENERATED ALWAYS AS (…) STORED
  2. A trigger that fills the column
  3. GENERATED ALWAYS AS (…) VIRTUAL, the new default
  4. A materialized view
Show answer

Answer: C. Virtual generated columns are computed on read and take no space, and adding one does not rewrite the table. Use STORED when you need to index the value or it is expensive to compute.

Exercise

What does UPDATE … RETURNING old.plan, new.plan return in PostgreSQL 18?

  1. An error, because RETURNING can only show the new row
  2. The plan before the update and the plan after it, for each changed row
  3. Only rows whose plan did not change
  4. The plan values of the previous transaction
Show answer

Answer: B. RETURNING now exposes both versions of the row, which removes the need for a separate SELECT before the update.

Exercise

Why does an upgraded PostgreSQL 18 cluster perform well right after pg_upgrade, when older versions often ran slowly until ANALYZE finished?

  1. Asynchronous I/O warms the cache automatically
  2. pg_upgrade rebuilds every index
  3. Skip scan replaces the need for statistics
  4. pg_upgrade now carries over the planner statistics from the old cluster
Show answer

Answer: D. With statistics preserved, the planner can choose good plans immediately. Extended statistics are not carried over, which vacuumdb –missing-stats-only fills in.

Key takeaways

  • PostgreSQL 18 adds uuidv7(), virtual generated columns, OLD/NEW in RETURNING, temporal keys and more flexible constraints.
  • Asynchronous I/O and B-tree skip scan speed up reads without any change to your queries.
  • OAuth login, checksums by default and the MD5 deprecation strengthen security.
  • pg_upgrade keeps statistics and gains --swap, making major upgrades faster and smoother.
  • Plan upgrades from 13 and 14 now, and read the migration notes before you do.

FAQ

Do I need to change my application to benefit from asynchronous I/O?

No. It works inside the server for scans and vacuum. Try io_method = io_uring on recent Linux kernels and compare with the default worker under your real workload.

Will virtual generated columns replace stored ones?

No, they complement them. Virtual columns cost nothing to store but are computed on every read and cannot be indexed; stored columns are the opposite.

Is PostgreSQL 19 out?

Not yet as this course is written: version 19 is in release-candidate testing, with the final release expected soon. Everything in this course keeps working on it; check its release notes for the new features once it ships.

Does uuidv7 leak information?

It reveals when the id was created, to the millisecond. If creation time is sensitive, keep random version-4 UUIDs for public identifiers.

Where can I read the full list of changes?

The official release notes at postgresql.org/docs/18/release-18.html list every change, including the “Migration to Version 18” section with incompatibilities. Read it before upgrading production.

↑ ↓ navigate Enter open Esc close