Handling graphs with SQL/PGQ in PostgreSQL

Starting with version 19 of PostgreSQL users will be able to enjoy something exceptionally useful which will help developers to build even more powerful applications even more quickly. SQL/PGQ — the ISO/IEC 9075-16 (2023) syntax for querying graphs that live in regular relational tables - will be available. This series of posts will explain how this new functionality works and how it can be used to leverage the power of PostgreSQL 19 and beyond.

Your First Graph Query in PostgreSQL 19

The addition introduces two SQL constructs: Namely CREATE PROPERTY GRAPHand GRAPH_TABLE. Let us take a look at the definition of the property graph:

friends=# \h CREATE PROPERTY GRAPH

Command: CREATE PROPERTY GRAPH

Description: define a new SQL-property graph

Syntax:

CREATE [ TEMP | TEMPORARY ] PROPERTY GRAPH name

[ {VERTEX|NODE} TABLES ( vertex_table_definition [, ...] ) ]

[ {EDGE|RELATIONSHIP} TABLES ( edge_table_definition [, ...] ) ]

where vertex_table_definition is:

vertex_table_name [ AS alias ]

[ KEY ( column_name [, ...] ) ]

[ element_table_label_and_properties ]

and edge_table_definition is:

edge_table_name [ AS alias ] [ KEY ( column_name [, ...] ) ]

SOURCE [ KEY ( column_name [, ...] )

REFERENCES ] source_table [ ( column_name [, ...] ) ]

DESTINATION [ KEY ( column_name [, ...] )

REFERENCES ] dest_table [ ( column_name [, ...] ) ]

[ element_table_label_and_properties ]

and element_table_label_and_properties is either:

NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES

( { expression [ AS property_name ] } [, ...] )

or:

{ { LABEL label_name | DEFAULT LABEL }

[ NO PROPERTIES | PROPERTIES ALL COLUMNS | PROPERTIES

( { expression [ AS property_name ] } [, ...] ) ] } [...]

URL: https://www.postgresql.org/docs/devel/sql-create-property-graph.html

Before we dig into this in more detail we need to understand what the purpose of all of this is: In a relational database things are stored as tables. What CREATE PROPERTY GRAPH does is to define a graph on top of this relational model (as metadata). To query this graph we can use the GRAPH_TABLE functionality inside our SQL statements. No extension is installed. No data is copied. The graph is based on the joins between tables you already have, expressed in a syntax designed for that case.

At the end of my tutorial you will understand exactly how this works. This post walks through the smallest useful example: Two tables and a couple of lines of SQL/PGQ.

Take the smallest possible social network:

A " person" table
A “ knows” table mapping who knows whom
You want "friends of friends" — for any person

Here is some sample data:

CREATE TABLE person (

id int PRIMARY KEY,

name text NOT NULL,

age int NOT NULL,

city text NOT NULL

);

CREATE TABLE knows (

a int NOT NULL REFERENCES person(id),

b int NOT NULL REFERENCES person(id),

since int NOT NULL,

PRIMARY KEY (a, b)

);

INSERT INTO person VALUES

(1, 'Alice', 30, 'Berlin'),

(2, 'Bob', 25, 'Berlin'),

(3, 'Carol', 35, 'Paris'),

(4, 'Dan', 28, 'Paris'),

(5, 'Eve', 40, 'London'),

(6, 'Frank', 33, 'London');

INSERT INTO knows VALUES

(1, 2, 2018), (2, 1, 2019),

(1, 3, 2020), (2, 3, 2020), (3, 2, 2021),

(3, 4, 2021), (4, 5, 2022),

(5, 6, 2019), (6, 1, 2023);

Defining a property graph in PostgreSQL

First we have to define a property graph. Here is how it works:

CREATE PROPERTY GRAPH social

VERTEX TABLES (

person KEY (id) LABEL person

PROPERTIES (id, name, age, city)

)

EDGE TABLES (

knows

SOURCE KEY (a) REFERENCES person (id)

DESTINATION KEY (b) REFERENCES person (id)

LABEL knows PROPERTIES (since)

);

Let us dissect this step by step. The first thing I would suggest is to read it like a contract: All rows in the “person” table are labelled as “person” (the idea here is to give those nodes a separate name which can be different from the underlying table). In general those nodes in the graph are called “vertices” which are connected by “edges”. Vertices expose various properties which (in my example) are just columns.

Finally we got edges which basically describe how vertices are connected to each other. So we basically link the nodes in our graph.

Running your first SQL/PGQ query

The first thing we have to approach is to understand the basic syntax of a graph query. Here is the most simple possible query which simply lists of people:

tutorial=# SELECT name

FROM GRAPH_TABLE (social

MATCH (p IS person)

COLUMNS (p.name)

)

ORDER BY name;

name

-------

Alice

Bob

Carol

Dan

Eve

Frank

(6 rows)

This is logically equivalent to:

SELECT name FROM person ORDER BY name

How does it work? The GRAPH_TABLE keyword will define which graph we are going to use. We use the “person” vertex and call it “p”. In this node we chose the “name” column. As I have mentioned this is the most simple query. However, let us play with some variations of this one:

tutorial=# SELECT *

FROM GRAPH_TABLE (social

MATCH (p IS person )

COLUMNS (p.id, p.name)

);

id | name

----+-------

1 | Alice

2 | Bob

3 | Carol

4 | Dan

5 | Eve

6 | Frank

(6 rows)

tutorial=# SELECT *

FROM GRAPH_TABLE (social

MATCH (p IS person )

COLUMNS (p.*)

);

ERROR: "*" is not supported here

LINE 1: ...OM GRAPH_TABLE (social MATCH (p IS person ) COLUMNS (p.*) );

One important side node: We can use multiple columns in the COLUMNS clause - however, “*” does not work. The columns list has to be defined as a list.

After taking a look at the most simplistic example we want to proceed with a more useful query which answers the following question: “Who knows whom?”.

Here is how it works:

tutorial=# SELECT *

FROM GRAPH_TABLE (social

MATCH (p IS person )-[IS knows]->(p2 IS person)

COLUMNS (p.id, p.name, p2.id, p2.name)

)

ORDER BY 1, 2, 3;

id | name | id | name

----+-------+----+-------

1 | Alice | 2 | Bob

1 | Alice | 3 | Carol

2 | Bob | 1 | Alice

2 | Bob | 3 | Carol

3 | Carol | 2 | Bob

3 | Carol | 4 | Dan

4 | Dan | 5 | Eve

5 | Eve | 6 | Frank

6 | Frank | 1 | Alice

(9 rows)

The idea of a graph query is to walk through vertices and edges. The way we do it is to start with a person. We walk along the edges we have defined to the “knows” table (which is an edge of our graph) to the other person. “ ->”. (a)-[IS knows]->(b) is an edge pattern. The arrow -> follows the edge in the direction it was declared (SOURCE → DESTINATION). <- follows it in reverse.

If we use “-” instead all the data will be listed twice because we are not walking in one direction but along both. A bare - is undirected.

The wrong query would look like this:

SELECT *

FROM GRAPH_TABLE (social

MATCH (p IS person )-[IS knows]-(p2 IS person)

COLUMNS (p.id, p.name, p2.id, p2.name)

) ORDER BY 1, 2, 3;

id | name | id | name

----+-------+----+-------

1 | Alice | 2 | Bob

1 | Alice | 3 | Carol

…

(18 rows)

The row count would be wrong because.
Background information: The undirected operator compiles to an OR clause

(person.id = knows.a AND person_1.id = knows.b) OR

(person_1.id = knows.a AND person.id = knows.b)

The undirected operator is most useful when the underlying data is conceptually symmetric.

Handling multiple hops in SQL/PGQ

In the next step we want to find friends of friends. The goal is to figure out if people know each other indirectly. The following query shows how this works:

SELECT *

FROM GRAPH_TABLE (social

MATCH (a IS person)-[IS knows]->

(b IS person)-[IS knows]->(c IS person)

COLUMNS (a.name AS a, b.name AS via, c.name AS c) ) ORDER BY a, c, via;

a | via | c

-------+-------+-------

Alice | Bob | Alice

Alice | Carol | Bob

Alice | Bob | Carol

Alice | Carol | Dan

Bob | Alice | Bob

Bob | Carol | Bob

Bob | Alice | Carol

Bob | Carol | Dan

Carol | Bob | Alice

Carol | Bob | Carol

Carol | Dan | Eve

Dan | Eve | Frank

Eve | Frank | Alice

Frank | Alice | Bob

Frank | Alice | Carol

(15 rows)

We need to expand the graph query and essentially add the edges from the second to the third hop. But, taking a look at the data we will find something interesting: We can see that Alice knows Bob who in turn also knows Alice (“Alice -> Bob -> Alice”). While the data reflects that it does not make too much sense semantically. In most cases one might want to skip those rows. So how can this be done? The answer is of course a WHERE-clause

SELECT *

FROM GRAPH_TABLE (social

MATCH (a IS person)-[IS knows]->

(b IS person)-[IS knows]->(c IS person)

WHERE a.id <> c.id

COLUMNS (a.name AS a, b.name AS via, c.name AS c)

)

ORDER BY a, c, via;

a | via | c

-------+-------+-------

Alice | Carol | Bob

Alice | Bob | Carol

Alice | Carol | Dan

Bob | Alice | Carol

Bob | Carol | Dan

Carol | Bob | Alice

Carol | Dan | Eve

Dan | Eve | Frank

Eve | Frank | Alice

Frank | Alice | Bob

Frank | Alice | Carol

(11 rows)

What matters here is that the WHERE-clause can be inside the GRAPH_TABLE definition.

SQL/PGQ queries under the hood

After this brief introduction to graph queries in PostgreSQL 19 and beyond, it makes sense to take a look and understand what happens under the hood. The best way to do this is to take a look at what EXPLAIN has to say:

tutorial=# explain SELECT *

FROM GRAPH_TABLE (social

MATCH (a IS person)-[IS knows]->

(b IS person)-[IS knows]->(c IS person)

WHERE a.id <> c.id

COLUMNS (a.name AS a, b.name AS via, c.name AS c)

)

ORDER BY a, c, via;

QUERY PLAN

------------------------------------------------------------------

Sort (cost=575.72..588.55 rows=5132 width=96)

Sort Key: person.name, person_2.name, person_1.name

-> Hash Join (cost=145.96..259.45 rows=5132 width=96)

Hash Cond: (knows_1.b = person_2.id)

Join Filter: (person.id <> person_2.id)

-> Hash Join (cost=117.74..217.66 rows=5138 width=72)

Hash Cond: (knows.b = person_1.id)

-> Hash Join (cost=28.23..64.01 rows=2040 width=40)

Hash Cond: (knows.a = person.id)

-> Seq Scan on knows

(cost=0.00..30.40 rows=2040 width=8)

-> Hash (cost=18.10..18.10 rows=810 width=36)

-> Seq Scan on person

(cost=0.00..18.10 rows=810 width=36)

-> Hash (cost=64.01..64.01 rows=2040 width=44)

-> Hash Join

(cost=28.23..64.01 rows=2040 width=44)

Hash Cond: (knows_1.a = person_1.id)

-> Seq Scan on knows knows_1

(cost=0.00..30.40 rows=2040 width=8)

-> Hash (cost=18.10..18.10 rows=810 width=36)

-> Seq Scan on person person_1

(cost=0.00..18.10 rows=810 width=36)

-> Hash (cost=18.10..18.10 rows=810 width=36)

-> Seq Scan on person person_2

(cost=0.00..18.10 rows=810 width=36)

(20 rows)

What can we actually see here? The important point here is that there are no additional executor nodes or additional things we can see in the execution plan. What PostgreSQL does is to simply rewrite the query behind the scenes to make the syntax easier and more compact to read which is a huge advantage. The CREATE PROPERTY GRAPHstatement also helps to describe your data model more to actually allow for this simplification of the SQL syntax.

Next: Heterogeneous Graphs in SQL/PGQ on PostgreSQL 19

In the next section of this tutorial we will start to deal with heterogeneous graphs in SQL and see how we can handle more complex data models.

If you want to learn more, stay tuned. Also, if you want to learn more, feel free to send feedback and more information.

2026

CYBERTEC PostgreSQL International GmbH

Code Snippet ma-customfonts 3.4.4

Your First Graph Query in PostgreSQL 19

SQL/PGQ: Handling a social network

Defining a property graph in PostgreSQL

Running your first SQL/PGQ query

Handling multiple hops in SQL/PGQ

SQL/PGQ queries under the hood

Next: Heterogeneous Graphs in SQL/PGQ on PostgreSQL 19