What's behind scenes of PostgreSQL from Apache AGE?
Tags: database, postgres, apacheage, db
Published: 2023-06-22 10:45:26

## PostgreSQL Backend with Apache AGE

In this blog post, we'll take a deep dive into what is happening in PostgreSQL's backend while working alongside Apache AGE, starting from creating a new graph and till querying data within that graph. 


I am excited to get started with you to learn about it!

### Creating a New Graph

First of all, let's create a new graph with the name `test`:

```SQL
SELECT * FROM create_graph('test');
```

Can you guess what happens in the background? 

Let me tell you: a lot of tables have been created for that graph. Not only tables, but we have also created a whole namespace for that graph named with the graph name. 

After that, what is the content of the graph?
- Nodes
- Edges

So, do we need tables for those?

```SQL
SELECT * FROM test._;
```
Click on the tab to see the suggestions. You will see the following tables:
- `_ag_label_edge`
- `_ag_label_edge_id_seq`
- `_ag_label_vertex`
- `_ag_label_vertex_id_seq`

Got it! These tables are created to store the nodes and edges in the graph.

### Querying Data from the Graph

Now that we have created a new graph, let's insert some data into it and retrieve it using a query. 

```SQL
SELECT * from cypher('test', $$ CREATE (n:Person{name: "Bob"}) RETURN n $$) as (res agtype);
SELECT * from cypher('test', $$ CREATE (n:Person{name: "Alice"}) RETURN n $$) as (res agtype)
```

We have inserted two vertices with the properties `name` set to `Alice` and `Bob`.

Let's retrieve these vertices using a query:

```SQL
SELECT * from cypher('test', $$ MATCH (n:Person) RETURN n $$) as (res agtype);
                                          res                                          
---------------------------------------------------------------------------------------
 {"id": 844424930131971, "label": "Person", "properties": {"name": "Bob"}}::vertex
 {"id": 844424930131972, "label": "Person", "properties": {"name": "Alice"}}::vertex
```

What if we want to do that using SQL?
```SQL
test=# select * from test._ag_label_vertex;
       id        |     properties      
-----------------+---------------------
 844424930131971 | {"name": "Bob"}
 844424930131972 | {"name": "Alice"}
```

What is the problem right there? We could not directly specify the LABEL of the query like when we asked for the nodes with LABEL **Person**
What if we have inserted some nodes with another label?
lets see 

```SQL
SELECT * from cypher('test', $$ MATCH (n:NotPerson{name: "NotAlice"}) RETURN n $$) as (res agtype);
                                            res                                             
--------------------------------------------------------------------------------------------
 {"id": 1125899906842625, "label": "NotPerson", "properties": {"name": "NotAlice"}}::vertex
```

Lets do the same query again
```SQL
test=# SELECT * from cypher('test', $$ MATCH (n:Person) RETURN n $$) as (res agtype);
                                          res                                          
---------------------------------------------------------------------------------------
 {"id": 844424930131971, "label": "Person", "properties": {"name": "Bob"}}::vertex
 {"id": 844424930131972, "label": "Person", "properties": {"name": "Alice"}}::vertex
```

And if we want to filter the another label we can do that for sure

```SQL
test=# SELECT * from cypher('test', $$ MATCH (n:NotPerson{name: "NotAlice"}) RETURN n $$) as (res agtype);
                                            res                                             
--------------------------------------------------------------------------------------------
 {"id": 1125899906842625, "label": "NotPerson", "properties": {"name": "NotAlice"}}::vertex
(1 row)
```

Can we do that in SQL?
```SQL
select * from  test._ag_label_vertex;
        id        |      properties      
------------------+----------------------
 844424930131971  | {"name": "Bob"}
 844424930131972  | {"name": "Alice"}
 1125899906842625 | {"name": "NotAlice"}
```

### Do we need a JOIN ?

**TBH** when I have shown you the tables created for the graph I have been honest but not **100%** because we are having on-going created **TABLES** like when we create a new **LABEL** or creating a new NODE with an non-existing **LABEL** it leads to make a new **TABLE**

So that in case we are going to list the tables with the namespace test we will see that new tables are there
```SQL
select * from  test.
test._ag_label_edge           test._ag_label_vertex_id_seq  test."NotPerson_id_seq"       
test._ag_label_edge_id_seq    test._label_id_seq            test."Person"                 
test._ag_label_vertex         test."NotPerson"              test."Person_id_seq"          
```

We have noticed that we got **TABLES** created for the **LABELS** and for sure they will include the NODES which are related to that **LABEL**
for **example** lets see the content of **NotPerson** Table

```SQL
select * from  test."NotPerson";
        id        |      properties      
------------------+----------------------
 1125899906842625 | {"name": "NotAlice"}
```

**Memory wise** it is not perfect but **joining-tables** and time-wise it is **perfect**

Similarly, we can insert edges between these vertices:

```SQL
INSERT INTO test."NotPerson" (properties) VALUES (agtype('{"name":"Mohamed"}'));
```
Make sure of it? works on SQL!
```
select * from test."NotPerson"
test-# ;
 1125899906842625 | {"name": "NotAlice"}
 1125899906842626 | {"name": "NotAlice2"}
 1125899906842627 | {"name": "Mohamed"}
```
DOES it work on MATCH cypher?
Lets see
```SQL
SELECT * from cypher('test', $$ MATCH (n:NotPerson{}) RETURN n $$) as (res agtype);
 {"id": 1125899906842625, "label": "NotPerson", "properties": {"name": "NotAlice"}}::vertex
 {"id": 1125899906842626, "label": "NotPerson", "properties": {"name": "NotAlice2"}}::vertex
 {"id": 1125899906842627, "label": "NotPerson", "properties": {"name": "Mohamed"}}::vertex
```
Works perfectly
As well as matching all nodes will include it:
```SQL
SELECT * from cypher('test', $$ MATCH (n) RETURN n $$) as (res agtype);
 {"id": 844424930131971, "label": "Person", "properties": {"name": "Bob"}}::vertex
 {"id": 844424930131972, "label": "Person", "properties": {"name": "Alice"}}::vertex
 {"id": 1125899906842625, "label": "NotPerson", "properties": {"name": "NotAlice"}}::vertex
 {"id": 1125899906842626, "label": "NotPerson", "properties": {"name": "NotAlice2"}}::vertex
 {"id": 1125899906842627, "label": "NotPerson", "properties": {"name": "Mohamed"}}::vertex
```

Which means inserting into LABEL's TABLE makes insertion into VERTEX's tables as well: which means we are safe till now

### Conclusion

In this blog post, we have seen how to create a new graph in **PostgreSQL** using Apache AGE and how to insert and **retrieve** data from the graph using queries and sql queries as well. 

I hope you found this blog post informative and helpful. Happy graphing!