The cover image above compares two representations of the same data model, separated by more than 45 years: one shows a PostgreSQL extension for Visual Studio Code visualizing a graph with Apache AGE, and the other displays the employee hierarchy from the Oracle 2.3 User Guide. Hierarchical and graph traversal queries have long been a topic in relational databases. Early SQL, or SEQUEL, used employee-department and manager relationships, which led to the need for graph traversal syntax beyond self-joins. The first commercial RDBMS had a CONNECT BY syntax (see Oracle 2.3 User Guide), later replaced by recursive WITH clauses in the SQL standard. PostgreSQL 19 adds SQL/PGQ support for property graph queries. Meanwhile, NoSQL graph databases like Neo4j, with Cypher, have gained popularity, and this capability is now accessible in PostgreSQL via the Apache AGE extension.
Apache AGE on PostgreSQL
I've built a small example based on the legendary EMP-DEPT schema from 45 years ago, running on Azure, because, according to https://www.pgextensions.org/, it is the only managed service that supports it:
I'm using HorizonDB, the PostgreSQL-compatible managed service for enterprise workloads, which is currently in preview (but you can also use the free ghcr.io/pglayers/pglayers-azure:17 image from pglayers). I've enabled Apache AGE by adding it to the azure.extensions list:
postgres=> \dconfig azure.extensions
List of configuration parameters
Parameter | Value
------------------+----------------------------------------------
azure.extensions | pg_diskann,vector,pg_textsearch,azure_ai,age
postgres=> \dconfig server_version
List of configuration parameters
Parameter | Value
----------------+-----------------------------------------------
server_version | 17.9 (Azure HorizonDB (81895d42565)(release))
The example is deliberately straightforward. Besides the departments reference, it includes the employee entity, and one relationship: each employee's immediate manager. In the relational model, both are stored in the same table, with the manager relationship represented through a self-referencing foreign key. SQL handles such relationships using joins, with CONNECT BY or WITH RECURSIVE for graph structures. Apache AGE represents relationships as graph edges and supports openCypher syntax, significantly simplifying complex graph queries.
Graph model
Property graph databases model the same information differently than relational databases. Entities are represented as nodes (aka vertices), and relationships (aka edges) connect them. Rather than reconstructing relationships through joins, relationships are stored explicitly as graph edges and traversed using graph patterns.
I install the extension and set the search path to include ag_catalog with the Apache AGE functions and datatypes:
create extension if not exists age;
set search_path = "$user", public, ag_catalog;
I generate a graph, which is equivalent to a schema:
select create_graph('emp_dept_graph');
PostgreSQL can now execute Cypher queries against this graph by calling cypher() with the graph name and query, returning an agtype result.
Graph nodes (vertices () )
In Apache AGE, all Cypher queries are in dollar-quoted strings. The following creates the nodes for the departments:
select * from cypher('emp_dept_graph', $openCypher$
CREATE
(:Department { deptno:10, name:"Administration", loc:"New York" }),
(:Department { deptno:20, name:"Research", loc:"San Francisco" }),
(:Department { deptno:30, name:"Sales", loc:"Chicago" }),
(:Department { deptno:40, name:"Operations", loc:"Boston" })
$openCypher$) AS (result agtype);
The parentheses () draw a node (think of an ASCII art version of a graph), (:Department) adds a label to it, and the JSON-like { deptno:40, name:'Operations', loc:'Boston'} adds properties as key-value pairs, similar to JSON.
I do the same to create the employees, without specifying their department, only their own properties:
select * from cypher('emp_dept_graph', $openCypher$
CREATE
(:Employee {empno:7839,name:"OATES",job:"President",sal:5000}),
(:Employee {empno:7566,name:"JONES",job:"Manager",sal:2975}),
(:Employee {empno:7698,name:"BLAKE",job:"Manager",sal:2850}),
(:Employee {empno:7782,name:"CLARK",job:"Manager",sal:2450}),
(:Employee {empno:7788,name:"SCOTT",job:"Analyst",sal:3000}),
(:Employee {empno:7902,name:"FORD",job:"Analyst",sal:3000}),
(:Employee {empno:7999,name:"WILSON",job:"Analyst",sal:2800}),
(:Employee {empno:7876,name:"ADAMS",job:"Clerk",sal:1100}),
(:Employee {empno:7369,name:"SMITH",job:"Clerk",sal:800}),
(:Employee {empno:8000,name:"JAKES",job:"Clerk",sal:1000}),
(:Employee {empno:7499,name:"ALLEN",job:"Salesman",sal:1600}),
(:Employee {empno:7521,name:"WARD",job:"Salesman",sal:1250}),
(:Employee {empno:7654,name:"MARTIN",job:"Salesman",sal:1250}),
(:Employee {empno:7844,name:"TURNER",job:"Salesman",sal:1500}),
(:Employee {empno:7900,name:"JAMES",job:"Clerk",sal:950}),
(:Employee {empno:8001,name:"CARTER",job:"Salesman",sal:1400}),
(:Employee {empno:7934,name:"MILLER",job:"Clerk",sal:1300})
$openCypher$) AS (result agtype);
The nodes are stored with their properties. Now I can define the relationships to form a graph.
Graph relationships (edges -[]->)
I'll add the relationship to show where an employee works in a department, and their position in the hierarchy.
In a SQL model, employees reference their department via a DEPTNO foreign key and their manager through an MGR foreign key, with the manager being another employee. In relational databases, relationships are represented by key values rather than direct pointers between rows, and entities are independent of the navigation between them. Instead, the department number and manager's employee number are attributes of the employee entity, and relationships are established during queries with joins. Simple many-to-one relationships are represented by foreign keys. However, more complex relationships, such as many-to-many relationships or relationships with their own attributes, require an additional association table.
In a graph database, relationships are at the core of the model. The nodes are the entities and the edges are the relationships. In ASCII art, this can be described as: (:Employee)-[:WORKS_IN]->(:Department).
In SQL, relationships are queried using joins, and prior to the JOIN syntax, they were expressed as a Cartesian product in the FROM clause, with a WHERE clause to filter the desired combinations. A similar approach applies here. To establish the employee-department relationship, I define the set of (:Employee), (:Department) pairs that represent where each employee works and create a -[:WORKS_IN]-> edge between them.
select * from cypher('emp_dept_graph', $openCypher$
MATCH (e:Employee),(d:Department)
WHERE
(e.empno=7839 AND d.deptno=10)
OR (e.empno=7782 AND d.deptno=10)
OR (e.empno=7934 AND d.deptno=10)
OR (e.empno=7566 AND d.deptno=20)
OR (e.empno=7788 AND d.deptno=20)
OR (e.empno=7902 AND d.deptno=20)
OR (e.empno=7369 AND d.deptno=20)
OR (e.empno=7876 AND d.deptno=20)
OR (e.empno=7999 AND d.deptno=20)
OR (e.empno=8000 AND d.deptno=20)
OR (e.empno=7698 AND d.deptno=30)
OR (e.empno=7499 AND d.deptno=30)
OR (e.empno=7521 AND d.deptno=30)
OR (e.empno=7654 AND d.deptno=30)
OR (e.empno=7844 AND d.deptno=30)
OR (e.empno=7900 AND d.deptno=30)
OR (e.empno=8001 AND d.deptno=30)
CREATE (e)-[:WORKS_IN]->(d)
$openCypher$) AS (result agtype);
Here is a similar query to declare the employee-manager relationship as (:Employee)-[:REPORTS_TO ]->(:Employee):
select * from cypher('emp_dept_graph', $openCypher$
MATCH (e:Employee),(m:Employee)
WHERE
(e.empno=7566 AND m.empno=7839)
OR (e.empno=7698 AND m.empno=7839)
OR (e.empno=7782 AND m.empno=7839)
OR (e.empno=7788 AND m.empno=7566)
OR (e.empno=7902 AND m.empno=7566)
OR (e.empno=7999 AND m.empno=7566)
OR (e.empno=7876 AND m.empno=7788)
OR (e.empno=7369 AND m.empno=7902)
OR (e.empno=8000 AND m.empno=7999)
OR (e.empno=7499 AND m.empno=7698)
OR (e.empno=7521 AND m.empno=7698)
OR (e.empno=7654 AND m.empno=7698)
OR (e.empno=7844 AND m.empno=7698)
OR (e.empno=7900 AND m.empno=7698)
OR (e.empno=8001 AND m.empno=7698)
OR (e.empno=7934 AND m.empno=7782)
CREATE (e)-[:REPORTS_TO { manager_level: 1 }]->(m)
$openCypher$ ) AS (result agtype);
To show an example of a relationship property, I've added the manager level using Cypher map syntax, which looks like JSON.
AGE Internals
Apache AGE stores metadata in two catalog tables:
postgres=> select * from ag_catalog.ag_graph
;
graphid | name | namespace
---------+----------------+----------------
26064 | emp_dept_graph | emp_dept_graph
(1 row)
postgres=> select * from ag_catalog.ag_label
;
name | graph | id | kind | relation | seq_name
------------------+-------+----+------+---------------------------------+-------------------------
_ag_label_vertex | 26064 | 1 | v | emp_dept_graph._ag_label_vertex | _ag_label_vertex_id_seq
_ag_label_edge | 26064 | 2 | e | emp_dept_graph._ag_label_edge | _ag_label_edge_id_seq
Department | 26064 | 3 | v | emp_dept_graph."Department" | Department_id_seq
Employee | 26064 | 4 | v | emp_dept_graph."Employee" | Employee_id_seq
WORKS_IN | 26064 | 5 | e | emp_dept_graph."WORKS_IN" | WORKS_IN_id_seq
REPORTS_TO | 26064 | 6 | e | emp_dept_graph."REPORTS_TO" | REPORTS_TO_id_seq
(6 rows)
The data is stored in nodes and edges tables:
postgres=> \d emp_dept_graph."Department"
Table "emp_dept_graph.Department"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+-----------------------------------------------------------------------------------------------------------------------------------
id | graphid | | not null | _graphid(_label_id('emp_dept_graph'::name, 'Department'::name)::integer, nextval('emp_dept_graph."Department_id_seq"'::regclass))
properties | agtype | | not null | agtype_build_map()
Indexes:
"Department_pkey" PRIMARY KEY, btree (id)
Inherits: emp_dept_graph._ag_label_vertex
postgres=> select * from emp_dept_graph."Department"
;
id | properties
-----------------+---------------------------------------------------------------------------------------------
844424930131969 | {"loc": "New York", "name": "Administration", "deptno": 10, "disp_label": "Administration"}
844424930131970 | {"loc": "San Francisco", "name": "Research", "deptno": 20, "disp_label": "Research"}
844424930131971 | {"loc": "Chicago", "name": "Sales", "deptno": 30, "disp_label": "Sales"}
844424930131972 | {"loc": "Boston", "name": "Operations", "deptno": 40, "disp_label": "Operations"}
(4 rows)
postgres=> \d emp_dept_graph."WORKS_IN"
Table "emp_dept_graph.WORKS_IN"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+-------------------------------------------------------------------------------------------------------------------------------
id | graphid | | not null | _graphid(_label_id('emp_dept_graph'::name, 'WORKS_IN'::name)::integer, nextval('emp_dept_graph."WORKS_IN_id_seq"'::regclass))
start_id | graphid | | not null |
end_id | graphid | | not null |
properties | agtype | | not null | agtype_build_map()
Indexes:
"WORKS_IN_end_id_idx" btree (end_id)
"WORKS_IN_start_id_idx" btree (start_id)
Inherits: emp_dept_graph._ag_label_edge
postgres=> select * from emp_dept_graph."WORKS_IN"
;
id | start_id | end_id | properties
------------------+------------------+-----------------+------------
1407374883553281 | 1125899906842625 | 844424930131969 | {}
1407374883553282 | 1125899906842626 | 844424930131970 | {}
1407374883553283 | 1125899906842627 | 844424930131971 | {}
1407374883553284 | 1125899906842628 | 844424930131969 | {}
1407374883553285 | 1125899906842629 | 844424930131970 | {}
1407374883553286 | 1125899906842630 | 844424930131970 | {}
1407374883553287 | 1125899906842631 | 844424930131970 | {}
1407374883553288 | 1125899906842632 | 844424930131970 | {}
1407374883553289 | 1125899906842633 | 844424930131970 | {}
1407374883553290 | 1125899906842634 | 844424930131970 | {}
1407374883553291 | 1125899906842635 | 844424930131971 | {}
1407374883553292 | 1125899906842636 | 844424930131971 | {}
1407374883553293 | 1125899906842637 | 844424930131971 | {}
1407374883553294 | 1125899906842638 | 844424930131971 | {}
1407374883553295 | 1125899906842639 | 844424930131971 | {}
1407374883553296 | 1125899906842640 | 844424930131971 | {}
1407374883553297 | 1125899906842641 | 844424930131969 | {}
(17 rows)
Looking at the table definitions, I can prevent duplicates by creating the following UNIQUE indexes:
CREATE UNIQUE INDEX department_deptno_uix
ON emp_dept_graph."Department"
(
(agtype_access_operator(properties, '"deptno"'))
);
CREATE UNIQUE INDEX employee_empno_uix
ON emp_dept_graph."Employee"
(
(agtype_access_operator(properties, '"empno"'))
);
CREATE UNIQUE INDEX works_in_uix
ON emp_dept_graph."WORKS_IN"(start_id,end_id);
CREATE UNIQUE INDEX reports_to_uix
ON emp_dept_graph."REPORTS_TO"(start_id,end_id);
I can also create a GIN index on the properties to accelerate searches by a property value:
CREATE INDEX employee_properties_gin
ON emp_dept_graph."Employee"
USING gin (properties);
Let's examine some queries and their corresponding translations on internal tables and indexes.
Query
I have already used the MATCH clause to identify the combinations of employees and departments to create the edges.
To find the manager of Jones (:Employee {name:"JONES"}), I match the relationship with a variable -[:REPORTS_TO]->(manager) and return manager.name property.
postgres=> SELECT * FROM cypher('emp_dept_graph', $openCypher$
MATCH (:Employee {name:"JONES"})-[:REPORTS_TO]->(manager)
RETURN manager.name
$openCypher$) AS (
manager agtype
);
manager
---------
"OATES"
(1 row)
I can add the department of the manager to the result by navigating through -[:WORKS_IN]->:
postgres=> SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (:Employee {name:"JONES"})
-[:REPORTS_TO]->(manager)
-[:WORKS_IN]->(department)
RETURN manager.name, department.name
$openCypher$) AS (
manager agtype,
department agtype
);
manager | department
---------+------------------
"OATES" | "Administration"
(1 row)
I can get the manager's manager with -[:REPORTS_TO]->()-[:REPORTS_TO]->(manager) but also with -[:REPORTS_TO*2]->:
postgres=> SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (:Employee {name:"JAKES"})-[:REPORTS_TO*2]->(manager)
RETURN manager.name
$openCypher$) AS <... (truncated)
by Franck Pachot
