This post is co-authored by Serdar Mumcu, Principal Software Engineer and Maxim Lukiyanov, Principal PM. 

We are excited to announce the addition of Apache AGE extension in Azure Database for PostgreSQL, a significant advancement that provides graph processing capabilities within the PostgreSQL ecosystem. This new extension brings a powerful toolset for developers looking to leverage a graph database with the robust enterprise features of Azure Database for PostgreSQL. 

What is Apache AGE?

Apache Graph Extension (AGE) is a PostgreSQL extension developed under the Apache Incubator project. AGE is designed to provide graph database functionality, enabling users to store and query graph data efficiently within PostgreSQL. It supports the openCypher query language, which allows for intuitive and expressive graph queries. With AGE, you can manage and analyze complex relationships within your data, uncovering insights that traditional relational databases and even semantic search might miss.

Key Features of AGE

• Graph and Relational Data Integration: AGE allows seamless integration of graph data with existing relational data in PostgreSQL. This hybrid approach enables you to benefit from both graph and relational models simultaneously.
• openCypher Query Language: AGE incorporates openCypher, a powerful and user-friendly query language specifically designed for graph databases. This feature simplifies the process of writing and executing graph queries.
• High Performance: AGE is optimized for performance, ensuring efficient storage and retrieval of graph data thanks to support for indexing of graph properties using GIN indices.
• Scalability: Built on PostgreSQL's proven architecture, AGE inherits its scalability and reliability, allowing it to handle growing datasets and increasing workloads.

Benefits of Using AGE in Azure Database for PostgreSQL

The integration of AGE in Azure Database for PostgreSQL brings numerous benefits to developers and businesses looking to leverage graph processing capabilities:

• Simplified Data Management: AGE's ability to integrate graph and relational data simplifies data management tasks, reducing the need for separate graph database solutions.
• Enhanced Data Analysis: With AGE, you can perform complex graph analyses directly within your PostgreSQL database, gaining deeper insights into relationships and patterns in your data.
• Cost Efficiency: By utilizing AGE within Azure Database for PostgreSQL, you can consolidate your database infrastructure, lowering overall costs and reducing the complexity of your data architecture.
• Security and Compliance: Leverage Azure's industry-leading security and compliance features, ensuring your graph data is protected and meets regulatory requirements.

Using AGE in Azure Database for PostgreSQL

To get started with Apache Graph Extension in Azure Database for PostgreSQL, follow these simple steps:

1. Create an Azure Database for PostgreSQL Instance

Begin by setting up a new instance of Azure Database for PostgreSQL through the Azure portal or using Azure CLI. Quickstart: Create with Azure portal - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn

2. Enable & Install the AGE Extension

Note: At this time, the AGE extension will only be available for newly created Azure Database for PostgreSQL Flexible Server instances running at least PG13 up to PG16.
Once your PostgreSQL instance is up and running, you can install the AGE extension by enabling the extension in the Server Parameters section of the Azure Database for PostgreSQL blade in the Azure Portal and then executing the following SQL command:

CREATE EXTENSION IF NOT EXISTS age CASCADE;

3. Create and Query Graph Data

With AGE installed, you can start creating and querying graph data using openCypher.  

In this example we’ll be using OpenCypher and AGE to determine the connections or relationships between the actor Kevin Bacon and other actors and directors.

To accomplish this, we’ll need to create a set of nodes (vertices) and relationships (edges):

Note: You will need to set the ag_catalog schema in your path to utilize cypher or you will need to specify it directly in the query as I’ve done in the following examples.

SET search_path = ag_catalog, "$user", public;

Or

ag_catalog.cypher(query)

1. Create nodes for Kevin Bacon, other actors, and directors:
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   CREATE (kb:Actor {name: 'Kevin Bacon'}),
          (a1:Actor {name: 'Actor 1'}),
          (a2:Actor {name: 'Actor 2'}),
          (d1:Director {name: 'Director 1'}),
          (d2:Director {name: 'Director 2'})
   $$) as (a agtype);​

   
   
   
   
2. Create movie nodes:
   
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   CREATE (m1:Movie {title: 'Movie 1'}),
          (m2:Movie {title: 'Movie 2'})
   $$) as (a agtype);​

   
   
   
3. Create relationships indicating Kevin Bacon acted in movies:
   
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (kb:Actor {name: 'Kevin Bacon'}), (m1:Movie {title: 'Movie 1'})
   CREATE (kb)-[:ACTED_IN]->(m1)
   $$) as (a agtype);
   
   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (kb:Actor {name: 'Kevin Bacon'}), (m2:Movie {title: 'Movie 2'})
   CREATE (kb)-[:ACTED_IN]->(m2)
   $$) as (a agtype);​

   
   
   
4. Create relationships indicating other actors acted in the same movies:
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (a1:Actor {name: 'Actor 1'}), (m1:Movie {title: 'Movie 1'})
   CREATE (a1)-[:ACTED_IN]->(m1)
   $$) as (a agtype);
   
   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (a2:Actor {name: 'Actor 2'}), (m2:Movie {title: 'Movie 2'})
   CREATE (a2)-[:ACTED_IN]->(m2)
   $$) as (a agtype);​

   
   
   
5. Create relationships indicating directors directed the movies:
   
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (d1:Director {name: 'Director 1'}), (m1:Movie {title: 'Movie 1'})
   CREATE (d1)-[:DIRECTED]->(m1)
   $$) as (a agtype);
   
   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (d2:Director {name: 'Director 2'}), (m2:Movie {title: 'Movie 2'})
   CREATE (d2)-[:DIRECTED]->(m2)
   $$) as (a agtype);​

Now that we have a populated graph, we can use cypher queries to demonstrate these relationships.

1. Find all actors who have acted with Kevin Bacon:
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (kb:Actor {name: 'Kevin Bacon'})-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(coactor:Actor)
   RETURN coactor.name AS CoActor
   $$) as (CoActor agtype);​

   
   
   
   
2. Find all directors who have directed Kevin Bacon:
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (kb:Actor {name: 'Kevin Bacon'})-[:ACTED_IN]->(m:Movie)<-[:DIRECTED]-(d:Director)
   RETURN d.name AS Director
   $$) as (Director agtype);​

   
   
   
   
3. Find all movies where Kevin Bacon and another specific actor have acted together:
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (kb:Actor {name: 'Kevin Bacon'})-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(coactor:Actor {name: 'Actor 1'})
   RETURN m.title AS Movie
   $$) as (Movie agtype);​

   
   
   
   
4. Find all directors who have directed movies with Kevin Bacon and another specific actor:
   

   SELECT * FROM ag_catalog.cypher('graph_name', $$
   MATCH (kb:Actor {name: 'Kevin Bacon'})-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(coactor:Actor {name: 'Actor 1'})
   MATCH (d:Director)-[:DIRECTED]->(m)
   RETURN d.name AS Director
   $$) as (Director agtype);​

These queries will help you explore the relationships between Kevin Bacon, other actors, and directors in your graph database. Remember to replace 'graph_name' with the actual name of your graph – perhaps 6degrees_graph.

Ready to dive in?

Get started for free with an Azure free account

Azure Database for PostgreSQL | Microsoft Azure

QuickStart: Create with Azure portal - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn

IMBDB Movie Dataset

Stay tuned for more updates and tutorials on how to make the most of AGE in Azure Database for PostgreSQL. Happy graph querying!

Learn More

Graphs — Apache AGE master documentation

Apache AGE’s documentation — Apache AGE master documentation

Using Cypher in a CTE Expression — Apache AGE master documentation