PostgreSQL 18 Beta 1 Summary

Question

PostgreSQL 18 Beta 1 - New Features and Breaking Changes

Source: GitHub - PostgreSQL 18 Beta 1 New Features

Overview

PostgreSQL 18 introduces over 200 new features and changes. This document summarizes all significant new features, improvements, and breaking changes introduced in the Beta 1 release. Meaning these features can disappear too, this is just a beta.

---

1. Highlights of Major New Features

1.1 Adaptation to Large Scale Environments

• Virtual Columns: Support for virtual (non-stored) columns in tables.
• ONLY Clause for VACUUM/ANALYZE: Now supports partitioned tables to avoid recursive partition scans.

1.2 Reliability Enhancements

• pg_dumpall Output Format: Supports formats other than plain text.
• pg_verifybackup Tar Format: Adds support for tar format verification.

1.3 Maintainability Improvements

• Statistics Migration: pg_dump and pg_dumpall can now include optimizer statistics.
• Functions for Stats Management: Added functions to set or clear optimizer stats per table/column.
  • pg_restore_relation_stats, pg_clear_relation_stats, pg_restore_attribute_stats, pg_clear_attribute_stats
  • Commits: d32d146

1.4 Programming Improvements

• Access to OLD and NEW in RETURNING clause.
• New Function: UUID v7 support.

1.5 Preparations for Future Features

• Asynchronous I/O Infrastructure: Introduced with io_method and io_workers.
  • Commits: da72269
• NUMA Support: Basic infrastructure and views (pg_shmem_allocations_numa).
  • Commit: 8cc139b

---

2. Breaking Changes & Incompatibilities

2.1 Platform & Tool Support

• Dropped support for: PA-RISC, LLVM < 14, OpenSSL < 1.1.1, Python < 3.6.8.
• Added support: Tcl 9.

2.2 Passwords

• MD5 passwords: Now deprecated; warning shown by default.
  • Commit: db6a4a9

2.3 Configuration

• Removed --disable-spin-locks and --disable-atomics.
  • Commits: e256266
• initdb now enables data checksums by default.
  • Commit: 04bec89

2.4 SQL & DDL Statements

• COPY FREEZE: Disallowed on foreign tables.
  • Commit: 401a695
• EXPLAIN: BUFFERS enabled by default.
  • Commit: c2a4078
• CREATE SUBSCRIPTION: Default streaming = parallel.
  • Commit: 1bf1140

---

3. Architecture Changes

3.1 Catalog Changes

• New Views: pg_aios, pg_shmem_allocations_numa
• Added Columns: Many views now include additional metadata, such as constraint enforcement flags, VACUUM/ANALYZE timing, and I/O stats.

3.2 Logical Replication

• Logs detailed conflict types like insert_exists, update_differ, delete_missing.
  • Commit: 9758174
• Propagation of generated columns via publish_generated_columns.
  • Commit: 7054186

3.3 Storage I/O Enhancements

• Streaming I/O: Broadened scope to VACUUM, CREATE DATABASE, GiST/SP-GiST, etc.
• Async I/O: Controlled with io_method, can use io_uring.

---

4. SQL Enhancements

4.1 ALTER Enhancements

• ALTER CONSTRAINT SET INHERIT/NO INHERIT
• ADD CONSTRAINT ... NOT VALID
• ALTER TABLE ONLY DROP CONSTRAINT

4.2 ANALYZE Improvements

• ONLY Clause: Skip partitions.
• Verbose Stats: Includes I/O, WAL, and delay stats.

---

5. Utilities & Tooling

• pg_combinebackup, pg_createsubscriber: New utilities.
• pg_stat_io: Combines WAL and I/O metrics.
• psql \conninfo: More detailed output.

---

6. Contrib Modules

Highlights:

• amcheck: Enhanced with streaming I/O.
• Enhancements to: pgcrypto, pg_stat_statements, postgres_fdw.

---

Summary

PostgreSQL 18 Beta 1 introduces significant architectural upgrades and functional improvements while laying the groundwork for upcoming innovations like async I/O and NUMA-aware processing. Compatibility testing is essential for a smooth transition.