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 collationPG_UNICODE_FASTgives fast, version-stable Unicode sorting.crc32()andcrc32c()compute checksums;min()andmax()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 ONLYandANALYZE ONLYprocess 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
ORconditions andINlists 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_thresholdcaps how many dead rows a huge table may collect. EXPLAIN ANALYZEnow shows buffer usage by default, plus index lookup counts per scan.
Security
- OAuth 2.0 authentication: a new
oauthmethod inpg_hba.conflets users log in with tokens from an identity provider, through a validator module configured withoauth_validator_libraries. - MD5 passwords are deprecated:
CREATE ROLEandALTER ROLEwarn 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_upgradekeeps planner statistics, so an upgraded cluster has good plans immediately instead of after a fullANALYZE. It also checks databases in parallel with--jobsand offers--swap, which moves data directories for the fastest switch.vacuumdb --missing-stats-onlyanalyzes only what has no statistics, the natural follow-up to an upgrade.pg_dumpandpg_restoregain--no-policies,--statistics-only,--no-statistics,--no-schemaand--no-data.idle_replication_slot_timeoutinvalidates slots that stay idle too long, protecting the disk (lesson 18).log_lock_failureslogsNOWAITlock failures, andlog_connectionscan log each connection step separately.- Monitoring views report more: I/O in bytes in
pg_stat_io, vacuum and analyze time per table inpg_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
\conninfoprints a table with much more detail: server version, protocol, TLS settings and more.- Pipelining:
\startpipeline,\sendpipeline,\syncpipelineand\endpipelinesend several queries without waiting for each result, cutting round trips on slow links. \dxshows each extension’s default version, so you can see which ones needALTER EXTENSION ... UPDATE.WATCH_INTERVALsets the default delay for\watch, and%Pin 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
| Feature | Example |
|---|---|
| Time-ordered UUID | DEFAULT uuidv7() |
| Virtual column | GENERATED ALWAYS AS (expr) VIRTUAL |
| Before and after values | RETURNING old.col, new.col |
| No overlapping periods | PRIMARY KEY (id, during WITHOUT OVERLAPS) |
| Unchecked constraint | CHECK (...) NOT ENFORCED |
| NOT NULL without a scan | ADD CONSTRAINT c NOT NULL col NOT VALID |
| Async I/O | io_method = worker / io_uring / sync |
| Upgrade keeps stats | pg_upgrade ... --swap --jobs 8 |
| Fill missing stats | vacuumdb --all --missing-stats-only --analyze-in-stages |
| Sort an array | array_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?
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?
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?
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/NEWinRETURNING, 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_upgradekeeps 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.