Introducing graph database support in Azure Database for PostgreSQL

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

We are pleased to announce the addition of the Apache AGE extension. Azure Database for PostgreSQLThis is an important advancement in providing graph processing capabilities within the PostgreSQL ecosystem. This new extension provides a powerful toolset for developers who want to leverage graph databases along with the powerful enterprise features of Azure Database for PostgreSQL. AGE allows teams to move beyond the traditional RAG application pattern. GraphRAG Based on Azure Database for PostgreSQL.

Apache graph extensions (AGE) is a PostgreSQL extension developed by the Apache Incubator project. AGE is designed to provide graph database capabilities, allowing users to efficiently store and query graph data within PostgreSQL. Supports the openCypher query language, which allows for intuitive and expressive graph queries. AGE allows you to manage and analyze complex relationships within your data to uncover insights that traditional relational databases and semantic search might miss.

Key Features of AGE

Graph and relational data integration: AGE allows you to seamlessly integrate graph data with existing relational data in PostgreSQL. This hybrid approach allows you to get the benefits of both graph and relational models simultaneously.
openCypher Query Language: AGE integrates openCypher, a powerful and user-friendly query language designed specifically for graph databases. This feature simplifies the process of building and executing graph queries.
High performance: AGE is optimized for performance, ensuring efficient storage and retrieval of graph data thanks to support for indexing graph properties using GIN indexes.
Scalability: Built on the proven architecture of PostgreSQL, AGE inherits scalability and reliability to handle growing data sets and growing workloads.

Integrating AGE with Azure Database for PostgreSQL provides numerous benefits to developers and enterprises 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 a separate graph database solution.
Improved data analysis: AGE allows you to perform complex graph analysis directly within your PostgreSQL database to gain deeper insight into relationships and patterns in your data.
Cost-effective: Leveraging AGE within Azure Database for PostgreSQL allows you to consolidate your database infrastructure, lowering overall costs and reducing the complexity of your data architecture.
Security and compliance: Take advantage of Azure’s industry-leading security and compliance capabilities to protect your graph data and meet regulatory requirements.

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

1. Create an Azure Database for PostgreSQL instance

Start by setting up a new instance of Azure Database for PostgreSQL through the Azure portal or using the Azure CLI. Quickstart: Create using the Azure portal – Azure Database for PostgreSQL – Flexible Server | Microsoft L…

2. Activate and install the AGE extension

memo: Currently, the AGE extension is only available for newly created Azure Database for PostgreSQL flexible server instances running at least PG13 through PG16.
Once your PostgreSQL instance is up and running, you can install the AGE extension via: Activate extension In the Server Parameters section of the Azure Database for PostgreSQL blade in the Azure portal, run the following SQL command:

CREATE EXTENSION IF NOT EXISTS age CASCADE;

3. Create and query graph data

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

This example uses OpenCypher and AGE to determine the connections, or relationships, between actor Kevin Bacon and other actors and directors.

To achieve this, we need to create a set of nodes (vertices) and relationships (edges).

memo: You need to set up: ag_catalog To utilize cypher, you need to add a schema to your path. Otherwise, you must specify it directly in the query, as done in the following example.

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

ag_catalog.cypher(query)

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);

Create a Movie Node:

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

Create a relationship indicating that Kevin Bacon appeared in a movie:

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);

Create relationships that represent different actors in the same movie:

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);

Create a relationship indicating that the director directed the movie:

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 a cryptic query to show these relationships.

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);

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);

Find all movies starring 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'})
RETURN m.title AS Movie
$$) as (Movie agtype);

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 a graph database. You should replace ‘graph_name’ with the actual name of your graph (e.g. 6degrees_graph).

Get started for free with an Azure free account

Azure Database for PostgreSQL | Microsoft Azure

Quickstart: Create using the Azure portal – Azure Database for PostgreSQL – Flexible Server | Microsoft L…

IMBDB Movie Dataset

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

Graphs — Apache AGE Master Documentation

Apache AGE Documentation — Apache AGE Master Documentation

Using Cypher in CTE Expressions — Apache AGE Master Documentation

GraphRAG: Unlocking LLM discovery for descriptive personal data – Microsoft Research

Source link