DEV Community

Mohamed Mokhtar
Mohamed Mokhtar

Posted on • Updated on

The script of AGE (ch. 01)


The AGE extension for PostgreSQL is a powerful tool that can be used to store and query graph data. It is easy to use and learn, and it is compatible with PostgreSQL.

In this blog post, we will take a deep dive into the internals of the AGE extension. We will discuss how it works, how it is implemented, and how it can be used to store and query graph data.

How it works

Introduction to Graph Databases

To understand the internals of the AGE extension, it is important to first understand the basics of graph databases.

A graph database is designed to store and manipulate data as a set of nodes and edges, where nodes are entities and edges are the relationships between them. Graph databases are particularly useful when working with complex data models with many relationships, allowing for faster queries and simpler data models.

Graph databases can be contrasted with traditional relational databases, which store data in rows and columns. While relational databases excel at storing data with regular, well-defined structures, graph databases are better suited to storing and querying data with complex and irregular relationships.

Introduction to PostgreSQL

PostgreSQL is a popular relational database management system (RDBMS) that is noted for its support of advanced features, such as stored procedures, triggers, and transactions. One of the reasons for PostgreSQL's popularity is its flexibility, since it can be used for a wide variety of applications.

Internals of AGE Extension

The AGE extension for PostgreSQL builds on top of the relational database foundations of PostgreSQL and adds graph database functionality by implementing a powerful and efficient graph storage engine. This section will outline the most important internals of the AGE extension.

The Script of the AGE

AGE has a sql script that is being called when it is loaded in top of postgresql, each version has its script and when the same major version it is updated we get a inter-update script which takes over the last version to the next one and the latest version is the last one with not preceding versions.

Here I will explain the current script age--1.2.0.sql

It starts with the APACHE license as expected
Then it creates a tables for the age catalog we can call it the menu of AGE

Image description

Explanation of the first created table:

CREATE TABLE ag_graph (
  name name NOT NULL,
  namespace regnamespace NOT NULL
Enter fullscreen mode Exit fullscreen mode

The CREATE TABLE statement in PostgreSQL is used to create a new SQL table. In the example you provided, we are creating a new table called ag_graph.

CREATE TABLE ag_graph (

This is where we specify the name of the new table we're creating.

name name NOT NULL,

The first column we're creating is called name. The name datatype is a built-in Postgres data type that allows you to store names or identifiers. We've also added a NOT NULL constraint to ensure that every row in the table has a value for this column.

namespace regnamespace NOT NULL

The second column we're creating is called namespace. Here we're using the regnamespace data type, which stands for registered namespace. This data type is used in Postgres to store the OID (object identifier) of a namespace. We've also added a NOT NULL constraint to ensure that every row in the table has a value for this column.

The WITH (OIDS) clause is an option that was available in previous versions of Postgres to enable the use of object identifiers (OIDs) in the table. However, OIDs have been deprecated since version 12 and this clause is no longer necessary. If you're running a version of Postgres that is 12 or later, you can omit this clause.

To summarize, the CREATE TABLE statement in the example creates a new table called ag_graph with two columns called name and namespace. The name column has a name data type and the namespace column has a regnamespace data type, and both columns have a NOT NULL constraint.

So that, when we create a new graph entry using the following query

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

A new entry gets inserted on that ag_graph table

select * from ag_graph;
 name | namespace 
 test | test
(1 row)
Enter fullscreen mode Exit fullscreen mode
rrr=# \d ag_graph;
                Table "ag_catalog.ag_graph"
  Column   |     Type     | Collation | Nullable | Default 
 name      | name         |           | not null | 
 namespace | regnamespace |           | not null | 
    "ag_graph_name_index" UNIQUE, btree (name)
    "ag_graph_namespace_index" UNIQUE, btree (namespace)
    "ag_graph_oid_index" UNIQUE, btree (oid)

Enter fullscreen mode Exit fullscreen mode

We have another table for the labels:

CREATE TABLE ag_label (
  name name NOT NULL,
  graph oid NOT NULL,
  id label_id,
  kind label_kind,
  relation regclass NOT NULL,
  seq_name name NOT NULL
Enter fullscreen mode Exit fullscreen mode

Other staff are the definitions of the functions like cypher function which is the starting point of the AGE the postgresql considers that a function like other functions but AGE considers that as the entry point
Here is the definition of them

-- query functions
CREATE FUNCTION ag_catalog.cypher(graph_name name = NULL,
                                  query_string cstring = NULL,
                                  params agtype = NULL)
Enter fullscreen mode Exit fullscreen mode


The AGE extension for PostgreSQL provides a powerful and flexible framework for working with graph databases. By building on top of the solid foundations of PostgreSQL, the AGE extension allows users to combine the benefits of a graph database with the scalability and efficiency of a relational database. Whether you are working on a small project or a large enterprise application, the AGE extension provides the tools you need to manage complex data models with ease.

References and resources

Top comments (0)