PostgreSQL as Graph database with AGE - simple example

Here is a very simple example of graph with AGE PostgreSQL extension. In this example, we will assume we are trying to match a donor who can potentially donate to a patient based on the matching blood group.

Let's first create a graph using AGE extension. We start at a place where the AGE extension is already loaded. Here is the link to get started, if you are on Azure Apache AGE Extension | Microsoft Learn and for vanilla AGE apache/age: post install .

postgres=# SELECT create_graph('blood_donation');
NOTICE:  graph "blood_donation" has been created
 create_graph 
--------------

(1 row)        

Now let's add the connections between the blood groups using openCypher queries (apache/age: README.md.) The connection is added into the graph using relationship CAN_GIVE_TO with known facts such as O- blood type is a universal donor and same blood group donation is sound etc.

postgres=# SELECT * FROM cypher('blood_donation', $$
  CREATE (o:Blood {type: 'O-'})-[:CAN_GIVE_TO]->(a:Blood {type: 'A+'})
  CREATE (o)-[:CAN_GIVE_TO]->(b:Blood {type: 'B+'})
  CREATE (a)-[:CAN_GIVE_TO]->(a)
$$) AS (r agtype);
 r 
---
(0 rows)        

A small diversion, we will quickly look at how these graphs are represented as the final PostgreSQL tables. We can see that edges are all the blood types with specific ids.

postgres=# SELECT * FROM cypher('blood_donation', $$
  MATCH (b:Blood) RETURN b
$$) AS (blood agtype);
                                      blood                                      
---------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Blood", "properties": {"type": "O-"}}::vertex
 {"id": 844424930131970, "label": "Blood", "properties": {"type": "A+"}}::vertex
 {"id": 844424930131971, "label": "Blood", "properties": {"type": "B+"}}::vertex        

And then vertices represent the connections between those edges. By looking at the "start_id" and "end_id" we can figure out the vertex representation.

postgres=# SELECT * FROM cypher('blood_donation', $$
  MATCH ()-[e:CAN_GIVE_TO]->() RETURN e
$$) AS (edge agtype);
                                                               edge                                                               
----------------------------------------------------------------------------------------------------------------------------------
 {"id": 1125899906842625, "label": "CAN_GIVE_TO", "end_id": 844424930131970, "start_id": 844424930131969, "properties": {}}::edge
 {"id": 1125899906842627, "label": "CAN_GIVE_TO", "end_id": 844424930131970, "start_id": 844424930131970, "properties": {}}::edge
 {"id": 1125899906842626, "label": "CAN_GIVE_TO", "end_id": 844424930131971, "start_id": 844424930131969, "properties": {}}::edge
(3 rows)        

Now let us enter a new set of vertices which indicates donors and patients and their respective blood groups.

postgres=# SELECT * FROM cypher('blood_donation', $$
  CREATE (:Donor {name: 'D1', blood: 'O-'})
  CREATE (:Donor {name: 'D2', blood: 'A+'})
  CREATE (:Patient {name: 'P1', blood: 'A+'})
  CREATE (:Patient {name: 'P2', blood: 'B+'})
$$) AS (r agtype);
 r 
---
(0 rows)        

Finally, let's use the magic of graph to find out who can donate to whom. As shown below with single query you have the insight at your disposal of which donors can donate to which patients.

postgres=# SELECT * FROM cypher('blood_donation', $$
  MATCH (d:Donor), (p:Patient),
        (db:Blood {type: d.blood})-[:CAN_GIVE_TO]->(pb:Blood {type: p.blood})
  RETURN d.name AS donor, d.blood AS d_type, p.name AS patient, p.blood AS p_type
$$) AS (donor agtype, d_type agtype, patient agtype, p_type agtype);
 donor | d_type | patient | p_type 
-------+--------+---------+--------
 "D1"  | "O-"   | "P1"    | "A+"
 "D1"  | "O-"   | "P2"    | "B+"
 "D2"  | "A+"   | "P1"    | "A+"
(3 rows)        

As evidenced AGE with PostgreSQL can be used to represent very serious workloads and gain insights efficiently. This example is purposefully kept simple to understand graph extension usage. In an upcoming post, we will explore how AI agent could use graph with AGE and PostgreSQL to augment the understanding of the data at hand and gain deep insights. Until then Bye 👋🙂

#Graph #PostgreSQL #AGEPostgreSQLExtension

Acknowledgements: Thank you John Gemignani for the review and suggestions.