Architecture Exploration

PostgreSQL 19 Property Graphs × post-graph

What SQL/PGQ brings to the table, where it overlaps with what post-graph already does, and a practical path for adopting it.

Bottom line: PG19 property graphs are a read-only query overlay on existing tables — they complement post-graph rather than replace it. The library's table creation, multi-tenancy, recursive traversal, shortest path, vector search, and audit system all remain essential. The opportunity is to layer CREATE PROPERTY GRAPH declarations on top and offer GRAPH_TABLE pattern matching as an alternative query surface for simple hops.

01 What PG19 Property Graphs Are

PostgreSQL 19 implements SQL/PGQ (ISO/IEC 9075-16) — a standard for declaring property graphs over relational tables and querying them with pattern-matching syntax. Two new constructs are introduced:

`CREATE PROPERTY GRAPH`

Declares a named graph as a virtual overlay. Vertex and edge tables are mapped from existing relations. Columns become properties. Labels classify nodes and edges. Nothing is materialized — it's a view-like metadata layer.

`GRAPH_TABLE()`

A table function in FROM clauses. Takes a graph name, a MATCH pattern, and a COLUMNS projection. Patterns use ASCII-art syntax: (v1)-[e]->(v2). Internally rewritten to joins.

Declaring a property graph

CREATE PROPERTY GRAPH mygraph
  VERTEX TABLES (
    people       KEY (realm, id)  LABEL person
                 PROPERTIES (space, payload, created_at),
    departments  KEY (realm, id)  LABEL department
  )
  EDGE TABLES (
    works_in     KEY (realm, id)
      SOURCE KEY (realm, from_id) REFERENCES people  (realm, id)
      DESTINATION KEY (realm, to_id) REFERENCES departments (realm, id)
      LABEL works_in
      PROPERTIES (relation_type, payload, space)
  );

Querying with pattern matching

SELECT person_name, dept_payload
FROM GRAPH_TABLE (
  mygraph
  MATCH (p IS person WHERE p.space = 'engineering')
        -[w IS works_in]->
        (d IS department)
  COLUMNS (p.payload AS person_name,
           d.payload AS dept_payload)
);

What's supported vs. what's not

Feature	PG19 Status
Vertex/edge declaration with labels	Supported
Explicit KEY & REFERENCES	Supported
Column → property mapping	Supported
Single-hop pattern matching	Supported
Multi-hop (explicit chaining)	Supported
WHERE inside patterns	Supported
Multi-label OR: `(IS a\|b)`	Supported
Directed & undirected edges	Supported
Quantified paths `{1,5}`	Not in PG19
SHORTEST PATH	Not in PG19
Path modes (WALK, TRAIL, SIMPLE)	Not in PG19
Graph mutations via graph syntax	Not in PG19
DROP PROPERTY GRAPH	Supported

Critical gap: PG19 has no variable-length path traversal. The pattern (a)-[*1..5]->(b) is not supported. Every hop must be spelled out explicitly. This means post-graph's recursive CTE traversal is strictly more powerful and cannot be replaced.

02 Feature-by-Feature Impact

Mapping each post-graph capability against PG19's property graph support:

post-graph Feature	PG19 Impact	Verdict
Table creation DDL `create_vertex_table()`, `create_edge_table()`	PG19 property graphs overlay existing tables — they don’t create them. post-graph’s DDL generation remains the foundation.	Keep as-is
Multi-tenancy `realm` column, composite PK	No native multi-tenancy in SQL/PGQ. The `realm` column can be exposed as a property and filtered in `WHERE` inside patterns, but there’s no built-in isolation.	Keep as-is
Schema-per-realm One PG schema per tenant	Each realm’s schema could declare its own property graph. Natural fit — `"tenant_a".mygraph` vs `"tenant_b".mygraph`.	Augment
Space isolation Logical sub-groups within a realm	Exposed as a property; filterable via `WHERE p.space = 'x'` in patterns.	Keep as-is
Neighbor queries `get_neighbors()`	Single-hop `GRAPH_TABLE MATCH` is a direct replacement. Cleaner syntax, same performance (rewritten to joins).	Augment
Recursive traversal `traverse()` via `WITH RECURSIVE`	PG19 cannot do variable-length traversal. Recursive CTE stays.	Keep as-is
Shortest path `shortest_path()`	Not available in PG19 SQL/PGQ. Recursive CTE stays.	Keep as-is
Cycle detection Pre-insert cycle check	Not available. Keep existing `shortest_path()`-based check.	Keep as-is
pgvector search `vector_search()`	Embedding columns can be exposed as properties, but PG19 pattern matching has no distance operators. Dedicated vector search stays.	Keep as-is
Audit triggers `_audit` tables	Orthogonal — property graphs are read-only overlays and don’t interact with triggers.	Keep as-is
Data history `_data` append-only tables	Data tables could be included as additional vertex tables in the graph with a `version` label for temporal queries.	Augment
CRUD operations `add_vertex()`, `add_edge()`, etc.	PG19 property graphs are read-only. All mutations go through regular SQL.	Keep as-is

03 Architecture: Current vs. Proposed

CURRENT                                  PROPOSED (PG19+)
═══════                                  ════════════════

┌──────────────────────┐                 ┌──────────────────────┐
│   Python Client      │                 │   Python Client      │
│  (AsyncPostGraph /   │                 │  (AsyncPostGraph /   │
│   SQLAlchemyPost...) │                 │   SQLAlchemyPost...) │
└──────┬───────────────┘                 └──────┬───────────────┘
       │                                        │
       │  add_vertex()                          │  add_vertex()
       │  add_edge()                            │  add_edge()
       │  traverse()         ┌──────────────┐   │  traverse()
       │  shortest_path()    │  NEW LAYER   │   │  shortest_path()
       │  vector_search()    │              │   │  vector_search()
       │  get_neighbors()    │  match()     │   │  get_neighbors()
       │                     │  graph_query()│   │
       ▼                     └──────┬───────┘   ▼
┌──────────────────────┐            │    ┌──────────────────────┐
│    Raw SQL           │            │    │    Raw SQL            │
│  INSERT / SELECT /   │            ▼    │  + GRAPH_TABLE()     │
│  WITH RECURSIVE      │    ┌────────────┤  + WITH RECURSIVE    │
│                      │    │  Property  │                      │
└──────┬───────────────┘    │  Graph     └──────┬───────────────┘
       │                    │  Declaration      │
       ▼                    └────────┬──────────▼
┌──────────────────────┐             │   ┌──────────────────────┐
│  PostgreSQL Tables   │             └──▶│  PostgreSQL Tables   │
│  vertices, edges,    │                 │  + CREATE PROPERTY   │
│  _audit, _data       │                 │    GRAPH overlay     │
└──────────────────────┘                 └──────────────────────┘

The property graph declaration sits alongside existing tables as metadata. All writes continue through regular SQL. The new match() method uses GRAPH_TABLE() for pattern-based reads.

04 Concrete Changes Required

New methods on AsyncPostGraph / SQLAlchemyPostGraph

Method	Purpose	Notes
`create_property_graph(name, realm?)`	Generate `CREATE PROPERTY GRAPH` DDL from the client’s known tables	Introspects `pg_class` / `pg_constraint` to discover vertex & edge tables and their FK relationships. In schema-per-realm mode, scopes to the realm’s schema.
`drop_property_graph(name)`	Execute `DROP PROPERTY GRAPH IF EXISTS`	Thin wrapper. Needed for cleanup and recreation after schema changes.
`refresh_property_graph(name)`	Drop and recreate after table changes	Since property graphs reference table structure at creation time, adding a new vertex/edge table requires recreation.
`match(graph, pattern, columns)`	Execute a `GRAPH_TABLE()` query and return results	Takes a pattern string and column projections. Returns list of dicts or model objects. Adds realm filtering automatically.

Property graph generation

The create_property_graph() method would introspect the database to build the DDL. Here's how post-graph's table structure maps to SQL/PGQ declarations:

-- Auto-generated from post-graph's table metadata:

CREATE PROPERTY GRAPH post_graph_default
  VERTEX TABLES (
    -- Each vertex table discovered via pg_class
    people   KEY (realm, id)
             LABEL person
             PROPERTIES (realm, id, space, payload, uuid,
                         created_at, updated_at),
    companies KEY (realm, id)
             LABEL company
             PROPERTIES (realm, id, space, payload, uuid,
                         created_at, updated_at)
  )
  EDGE TABLES (
    -- Each edge table discovered via FK constraints
    peopleTOcompanies KEY (realm, id)
      SOURCE KEY (realm, from_id) REFERENCES people    (realm, id)
      DESTINATION KEY (realm, to_id) REFERENCES companies (realm, id)
      LABEL employs
      PROPERTIES (realm, id, space, from_id, to_id,
                  relation_type, payload, uuid,
                  created_at, updated_at)
  );

Label strategy

Two options for mapping post-graph concepts to PG19 labels:

Option A: Table name = Label

Each vertex table becomes a label (e.g., people → LABEL person). Each edge table becomes a label. Simple, automatic. Edge relation_type is just a filterable property.

Option B: relation_type = Label Complex

Map edge relation_type values to separate labels. Requires knowing all relation types at graph creation time — either via introspection (SELECT DISTINCT relation_type) or user declaration. More expressive but harder to keep in sync.

Recommendation: Start with Option A. The relation_type column is already filterable via WHERE in patterns, so there's no loss of expressiveness. Option B can be added later as an opt-in feature.

Multi-tenancy considerations

The realm column is part of every composite PK and FK. This needs careful handling:

Default mode: The property graph spans all realms. Queries must include WHERE v.realm = 'tenant_a' in every pattern. The match() wrapper injects this automatically.
Schema-per-realm: Each realm schema declares its own property graph. This is cleaner — no cross-realm leakage possible. create_property_graph() generates one per realm.

Version detection

# In client initialization:
async def _check_pg_version(self):
    row = await self._fetchrow("SHOW server_version_num")
    self._pg_version = int(row['server_version_num'])
    self._has_property_graphs = self._pg_version >= 190000

All property graph methods would check self._has_property_graphs and raise a clear error on older versions.

05 What a match() Query Looks Like

Compare the current programmatic API with the proposed pattern-matching surface:

Finding neighbors (1-hop)

Current API With PG19 match()

Current API	With PG19 match()
`steps = await client.get_neighbors( "people", realm, vertex_id, edge_tables=["knows"], direction="out" )`	`results = await client.match( "mygraph", realm, "(p IS person)-[k IS knows]->(f IS person)", columns={"p.payload": "person", "f.payload": "friend"}, where={"p.id": vertex_id} )`

steps = await client.get_neighbors(
    "people", realm, vertex_id,
    edge_tables=["knows"],
    direction="out"
)

results = await client.match(
    "mygraph", realm,
    "(p IS person)-[k IS knows]->(f IS person)",
    columns={"p.payload": "person",
             "f.payload": "friend"},
    where={"p.id": vertex_id}
)

Two-hop traversal

Current API With PG19 match()

Current API	With PG19 match()
`# Requires recursive CTE (max_depth=2) steps = await client.traverse( "people", realm, start_id, edge_tables=["knows"], direction="out", max_depth=2 )`	`# Explicit 2-hop pattern results = await client.match( "mygraph", realm, """(a IS person) -[IS knows]->(b IS person) -[IS knows]->(c IS person)""", columns={"a.payload": "start", "b.payload": "mid", "c.payload": "end"} )`

# Requires recursive CTE (max_depth=2)
steps = await client.traverse(
    "people", realm, start_id,
    edge_tables=["knows"],
    direction="out",
    max_depth=2
)

# Explicit 2-hop pattern
results = await client.match(
    "mygraph", realm,
    """(a IS person)
       -[IS knows]->(b IS person)
       -[IS knows]->(c IS person)""",
    columns={"a.payload": "start",
             "b.payload": "mid",
             "c.payload": "end"}
)

Limitation: The match() pattern is fixed at 2 hops. For traverse(max_depth=10), there's no PG19 equivalent — you'd need 10 chained pattern segments. The recursive CTE remains the only viable approach for variable-depth traversal.

06 Migration Path

Version detection & feature flag

Add _pg_version / _has_property_graphs to both clients during connect(). Gate all new methods behind this flag. Zero impact on PG < 19 users.

Property graph lifecycle methods

Implement create_property_graph(), drop_property_graph(), and refresh_property_graph(). These introspect existing tables and FK constraints to auto-generate the DDL. Add optional auto_property_graph=True parameter to create_vertex_table() and create_edge_table().

Pattern matching query method

Implement match(graph, realm, pattern, columns, where). This wraps GRAPH_TABLE() with automatic realm injection. Returns results as dicts or Vertex/Edge model objects where possible.

Optimize get_neighbors() on PG19+

Optionally rewrite get_neighbors() to use GRAPH_TABLE internally when a property graph exists. This is transparent to callers but lets the query planner use the graph metadata for optimization.

Future: track PG20+ additions

When PostgreSQL adds quantified paths and shortest path to SQL/PGQ, revisit traverse() and shortest_path() to optionally delegate to native graph operations.

07 Risks & Considerations

Sync Graph staleness

Property graphs capture table structure at creation time. Adding a column or a new table requires DROP + CREATE. The library must track when to refresh — either eagerly (after every DDL) or lazily (before first match() call).

Perf Planner overhead

PG19 rewrites GRAPH_TABLE to joins internally. For simple 1-hop patterns this is equivalent to what post-graph already generates. Benchmark before assuming gains — the overhead of the rewrite step might offset any benefit.

API Pattern injection

If users can pass raw pattern strings to match(), SQL injection via the pattern language is possible. The _validate_identifier() approach won't work for GQL patterns. Need a safe parameterization strategy or a builder API.

Scope Namespace collision

Property graphs share the namespace with tables and views. The graph name must not collide with existing table names. Use a prefix convention like pg_graph_{realm} or let users choose.

08 Summary

Category	Count	Detail
Features that stay unchanged	9	DDL, CRUD, traverse, shortest path, cycle detection, vector search, audit, data history, space isolation
Features to augment	3	Schema-per-realm (auto-declare graphs), neighbors (optional GRAPH_TABLE backend), data history (temporal graph queries)
New methods to add	4	`create_property_graph`, `drop_property_graph`, `refresh_property_graph`, `match`
PG19 gaps vs. post-graph	5	Variable-length paths, shortest path, path modes, mutations, multi-tenancy

Net assessment: PG19 SQL/PGQ is additive. It gives post-graph users a standardized, declarative query syntax for simple graph patterns while the library's core value — multi-tenant schema management, deep traversal, vector search, and audit trails — remains unmatched by native PostgreSQL. The safest approach is to layer it in as an optional feature behind a version check, touching zero existing behavior.