DuckPGQ: Empowering DuckDB with Graph Workloads

WIP Disclaimer: This project is currently a Work In Progress. Please refer to the documentation and resources for the latest updates.

DuckPGQ is a community-driven extension for DuckDB designed specifically for graph-based analytical workloads. It implements the SQL/PGQ standard, allowing users to execute high-performance graph queries using familiar SQL syntax directly within their existing analytical pipelines.

By integrating graph capabilities into DuckDB, DuckPGQ eliminates the need for separate graph databases for many use cases, bringing the power of graph pattern matching to an in-process analytical engine.

---

🚀 Getting Started

To begin using DuckPGQ, you must first install and load the extension. Below is a comprehensive guide for various environments.

Installation Matrix

Implementation Snippets

Python

import duckdb
conn = duckdb.connect()
conn.install_extension('duckpgq', repository='community')
conn.load_extension('duckpgq')

NodeJS

import { DuckDBInstance } from '@duckdb/node-api';
const instance = await DuckDBInstance.run('LOAD duckpgq');

R

library(duckdb)
con <- dbConnect(duckdb())
dbExecute(con, "INSTALL duckpgq FROM community")
dbExecute(con, "LOAD duckpgq")

---

🛠 Key Features

• SQL/PGQ Compliance: Full support for graph pattern matching as defined in the SQL:2023 standard.
• Extreme Performance: Utilizes DuckDB's columnar engine for efficient, in-process graph analytics.
• Zero-Friction Setup: Requires complex server configuration $\rightarrow$ Seamless integration with no external dependencies.

Mathematical Foundation

In graph theory, a graph is typically defined as $G = (V, E)$, where $V$ is a set of vertices and $E$ is a set of edges. DuckPGQ allows you to map these mathematical constructs directly onto relational tables.

---

🌐 Domain Explorations

1. Social Network Analysis

Using the snb.duckdb dataset, we can model relationships between people and forums.

Setup Checklist:

• Attach dataset
• Install/Load extension
• Define Property Graph

ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/snb.duckdb';
USE snb;
INSTALL duckpgq FROM community;
LOAD duckpgq;

CREATE OR REPLACE PROPERTY GRAPH snb 
VERTEX TABLES (Person, Forum) 
EDGE TABLES (
    Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id) DESTINATION KEY (Person2Id) REFERENCES Person (id) LABEL knows, 
    Forum_hasMember_Person SOURCE KEY (ForumId) REFERENCES Forum (id) DESTINATION KEY (PersonId) REFERENCES Person (id) LABEL hasMember
);

Graph Visualization:

Example Queries:

• Shortest Path: Find the distance from one person to all others.

  FROM GRAPH_TABLE (snb MATCH p = ANY SHORTEST (p1 : person WHERE p1.id as other_person_id, element_id(p), path_length(p)));
  

• Mutual Friends: Find common connections between two specific users.

  FROM GRAPH_TABLE (snb MATCH (p1 : Person WHERE p1.id = 16) - [k : knows] - (p2 : Person) - [k2 : knows] - (p3 : Person WHERE p3.firstName));
  

• Popularity Contest: Identify the top 3 most followed individuals.

  FROM GRAPH_TABLE (snb MATCH (follower : Person) - [follows : knows] - (person : Person) COLUMNS (person.id AS followerID)) 
  SELECT personID, firstname, lastname, COUNT(followerID) AS numFollowers 
  GROUP BY ALL ORDER BY numFollowers DESC LIMIT 3;
  

---

2. Airline Logistics

Analyzing flight routes and booking data using airline-data-small.duckdb.

Schema Definition:

ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/airline-data/datasets/airline-data-small.duckdb' AS airline;
USE airline;
INSTALL duckpgq FROM community;
LOAD duckpgq;

CREATE PROPERTY GRAPH flight_graph 
VERTEX TABLES (aircrafts_data, airports_data, bookings, flights, tickets, seats) 
EDGE TABLES (
    route SOURCE KEY (departure_airport) REFERENCES airports_data (airport_code) DESTINATION KEY (arrival_airport) REFERENCES airports_data (airport_code), 
    ticket_flights SOURCE KEY (ticket_no) REFERENCES tickets (ticket_no) DESTINATION KEY (flight_id) REFERENCES flights (flight_id), 
    bookings_tickets SOURCE KEY (book_ref) REFERENCES bookings (book_ref) DESTINATION KEY (ticket_no) REFERENCES tickets (ticket_no), 
    boarding_passes SOURCE KEY (ticket_no) REFERENCES tickets (ticket_no) DESTINATION KEY (seat_no) REFERENCES seats (seat_no)
);

Key Analysis:

• Route Optimization: Finding the shortest path between airport 'UKX' and 'CNN'.

  FROM (SELECT unnest(flights) AS flights FROM GRAPH_TABLE (flight_graph MATCH o = ANY SHORTEST (a : airports_data WHERE a.airport_code = 'UKX') - [fr : route] - * (a2 : airports_data WHERE a2.airport_code = 'CNN') COLUMNS (edges(o) AS flights))) 
  JOIN route f ON f.rowid = flights;
  

• Revenue Analysis: Finding the average price of the most expensive seats.

  FROM GRAPH_TABLE (flight_graph MATCH (b : bookings) - [bt : bookings_tickets] - (t : tickets) - [bp : boarding_passes] - (s : seats)) 
  SELECT round(avg(total_amount), 2) avg_amount, seat_no GROUP BY seat_no ORDER BY avg_amount DESC;
  

---

3. Financial Benchmarking

Using finbench.duckdb to track complex monetary flows.

ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/finbench.duckdb' AS finbench;
USE finbench;
INSTALL duckpgq FROM community;
LOAD duckpgq;

CREATE OR REPLACE PROPERTY GRAPH finbench 
VERTEX TABLES (Account, Company, Loan, Medium, Person) 
EDGE TABLES (
    AccountRepayLoan SOURCE KEY (accountId) REFERENCES Account (accountId) DESTINATION KEY (loanId) REFERENCES Loan (loanId) LABEL repay, 
    AccountTransferAccount SOURCE KEY (fromId) REFERENCES Account (accountId) DESTINATION KEY (toId) REFERENCES Account (AccountId) LABEL transfer, 
    AccountWithdrawAccount SOURCE KEY (fromId) REFERENCES Account (accountId) DESTINATION KEY (toId) REFERENCES Account (AccountId) LABEL withdraw, 
    CompanyApplyLoan SOURCE KEY (companyId) REFERENCES Company (companyId) DESTINATION KEY (loanId) REFERENCES Loan (loanId) LABEL companyApply, 
    CompanyGuaranteeCompany SOURCE KEY (fromId) REFERENCES Company (companyId) DESTINATION KEY (toId) REFERENCES Company (companyId) LABEL companyGuarantee, 
    CompanyInvestCompany SOURCE KEY (investorId) REFERENCES Company (companyId) DESTINATION KEY (companyId) REFERENCES Company (companyId) LABEL companyInvest, 
    CompanyOwnAccount SOURCE KEY (companyId) REFERENCES Company (companyId) DESTINATION KEY (accountId) REFERENCES Account (accountId) LABEL companyOwn, 
    LoanDepositAccount SOURCE KEY (loanId) REFERENCES ...