SQL Property Graph Queries in Postgres 19

• From: Mona Hussain | Date: Fri, 24 Jul 2026 04:27:27 +0000 #
  Subject: SQL Property Graph Queries in Postgres 19

  PostgreSQL 19 introduces SQL/PGQ (SQL Property Graph Queries), a feature standardized in SQL:2023 for querying graph-structured data within relational databases. SQL/PGQ extends SQL with native graph capabilities through constructs such as CREATE PROPERTY GRAPH for defining graph schemas and GRAPH_TABLE for expressing graph pattern-matching queries. Consider an employees table in which the reports_to column is a self-referencing foreign key representing the organizational hierarchy. Suppose the objective is to identify all employees who are exactly two managerial levels above an employee named Alice. Traditionally, this type of hierarchical traversal is implemented using recursive common table expressions (CTEs). While recursive CTEs are powerful, they often become verbose and difficult to read when expressing graph-oriented traversals. SQL/PGQ provides a more intuitive, declarative approach by allowing the hierarchy to be modeled as a property graph and queried using graph pattern syntax.

  -- Define a property graph based on the employees table.
  CREATE PROPERTY GRAPH employee_graph
    VERTEX TABLES (
      -- Each row in the employees table becomes a vertex (node).
      employees KEY (id)
        LABEL employee
        PROPERTIES (id, name)
    )
    EDGE TABLES (
      -- Create directed edges using the self-referencing reports_to column.
      -- Each edge connects an employee (SOURCE) to their manager (DESTINATION).
      employees AS reports_to_edge
        SOURCE KEY (id) REFERENCES employees (id)
        DESTINATION KEY (reports_to) REFERENCES employees (id)
        LABEL reports_to
    );
  
  -- Find the manager located exactly two reporting levels above Alice.
  SELECT manager_name
  FROM GRAPH_TABLE (
    employee_graph
  
    -- Match the employee named Alice, then traverse two reports_to edges
    -- to reach the manager two levels above.
    MATCH (alice IS employee WHERE alice.name = 'Alice')
          -[IS reports_to]->()
          -[IS reports_to]->(manager IS employee)
  
    -- Return the name of the matched manager.
    COLUMNS (manager.name AS manager_name)
  );

  In this example, the MATCH clause begins at the employee vertex representing Alice and traverses two consecutive reports_to edges, ultimately identifying the manager located two levels higher in the reporting hierarchy. Compared with a recursive CTE, the graph query more closely reflects the conceptual structure of the problem, resulting in code that is both more concise and easier to understand.

  [ Reply ]
  • |-- Reply
    From: Stefanie Tran | Date: Fri, 31 Jul 2026 22:12:26 +0000 #

    PGQ/SQL support in PostgreSQL 19 represents a significant step forward, bringing property graphs into the relational ecosystem without requiring separate graph database. However, the underlying architecture remains unchanged: the graph is a read-only view over relational storage, and queries are still executed by a planner optimized for relational joins rather than graph traversals. This distinction becomes increasingly important for multi-hop reachability queries. The performance gap reflects a fundamental architectural difference rather than a tuning issue, as each additional hop in PostgreSQL requires repeated expansion through join operations instead of traversing a native graph structure. SQL/PGQ is therefore an important milestone that legitimizes property graphs as a first-class SQL concept and will broaden adoption of graph querying among relational database users, but support for graph syntax should not be mistaken for the capabilities of a database designed specifically for graph traversal.

    [ Reply ]