Whats New In Postgres 18 => Demos Only

postgres18demos.sql

\q
;/*
Choose Your Own Adventure: What's New In PostgreSQL 18
Robert Treat (@robtreat2) - v6 - 2026-02-28

Requirements:
Postgres 18
Pagila Sample Database (https://github.com/xzilla/pagila)

## Table of Contents ##

UUIDv7

SKIP SCAN

CASEFOLD

TIME IN VACUUM
VACUUM DELAYS
VACUUM TRUNCATE

PSQL EXTENDED VIEW

PER BACKEND STATS

GET ACL

TOAST MY INDEX

CRC32 Functions

ROMAN NUMBERS

ARRAY SORT
ARRAY REVERSE

COPY REJECTS

NOT ENFORCED NOT VALID

RETURNING OLD NEW

CONNINFO

MD5 PASSWORD WARNING

VIRTUAL GENERATED COLUMNS

EXPLAIN FRACTIONAL ROWS

DATA CHECKSUMS

ASYNCIO Framework
*/



/* UUIDv7 */
-- https://git.postgresql.org/pg/commitdiff/78c5e141e9c139fc2ff36a220334e4aa25e1b0eb

select uuidv7();

select gen_random_uuid(), uuidv7() from generate_series(1,5);

with uu as (
    select uuidv4() as id union all
    select '10000000-1000-1000-a456-426614174000' union all
    select uuidv7()
    ) select uuid_extract_version(id), uuid_extract_timestamp(id) FROM uu;

with u as (select uuidv7() from generate_series(1,5)) select uuidv7, uuid_extract_timestamp(uuidv7) from u;


/* SKIP SCAN */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=92fe23d93d0708e60aac7bf3c4fb2fa0eff83a44
CREATE INDEX idx_film_rating_length ON film(rating, length);

EXPLAIN (COSTS OFF)
SELECT title, rating, length
FROM film
WHERE rating = 'PG-13' AND length > 120;

EXPLAIN (COSTS OFF)
SELECT title, rating, length
FROM film
WHERE length > 180
ORDER BY length;


/* CASEFOLD */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bfc5992069cf00b189af83d96a83ae5ebb65e938

SELECT title FROM film WHERE lower(title) = lower('ACADEMY DINOSAUR');

SELECT title FROM film WHERE casefold(title) = casefold('academy dinosaur');

SELECT -- scharfes S
    'Straße' as original,
    lower('Straße') as with_lower,
    casefold('Straße') as with_casefold;

SELECT
    casefold('STRASSE') = casefold('Straße') as casefold_match,
    lower('STRASSE') = lower('Straße') as lower_match;


/* TIME IN VACUUM */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=30a6ed0ce4bb18212ec38cdb537ea4b43bc99b83

  SELECT relname, total_vacuum_time, total_autovacuum_time, total_analyze_time, total_autoanalyze_time
    FROM pg_stat_all_tables
ORDER BY total_vacuum_time+total_autovacuum_time+total_analyze_time+total_autoanalyze_time DESC -- total_milliseconds
   LIMIT 5;


/* VACUUM DELAYS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb8dff9995f2cf501376772898bcbcf58aa05cde
show track_cost_delay_timing;
set track_cost_delay_timing = on;

vacuum verbose actor; -- grep delay time

select delay_time from pg_stat_progress_vacuum
union
select delay_time from pg_stat_progress_analyze;

set vacuum_cost_delay = 100;
set vacuum_cost_limit = 1;

vacuum verbose actor; -- grep delay time


/* VACUUM TRUNCATE */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0164a0f9ee12e0eff9e4c661358a272ecd65c2d4

SELECT * FROM pg_settings WHERE name = 'vacuum_truncate' \gx


/* PSQL EXTENDED VIEW */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=00f4c2959d631c7851da21a512885d1deab28649

\df rewards_report
\dfx rewards_report

\dv rental_report
\dvx rental_report


/* PER BACKEND STATS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9aea73fc61d4e77e000724ce0b2f896590a10e03
select * from pg_stat_get_backend_io(pg_backend_pid());

-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=76def4cdd7c2b32d19e950a160f834392ea51744
select * from pg_stat_get_backend_wal(pg_backend_pid());


/* GET ACL */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4564f1cebd437d93590027c9ff46ef60bc3286ae

CREATE ROLE analyst;
CREATE ROLE manager;

GRANT SELECT ON customer TO analyst;
GRANT SELECT, INSERT, UPDATE ON customer TO manager;
GRANT SELECT ON rental TO analyst;
GRANT SELECT ON sales_by_store TO analyst;
GRANT EXECUTE ON FUNCTION get_customer_balance(integer, timestamp) to analyst;
GRANT USAGE ON SCHEMA public TO analyst, manager;

SELECT
    (pg_identify_object(s.classid, s.objid, s.objsubid)).*,
    pg_get_acl(s.classid, s.objid, s.objsubid) AS acl
FROM pg_shdepend AS s
JOIN pg_database AS d
    ON d.datname = current_database() AND d.oid = s.dbid
JOIN pg_authid AS a
    ON a.oid = s.refobjid
    AND s.refclassid = 'pg_authid'::regclass
WHERE s.deptype = 'a'
ORDER BY type, schema, name;


/* TOAST MY INDEX */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b52c4fc3c09ec0ec9c1f9aa676f6d74304cc2f6f

   SELECT t.relname AS table_name, coalesce(toast.relname,'<no toast table>') AS toast_table_name
     FROM pg_class t
LEFT JOIN pg_class toast ON t.reltoastrelid = toast.oid
    WHERE t.relname IN ('pg_class', 'pg_attribute', 'pg_index', 'pg_proc')
 ORDER BY t.relname;


/* CRC32 */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=760162fedb4f7ee6f0167cc6acfadee6ccb6be66

select title, crc32(convert_to(title,'UTF8')), crc32c(convert_to(title,'UTF8')) from film limit 5;

alter table film
    add column description_crc32 bigint
        generated always as (crc32(description::bytea)) stored;

create index on film (description_crc32);

select title from film
 where description_crc32 = crc32(convert_to('A Unbelieveable Panorama of a Composer And a Butler who must Overcome a Database Administrator in The First Manned Space Station','UTF8'));


/* ROMAN NUMBERS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=172e6b3adb2e2749883ad0746172e4cf97139961

SELECT
    to_number('XVIII', 'RN') as eighteen,
    to_number('MMXXVI', 'RN') as year_2026,
    to_number('XLII', 'RN') as forty_two;

insert into film (title, language_id) values ('ROCKY WAR IX',1),('ROCKY WAR VI',1),('ROCKY WAR II',1);

select title from film where title ~ 'ROCKY WAR';

select title from film where title ~ 'ROCKY WAR' order by 1;

SELECT title FROM film WHERE title ~ 'ROCKY WAR'
    ORDER BY to_number(substring(title from 'ROCKY WAR (.+)$'), 'RN') NULLS FIRST;


/* ARRAY SORT */
/* ARRAY REVERSE */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=49d6c7d8d
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=6c12ae09f

WITH special_features AS (SELECT distinct(special_features) as features FROM film)
SELECT features, array_sort(features), array_reverse(features) FROM special_features ORDER BY 1;


/* COPY REJECTS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4ac2a9beceb10d44806d2cf157d5a931bdade39e

CREATE TABLE film_import (
    film_id int,
    title text,
    rental_rate numeric(4,2)
);

COPY (
    SELECT 'film_id,title,rental_rate'
    UNION ALL SELECT '1,Film One,1.99'
    UNION ALL SELECT '2,Film Two,two-ninty-nine'  -- Invalid
    UNION ALL SELECT '3,Film Three,3.99'
    UNION ALL SELECT 'IV,Film Four,4.99'  -- Invalid
    UNION ALL SELECT '5,Film Five,5.99'
) TO '/tmp/films_reject.csv';

\! cat /tmp/films_reject.csv

COPY film_import
FROM '/tmp/films_reject.csv'
WITH (FORMAT csv, HEADER, ON_ERROR ignore, REJECT_LIMIT 2);

SELECT * FROM film_import;


/* NOT ENFORCED NOT VALID */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e2fccf81cec6fd45698dde9fae0ab570
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=eec0040c4bcd650993bb058ebdf61ab94171fda4
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a379061a22a8fdf421e1a457cc6af8503def6252

CREATE TABLE film_staging (
    film_id int PRIMARY KEY,
    title text,
    release_year int,
    rental_rate numeric(4,2),
        CONSTRAINT valid_rate CHECK (rental_rate > 0) NOT ENFORCED,   -- This constraint documents the rule but doesn't enforce it
    language_id int,
        CONSTRAINT fk_language FOREIGN KEY (language_id)              -- FK that documents relationship without enforcing
        REFERENCES language(language_id) NOT ENFORCED
);

INSERT INTO film_staging (film_id, title, release_year, rental_rate, language_id)
VALUES (9999, 'Test Film', 2024, -1.00, (select max(language_id)+1  from language));  -- Negative rate, invalid FK

\d film_staging
SELECT conname, conenforced, convalidated FROM pg_constraint WHERE conrelid = 'film_staging'::regclass;

alter table film_staging alter constraint valid_rate enforced;

alter table film_staging alter constraint fk_language enforced;

update film_staging set language_id = 2;

alter table film_staging alter constraint fk_language enforced;

\d film_staging

ALTER TABLE address ADD CONSTRAINT a2nn NOT NULL address2;

ALTER TABLE address ADD CONSTRAINT a2nn NOT NULL address2 NOT VALID;

UPDATE address SET address2 = '' WHERE address2 IS NULL;

ALTER TABLE address VALIDATE CONSTRAINT a2nn;


/* RETURNING OLD NEW */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=80feb727c869cc0b2e12bd1543bafa449be9c8e2

INSERT INTO rental (inventory_id, customer_id, staff_id)
    VALUES (2,11,2),(7,33,1)
    RETURNING OLD.rental_id, NEW.*;

UPDATE rental SET rental_period = tsrange(lower(rental_period), timeofday()::timestamp)
    WHERE lower(rental_period) > current_date
    RETURNING NEW.rental_id, OLD.rental_period as old_period, NEW.rental_period as new_period;

DELETE FROM rental WHERE rental_period <@ tsrange(current_date, current_date+1)
    RETURNING NEW.rental_id as new_id,  OLD.rental_id as old_id, new.rental_period as new_period, old.rental_period old_period;


/* CONNINFO */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bba2fbc6238b2a0a7f348fbbb5c31ffa7623bc39

\conninfo


/* MD5 PASSWORD WARNING */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=db6a4a985bc09d260d5c29848e3c97f080646a53

select name, setting from pg_settings where name in ('password_encryption', 'md5_password_warnings') order by 1 desc;

set password_encryption = 'md5';

create role mdfive password 'warning';


/* VIRTUAL GENERATED COLUMNS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b

CREATE TABLE film_metrics (
    film_id int PRIMARY KEY,
    rentals_count int DEFAULT 0,
    total_revenue numeric(10,2) DEFAULT 0,
    avg_revenue_per_rental numeric(10,2) GENERATED ALWAYS AS (
        CASE WHEN rentals_count > 0 THEN total_revenue / rentals_count ELSE 0 END
    ),
    revenue_category text GENERATED ALWAYS AS (
        CASE WHEN total_revenue >= 100 THEN 'High' WHEN total_revenue >= 50 THEN 'Medium' ELSE 'Low' END
    ) STORED
);

INSERT INTO film_metrics SELECT film_id, count(rental_id), sum(amount) FROM payment JOIN rental USING (rental_id) JOIN inventory USING (inventory_id) GROUP BY film_id;

SELECT * FROM film_metrics LIMIT 5;


/* EXPLAIN FRACTIONAL ROWS */
-- https://git.postgresql.org/pg/commitdiff/95dbd827f2edc4d10bebd7e840a0bd6782cf69b7

CREATE TABLE movies (ratings) AS VALUES ('G'),('G'),('G'),('R'),('G'::mpaa_rating);

explain analyze
    SELECT
        s.*, m.*
    FROM
        unnest(ARRAY['PG','PG-13','R','NC-17']::mpaa_rating[]) WITH ORDINALITY AS s (r, idx),
        LATERAL (
            SELECT * FROM movies WHERE ratings = s.r ORDER BY ratings DESC LIMIT 1
        ) m;


/* DATA CHECKSUMS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=04bec894a04cb0d32533f1522ab81b7016141ff1

VALUES
    ('v18 data checksums by default', 'on'),
    ('initdb --no-data-checksums','off'),
    ('data checksums on this cluster?',(select setting from pg_settings where name = 'data_checksums')) ;


/* CHECKPOINT MONITORING */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=559efce1d684069acf234a5cb032acba84e70938

SELECT num_timed, num_requested, num_done -- only completed checkpoints
  FROM pg_stat_checkpointer;


/* PARALLEL WORKERS */
-- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e7a9496de90657e2161f68b3a5a9b2d9b0b7bb07

SELECT datname, parallel_workers_to_launch, parallel_workers_launched FROM pg_stat_database WHERE datname = current_database();


/* ASYNCIO Framework */
-- https://postgr.es/c/02844012b, https://postgr.es/c/da7226993, https://postgr.es/c/55b454d0e
-- https://postgr.es/c/247ce06b8, https://postgr.es/c/10f664684, https://postgr.es/c/06fb5612c
-- https://postgr.es/c/c325a7633, https://postgr.es/c/50cb7505b, https://postgr.es/c/047cba7fa
-- https://postgr.es/c/12ce89fd0, https://postgr.es/c/2a5e709e7

select * from pg_settings where name in ('io_method','io_combine_limit','io_max_combine_limit');
\dv+ pg_aios


-- FIN
;/*
PostgreSQL License
Copyright (c) 2025,2026 Robert Treat
Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,
SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING
OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE AUTHOR HAS
BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
 THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE AUTHOR HAS NO
OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
MODIFICATIONS.
*/