PostgreSQL 18 was released on September 25, 2025. The asynchronous I/O subsystem is the headline change for operators. For application developers, the more interesting story sits in five smaller features that retire long-standing workarounds — UUIDv7 generation, virtual generated columns, before/after values in RETURNING, automatic buffer accounting in EXPLAIN ANALYZE, and a unified ACL lookup.
Native UUIDv7 support
uuidv7() returns a UUID version 7 — random in its low bits, timestamp-ordered overall. The ordering is what makes UUIDv7 viable as a primary key: clustered inserts, fewer B-tree page splits, better cache locality than UUIDv4, while keeping the global uniqueness that integer sequences cannot offer.
-- Generate a UUIDv7
SELECT uuidv7();
-- Create a table using UUIDv7 as primary key
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT uuidv7(),
customer_id INT,
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT NOW()
);The uuid-ossp extension never shipped UUIDv7. The few extensions that did were rarely available on managed PostgreSQL services. Most teams generated UUIDv7 in the application layer instead. Native support removes that out-of-database step.
VIRTUAL generated columns
- Commit: 83ea6c540
- Docs: https://www.postgresql.org/docs/18/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-STORED
PostgreSQL 12 introduced generated columns with STORED only. Adding one rewrote the entire table and consumed disk for every derived value.
PostgreSQL 18 adds VIRTUAL and makes it the default. Virtual columns compute on read, not on write — the behavior most other major databases already use.
-- Create table with virtual generated column
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
salary DECIMAL(10,2),
-- VIRTUAL is the default and can be omitted
annual_salary DECIMAL(12,2) GENERATED ALWAYS AS (salary * 12) VIRTUAL
);
-- Insert data (generated columns are computed automatically)
INSERT INTO employees (first_name, last_name, salary) VALUES
('John', 'Doe', 5000.00),
('Jane', 'Smith', 6500.00);
SELECT first_name, last_name, salary, annual_salary
FROM employees;Pick VIRTUAL for cheap expressions on lightly read columns. Pick STORED when the expression is expensive enough that paying for it on every read costs more than the disk it occupies. The hard limit most schemas will hit first: VIRTUAL columns cannot be indexed.
PostgreSQL 18 also adds logical replication support for stored generated columns (commit, docs). Virtual columns remain replication-incompatible.
OLD and NEW values in RETURNING
RETURNING now exposes old and new aliases. A single statement returns the row before and after the change — no second query, no trigger, no race window between them.
UPDATE
-- Update prices and see both old and new values
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;INSERT ... ON CONFLICT
-- Upsert with change tracking
INSERT INTO products (name, price) VALUES ('Widget', 25.00)
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 is_new_record;DELETE
-- Track what was deleted
DELETE FROM products
WHERE price < 10.00
RETURNING
old.name AS deleted_product,
old.price AS deleted_price;Audit pipelines that previously paired a SELECT with the mutating statement collapse into the mutating statement alone.
EXPLAIN ANALYZE with BUFFERS by default
EXPLAIN ANALYZE now reports buffer usage without the explicit BUFFERS option. The information that separates a slow plan from a slow disk is in the default output.
postgres=# EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
-- PostgreSQL 18: BUFFERS included automatically
Seq Scan on users (cost=0.00..18.75 rows=125 width=64) (actual time=0.029..0.087 rows=178 loops=1)
Filter: (age > 25)
Rows Removed by Filter: 89
Buffers: shared hit=12
Planning:
Buffers: shared hit=156 read=3
I/O Timings: shared read=0.024
Planning Time: 0.512 ms
Execution Time: 0.734 mspg_get_acl() for permission inspection
- Commit: 4564f1ceb
- Docs: https://www.postgresql.org/docs/18/functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE
PostgreSQL 18 introduces the pg_get_acl() function for programmatically retrieving Access Control Lists (ACLs) for database objects. If you've ever spent time debugging the infamous ERROR 42501: permission denied messages, you'll appreciate having a unified way to inspect object privileges.
Previously, troubleshooting permissions required querying different system catalogs (pg_class, pg_proc, pg_namespace, pg_attribute) depending on the object type, each with their own ACL format.
The pg_get_acl() function provides a unified interface for retrieving ACLs from any database object, eliminating the need to remember which catalog to query for different object types.
postgres=# SELECT
(pg_identify_object(s.classid,s.objid,s.objsubid)).*,
pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl
FROM pg_catalog.pg_shdepend AS s
JOIN pg_catalog.pg_database AS d
ON d.datname = current_database() AND
d.oid = s.dbid
JOIN pg_catalog.pg_authid AS a
ON a.oid = s.refobjid AND
s.refclassid = 'pg_authid'::regclass
WHERE s.deptype = 'a';
-[ RECORD 1 ]-----------------------------------------
type | table
schema | public
name | testtab
identity | public.testtab
acl | {postgres=arwdDxtm/postgres,foo=r/postgres}While not as headline-grabbing as the new AIO, these quality-of-life improvements make day-to-day development easier—and clearer interfaces like pg_get_acl() benefit not only human developers, but AI agents as well.