# What's New in PostgreSQL 18: A Developer's Perspective

> PostgreSQL 18 features that change how application developers write and ship code.

Tianzhou | 2026-04-30 | Source: https://www.bytebase.com/blog/what-is-new-in-postgres-18-for-developer/

---

PostgreSQL 18 was [released on September 25, 2025](https://www.postgresql.org/about/news/postgresql-18-released-3142/). 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

- Commit: [78c5e141e](https://postgr.es/c/78c5e141e)
- Docs: https://www.postgresql.org/docs/18/datatype-uuid.html

`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.

```sql
-- 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](https://postgr.es/c/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.

```sql
-- 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](https://postgr.es/c/745217a05), [docs](https://www.postgresql.org/docs/18/logical-replication-gencols.html)). Virtual columns remain replication-incompatible.

## OLD and NEW values in RETURNING

- Commit: [80feb727c](https://postgr.es/c/80feb727c)
- Docs: https://www.postgresql.org/docs/current/dml-returning.html

`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**

```sql
-- 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**

```sql
-- 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**

```sql
-- 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

- Commit: [c2a4078eb](https://postgr.es/c/c2a4078eb)
- Docs: https://www.postgresql.org/docs/current/sql-explain.html

`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.

```sql
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 ms
```

## pg_get_acl() for permission inspection

- Commit: [4564f1ceb](https://postgr.es/c/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.

```sql
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.

## Further Readings

- [Postgres 18 full release notes](https://www.postgresql.org/docs/18/release-18.html)
- [DBA's perspective about Postgres 18](/blog/what-is-new-in-postgres-18)