PostgreSQL 19 Beta 3: New SQL Features and Migration Checklist

Analyze PostgreSQL 19 Beta 3 release notes for new SQL features like property graphs and REPACK, plus critical compatibility breaks for developers.

New SQL Capabilities in Beta 3

PostgreSQL 19 Beta 3 adds support for property graph queries (SQL/PGQ), allowing developers to model and query graph-like data structures directly within the relational engine. This is relevant for applications in the GCC region moving toward hybrid data models without adopting a separate graph database.

A new REPACK command reclaims disk space and reorganizes table contents, combining the functionality of VACUUM FULL and CLUSTER. The CONCURRENTLY option allows repacking without blocking reads and writes, reducing maintenance window requirements for large tables. A new WAIT FOR command supports 'read-your-writes' patterns on standbys by waiting until a standby has replayed changes up to a chosen point.

Property graph queries (SQL/PGQ) are now supported for graph data modeling.
REPACK command reclaims space and reorganizes tables, with a CONCURRENTLY option for non-blocking operations.
WAIT FOR command enables read-your-writes consistency on standby servers.

Critical Compatibility Breaks

Developers must address several breaking changes before migrating to PostgreSQL 19. The most impactful change is the forced enabling of standard_conforming_strings. Dumps created with pre-19 versions of pg_dump or pg_dumpall using standard_conforming_strings = off will not load correctly into PostgreSQL 19. Teams must regenerate dumps using PostgreSQL 19 tools or ensure the setting is on during the dump process.

The default index opclasses for inet and cidr data types have changed from btree_gist to GiST. The btree_gist opclasses for these types were broken and could exclude valid rows. pg_upgrade will disallow upgrading clusters that use btree_gist inet/cidr indexes. Developers must drop and recreate these indexes using the new GiST opclasses before migration.

standard_conforming_strings is forced to on; old dumps with it off will fail to load.
Default index opclasses for inet/cidr changed to GiST; btree_gist indexes must be recreated.
Carriage returns and line feeds are disallowed in database, role, and tablespace names.

Behavioral Changes in Transactions and Locking

Transaction behavior has changed for postgres_fdw sessions. READ ONLY and DEFERRABLE status now passes to postgres_fdw sessions, meaning READ ONLY transactions can no longer modify rows processed by postgres_fdw. This may break applications that relied on the previous permissive behavior.

The default for max_locks_per_transaction has increased from 64 to 128. However, lock size allocation has changed, so settings must be doubled to match the capacity of previous releases. Additionally, JIT (Just-in-Time Compilation) is now disabled by default. Sites performing many large analytical queries must manually enable JIT, as the previous automatic costing was deemed unreliable.

READ ONLY transactions cannot modify rows via postgres_fdw.
max_locks_per_transaction default increased to 128; adjust settings to maintain previous capacity.
JIT is disabled by default; enable manually for analytical workloads.

Migration and Testing Checklist

Before migrating to PostgreSQL 19, teams should execute a structured testing phase. First, audit all dumps and restore processes to ensure they use PostgreSQL 19 tools or have standard_conforming_strings enabled. Second, identify and recreate any btree_gist indexes on inet or cidr columns. Third, review application code for reliance on READ ONLY transactions modifying data via postgres_fdw.

Test the new REPACK command in a staging environment to verify performance gains and ensure the CONCURRENTLY option meets availability requirements. Finally, validate that any applications using MULE_INTERNAL encoding are migrated, as this encoding has been removed. Databases using it must be dumped and restored with a different encoding.

Regenerate dumps using PostgreSQL 19 tools or ensure standard_conforming_strings = on.
Recreate btree_gist inet/cidr indexes using GiST opclasses.
Test REPACK CONCURRENTLY in staging to verify non-blocking behavior.
Migrate any databases using the removed MULE_INTERNAL encoding.