PostgreSQL 19 Native Graph Queries and Atomic Upserts

Native Graph Querying

PostgreSQL 19 introduces native property graph support, allowing developers to query relational data using graph traversal syntax instead of complex multi-table joins. This feature functions as a virtual layer over existing tables rather than requiring a schema migration. Users define vertices and edges using the CREATE PROPERTY GRAPH command:

CREATE PROPERTY GRAPH my_shop
  VERTEX TABLES (customers, orders, products)
  EDGE TABLES (
    customer_orders SOURCE customers DESTINATION orders,
    order_items SOURCE orders DESTINATION products
  );

Once defined, users can traverse relationships with significantly more readable syntax than standard SQL joins. While this improves query ergonomics for relational data, it does not replace dedicated graph databases like Neo4j for high-performance graph-specific storage or specialized traversal workloads.

Atomic Upserts and Disk Management

The release adds an atomic ON CONFLICT DO SELECT operation, which eliminates the need for separate INSERT and SELECT statements. This ensures atomicity when checking for existence and retrieving row data in a single round trip. Additionally, PostgreSQL 19 includes a native REPACK capability to reclaim disk space. Unlike VACUUM FULL, which locks tables, the new native implementation supports concurrent execution, allowing tables to remain readable and writable during the rewrite process. Note that this requires sufficient free disk space to hold a full copy of the table and its indexes during the operation.

Operational Improvements

• Query Stability: The new pg_plan_advice module allows developers to capture and pin query execution plans to prevent performance regressions caused by the planner changing strategies.
• JIT Compilation: Just-In-Time compilation is now disabled by default due to unreliable cost estimation in previous versions.
• Parallel Vacuuming: Index cleanup during vacuum operations now supports multiple parallel workers, reducing maintenance windows for large tables.
• Data Export: The COPY command now supports direct export to JSON format, removing the need for intermediate CSV conversion.