How PostgreSQL, PgVector, and Apache AGE Power Next-Gen Copilots

GraphRAG Solution Accelerator Project Overview

The GraphRAG Solution Accelerator for Azure Database for PostgreSQL uses an interesting solution for building a legal research copilot. Git

It demonstrates the integration of three advanced information retrieval technologies: vector search, semantic ranking, and GraphRAG. The project uses a dataset of 0.5 million US legal cases as the factual data source and showcases how these technologies can be combined to deliver high-quality answers to legal research queries.

GraphRAG Legal Cases PostgreSQL Project Architecture

Key Architectural Components

1. Technology Foundation

Azure Database for PostgreSQL Flexible Server serves as the central component of the architecture, functioning as both a relational and a graph database. This is achieved through the integration of the Apache AGE (A Graph Extension), which adds graph database capabilities to PostgreSQL.

PgVector is a PostgreSQL extension that provides powerful functionalities for working with vectors in high-dimensional space. This extension introduces a dedicated vector data type, operators, and functions that enable efficient storage, manipulation, and analysis of vector data directly within the PostgreSQL database. PgVector allows PostgreSQL to handle vector embeddings and perform similarity searches, making it an ideal choice for AI applications, recommendation systems, image recognition, natural language processing, and any scenario that requires handling high-dimensional vector data.

The extension supports vectors of up to 2,000 dimensions, with additional support recently added to store half vectors of up to 4,000 dimensions. PgVector enables efficient nearest neighbor search using various distance metrics including Euclidean distance (L2), cosine similarity, and inner product. It provides seamless integration with SQL queries, allowing users to combine vector similarity search with other SQL filtering or aggregation operations.

Key features of PgVector include native vector storage and search capabilities within PostgreSQL, support for various vector embedding types including text, images, and audio, and efficient indexing strategies such as IVFFlat and HNSW for approximate nearest neighbor search.

PgVector maintains ACID compliance, replication, point-in-time recovery, JOINs, and other PostgreSQL features while adding vector capabilities.

Apache AGE is a PostgreSQL extension that allows users to store and query graph data using the openCypher query language. The AGE architecture includes five main components, each operating at the transactional cache and storage levels of PostgreSQL:

Cypher query parser, embedded in cypher function calls
Query transformer that converts Cypher queries into query trees
Planner that understands graph operations
Executor for graph operation plan nodes
Full PostgreSQL transactional system (ACID)

2. Information Retrieval Architecture

Vector search is implemented by creating 1536-dimensional vectors of descriptions for each legal case, enabling semantic search across document content. The system stores vector data in specialized tables and uses them for the initial retrieval of relevant information.

Semantic ranking improves the accuracy of vector search by reranking results with a semantic ranking model, potentially increasing the relevance of top results by 10–20% (measured by NDCG@10). This requires a deployed Azure ML endpoint with a ranking model such as "bge-reranker-v2-m3".

GraphRAG is an advanced RAG technique introduced by Microsoft Research, involving three main stages:

Extracting a graph from the source data
Summarizing entities
Generating graph queries at runtime

3. Infrastructure Components

Azure OpenAI Service provides access to language models for natural language processing. The system requires the following minimum capacities:

GPT-4o: 30K tokens
GPT-4: 80K tokens
text-embedding-3-small: 120K tokens

Azure Container App Service is used for deploying and managing containerized applications, ensuring scalability and lifecycle management.

Azure ML Endpoint provides an endpoint for semantic ranking, reranking search results using specialized machine learning models.

Architecture Diagram

GraphRAG Legal Cases PostgreSQL Architecture

Key Architectural Features

Hybrid Data Model

The architecture uses PostgreSQL + PgVector + Apache Age as a unified store for both relational and graph data, simplifying data management and eliminating the need for separate graph databases. Apache AGE enables hybrid queries, combining traditional SQL with openCypher for graph operations.

Example:

SELECT * FROM cases 
WHERE vector_column <-> '[0.1, 0.2, ...]' < 0.5 
AND id IN ( 
    SELECT id FROM cypher('MATCH (c:Case)-[:CITES]->(p:Precedent) RETURN c.id') 
)

Information Retrieval GraphRAG Pipeline

The system leverages the citation graph structure at query time through specialized graph queries. The graph query is designed to use the prominence of legal cases as a signal to improve the accuracy of the information retrieval pipeline. The query is expressed as a mix of traditional relational and OpenCypher graph queries, executed in Postgres using the Apache AGE extension.

Scalability and Performance

The architecture ensures high performance through Apache AGE's optimization for efficient storage and retrieval of graph data, with support for property indexing using GIN indexes. The system inherits PostgreSQL's scalability and reliability, allowing it to handle growing datasets and increasing workloads.

Deployment and Integration Process

Deployment is performed via the Azure Developer CLI (azd), which automatically provisions all required Azure resources. The system supports both a quick-start approach using ready-made CSV files and full integration with the Microsoft GraphRAG library for custom data processing.

GraphRAG processing includes creating filtered tables from the original case data, exporting to CSV, indexing with the GraphRAG library, and generating embeddings for text units and community reports. The system supports backup and restoration of graph data between Docker containers and external databases.

This architecture represents a modern solution for legal research, combining the strengths of traditional relational databases with graph computing capabilities and advanced information retrieval techniques powered by artificial intelligence.

Conclusion

The combination of PostgreSQL with PgVector and Apache AGE creates a powerful foundation for developing sophisticated AI-driven applications. This trio enables developers to handle both structured relational data, high-dimensional vector embeddings, and complex graph relationships within a single database system, eliminating the need for multiple specialized databases.

Key Capabilities:

Semantic Search & Vector Analytics
- PgVector enables similarity searches across text, images, or audio embeddings, supporting applications like legal document retrieval, recommendation systems, and fraud detection.
- Hybrid search pipelines combine vector similarity with SQL-based metadata filtering, such as filtering legal cases by court jurisdiction before applying semantic search.
Graph-Powered Context
- Apache AGE adds graph database functionality, allowing AI models to traverse relationships in knowledge graphs, such as analyzing citation networks in legal cases or social connections in fraud detection.
- Supports openCypher queries for dynamic relationship discovery, which is essential for applications requiring contextual reasoning.
Unified Architecture
- Scalability: Inherits PostgreSQL’s ACID compliance and horizontal scaling, handling millions of vectors and complex graph traversals.
- Cost Efficiency: Avoids data duplication and reduces infrastructure complexity by merging relational, vector, and graph data layers.

Use Cases:

Legal Research Copilots: Combine vector search for semantic case matching with graph analysis of precedent citations.
AI Agents: Use graph relationships to guide LLM reasoning paths while grounding responses in vector-retrieved evidence.
Recommendation Engines: Blend collaborative filtering (graph-based user-item interactions) with content similarity (vector embeddings).

Advantages Over Siloed Solutions:

Hybrid Queries: Execute SQL, vector search, and graph traversals in a single query, such as finding legal cases semantically similar to a query and connected to specific statutes via citation graphs.
Real-Time Updates: Maintain consistency across vectors, graphs, and transactional data without ETL pipelines.
Enterprise Readiness: Leverage PostgreSQL’s security, backups, and role-based access control for AI workloads.

This integrated approach positions PostgreSQL as a versatile AI data platform, enabling developers to build context-aware applications that combine semantic understanding with relational and graph-based insights.

Published on 6/15/2025