Graph queries with SQL/PGQ

PostgreSQL 19 implements SQL/PGQ (ISO/IEC 9075-16, SQL:2023 Part 16): graph pattern matching over ordinary relational tables. A property graph is a read-only view over tables you already have — no extension, no data copy — and GRAPH_TABLE queries are planned through the same relational planner as any join query.

When graph queries pay off

• Social relationships: friends of friends, mutual connections.
• Data lineage: which source rows and transformations produced a report number.
• Fraud and audit: money flows, suspicious paths, compliance tracing.

The trade-off is fixed depth. SQL/PGQ in PostgreSQL 19 matches patterns hop by hop; variable-length paths are not supported. Within a few hops the pattern syntax is much clearer than the equivalent join chain; beyond that, use WITH RECURSIVE (see Limits and the WITH RECURSIVE boundary).

Define the graph: CREATE PROPERTY GRAPH

The canonical social network: person and knows.

CREATE TABLE person (
  id    int PRIMARY KEY,
  name  text NOT NULL,
  age   int,
  city  text
);

CREATE TABLE knows (
  a     int NOT NULL REFERENCES person(id),  -- knows whom
  b     int NOT NULL REFERENCES person(id),  -- known by whom
  since int,
  PRIMARY KEY (a, b)
);

Declare it as a graph — vertices are person, edges are knows, directed from a to b:

CREATE PROPERTY GRAPH social
VERTEX TABLES (
  person KEY (id) LABEL person PROPERTIES (id, name, age, city)
)
EDGE TABLES (
  knows
  SOURCE KEY (a) REFERENCES person (id)
  DESTINATION KEY (b) REFERENCES person (id)
  LABEL knows PROPERTIES (since)
);

Read it as a contract:

• A vertex table's KEY is usually its primary key; vertex tables need one.
• SOURCE KEY ... REFERENCES ... and DESTINATION KEY ... REFERENCES ... state the edge direction: from which column, to which column of which table.
• LABEL is the name inside the graph (it may differ from the table name); PROPERTIES controls which columns are visible in the graph. When table and column names already match what you want as labels and properties, both clauses can be omitted.

Query with GRAPH_TABLE

List everyone:

SELECT name
FROM GRAPH_TABLE (social
  MATCH (p IS person)
  COLUMNS (p.name)
)
ORDER BY name;

Logically this is SELECT name FROM person ORDER BY name. MATCH (p IS person) walks every person vertex and binds it to p; COLUMNS projects the output. The result of GRAPH_TABLE is an ordinary table: it can be aliased, filtered, and joined like any other FROM item.

Edge patterns and direction

SELECT *
FROM GRAPH_TABLE (social
  MATCH (p IS person)-[IS knows]->(p2 IS person)
  COLUMNS (p.id, p.name, p2.id, p2.name)
)
ORDER BY 1, 2, 3;

(p)-[IS knows]->(p2) is an edge pattern:

• -> follows the declared direction (SOURCE → DESTINATION); <- walks it in reverse.
• A bare - matches either direction.

Multiple hops

SELECT *
FROM GRAPH_TABLE (social
  MATCH (a IS person)-[IS knows]->
        (b IS person)-[IS knows]->(c IS person)
  WHERE a.id <> c.id
  COLUMNS (a.name AS a, b.name AS via, c.name AS c)
)
ORDER BY a, c, via;

• Each extra hop is another -[IS knows]->(...) in the chain.
• WHERE a.id <> c.id lives inside MATCH and filters out cycles such as "Alice → Bob → Alice". Drop it to see all two-hop paths including the circular ones.

Multiple vertex and edge types

Real models span several tables. Add companies and employment:

CREATE TABLE company (
  id       int PRIMARY KEY,
  name     text NOT NULL,
  industry text NOT NULL
);

CREATE TABLE works_at (
  pid  int NOT NULL REFERENCES person(id),
  cid  int NOT NULL REFERENCES company(id),
  role text NOT NULL,
  PRIMARY KEY (pid, cid)
);

CREATE PROPERTY GRAPH company_social
VERTEX TABLES (
  person  KEY (id) LABEL person  PROPERTIES (id, name, age, city),
  company KEY (id) LABEL company PROPERTIES (id, name, industry)
)
EDGE TABLES (
  knows
  SOURCE KEY (a) REFERENCES person (id)
  DESTINATION KEY (b) REFERENCES person (id)
  LABEL knows PROPERTIES (since),
  works_at
  SOURCE KEY (pid) REFERENCES person (id)
  DESTINATION KEY (cid) REFERENCES company (id)
  LABEL works_at PROPERTIES (role)
);

One graph, two vertex types and two edge types, traversed in a single query — "where do Alice's friends work?":

SELECT *
FROM GRAPH_TABLE (company_social
  MATCH (me IS person WHERE me.name = 'Alice')
        -[IS knows]->(friend IS person)
        -[IS works_at]->(co IS company)
  COLUMNS (friend.name AS friend, co.name AS company)
)
ORDER BY friend, company;

WHERE can sit directly inside a vertex pattern. The equivalent plain SQL needs a multi-table join; the graph syntax encodes "which relationship to walk" in the pattern itself.

No multi-pattern MATCH: join two GRAPH_TABLEs

The SQL standard's comma-separated MATCH (a...), (b...) is not implemented in PostgreSQL 19. The workaround is the most useful idiom on this page: a GRAPH_TABLE result is an ordinary table, so project the IDs and join:

SELECT m.me, m.via, m.coworker, w.company
FROM GRAPH_TABLE (company_social
  MATCH (a IS person)-[IS knows]->(b IS person)-[IS knows]->(c IS person)
  WHERE a.id <> c.id
  COLUMNS (a.id AS aid, c.id AS cid, a.name AS me,
           b.name AS via, c.name AS coworker)
) m
JOIN GRAPH_TABLE (company_social
  MATCH (x IS person)-[IS works_at]->(co IS company)
        <-[IS works_at]-(y IS person)
  WHERE x.id <> y.id
  COLUMNS (x.id AS xid, y.id AS yid, co.name AS company)
) w ON w.xid = m.aid AND w.yid = m.cid
ORDER BY me, coworker;

"Co-workers who know each other" decomposes exactly like this: one GRAPH_TABLE per graph pattern, IDs as the bridge.

Performance: it is planned as joins

The release notes state that SQL/PGQ queries "are processed like views so are written as standard relational queries". A two-hop pattern becomes a multi-way join and goes through the ordinary planner; there are no graph-specific executor nodes in EXPLAIN. That is why performance is predictable: the plan shape follows the pattern shape, and the indexing rules are the ones you already know from joins.

The one rule that matters: the edge table's primary key covers the source side; add an index on the destination column if you traverse in reverse.

• Forward ("whom does 42 know") looks up knows.a = 42, which the primary key (a, b) covers.
• Reverse ("who knows 42") filters on knows.b, which that primary key cannot serve — the scan falls back to reading the whole edge table, and the cost gap grows with table size.

CREATE INDEX ON knows (b);

Verify with EXPLAIN (ANALYZE, BUFFERS) exactly as you would for any join; see Indexes and EXPLAIN.

Data lineage pattern

The classic compliance question is "where does this number in the report come from?". Model the ETL pipeline as a graph:

• One vertex table per layer: clickstream_source (raw) → staging → fact → report, each with an id primary key and a name.
• One edge table per transformation type, with PRIMARY KEY (src, dst) and foreign keys to the two layers it connects: loads_into (straight copy), aggregates_into (grouped aggregation), rollup_into (further summarization).

CREATE PROPERTY GRAPH lineage
VERTEX TABLES (
  clickstream_source KEY (id),
  staging KEY (id),
  fact KEY (id),
  report KEY (id)
)
EDGE TABLES (
  loads_into
  SOURCE KEY (src) REFERENCES clickstream_source (id)
  DESTINATION KEY (dst) REFERENCES staging (id),
  aggregates_into
  SOURCE KEY (src) REFERENCES staging (id)
  DESTINATION KEY (dst) REFERENCES fact (id),
  rollup_into
  SOURCE KEY (src) REFERENCES fact (id)
  DESTINATION KEY (dst) REFERENCES report (id)
);

Edge labels carry semantics — that is the advantage over "foreign keys plus a recursive CTE": rollup_into tells you what kind of transformation happened, while a foreign key only says a relationship exists. The CREATE PROPERTY GRAPH statement itself can be generated from orchestration metadata (dbt, Airflow and similar tools already track upstream/downstream relationships).

Four recurring query patterns:

-- 1. Value trace: what fed this report number
SELECT *
FROM GRAPH_TABLE (lineage
  MATCH (r IS report)<-[IS rollup_into]-(f IS fact)
  COLUMNS (r.name AS report, f.name AS fact)
);

-- 2. Impact analysis: if this source changes, which reports break?
SELECT DISTINCT rpt
FROM GRAPH_TABLE (lineage
  MATCH (s IS clickstream_source WHERE s.name = 'events_raw')
        -[IS loads_into]->(IS staging)
        -[IS aggregates_into]->(IS fact)
        -[IS rollup_into]->(r IS report)
  COLUMNS (r.name AS rpt)
);

-- 3. Full audit trail: same MATCH as 2, but COLUMNS outputs every hop
--    (s.name, staging name, fact name, r.name)

-- 4. Black holes: source data with no downstream consumer
SELECT src.name
FROM GRAPH_TABLE (lineage
  MATCH (s IS clickstream_source)
  COLUMNS (s.id AS sid, s.name AS name)
) src
WHERE NOT EXISTS (
  SELECT 1 FROM loads_into l WHERE l.src = src.sid
);

Pattern 4 shows the escape hatch: GRAPH_TABLE results compose with ordinary SQL, so anti-joins, aggregates, and EXISTS all work on top of graph output.

Limits and the WITH RECURSIVE boundary

PostgreSQL 19's SQL/PGQ does not yet have:

• Variable-length paths: (a)-[IS knows]->{1,3}(b) is rejected. Either unroll hop by hop and UNION, or fall back to WITH RECURSIVE.
• Multi-pattern MATCH (comma-separated) — join two GRAPH_TABLEs instead, as shown above.
• Path variable binding (p = (a)->(b)) and ANY SHORTEST / ALL SHORTEST path finding.

For chains beyond roughly five hops, a recursive CTE is still the right tool:

WITH RECURSIVE chain AS (
  SELECT a, b, 1 AS depth FROM knows WHERE a = 42
  UNION ALL
  SELECT k.a, k.b, c.depth + 1
  FROM chain c JOIN knows k ON k.a = c.b
  WHERE c.depth < 5
)
SELECT * FROM chain;

See Query toolbox for more on CTEs.

AI prompt: draft a graph query

Help me write a graph query on PostgreSQL 19.

1. My schema: (paste CREATE TABLE statements)
2. The question I'm answering: (describe the intent, e.g. "find people Alice knows who work at Acme")
3. Please:
 - Write CREATE PROPERTY GRAPH first (vertex tables need a primary key; edge tables need SOURCE/DESTINATION KEY)
 - Then the GRAPH_TABLE ... MATCH ... COLUMNS query
 - Remember: PG 19 has no variable-length paths, no multi-pattern MATCH, no * in COLUMNS; name edge labels in multi-edge-type graphs
 - Finish with EXPLAIN-based index advice (reverse traversal needs an index on the destination column)
4. If the query needs more than ~5 hops, use WITH RECURSIVE instead and explain.

Related

• PostgreSQL 19 release and upgrade guide — version boundary for SQL/PGQ
• Indexes and EXPLAIN — verifying the reverse-traversal index
• Query toolbox — CTEs and WITH RECURSIVE