DuckPGQ is a DuckDB community extension for graph workloads that supports the SQL/PGQ standard.

Leveraging the power of DuckDB to bring high-performance, SQL-based graph query capabilities directly to your analytical workflows.

Install and Load DuckPGQ

CLIPythonNodeJSRJava

[](#__codelineno-0-1)INSTALL duckpgq FROM community; [](#__codelineno-0-2)LOAD duckpgq;

[](#__codelineno-1-1)import duckdb [](#__codelineno-1-2)conn = duckdb.connect() [](#__codelineno-1-3)conn.install_extension("duckpgq", repository="community") [](#__codelineno-1-4)conn.load_extension("duckpgq")

[](#__codelineno-2-1)import { DuckDBInstance } from '@duckdb/node-api'; [](#__codelineno-2-2)const instance = await DuckDBInstance.create(); [](#__codelineno-2-3)const connection = await instance.connect(); [](#__codelineno-2-4)await connection.run("INSTALL duckpgq FROM community"); [](#__codelineno-2-5)await connection.run("LOAD duckpgq");

[](#__codelineno-3-1)library(duckdb) [](#__codelineno-3-2)con <- dbConnect(duckdb()) [](#__codelineno-3-3)dbExecute(con, "INSTALL duckpgq FROM community") [](#__codelineno-3-4)dbExecute(con, "LOAD duckpgq")

[](#__codelineno-4-1)Connection conn = DriverManager.getConnection("jdbc:duckdb:"); [](#__codelineno-4-2)Statement st = conn.createStatement(); [](#__codelineno-4-3)st.execute("INSTALL duckpgq FROM community"); [](#__codelineno-4-4)st.execute("LOAD duckpgq");

Key Features

• SQL/PGQ Standard
  Supports graph pattern matching with SQL/PGQ, following the SQL:2023 standard.
• High Performance
  Leverages DuckDB for efficient, in-process graph analytics.
• Easy To Install
  Seamlessly integrate with DuckDB through a simple setup process, requiring no dependencies.
• Open Source
  Community-driven, extensible, and free to use.

Explore DuckPGQ Across Domains

Social NetworksAirline DataFinancial Data

Setup

[](#__codelineno-5-1)ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/snb.duckdb'; [](#__codelineno-5-2)[](#__codelineno-5-3)use snb; [](#__codelineno-5-4)install duckpgq from community; [](#__codelineno-5-5)load duckpgq; [](#__codelineno-5-6)[](#__codelineno-5-7)CREATE or replace PROPERTY GRAPH snb [](#__codelineno-5-8)VERTEX TABLES ( [](#__codelineno-5-9) Person, Forum [](#__codelineno-5-10)) [](#__codelineno-5-11)EDGE TABLES ( [](#__codelineno-5-12) Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id) [](#__codelineno-5-13) DESTINATION KEY (Person2Id) REFERENCES Person (id) [](#__codelineno-5-14) LABEL knows, [](#__codelineno-5-15) Forum_hasMember_Person SOURCE KEY (ForumId) REFERENCES Forum (id) [](#__codelineno-5-16) DESTINATION KEY (PersonId) REFERENCES Person (id) [](#__codelineno-5-17) LABEL hasMember [](#__codelineno-5-18));

Shortest Path QueryFind Mutual FriendsMost Popular PeopleForum count of the most-followed person

[](#__codelineno-6-1)-- find the shortest path from one person to all other persons [](#__codelineno-6-2)FROM GRAPH_TABLE (snb [](#__codelineno-6-3) MATCH p = ANY SHORTEST (p1:person WHERE p1.id = 14)-[k:knows]->*(p2:person) [](#__codelineno-6-4) COLUMNS (p1.id, p2.id as other_person_id, element_id(p), path_length(p)) [](#__codelineno-6-5));

[](#__codelineno-7-1)-- Find mutual friends between two users [](#__codelineno-7-2)FROM GRAPH_TABLE (snb [](#__codelineno-7-3) MATCH (p1:Person WHERE p1.id = 16)-[k:knows]->(p2:Person)<-[k2:knows]-(p3:Person WHERE p3.id = 32) [](#__codelineno-7-4) COLUMNS (p2.firstName) [](#__codelineno-7-5));

[](#__codelineno-8-1)-- Find the 3 most popular people [](#__codelineno-8-2)FROM GRAPH_TABLE (snb [](#__codelineno-8-3) MATCH (follower:Person)-[follows:knows]->(person:Person) [](#__codelineno-8-4) COLUMNS (person.id AS personID, person.firstname, person.lastname, follower.id AS followerID) [](#__codelineno-8-5)) [](#__codelineno-8-6)SELECT personID, firstname, lastname, COUNT(followerID) AS numFollowers [](#__codelineno-8-7)GROUP BY ALL [](#__codelineno-8-8)ORDER BY numFollowers DESC [](#__codelineno-8-9)LIMIT 3;

[](#__codelineno-9-1)-- Number of forums posted on by the most followed person [](#__codelineno-9-2)WITH [](#__codelineno-9-3)mfp AS ( [](#__codelineno-9-4) FROM GRAPH_TABLE (snb [](#__codelineno-9-5) MATCH (follower:Person)-[follows:knows]->(person:Person) [](#__codelineno-9-6) COLUMNS (person.id AS personID, person.firstname, follower.id AS followerID) [](#__codelineno-9-7) ) [](#__codelineno-9-8)SELECT personID, firstname, COUNT(followerID) AS numFollowers [](#__codelineno-9-9)GROUP BY ALL ORDER BY numFollowers DESC LIMIT 1 [](#__codelineno-9-10)) [](#__codelineno-9-11)FROM [](#__codelineno-9-12) mfp, [](#__codelineno-9-13) GRAPH_TABLE (snb [](#__codelineno-9-14) MATCH (person:Person)<-[fhm:hasMember]-(f:Forum) [](#__codelineno-9-15) COLUMNS (person.id AS personID, f.id as forumId) [](#__codelineno-9-16)) mem [](#__codelineno-9-17)SELECT mfp.personID, mfp.firstname, mfp.numFollowers, count(mem.forumId) forumCount [](#__codelineno-9-18)WHERE mfp.personID = mem.personID [](#__codelineno-9-19)GROUP BY ALL;

Setup

[](#__codelineno-10-1)ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/airline-data/datasets/airline-data-small.duckdb' as airline; [](#__codelineno-10-2)[](#__codelineno-10-3)use airline; [](#__codelineno-10-4)install duckpgq from community; [](#__codelineno-10-5)load duckpgq; [](#__codelineno-10-6)[](#__codelineno-10-7)CREATE PROPERTY GRAPH flight_graph [](#__codelineno-10-8) VERTEX TABLES ( [](#__codelineno-10-9) aircrafts_data, airports_data, [](#__codelineno-10-10) bookings, flights, [](#__codelineno-10-11) tickets, seats [](#__codelineno-10-12) ) [](#__codelineno-10-13) EDGE TABLES ( [](#__codelineno-10-14) route [](#__codelineno-10-15) SOURCE KEY (departure_airport) REFERENCES airports_data(airport_code) [](#__codelineno-10-16) DESTINATION KEY (arrival_airport) REFERENCES airports_data(airport_code), [](#__codelineno-10-17) ticket_flights [](#__codelineno-10-18) SOURCE KEY (ticket_no) REFERENCES tickets(ticket_no) [](#__codelineno-10-19) DESTINATION KEY (flight_id) REFERENCES flights(flight_id), [](#__codelineno-10-20) bookings_tickets [](#__codelineno-10-21) SOURCE KEY (book_ref) REFERENCES bookings(book_ref) [](#__codelineno-10-22) DESTINATION KEY (ticket_no) REFERENCES tickets(ticket_no), [](#__codelineno-10-23) boarding_passes [](#__codelineno-10-24) SOURCE KEY (ticket_no) REFERENCES tickets(ticket_no) [](#__codelineno-10-25) DESTINATION KEY (seat_no) REFERENCES seats(seat_no) [](#__codelineno-10-26));

Shortest Route Between AirportsMost Expensive Seats on Average

[](#__codelineno-11-1)FROM ( [](#__codelineno-11-2) SELECT unnest(flights) AS flights [](#__codelineno-11-3) FROM GRAPH_TABLE ( [](#__codelineno-11-4) flight_graph [](#__codelineno-11-5) MATCH o = ANY SHORTEST (a:airports_data WHERE a.airport_code = 'UKX') [](#__codelineno-11-6) -[fr:route]->* [](#__codelineno-11-7) (a2:airports_data WHERE a2.airport_code = 'CNN') [](#__codelineno-11-8) COLUMNS (edges(o) AS flights) [](#__codelineno-11-9) ) [](#__codelineno-11-10)) [](#__codelineno-11-11)JOIN route f [](#__codelineno-11-12) ON f.rowid = flights;

[](#__codelineno-12-1)FROM GRAPH_TABLE ( [](#__codelineno-12-2) flight_graph [](#__codelineno-12-3) MATCH (b:bookings)-[bt:bookings_tickets]->(t:tickets)-[bp:boarding_passes]->(s:seats) [](#__codelineno-12-4)) [](#__codelineno-12-5)SELECT round(avg(total_amount), 2) avg_amount, seat_no [](#__codelineno-12-6)GROUP BY seat_no [](#__codelineno-12-7)ORDER BY avg_amount DESC;

Setup

[](#__codelineno-13-1)ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/finbench.duckdb' AS finbench; [](#__codelineno-13-2)use finbench; [](#__codelineno-13-3)INSTALL duckpgq FROM community; [](#__codelineno-13-4)LOAD duckpgq; [](#__codelineno-13-5)[](#__codelineno-13-6)CREATE OR REPLACE PROPERTY GRAPH finbench [](#__codelineno-13-7)VERTEX TABLES ( [](#__codelineno-13-8) Account, Company, Loan, [](#__codelineno-13-9) Medium, Person [](#__codelineno-13-10)) [](#__codelineno-13-11)EDGE TABLES ( [](#__codelineno-13-12) AccountRepayLoan SOURCE KEY (accountId) REFERENCES Account (accountId) [](#__codelineno-13-13) DESTINATION KEY (loanId) REFERENCES Loan (loanId) [](#__codelineno-13-14) LABEL repay, [](#__codelineno-13-15) AccountTransferAccount SOURCE KEY (fromId) REFERENCES Account (accountId) [](#__codelineno-13-16) DESTINATION KEY (toId) REFERENCES Account (AccountId) [](#__codelineno-13-17) LABEL transfer, [](#__codelineno-13-18) AccountWithdrawAccount SOURCE KEY (fromId) REFERENCES Account (accountId) [](#__codelineno-13-19) DESTINATION KEY (toId) REFERENCES Account (AccountId) [](#__codelineno-13-20) LABEL withdraw, [](#__codelineno-13-21) CompanyApplyLoan SOURCE KEY (companyId) REFERENCES Company (companyId) [](#__codelineno-13-22) DESTINATION KEY (loanId) REFERENCES Loan (loanId) [](#__codelineno-13-23) LABEL companyApply, [](#__codelineno-13-24) CompanyGuaranteeCompany SOURCE KEY (fromId) REFERENCES Company (companyId) [](#__codelineno-13-25) DESTINATION KEY (toId) REFERENCES Company (companyId) [](#__codelineno-13-26) LABEL companyGuarantee, [](#__codelineno-13-27) CompanyInvestCompany SOURCE KEY (investorId) REFERENCES Company (companyId) [](#__codelineno-13-28) DESTINATION KEY (companyId) REFERENCES Company (companyId) [](#__codelineno-13-29) LABEL companyInvest, [](#__codelineno-13-30) CompanyOwnAccount SOURCE KEY (companyId) REFERENCES Company (companyId) [](#__codelineno-13-31) DESTINATION KEY (accountId) REFERENCES Account (accountId) [](#__codelineno-13-32) LABEL companyOwn, [](#__codelineno-13-33) LoanDepositAccount SOURCE KEY (loanId) REFERENCES Loan (loanId) [](#__codelineno-13-34) DESTINATION KEY (accountId) REFERENCES Account (accountId) [](#__codelineno-13-35) LABEL deposit, [](#__codelineno-13-36) MediumSignInAccount SOURCE KEY (mediumId) REFERENCES Medium (mediumId) [](#__codelineno-13-37) DESTINATION KEY (accountId) REFERENCES Account (accountId) [](#__codelineno-13-38) LABEL signIn, [](#__codelineno-13-39) PersonApplyLoan SOURCE KEY (personId) REFERENCES Person (personId) [](#__codelineno-13-40) DESTINATION KEY (loanId) REFERENCES Loan (loanId) [](#__codelineno-13-41) LABEL personApply, [](#__codelineno-13-42) PersonGuaranteePerson SOURCE KEY (fromId) REFERENCES Person (personId) [](#__codelineno-13-43) DESTINATION KEY (toId) REFERENCES Person (personId) [](#__codelineno-13-44) LABEL personGuarantee, [](#__codelineno-13-45) PersonInvestCompany SOURCE KEY (investorId) REFERENCES Person (personId) [](#__codelineno-13-46) DESTINATION KEY (companyId) REFERENCES Company (companyId) [](#__codelineno-13-47) LABEL personInvest, [](#__codelineno-13-48) PersonOwnAccount SOURCE KEY (personId) REFERENCES Person (personId) [](#__codelineno-13-49) DESTINATION KEY (accountId) REFERENCES Account (accountId) [](#__codelineno-13-50) LABEL personOwn [](#__codelineno-13-51));

Find blocked accounts via transfersFilter high-value transfers by time

[](#__codelineno-14-1)FROM GRAPH_TABLE ( [](#__codelineno-14-2) finbench [](#__codelineno-14-3) MATCH (src:Account where src.accountId = 16607023625929101) [](#__codelineno-14-4) <-[e1:transfer]-(mid:Account) [](#__codelineno-14-5) -[e2:transfer]->(dst:Account where dst.isBlocked = true) [](#__codelineno-14-6) COLUMNS (src.accountId as src_id, dst.accountId as dst_id) [](#__codelineno-14-7)) [](#__codelineno-14-8)SELECT src_id, dst_id [](#__codelineno-14-9)WHERE src_Id <> dst_id;

[](#__codelineno-15-1)FROM GRAPH_TABLE ( [](#__codelineno-15-2) finbench [](#__codelineno-15-3) MATCH (src:Account)-[e:Transfer]->(dst:Account) [](#__codelineno-15-4) WHERE '2022-07-13 09:18:33.137' < e.createtime [](#__codelineno-15-5) AND e.createtime < '2022-09-03 02:31:47.812' [](#__codelineno-15-6) AND e.amount > 4829783 [](#__codelineno-15-7) );

Daniël ten Wolde

Lead Developer of DuckPGQ and PhD student at CWI specializing in graph analytics and database systems.

WIP Disclaimer

DuckPGQ is currently a research project and still a work in progress. While we encourage you to explore and experiment with it, please be aware that there may be bugs, incomplete features, or unexpected behaviour.

We greatly appreciate any feedback or bug reports that help us improve and evolve the extension. Feel free to share your experiences!