Postgres Is Releasing An Incredible New Feature

Highlights

• Postgres 19 introduces native graph query support, eliminating complex multi-table join syntax for related data.

• The new on conflict do select clause enables atomic insert-or-select operations in a single query.

• A built-in repack feature rewrites tables to reclaim unused disk space without locking tables when using the concurrently keyword.

• A new module called pg plan advice allows developers to capture and pin fast query execution plans.

• Just-in-Time compilation is now turned off by default due to unreliable cost estimates.

Timeline

1. 00:30 - 01:33

   On Conflict Do Select

   Standard workflows requiring an insert followed by a select lacked atomicity. Postgres 19 solves this by adding an on conflict do select clause to single statements. This update simplifies common code patterns for checking and returning existing records.

   • A single query handles inserting a row or returning it if it already exists.
   • The operation is atomic, preventing race conditions between separate insert and select statements.
   • The syntax supports row-level locking modifiers like for update.
2. 01:33 - 05:13

   Native Graph Queries

   Complex database schemas involving customers, orders, and products previously required lengthy join syntax across multiple pivot tables. Postgres 19 lets developers define a property graph pointing to existing tables. This allows simpler query patterns while keeping the underlying schema intact.

   • Property graphs define vertices and edges using existing tables without altering the underlying schema.
   • Graph syntax replaces long chains of table joins with simpler traversal patterns.
   • Dedicated graph databases remain superior for heavy storage and traversal performance needs.
3. 05:13 - 06:10

   Built-in Repack for Disk Space

   Postgres updates write new row versions rather than modifying them in place, leaving dead space behind. While vacuum made this space reusable, it never decreased actual disk usage. Postgres 19 includes a built-in repack feature that removes wasted space without requiring external extensions like pg repack.

   • Vacuum only marks dead space reusable without returning disk space to the operating system.
   • The new built-in repack command rewrites tables into fresh files to reclaim physical disk space.
   • Using the concurrently keyword keeps tables readable and writable during the rewrite process.
4. 06:10 - 07:31

   Query Hints and Additional Features

   The Postgres 19 release includes several smaller enhancements aimed at developer workflow and performance tuning. Query execution plans can now be locked to prevent performance regressions caused by shifting planner decisions. Additionally, parallel workers speed up index cleanup during vacuum operations.

   • The pg plan advice module captures and pins fast query execution plans.
   • JIT compilation is disabled by default due to unreliable planner cost estimates.
   • The copy keyword now supports direct export to JSON format.