DOCSEARCHbyAlgolia
Submit Docs

pgGraph Documentation

repository·main·Indexed 19 days ago

https://github.com/evokoa/pggraph

pgGraph is a PostgreSQL extension (supported on versions 14 through 18) that provides a high-speed graph execution layer for traversals and shortest path queries. It operates as a derived state engine, using existing relational tables as the source of truth and building optimized Compressed Sparse Row (CSR) edge stores to avoid expensive recursive SQL joins. Key features include mmap-based memory efficiency, circuit breakers for resource safety, and a stabilized SQL API.

Tokens
143.3K
Snippets
338
Records
608
Agent score
64%

What's inside pgGraph

  1. Overview of pgGraph

    main

    pgGraph provides graph database capabilities directly on top of existing PostgreSQL data. It allows you to perform complex graph queries—such as finding shortest paths, mapping relationships, and discovering networks—using standard SQL without migrating your data to a separate graph database.

    PostgreSQL remains your single source of truth, meaning all application writes continue to use standard Postgres tables, and all existing constraints, WAL, MVCC, backups, ACLs, and Row Level Security (RLS) policies remain fully applicable.

  2. Overview of the pgGraph SQL API

    main

    The pgGraph SQL surface is implemented by the graph crate. All public functions are located within the graph schema.

    For authoritative technical specifications, refer to:

    • SQL 1.0 profile: A machine-checked list of public function groups, compatibility dispositions, and executable assurance dimensions.
    • Exact SQL Signatures: The authoritative source for overloads, defaults, return types, and result columns, generated from the frozen release schema.

  3. Overview of pgGraph 1.0 Release

    main

    pgGraph 1.0 is a production-ready, PostgreSQL-first graph engine. It is designed to run on PostgreSQL 14 through 18.

    Key characteristics of the 1.0 release include:

    • Data Model: PostgreSQL tables serve as the source of truth for every durable mutation.
    • API Support: Stabilized SQL API and an explicitly documented GQL profile.
    • Core Features: Projection storage and synchronization, operational tooling, and defined upgrade behavior.

    Note on Scope: Version 1.0 does not include full ISO GQL conformance, PostgreSQL 19 SQL/PGQ support, WAL-driven synchronization, semantic-guided search, distributed execution, or schema-flexible dynamic graphs. These features are part of the post-1.0 roadmap.

  4. What is pgGraph?

    main

    pgGraph is a PostgreSQL extension that enables searching, traversing, and explaining relationships within standard PostgreSQL tables. It operates by registering tables and relationships to build a specialized graph index, while PostgreSQL remains the primary source of truth.

    Key capabilities include:

    • Table & Edge Registration: Define nodes and relationships (FK-style or edge-tables).
    • Search: SQL-based search over registered columns using contains, exact, prefix, and token modes.
    • Traversal: Bounded BFS and DFS over CSR edges with support for direction, edge types, filtering, and pagination.
    • GQL Support: A subset of GQL via graph.gql() for pattern matching and mapped writes (CREATE, SET, REMOVE, DELETE, DETACH DELETE, MERGE).
    • Pathfinding: Unweighted and weighted shortest path calculations.
    • Persistence: Fast startup using .pggraph artifacts stored in PostgreSQL-managed storage.

    Note: The current public API consists of SQL functions in the graph schema. SQL/PGQ is not supported in version 1.0 on PostgreSQL 14 through 18.

  5. Understand the pgGraph Build Sequence and Roadmap

    main

    The pgGraph development follows a structured multi-stage build sequence designed to move from CI/CD stability to SQL/PGQ compatibility, operational hardening, and high-performance graph engine capabilities.

    Key Development Stages:

    • Stage 0 (CI): Focuses on code quality (fmt, clippy, tests, docs) and pgrx integration.
    • Stage 1 (Correctness & Security): Implements durable ingest, bidirectional BFS, _sync_log retention, and RLS (Row-Level Security) topology boundaries.
    • Stage 2 (Operational Hardening): Adds poison-row dead-lettering, atomic artifact commits, and truncated signals for capped traversals.
    • Stage 2P (SQL/PGQ Compatibility): A parallel track targeting PostgreSQL 19 GA. It includes property graph importing (graph.import_property_graph()), LDBC benchmarking, and the graph.graph_table() SQL/PGQ pattern grammar.
    • Stage 3 (Live Mode): Enables real-time features like auto-maintenance, weighted shortest paths over layered overlays, and WAL/logical-decoding sync designs.
    • Stage 4 (Hybrid Auto Mode): Implements projection_mode = 'auto' policies.
    • Stage 5 (Performance): Focuses on artifact validation caching, shared mmap sections, and direction-optimizing BFS.
    • Stage 6 (Refactors): Finalizes overlay unification and WAL sync implementation.

  6. Querying the graph with SQL

    main

    Graph queries in pgGraph are executed as standard SQL calls. The API provides several ways to interact with the graph data depending on your use case:

    • Primitive functions: For exact, low-level control.
    • Workflow wrappers: Application-friendly wrappers for common tasks like search, expansion, paths, connections, and summaries.
    • GQL/openCypher: A GQL-compatible subset and an openCypher compatibility surface for graph pattern queries.
    • Mapped writes: Narrow writes performed over registered tables and edges.

  7. Understand the current state of the pgGraph Full Graph Engine

    main

    The Full Graph Engine is currently at version 1.0.0. The authoritative documentation for the completed 1.0 scope is located in ../v1-release/README.md.

    Key completed features for the 1.0 release include:

    • Rust/pgrx Boundary: Mapped stores with validated ranges, Rust ASan/Miri compliance, and error unwinding through pgrx.
    • Security and Identity: R1 correctness, authorization, durable identity, and transaction isolation.
    • Memory Containment: Enforced checked build policies, live construction reservations, and repeatable RSS/Linux-PSS gates.
    • Safe Publication: Checksummed pointer CAS, cross-process locks, and bounded GC.
    • Artifact Management: Coherent source fence, governed runs, and mapped artifact v6.
    • Bounded Operations: Mmap residency, bounded sync ingest, and range compaction.

    Future work (Post-1.0) includes full ISO GQL support, PostgreSQL 19 SQL/PGQ integration, and generalized costed streaming.

  8. Current pgGraph Capabilities and Supported PostgreSQL Versions

    main

    As of the current baseline, pgGraph is designed to work with PostgreSQL 14 through 18. It uses PostgreSQL tables as the primary source of truth.

    Core Engine Features:

    • Graph Management: Manual and discovered table/relationship registration, named graphs, grants, quotas, and tenant scoping.
    • Querying & Analytics: Bounded search, traversal, paths, workflows, filters, aggregation, and connected-component analytics.
    • GQL Writes: Supported GQL writes update mapped PostgreSQL tables first, ensuring that PostgreSQL constraints, triggers, ACLs, RLS, MVCC, and row identity are preserved.
    • Maintenance: Trigger-log synchronization and scheduled maintenance (compaction, repair, and cleanup).
    • Indexing: Atomic, validated persisted indexes with crash recovery.
    • Resource Management: Enforced policies for byte, row, work, disk, file, and elapsed-time usage with stable resource diagnostics.

    For specific feature availability, refer to the SQL 1.0 profile, GQL 1.0 profile, and Versioning And Compatibility documentation.

  9. Understand the pgGraph repository structure

    main

    The graph/ directory is the core extension crate. Production logic resides in graph/src. The repository is organized into several functional layers:

    • Source Modules: The core engine, including storage (node_store.rs, edge_store.rs), query execution (query/), and persistence (persistence.rs).
    • SQL Facade Modules: The user-facing SQL interface located in src/sql_facade/. These modules wrap the internal engine logic into accessible SQL entrypoints.
    • Test Layout: Includes pure Rust unit tests, pgrx SQL integration tests (src/pg_tests/), and heavy release gates (tests/heavy/).
    • Feature Flags: Controls PostgreSQL version support and development capabilities.

  10. Access the pgGraph Sandbox

    main
    The sandbox directory contains temporary scripts used for the pgGraph playground and SQL-facing benchmarks. It serves as a workspace for interacting with the playground environment and running performance evaluations.

  11. What is pgGraph?

    main

    pgGraph is a high-speed, cache-friendly graph execution layer designed to run inside PostgreSQL. Unlike a dedicated graph database that requires moving data, pgGraph operates on data that already lives in your standard relational tables.

    Core Concepts

    • Derived State: pgGraph is not a replacement for PostgreSQL. It treats your relational tables as the system of record and builds an optimized, read-heavy graph runtime from that metadata. PostgreSQL remains responsible for storage, WAL, MVCC, and durability.
    • CSR-based Execution: It uses Compressed Sparse Row (CSR) edge stores to achieve $O(1)$ adjacency. This allows traversals to be executed as raw, graph-native memory scans rather than expensive recursive SQL joins.
    • Memory Efficiency: Persisted .pggraph artifacts are mapped read-only. This allows the operating system page cache to share physical graph pages across multiple PostgreSQL backends, reducing memory overhead.
    • Safety Mechanisms: To prevent unbounded graph expansion from crashing the database, pgGraph implements circuit breakers including depth limits, visited-node tracking, frontier limits, and strict OOM/memory safeguards.

  12. What is pgGraph and how does it work?

    main

    pgGraph is a cache-friendly graph execution layer designed to run on top of existing relational data within PostgreSQL. Unlike graph databases that require moving data into a new storage format, pgGraph treats PostgreSQL as the source of truth and builds a read-optimized graph runtime from your existing tables.

    Core Concepts

    • Derived State: pgGraph state is strictly derived from your original PostgreSQL tables. Source tables, constraints, indexes, ACLs, RLS, and backups remain standard PostgreSQL features.
    • CSR-based Execution: It uses Compressed Sparse Row (CSR) edge storage (both forward and backward) to achieve $O(1)$ adjacency access. This allows traversals to be performed as native memory scans rather than recursive SQL joins.
    • Memory Efficiency: Persistent .pggraph files are memory-mapped as read-only. This allows multiple PostgreSQL backend processes to share the same physical pages via the OS page cache, avoiding redundant copies in the Rust heap.
    • Safety Mechanisms: To prevent database crashes during unbounded traversals, pgGraph implements explicit circuit breakers, including depth limits, visited node tracking, frontier limits, pagination, and strict OOM/memory protection.

    Comparison with other technologies

    • vs. Apache AGE: Apache AGE is a storage layer (property graph database) that requires moving data to agtype and using Cypher. pgGraph is an execution layer that works with your existing schema using SQL functions like graph.search() and graph.shortest_path().
    • vs. PostgreSQL 19 SQL/PGQ: SQL/PGQ is a query language and pattern matching standard integrated into the PostgreSQL optimizer. pgGraph is a specialized engine for workloads involving repeated traversals of the same topology, utilizing pre-computed CSR adjacency and rebuildable artifacts for high-speed, bounded traversals.