# What's New in PostgreSQL 18 > PostgreSQL 18 shipped with native UUIDv7, virtual generated columns, OLD/NEW in RETURNING, temporal constraints, skip scan, and zero-downtime NOT NULL. Published: 2026-03-03 Canonical: https://layerbase.com/blog/whats-new-in-postgresql-18 Short version: PostgreSQL 18 is worth upgrading for six things you will actually use. `uuidv7()` gives you time-sortable UUIDs natively, so your primary keys stop causing index page splits. Generated columns can be `VIRTUAL` and now are by default, so a computed column costs no disk and no write overhead. `OLD` and `NEW` work in `RETURNING`, which deletes the extra `SELECT` around every mutation. `WITHOUT OVERLAPS` makes non-overlapping time ranges a declarative constraint. `NOT NULL ... NOT VALID` finally gets the two-phase treatment `CHECK` and `FOREIGN KEY` already had. And B-tree skip scan lets a composite index serve queries that do not filter on its leading column. PostgreSQL 18 dropped in September 2025, and it's one of the most feature-rich major releases in years. I'm not going to list every change from the release notes. Instead, here are the features with the most practical impact for application developers, with SQL examples you can run yourself. ## Set Up PostgreSQL 18 Every example runs on a stock PostgreSQL 18 install, no extensions. The quickest managed option is [Layerbase Cloud](https://cloud.layerbase.com/create?engine=postgresql): create a PostgreSQL 18 instance and you get TLS, backups, and connection details in the Quick Connect panel, with the same stock PostgreSQL behavior these examples use. If you'd rather run it on your own machine, [the Layerbase CLI](https://layerbase.com/docs/cli) (formerly SpinDB) is a CLI for running databases locally. ([What is the Layerbase CLI?](https://layerbase.com/docs/cli)) ```bash # Install the Layerbase CLI npm i -g layerbase # Create a PostgreSQL instance named "pg18", start it, and connect lbase create pg18 --start --connect ``` No Homebrew formulas, no Docker, no manual configuration. The Layerbase CLI downloads the correct binary for your platform, initializes a data directory, starts the server, and drops you into `psql`. Paste away. If you get disconnected or close your terminal, reconnect any time: ```bash lbase connect pg18 ``` ## Native UUIDv7 The headliner. PostgreSQL 18 adds a built-in `uuidv7()` function that generates UUID version 7 values. UUIDv7 embeds a timestamp in the first 48 bits, so these IDs are globally unique _and_ chronologically sortable. ```sql SELECT uuidv7(); -- 01936f6e-8b50-7cc2-a174-1dd6b0f37b8f ``` Use it as a default primary key: ```sql CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT uuidv7(), customer_name TEXT NOT NULL, total DECIMAL(10, 2) NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); INSERT INTO orders (customer_name, total) VALUES ('Alice', 99.99); INSERT INTO orders (customer_name, total) VALUES ('Bob', 149.50); INSERT INTO orders (customer_name, total) VALUES ('Charlie', 75.00); SELECT id, customer_name, total FROM orders ORDER BY id; ``` Because UUIDv7 values sort by creation time, `ORDER BY id` gives you insertion order without a separate timestamp column. B-tree indexes stay efficient because new values cluster at the end, avoiding the page splits that random UUIDv4 causes. PostgreSQL has had `gen_random_uuid()` (v4) since version 13, and `uuid-ossp` offers time-based v1, but neither produces time-sorted, globally unique IDs. Before v18, UUIDv7 required application-level generation. Native `uuidv7()` gives you UUID uniqueness with sequential index performance. ## Virtual Generated Columns Generated columns have existed since version 12, but they were always `STORED`: the computed value written to disk on every insert and update. PostgreSQL 18 adds `VIRTUAL` generated columns and makes them the default. Virtual columns compute at read time. No disk space, no write overhead. ```sql CREATE TABLE line_items ( id UUID PRIMARY KEY DEFAULT uuidv7(), product_name TEXT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) ); INSERT INTO line_items (product_name, quantity, unit_price) VALUES ('Widget', 5, 12.99), ('Gadget', 2, 49.99), ('Sprocket', 10, 3.50); SELECT product_name, quantity, unit_price, total FROM line_items; ``` ```text product_name | quantity | unit_price | total --------------+----------+------------+-------- Widget | 5 | 12.99 | 64.95 Gadget | 2 | 49.99 | 99.98 Sprocket | 10 | 3.50 | 35.00 ``` `total` is never stored on disk. It's computed from `quantity * unit_price` on every read. You can still use `STORED` explicitly, but for most computed columns, virtual is the better default. ## OLD/NEW in RETURNING This eliminates the common pattern of running a separate `SELECT` before or after a mutation to capture previous values. PostgreSQL 18 lets you reference `OLD` (pre-mutation) and `NEW` (post-mutation) directly in the `RETURNING` clause. ```sql CREATE TABLE products ( id UUID PRIMARY KEY DEFAULT uuidv7(), name TEXT UNIQUE NOT NULL, price DECIMAL(10, 2) NOT NULL ); INSERT INTO products (name, price) VALUES ('Keyboard', 79.99), ('Mouse', 29.99), ('Monitor', 349.99); -- Apply a 10% price increase and see old vs. new in one statement UPDATE products SET price = price * 1.10 RETURNING name, OLD.price AS old_price, NEW.price AS new_price; ``` ```text name | old_price | new_price ----------+-----------+----------- Keyboard | 79.99 | 87.99 Mouse | 29.99 | 32.99 Monitor | 349.99 | 384.99 ``` This also works with `INSERT ... ON CONFLICT` (upserts), where `OLD` values are `NULL` for newly inserted rows: ```sql INSERT INTO products (name, price) VALUES ('Keyboard', 89.99) 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 was_inserted; ``` No more CTEs or before/after queries. Audit logging, CDC, and undo features all get simpler. ## Temporal Constraints with WITHOUT OVERLAPS Anyone who's built a booking system knows the pain of enforcing non-overlapping time ranges in application code or trigger logic. PostgreSQL 18 makes this a declarative constraint. ```sql CREATE TABLE room_bookings ( room_id INT, booking_period TSTZRANGE NOT NULL, guest_name TEXT NOT NULL, PRIMARY KEY (room_id, booking_period WITHOUT OVERLAPS) ); -- Alice books room 101 for March 1-5 INSERT INTO room_bookings VALUES (101, '[2026-03-01, 2026-03-05)', 'Alice'); -- Bob books room 102 for the same dates (different room, no conflict) INSERT INTO room_bookings VALUES (102, '[2026-03-01, 2026-03-05)', 'Bob'); -- Charlie tries to book room 101 for March 3-7 (overlaps Alice) INSERT INTO room_bookings VALUES (101, '[2026-03-03, 2026-03-07)', 'Charlie'); -- ERROR: conflicting key value violates exclusion constraint ``` The database enforces non-overlapping ranges per room via a GiST index. No triggers, no application-side validation, no race conditions. Works with any range type: `TSTZRANGE`, `DATERANGE`, `INT4RANGE`, etc. ## Zero-Downtime NOT NULL with NOT VALID Adding `NOT NULL` to an existing column on a large table used to require a full table scan under an `AccessExclusiveLock`, blocking everything. PostgreSQL 18 lets you split it into two steps: ```sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT uuidv7(), name TEXT NOT NULL, email TEXT ); INSERT INTO users (name) VALUES ('Legacy User'); -- Step 1: Add the constraint instantly (no table scan, brief lock) ALTER TABLE users ADD CONSTRAINT users_email_not_null NOT NULL email NOT VALID; -- New inserts must satisfy the constraint immediately INSERT INTO users (name) VALUES ('Another User'); -- ERROR: null value in column "email" violates not-null constraint -- But existing rows with NULL are not checked yet. -- Step 2: Clean up existing NULLs at your own pace UPDATE users SET email = name || '@unknown.com' WHERE email IS NULL; -- Step 3: Validate the constraint (table scan, but with a weaker lock) ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null; ``` Same two-phase pattern that's existed for `CHECK` and `FOREIGN KEY`, but `NOT NULL` didn't support it until now. If you run zero-downtime deployments, this is a big deal. ## B-tree Skip Scan Subtle but impactful. Previously, a composite B-tree index only helped if your query filtered on the leading column(s). PostgreSQL 18 can now skip through distinct values of the leading column to reach what you're actually filtering on. ```sql CREATE TABLE sales ( id UUID PRIMARY KEY DEFAULT uuidv7(), region TEXT NOT NULL, category TEXT NOT NULL, sale_date DATE NOT NULL, amount DECIMAL(10, 2) NOT NULL ); CREATE INDEX idx_sales_region_cat_date ON sales (region, category, sale_date); -- In PG 17, this query could NOT use the index (no filter on 'region') -- In PG 18, skip scan makes this efficient: SELECT * FROM sales WHERE category = 'electronics' AND sale_date > '2026-01-01'; ``` Most effective when the leading column has low cardinality (few distinct values). In practice, you maintain fewer indexes. One composite index now serves more query patterns than before. ## Enhanced EXPLAIN Small but welcome: `EXPLAIN ANALYZE` now includes buffer usage statistics by default. No more remembering to write `EXPLAIN (ANALYZE, BUFFERS)` to see shared buffer hits vs. disk reads. ```sql -- PG 17: EXPLAIN ANALYZE omits buffer stats unless you add BUFFERS -- PG 18: buffer stats are included by default EXPLAIN ANALYZE SELECT * FROM sales WHERE category = 'electronics'; ``` The output now includes lines like: ```text Buffers: shared hit=128 read=45 I/O Timings: shared read=12.34 ``` Buffer usage is the single most useful metric for query performance. Whether a query reads from shared buffers (fast) or disk (slow) tells you more than row counts or cost estimates ever will. ## Asynchronous I/O PostgreSQL 18 introduces an async I/O subsystem that queues multiple read requests instead of waiting for each sequentially. Up to 3x speedups for I/O-bound workloads: large sequential scans, bitmap heap scans, and VACUUM. ```sql -- Check the I/O method SHOW io_method; -- sync (traditional), worker (thread pool), io_uring (Linux, fastest) ``` On Linux, `io_uring` gives the best performance. On macOS, `worker` uses a thread pool as a portable alternative. No application changes needed. ## What Else Changed A few more worth mentioning: - **OAuth 2.0 authentication**: native OAUTHBEARER support in `pg_hba.conf`, so clients can authenticate with bearer tokens from identity providers like Okta or Azure AD instead of passwords - **Logical replication improvements**: parallel streaming is now the default for new subscriptions, generated columns can be replicated, and conflict logging gives visibility into replication issues - **pg_upgrade --swap and --jobs**: parallel consistency checks and directory swapping significantly reduce upgrade downtime - **pg_stat_io reports in bytes**: I/O statistics now include byte counts in addition to page counts, with per-backend granularity ## FAQ ### Do I still need to generate UUIDv7 in my application? No. `uuidv7()` is built in as of 18, so `DEFAULT uuidv7()` on a primary key is all it takes. The value embeds a timestamp in its first 48 bits, which is what makes it globally unique and chronologically sortable at the same time. `gen_random_uuid()` has been there since 13 but produces v4, which is random and therefore causes the index page splits UUIDv7 avoids. ### What is the difference between a virtual and a stored generated column? A stored column writes the computed value to disk on every insert and update. A virtual column computes at read time, so it costs no disk space and no write overhead. Virtual is the default in 18; `STORED` is still there when you want the value materialized, for example to index it. ### How do I get the previous value of a row I just updated? Reference `OLD` in the `RETURNING` clause. Before 18 that meant a separate `SELECT` before or after the mutation, with the race condition that implies. `NEW` gives you the post-mutation value in the same statement. ### Can I enforce non-overlapping bookings without a trigger? Yes, with `PRIMARY KEY (room_id, booking_period WITHOUT OVERLAPS)`. The database enforces it through a GiST index, so there is no trigger, no application-side validation, and no race window. Any range type works. ### How do I add NOT NULL to a big table without locking it? In two phases. `ADD CONSTRAINT ... NOT NULL NOT VALID` is instant and applies to new writes immediately, then you clean up the existing NULLs at your own pace, then `VALIDATE CONSTRAINT` does the scan under a weaker lock. This is the same pattern `CHECK` and `FOREIGN KEY` have had for years; `NOT NULL` just did not support it until 18. ### What does B-tree skip scan change about my indexes? It means a composite index can serve a query that does not filter on the leading column, by skipping through that column's distinct values. It works best when the leading column has low cardinality. Practically, one composite index now covers more query patterns, so you maintain fewer of them. ### Do I need to change anything to benefit from asynchronous I/O? No application changes. It queues multiple read requests instead of waiting on each in turn, which is worth up to 3x on I/O-bound work like large sequential scans, bitmap heap scans, and `VACUUM`. `SHOW io_method` tells you which mode you are in: `io_uring` is the fastest on Linux, and `worker` is the portable thread-pool fallback. ## Connect From Your Application Once you've tried the examples, connect your application to the same instance. The Layerbase CLI gives you a connection string: ```bash lbase url pg18 # postgresql://postgres:postgres@localhost:5432/postgres ``` Use that URL with any PostgreSQL client library: `pg` for Node.js, `psycopg` for Python, `pgx` for Go, or any ORM that supports PostgreSQL. Stop when you're done, start when you need it again: ```bash lbase stop pg18 lbase start pg18 ``` The Layerbase CLI manages PostgreSQL and [20 other database engines](https://layerbase.com/docs/cli) with the same commands. Need Redis, MongoDB, or DuckDB alongside PostgreSQL? Same CLI. For a hosted PostgreSQL 18 endpoint, use [Layerbase Cloud](https://cloud.layerbase.com/create?engine=postgresql). For the full list of PostgreSQL 18 changes, see the [official release notes](https://www.postgresql.org/docs/current/release-18.html). Looking past 18, [Postgres 19 Beta is live on Layerbase](/blog/postgres-19-beta) covers what to test in the next major, and [when Postgres 19 will be released](/blog/when-will-postgres-19-be-released) tracks the schedule.