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');
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._;
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)
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
What if we want to do that using 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
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
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
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?
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
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
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:
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
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:
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!
Top comments (0)