TheLazy.Dev
Portfolio GitHub

Blog

database |postgres |apacheage |db

What's behind scenes of PostgreSQL from Apache AGE?

rrrokhtar

Mohamed Mokhtar

Posted on June 22, 2023

What's behind scenes of PostgreSQL from Apache AGE?

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:

SELECT * FROM create_graph('test');
Enter fullscreen mode Exit fullscreen mode

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?

SELECT * FROM test._;
Enter fullscreen mode Exit fullscreen mode

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.

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)
Enter fullscreen mode Exit fullscreen mode

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

Let's retrieve these vertices using a query:

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
Enter fullscreen mode Exit fullscreen mode

What if we want to do that using SQL?

test=# select * from test._ag_label_vertex;
       id        |     properties      
-----------------+---------------------
 844424930131971 | {"name": "Bob"}
 844424930131972 | {"name": "Alice"}
Enter fullscreen mode Exit fullscreen mode

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

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

Lets do the same query again

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
Enter fullscreen mode Exit fullscreen mode

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

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)
Enter fullscreen mode Exit fullscreen mode

Can we do that in SQL?

select * from  test._ag_label_vertex;
        id        |      properties      
------------------+----------------------
 844424930131971  | {"name": "Bob"}
 844424930131972  | {"name": "Alice"}
 1125899906842625 | {"name": "NotAlice"}
Enter fullscreen mode Exit fullscreen mode

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

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"
Enter fullscreen mode Exit fullscreen mode

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

select * from  test."NotPerson";
        id        |      properties      
------------------+----------------------
 1125899906842625 | {"name": "NotAlice"}
Enter fullscreen mode Exit fullscreen mode

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

Similarly, we can insert edges between these vertices:

INSERT INTO test."NotPerson" (properties) VALUES (agtype('{"name":"Mohamed"}'));
Enter fullscreen mode Exit fullscreen mode

Make sure of it? works on SQL!

select * from test."NotPerson"
test-# ;
 1125899906842625 | {"name": "NotAlice"}
 1125899906842626 | {"name": "NotAlice2"}
 1125899906842627 | {"name": "Mohamed"}
Enter fullscreen mode Exit fullscreen mode

DOES it work on MATCH cypher?
Lets see

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
Enter fullscreen mode Exit fullscreen mode

Works perfectly
As well as matching all nodes will include it:

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
Enter fullscreen mode Exit fullscreen mode

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!

💖 💪 🙅 🚩
rrrokhtar
Mohamed Mokhtar

Posted on June 22, 2023

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

What's behind scenes of PostgreSQL from Apache AGE?
database What's behind scenes of PostgreSQL from Apache AGE?

June 22, 2023