From Joins to Graph Edges: SQL/PGQ in PostgreSQL 19
In the previous post, Cypher graph queries on PostgreSQL with Apache AGE, I showed how to model and query a property graph in PostgreSQL using Apache AGE. The graph was materialized as vertices and edges stored in dedicated tables.
PostgreSQL 19, currently in beta, takes a different route for built-in support of graph queries. With SQL/PGQ, a property graph is defined as a logical model on top of existing relational tables, without duplicating the underlying data. In short:
- Apache AGE: the graph is a stored data structure.
- SQL/PGQ: the graph is a semantic layer over relational data.
Unlike graph databases, or extensions such as Apache AGE, SQL/PGQ (SQL Property Graph Queries) does not introduce a separate graph storage model. Property graphs are defined on top of existing relational tables. The data remains relational, while graph queries become another way to access it.
Relational model
I'll use the legendary EMP/DEPT schema from more than 45 years ago:
create table "department" (
"deptno" integer primary key,
"name" text not null,
"loc" text not null
);
create table "employee" (
"empno" integer primary key,
"name" text not null,
"job" text not null,
"mgr" integer references "employee"("empno"),
"deptno" integer not null references "department"("deptno"),
"sal" integer not null
);
insert into "department" ("deptno", "name", "loc") values
(10, 'Administration', 'New York'),
(20, 'Research', 'San Francisco'),
(30, 'Sales', 'Chicago'),
(40, 'Operations', 'Boston');
insert into "employee" ("empno", "name", "job", "mgr", "deptno", "sal") values
(7839, 'OATES', 'President', NULL, 10, 5000),
(7566, 'JONES', 'Manager', 7839, 20, 2975),
(7698, 'BLAKE', 'Manager', 7839, 30, 2850),
(7782, 'CLARK', 'Manager', 7839, 10, 2450),
(7788, 'SCOTT', 'Analyst', 7566, 20, 3000),
(7902, 'FORD', 'Analyst', 7566, 20, 3000),
(7999, 'WILSON', 'Analyst', 7566, 20, 2800),
(7876, 'ADAMS', 'Clerk', 7788, 20, 1100),
(7369, 'SMITH', 'Clerk', 7902, 20, 800),
(8000, 'JAKES', 'Clerk', 7999, 20, 1000),
(7499, 'ALLEN', 'Salesman', 7698, 30, 1600),
(7521, 'WARD', 'Salesman', 7698, 30, 1250),
(7654, 'MARTIN', 'Salesman', 7698, 30, 1250),
(7844, 'TURNER', 'Salesman', 7698, 30, 1500),
(7900, 'JAMES', 'Clerk', 7698, 30, 950),
(8001, 'CARTER', 'Salesman', 7698, 30, 1400),
(7934, 'MILLER', 'Clerk', 7782, 10, 1300);
The employee hierarchy is represented through a self-referencing foreign key employee.mgr -> employee.empno and the employee-department relationship through employee.deptno -> department.deptno. This is the same model that has been used for decades in relational databases.
This model can be queried with joins and when there is a variable level of joins, WITH RECURSIVE clause can iterate in them. However, the queries quickly become complex and you need to think about the graph traversal for each query.
Property graph definition
SQL/PGQ introduces a property graph definition on top of relational tables, allowing them to be queried as vertices and edges rather than through joins.
The graph definition maps tables to vertices and foreign-key relationships to edges:
create property graph "emp_dept_graph"
vertex tables (
"department" label "department",
"employee" label "employee"
)
edge tables (
"employee" as "reports"
source key ("empno") references "employee" ("empno")
destination key ("mgr") references "employee" ("empno")
label "reports_to",
"employee" as"works"
source key ("empno") references "employee" ("empno")
destination key ("deptno") references "department" ("deptno")
label "works_in"
);
Unlike Apache AGE, no vertices or edges are stored separately. PostgreSQL exposes existing rows as graph elements. The relational tables remain the source of truth. The property graph is a queryable layer that maps the relational model to a graph model.
Querying the graph
To find Jones's manager, the Cypher query was:
MATCH (:Employee {name:"JONES"})-[:REPORTS_TO]->(manager:Employee)
RETURN manager.name
The SQL/PGQ is similar, using ASCII art with () for vertices and -[]-> for edges, but closer to SQL:
postgres=# select * from graph_table (
"emp_dept_graph"
match
(e is "employee" where e."name" = 'JONES')
-[is "reports_to"]->
(m is "employee")
columns (
m."name" as "manager_name"
)
);
manager_name
--------------
OATES
(1 row)
This query is conceptually equivalent to:
select m."name" as "manager_name"
from "employee" e
join "employee" m on m."empno" = e."mgr"
where e."name" = 'JONES'
;
but expressed as a graph pattern.
Both have the same execution plan except for the alias names:
QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=1.22..2.47 rows=1 width=6)
Hash Cond: (employee_1.empno = employee.mgr)
-> Seq Scan on employee employee_1 (cost=0.00..1.17 rows=17 width=10)
-> Hash (cost=1.21..1.21 rows=1 width=8)
-> Seq Scan on employee (cost=0.00..1.21 rows=1 width=8)
Filter: (name = 'JONES'::text)
With such a simple query, SQL does not look particularly complex. However, it already reveals a limitation of the relational model where relationships are not first-class citizens. Rather than navigating the domain model through named associations such as "reports to" or "works in", SQL developers must understand the physical schema and determine, for each query, which columns can be combined through joins.
The relational model was deliberately designed to do the opposite of graph or document databases, with their network or hierarchical models: relationships are represented indirectly through business values rather than explicit links, allowing entities to be stored independently of pointers, access patterns, or traversal directions.
Foreign key constraints in SQL add information about those relationships and enforce referential integrity during inserts, updates, and deletes. However, they do not provide a queryable graph structure or predefined navigation paths. Queries ignore the foreign keys and still need to specify how relationships are traversed with join predicates.
SQL/PGQ restores that semantic layer by defining a graph model on top of relational data. Associations from the domain model become explicit graph edges, allowing queries to traverse relationships by their business meaning rather than by manually assembling joins between columns.
Combining relationships
Graph patterns become more expressive when traversing multiple relationships. To retrieve both Jones's manager and that manager's department:
postgres=# select * from graph_table (
"emp_dept_graph"
match
(e is "employee" where e."name" = 'JONES')
-[is "reports_to"]->
(m is "employee")
-[is "works_in"]->
(d is "department")
columns (
m.name as "manager_name",
d.name as "department_name"
)
);
manager_name | department_name
--------------+-----------------
OATES | Administration
(1 row)
The equivalent relational query would require two joins and a self-join.
Traversing hierarchies
The EMP table is famous for hierarchical queries. To find the manager's manager of JAKES:
postgres=# select * from graph_table (
"emp_dept_graph"
match
(e is "employee" where e."name" = 'JAKES')
-[is "reports_to"]->()-[is "reports_to"]->
(m is "employee")
columns (
m."name" as "manager_name"
)
);
manager_name
--------------
JONES
(1 row)
SQL/PGQ standard allows the -[is "reports_to"]->{2} syntax where the {2} quantifier specifies a traversal depth of exactly two relationships, but this is not supported in PostgreSQL 19 so I used -[is "reports_to"]->()-[is "reports_to"]-> instead.
PostgreSQL 19 implements the core SQL/PGQ functionality needed to define property graphs over relational tables and query them with graph pattern matching. However, many advanced graph features are not yet supported, including path variables, shortest-path search, variable-length traversals, path analytics, and advanced path pattern expressions. The list is in sqlfeatures.txt.
Relational and graph models
The EMP/DEPT example has been used for decades to explain relational modeling, self-referencing relationships, and hierarchical queries. SQL/PGQ introduces a new way to query the same data, but it does not change how the data is stored. Tables, primary keys, foreign keys, indexes, constraints, the optimizer, and storage structures remain unchanged.
This is an interesting evolution of database history. The relational model was created in part to move away from the navigational nature of hierarchical and network databases, where applications followed predefined links and access paths. By representing relationships through values rather than pointers, relational databases achieved data independence: the schema describes the data without embedding specific navigation patterns or application use cases.
SQL/PGQ does not reverse that design choice. Relational tables remain the source of truth, and relationships are still represented through values rather than pointers. Instead, it adds a semantic layer that maps the relational schema to the domain model. Relationships that exist implicitly through foreign keys and join predicates become explicit graph edges such as reports_to or works_in. It is like an in-database Object-Relational Mapper (ORM) for graph use cases.
The benefit is primarily developer experience. Applications can navigate the domain model through graph patterns rather than reconstructing relationships from foreign keys and joins in every query. The relational model keeps its flexibility and data independence, while SQL/PGQ provides a more natural way to express traversals and relationship-oriented queries.
PostgreSQL 19 brings this graph abstraction directly into standard SQL. Graph queries run on the same tables, indexes, optimizer, and execution engine as traditional relational queries, without introducing a separate graph storage model.
In that sense, SQL/PGQ is not a return to hierarchical or network databases. The relational model remains the foundation. SQL/PGQ simply adds a semantic mapping between relational structures and business relationships, making graph-oriented queries easier to express while preserving the data independence that made relational databases successful in the first place.
