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 (
manager agtype
);
manager
---------
"JONES"
(1 row)
I can also get all managers higher in the hierarchy with [:REPORTS_TO*2..] where *2.. defines the number of hops, from 2 to an unbounded upper limit.
Visualization
The following query returns all -[REPORTS_TO]-> relationships without specifying a start or an end. It represents the company's management hierarchy:
postgres=> SELECT * FROM cypher('emp_dept_graph', $openCypher$
MATCH (e)-[r:REPORTS_TO]->(m)
RETURN e,r,m
$openCypher$ ) AS (
employee agtype,
reports_to agtype,
manager agtype
);
employee | reports_to | manager
----------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex | {"id": 1688849860263937, "label": "REPORTS_TO", "end_id": 1125899906842625, "start_id": 1125899906842626, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842625, "label": "Employee", "properties": {"job": "President", "sal": 5000, "name": "OATES", "empno": 7839, "disp_label": "OATES"}}::vertex
{"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex | {"id": 1688849860263938, "label": "REPORTS_TO", "end_id": 1125899906842625, "start_id": 1125899906842627, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842625, "label": "Employee", "properties": {"job": "President", "sal": 5000, "name": "OATES", "empno": 7839, "disp_label": "OATES"}}::vertex
{"id": 1125899906842628, "label": "Employee", "properties": {"job": "Manager", "sal": 2450, "name": "CLARK", "empno": 7782, "disp_label": "CLARK"}}::vertex | {"id": 1688849860263939, "label": "REPORTS_TO", "end_id": 1125899906842625, "start_id": 1125899906842628, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842625, "label": "Employee", "properties": {"job": "President", "sal": 5000, "name": "OATES", "empno": 7839, "disp_label": "OATES"}}::vertex
{"id": 1125899906842629, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "SCOTT", "empno": 7788, "disp_label": "SCOTT"}}::vertex | {"id": 1688849860263940, "label": "REPORTS_TO", "end_id": 1125899906842626, "start_id": 1125899906842629, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex
{"id": 1125899906842630, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "FORD", "empno": 7902, "disp_label": "FORD"}}::vertex | {"id": 1688849860263941, "label": "REPORTS_TO", "end_id": 1125899906842626, "start_id": 1125899906842630, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex
{"id": 1125899906842631, "label": "Employee", "properties": {"job": "Analyst", "sal": 2800, "name": "WILSON", "empno": 7999, "disp_label": "WILSON"}}::vertex | {"id": 1688849860263942, "label": "REPORTS_TO", "end_id": 1125899906842626, "start_id": 1125899906842631, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842626, "label": "Employee", "properties": {"job": "Manager", "sal": 2975, "name": "JONES", "empno": 7566, "disp_label": "JONES"}}::vertex
{"id": 1125899906842635, "label": "Employee", "properties": {"job": "Salesman", "sal": 1600, "name": "ALLEN", "empno": 7499, "disp_label": "ALLEN"}}::vertex | {"id": 1688849860263946, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842635, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
{"id": 1125899906842636, "label": "Employee", "properties": {"job": "Salesman", "sal": 1250, "name": "WARD", "empno": 7521, "disp_label": "WARD"}}::vertex | {"id": 1688849860263947, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842636, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
{"id": 1125899906842637, "label": "Employee", "properties": {"job": "Salesman", "sal": 1250, "name": "MARTIN", "empno": 7654, "disp_label": "MARTIN"}}::vertex | {"id": 1688849860263948, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842637, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
{"id": 1125899906842638, "label": "Employee", "properties": {"job": "Salesman", "sal": 1500, "name": "TURNER", "empno": 7844, "disp_label": "TURNER"}}::vertex | {"id": 1688849860263949, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842638, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
{"id": 1125899906842639, "label": "Employee", "properties": {"job": "Clerk", "sal": 950, "name": "JAMES", "empno": 7900, "disp_label": "JAMES"}}::vertex | {"id": 1688849860263950, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842639, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
{"id": 1125899906842640, "label": "Employee", "properties": {"job": "Salesman", "sal": 1400, "name": "CARTER", "empno": 8001, "disp_label": "CARTER"}}::vertex | {"id": 1688849860263951, "label": "REPORTS_TO", "end_id": 1125899906842627, "start_id": 1125899906842640, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842627, "label": "Employee", "properties": {"job": "Manager", "sal": 2850, "name": "BLAKE", "empno": 7698, "disp_label": "BLAKE"}}::vertex
{"id": 1125899906842641, "label": "Employee", "properties": {"job": "Clerk", "sal": 1300, "name": "MILLER", "empno": 7934, "disp_label": "MILLER"}}::vertex | {"id": 1688849860263952, "label": "REPORTS_TO", "end_id": 1125899906842628, "start_id": 1125899906842641, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842628, "label": "Employee", "properties": {"job": "Manager", "sal": 2450, "name": "CLARK", "empno": 7782, "disp_label": "CLARK"}}::vertex
{"id": 1125899906842632, "label": "Employee", "properties": {"job": "Clerk", "sal": 1100, "name": "ADAMS", "empno": 7876, "disp_label": "ADAMS"}}::vertex | {"id": 1688849860263943, "label": "REPORTS_TO", "end_id": 1125899906842629, "start_id": 1125899906842632, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842629, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "SCOTT", "empno": 7788, "disp_label": "SCOTT"}}::vertex
{"id": 1125899906842633, "label": "Employee", "properties": {"job": "Clerk", "sal": 800, "name": "SMITH", "empno": 7369, "disp_label": "SMITH"}}::vertex | {"id": 1688849860263944, "label": "REPORTS_TO", "end_id": 1125899906842630, "start_id": 1125899906842633, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842630, "label": "Employee", "properties": {"job": "Analyst", "sal": 3000, "name": "FORD", "empno": 7902, "disp_label": "FORD"}}::vertex
{"id": 1125899906842634, "label": "Employee", "properties": {"job": "Clerk", "sal": 1000, "name": "JAKES", "empno": 8000, "disp_label": "JAKES"}}::vertex | {"id": 1688849860263945, "label": "REPORTS_TO", "end_id": 1125899906842631, "start_id": 1125899906842634, "properties": {"manager_level": 1}}::edge | {"id": 1125899906842631, "label": "Employee", "properties": {"job": "Analyst", "sal": 2800, "name": "WILSON", "empno": 7999, "disp_label": "WILSON"}}::vertex
(16 rows)
The output looks like JSON but is actually agtype, containing internal identifiers, labels, and properties. This result can be visualized in VS Code using the Microsoft extension for PostgreSQL. After running the same query, you can click on the 🫧 "Switch to Graph" button on the right side of the tabular result to view it as:
Here is more information about this VS Code extension: https://marketplace.visualstudio.com/items?itemName=ms-ossdata.vscode-pgsql
Execution Plan
Using EXPLAIN (ANALYZE, BUFFERS), I can examine the PostgreSQL execution plan that reads the edges and nodes:
Plan:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Merge Join (actual time=0.046..0.094 rows=13 loops=1)
Merge Cond: (m.id = r.end_id)
Buffers: shared hit=46
-> Merge Append (actual time=0.013..0.016 rows=11 loops=1)
Sort Key: m.id
Buffers: shared hit=5
-> Index Scan using _ag_label_vertex_pkey on _ag_label_vertex m_1 (actual time=0.003..0.003 rows=0 loops=1)
Buffers: shared hit=1
-> Index Scan using "Department_pkey" on "Department" m_2 (actual time=0.004..0.005 rows=4 loops=1)
Buffers: shared hit=2
-> Index Scan using "Employee_pkey" on "Employee" m_3 (actual time=0.004..0.005 rows=7 loops=1)
Buffers: shared hit=2
-> Materialize (actual time=0.021..0.051 rows=13 loops=1)
Buffers: shared hit=41
-> Nested Loop (actual time=0.018..0.043 rows=13 loops=1)
Buffers: shared hit=41
-> Index Scan using "REPORTS_TO_end_id_idx" on "REPORTS_TO" r (actual time=0.006..0.008 rows=13 loops=1)
Buffers: shared hit=2
-> Append (actual time=0.002..0.002 rows=1 loops=13)
Buffers: shared hit=39
-> Seq Scan on _ag_label_vertex e_1 (actual time=0.000..0.000 rows=0 loops=13)
Filter: (id = r.start_id)
-> Index Scan using "Department_pkey" on "Department" e_2 (actual time=0.000..0.000 rows=0 loops=13)
Index Cond: (id = r.start_id)
Buffers: shared hit=13
-> Index Scan using "Employee_pkey" on "Employee" e_3 (actual time=0.001..0.001 rows=1 loops=13)
Index Cond: (id = r.start_id)
Buffers: shared hit=26
Planning:
Buffers: shared hit=6
Planning Time: 0.293 ms
Execution Time: 0.142 ms
By converting Cypher queries into SQL, Apache AGE enables the PostgreSQL query planner to select optimal access methods.
The GIN index I created can be used for equality predicates on property values:
postgres=> EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (e:Employee {job:"Salesman"})
RETURN e.name, e.job, e.sal
ORDER BY e.name
$openCypher$) AS (
employee agtype,
job agtype,
salary agtype
);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=8.99..8.99 rows=1 width=96) (actual time=0.057..0.058 rows=5 loops=1)
Sort Key: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(e.id, _label_name('26064'::oid, e.id), e.properties), '"name"'::agtype]))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4
-> Bitmap Heap Scan on "Employee" e (cost=6.94..8.98 rows=1 width=96) (actual time=0.032..0.048 rows=5 loops=1)
Recheck Cond: (properties @> '{"job": "Salesman"}'::agtype)
Heap Blocks: exact=1
Buffers: shared hit=4
-> Bitmap Index Scan on employee_properties_gin (cost=0.00..6.94 rows=1 width=0) (actual time=0.010..0.010 rows=5 loops=1)
Index Cond: (properties @> '{"job": "Salesman"}'::agtype)
Buffers: shared hit=3
Planning:
Buffers: shared hit=1
Planning Time: 0.138 ms
Execution Time: 0.140 ms
(15 rows)
The main benefit of using PostgreSQL as a multi-model database is that extensions introduce new query APIs and data models, yet you don't need to learn the intricacies of a different database when troubleshooting performance issues.
Multiple relationships
The previous query was limited to one relationship (e)-[r:REPORTS_TO]->(m), but I can query all relationships with (e)-[r]->(m):
SELECT *
FROM cypher('emp_dept_graph', $openCypher$
MATCH (e)-[r]->(m)
RETURN e,r,m
$openCypher$ ) AS (
e agtype,
r agtype,
m agtype
);
The graphical visualization shows all relationships as edges and nodes of different colors:
Conclusion
The EMP/DEPT example has been used for decades to explain relational modeling, self-referencing relationships, and hierarchical queries. The same data can also be represented as a property graph, where relationships become first-class graph elements rather than being represented through foreign-key values stored in columns.
Apache AGE exposes this model as an extension to PostgreSQL. Nodes, edges, properties, indexes, execution plans, and storage remain PostgreSQL objects. Cypher queries are translated into SQL execution plans, allowing graph traversals to benefit from the PostgreSQL optimizer and indexing infrastructure.
Top comments (0)