PostgreSQL 18 and 19 technical overview: New SQL features and management tools

A recent technical meetup in Edinburgh provided a detailed breakdown of the upcoming and current PostgreSQL releases. PostgreSQL 18 is currently available and includes significant performance and security updates, such as asynchronous I/O and the deprecation of MD5 passwords in favor of OAuth 2.0.

PostgreSQL 19 is currently in a testing phase, having experienced release delays due to the reversion of several features, including SQL property graph queries. The upcoming version focuses on improving database maintenance through built-in REPACK capabilities and enhancing analytical capabilities via new window functions and experimental plan advice tools.

Technical breakdown of PostgreSQL 18 and 19

• Core Engine & Performance: PostgreSQL 18 introduces asynchronous I/O for disk operations, B-tree skip scans for improved composite index utility, and faster GIN and GiST index builds.
• SQL Syntax & Data Types: Version 18 adds uuidv7(), virtual generated columns, and temporal primary keys using WITHOUT OVERLAPS; version 19 introduces INSERT ... ON CONFLICT DO SELECT and IGNORE NULLS for window functions.
• Database Administration: PostgreSQL 19 implements built-in REPACK (including CONCURRENTLY) to manage table bloat, alongside parallel autovacuum workers.
• Security & Authentication: PostgreSQL 18 implements OAuth 2.0 authentication and officially deprecates the use of MD5 passwords.
• Analytics & Extensions: The ecosystem is expanding with pg_lake for Iceberg/Parquet support, pg_duckdb, and the experimental pg_plan_advice tool for query optimization.

Why it matters

These updates provide sysadmins with more robust native tools for managing bloat and security, reducing reliance on external scripts. For data engineers, the integration of Iceberg and DuckDB via extensions significantly expands PostgreSQL's utility in big data and analytical workloads.