Created
August 14, 2026 11:39
-
-
Save crajah/387626fc4a241ac3db7c50b26be29615 to your computer and use it in GitHub Desktop.
PostgreSQL 19 Property Graphs × post-graph — architecture exploration
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --- | |
| layout: null | |
| --- | |
| <!DOCTYPE html> | |
| <html lang="en"> | |
| <head> | |
| <meta charset="utf-8"> | |
| <meta name="viewport" content="width=device-width, initial-scale=1"> | |
| <title>PG19 Property Graphs × post-graph</title> | |
| <style> | |
| /* ── Tokens ── */ | |
| :root { | |
| --ink: #1a1e2e; | |
| --ink-secondary: #4a5068; | |
| --ground: #f7f6f4; | |
| --surface: #ebeef2; | |
| --surface-alt: #e2e6ec; | |
| --accent: #2d6a8f; | |
| --accent-dim: #2d6a8f22; | |
| --highlight: #c4862e; | |
| --highlight-dim: #c4862e18; | |
| --border: #d0d4dc; | |
| --code-bg: #f0f2f6; | |
| --positive: #2a7d4f; | |
| --positive-dim: #2a7d4f15; | |
| --caution: #b07d1a; | |
| --caution-dim: #b07d1a15; | |
| --negative: #a03030; | |
| --negative-dim: #a0303015; | |
| --neutral-dim: #6b728015; | |
| } | |
| @media (prefers-color-scheme: dark) { | |
| :root { | |
| --ink: #e0e2ea; | |
| --ink-secondary: #9499ae; | |
| --ground: #13151e; | |
| --surface: #1c1f2c; | |
| --surface-alt: #252838; | |
| --accent: #5ba4cc; | |
| --accent-dim: #5ba4cc1a; | |
| --highlight: #daa04a; | |
| --highlight-dim: #daa04a18; | |
| --border: #2e3244; | |
| --code-bg: #1a1d28; | |
| --positive: #4aad72; | |
| --positive-dim: #4aad7218; | |
| --caution: #d4a030; | |
| --caution-dim: #d4a03018; | |
| --negative: #d05050; | |
| --negative-dim: #d0505018; | |
| --neutral-dim: #9499ae15; | |
| } | |
| } | |
| /* ── Base ── */ | |
| * { margin: 0; padding: 0; box-sizing: border-box; } | |
| body { | |
| font-family: "Inter", -apple-system, system-ui, "Segoe UI", sans-serif; | |
| background: var(--ground); | |
| color: var(--ink); | |
| line-height: 1.65; | |
| font-size: 15px; | |
| -webkit-font-smoothing: antialiased; | |
| } | |
| /* ── Layout ── */ | |
| .page { | |
| max-width: 860px; | |
| margin: 0 auto; | |
| padding: 3rem 1.5rem 5rem; | |
| } | |
| /* ── Type scale ── */ | |
| h1, h2, h3, h4 { | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", "Fira Code", monospace; | |
| text-wrap: balance; | |
| letter-spacing: -0.01em; | |
| } | |
| .hero-label { | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", monospace; | |
| font-size: 0.72rem; | |
| font-weight: 600; | |
| text-transform: uppercase; | |
| letter-spacing: 0.12em; | |
| color: var(--accent); | |
| margin-bottom: 0.75rem; | |
| } | |
| h1 { | |
| font-size: 1.75rem; | |
| font-weight: 700; | |
| line-height: 1.2; | |
| margin-bottom: 0.4rem; | |
| } | |
| .hero-sub { | |
| font-size: 1.05rem; | |
| color: var(--ink-secondary); | |
| line-height: 1.55; | |
| max-width: 640px; | |
| margin-bottom: 2.5rem; | |
| } | |
| h2 { | |
| font-size: 1.15rem; | |
| font-weight: 700; | |
| margin-top: 3rem; | |
| margin-bottom: 1rem; | |
| padding-bottom: 0.4rem; | |
| border-bottom: 2px solid var(--border); | |
| } | |
| h3 { | |
| font-size: 0.95rem; | |
| font-weight: 600; | |
| margin-top: 1.8rem; | |
| margin-bottom: 0.6rem; | |
| color: var(--accent); | |
| } | |
| p { margin-bottom: 0.9rem; max-width: 65ch; } | |
| /* ── Verdict banner ── */ | |
| .verdict { | |
| background: var(--accent-dim); | |
| border-left: 3px solid var(--accent); | |
| padding: 1rem 1.25rem; | |
| margin-bottom: 2.5rem; | |
| border-radius: 0 6px 6px 0; | |
| } | |
| .verdict p { margin: 0; font-size: 0.92rem; } | |
| .verdict strong { color: var(--accent); } | |
| /* ── Code ── */ | |
| code { | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", monospace; | |
| font-size: 0.82em; | |
| background: var(--code-bg); | |
| padding: 0.15em 0.35em; | |
| border-radius: 3px; | |
| } | |
| pre { | |
| background: var(--code-bg); | |
| border: 1px solid var(--border); | |
| border-radius: 6px; | |
| padding: 1rem 1.25rem; | |
| overflow-x: auto; | |
| margin-bottom: 1.2rem; | |
| font-size: 0.8rem; | |
| line-height: 1.6; | |
| } | |
| pre code { | |
| background: none; | |
| padding: 0; | |
| font-size: inherit; | |
| } | |
| /* ── Tables ── */ | |
| .table-wrap { | |
| overflow-x: auto; | |
| margin-bottom: 1.5rem; | |
| border-radius: 6px; | |
| border: 1px solid var(--border); | |
| } | |
| table { | |
| width: 100%; | |
| border-collapse: collapse; | |
| font-size: 0.85rem; | |
| font-variant-numeric: tabular-nums; | |
| } | |
| th { | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", monospace; | |
| font-size: 0.72rem; | |
| font-weight: 600; | |
| text-transform: uppercase; | |
| letter-spacing: 0.08em; | |
| text-align: left; | |
| padding: 0.65rem 1rem; | |
| background: var(--surface); | |
| border-bottom: 2px solid var(--border); | |
| color: var(--ink-secondary); | |
| white-space: nowrap; | |
| } | |
| td { | |
| padding: 0.6rem 1rem; | |
| border-bottom: 1px solid var(--border); | |
| vertical-align: top; | |
| } | |
| tr:last-child td { border-bottom: none; } | |
| /* ── Tags ── */ | |
| .tag { | |
| display: inline-block; | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", monospace; | |
| font-size: 0.68rem; | |
| font-weight: 600; | |
| text-transform: uppercase; | |
| letter-spacing: 0.06em; | |
| padding: 0.15em 0.55em; | |
| border-radius: 3px; | |
| white-space: nowrap; | |
| } | |
| .tag-replace { background: var(--positive-dim); color: var(--positive); } | |
| .tag-augment { background: var(--accent-dim); color: var(--accent); } | |
| .tag-keep { background: var(--neutral-dim); color: var(--ink-secondary); } | |
| .tag-gap { background: var(--negative-dim); color: var(--negative); } | |
| .tag-new { background: var(--highlight-dim); color: var(--highlight); } | |
| .tag-caution { background: var(--caution-dim); color: var(--caution); } | |
| /* ── Callout boxes ── */ | |
| .callout { | |
| padding: 0.9rem 1.1rem; | |
| border-radius: 6px; | |
| margin-bottom: 1.2rem; | |
| font-size: 0.88rem; | |
| } | |
| .callout p { margin: 0; } | |
| .callout-gap { background: var(--negative-dim); border-left: 3px solid var(--negative); } | |
| .callout-note { background: var(--accent-dim); border-left: 3px solid var(--accent); } | |
| .callout-positive { background: var(--positive-dim); border-left: 3px solid var(--positive); } | |
| /* ── Feature grid ── */ | |
| .feature-grid { | |
| display: grid; | |
| grid-template-columns: 1fr 1fr; | |
| gap: 1rem; | |
| margin-bottom: 1.5rem; | |
| } | |
| @media (max-width: 600px) { | |
| .feature-grid { grid-template-columns: 1fr; } | |
| } | |
| .feature-card { | |
| background: var(--surface); | |
| border: 1px solid var(--border); | |
| border-radius: 6px; | |
| padding: 1rem 1.1rem; | |
| } | |
| .feature-card h4 { | |
| font-size: 0.82rem; | |
| font-weight: 600; | |
| margin-bottom: 0.35rem; | |
| display: flex; | |
| align-items: center; | |
| gap: 0.5rem; | |
| } | |
| .feature-card p { | |
| font-size: 0.82rem; | |
| color: var(--ink-secondary); | |
| margin: 0; | |
| line-height: 1.5; | |
| } | |
| /* ── Diagram ── */ | |
| .diagram { | |
| background: var(--surface); | |
| border: 1px solid var(--border); | |
| border-radius: 6px; | |
| padding: 1.5rem; | |
| margin-bottom: 1.5rem; | |
| overflow-x: auto; | |
| } | |
| .diagram pre { | |
| background: none; | |
| border: none; | |
| padding: 0; | |
| margin: 0; | |
| font-size: 0.78rem; | |
| line-height: 1.55; | |
| color: var(--ink-secondary); | |
| } | |
| .diagram pre code { background: none; } | |
| .diagram-caption { | |
| font-size: 0.75rem; | |
| color: var(--ink-secondary); | |
| margin-top: 0.75rem; | |
| font-style: italic; | |
| } | |
| /* ── Section numbering ── */ | |
| .section-num { | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", monospace; | |
| font-size: 0.72rem; | |
| font-weight: 600; | |
| color: var(--highlight); | |
| margin-right: 0.5rem; | |
| } | |
| /* ── Lists ── */ | |
| ul, ol { | |
| margin-bottom: 1rem; | |
| padding-left: 1.4rem; | |
| } | |
| li { | |
| margin-bottom: 0.35rem; | |
| font-size: 0.92rem; | |
| } | |
| li code { font-size: 0.8em; } | |
| /* ── Migration phases ── */ | |
| .phase { | |
| display: flex; | |
| gap: 1rem; | |
| margin-bottom: 1.2rem; | |
| align-items: flex-start; | |
| } | |
| .phase-marker { | |
| flex-shrink: 0; | |
| width: 2.2rem; | |
| height: 2.2rem; | |
| background: var(--accent-dim); | |
| border: 2px solid var(--accent); | |
| border-radius: 50%; | |
| display: flex; | |
| align-items: center; | |
| justify-content: center; | |
| font-family: "SF Mono", "Cascadia Code", "JetBrains Mono", monospace; | |
| font-size: 0.78rem; | |
| font-weight: 700; | |
| color: var(--accent); | |
| } | |
| .phase-body { flex: 1; } | |
| .phase-body h4 { | |
| font-size: 0.88rem; | |
| font-weight: 600; | |
| margin-bottom: 0.25rem; | |
| } | |
| .phase-body p { | |
| font-size: 0.85rem; | |
| color: var(--ink-secondary); | |
| margin: 0; | |
| } | |
| /* ── Footer ── */ | |
| .footer { | |
| margin-top: 3rem; | |
| padding-top: 1.5rem; | |
| border-top: 1px solid var(--border); | |
| font-size: 0.75rem; | |
| color: var(--ink-secondary); | |
| } | |
| </style> | |
| </head> | |
| <body> | |
| <div class="page"> | |
| <div class="hero-label">Architecture Exploration</div> | |
| <h1>PostgreSQL 19 Property Graphs × post-graph</h1> | |
| <p class="hero-sub">What SQL/PGQ brings to the table, where it overlaps with what post-graph already does, and a practical path for adopting it.</p> | |
| <div class="verdict"> | |
| <p><strong>Bottom line:</strong> 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 <code>CREATE PROPERTY GRAPH</code> declarations on top and offer <code>GRAPH_TABLE</code> pattern matching as an alternative query surface for simple hops.</p> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">01</span> What PG19 Property Graphs Are</h2> | |
| <p>PostgreSQL 19 implements <strong>SQL/PGQ</strong> (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:</p> | |
| <div class="feature-grid"> | |
| <div class="feature-card"> | |
| <h4><code>CREATE PROPERTY GRAPH</code></h4> | |
| <p>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.</p> | |
| </div> | |
| <div class="feature-card"> | |
| <h4><code>GRAPH_TABLE()</code></h4> | |
| <p>A table function in <code>FROM</code> clauses. Takes a graph name, a <code>MATCH</code> pattern, and a <code>COLUMNS</code> projection. Patterns use ASCII-art syntax: <code>(v1)-[e]->(v2)</code>. Internally rewritten to joins.</p> | |
| </div> | |
| </div> | |
| <h3>Declaring a property graph</h3> | |
| <pre><code>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) | |
| );</code></pre> | |
| <h3>Querying with pattern matching</h3> | |
| <pre><code>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) | |
| );</code></pre> | |
| <h3>What's supported vs. what's not</h3> | |
| <div class="table-wrap"> | |
| <table> | |
| <thead> | |
| <tr><th>Feature</th><th>PG19 Status</th></tr> | |
| </thead> | |
| <tbody> | |
| <tr><td>Vertex/edge declaration with labels</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Explicit KEY & REFERENCES</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Column → property mapping</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Single-hop pattern matching</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Multi-hop (explicit chaining)</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>WHERE inside patterns</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Multi-label OR: <code>(IS a|b)</code></td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Directed & undirected edges</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| <tr><td>Quantified paths <code>{1,5}</code></td><td><span class="tag tag-gap">Not in PG19</span></td></tr> | |
| <tr><td>SHORTEST PATH</td><td><span class="tag tag-gap">Not in PG19</span></td></tr> | |
| <tr><td>Path modes (WALK, TRAIL, SIMPLE)</td><td><span class="tag tag-gap">Not in PG19</span></td></tr> | |
| <tr><td>Graph mutations via graph syntax</td><td><span class="tag tag-gap">Not in PG19</span></td></tr> | |
| <tr><td>DROP PROPERTY GRAPH</td><td><span class="tag tag-replace">Supported</span></td></tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| <div class="callout callout-gap"> | |
| <p><strong>Critical gap:</strong> PG19 has no variable-length path traversal. The pattern <code>(a)-[*1..5]->(b)</code> 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.</p> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">02</span> Feature-by-Feature Impact</h2> | |
| <p>Mapping each post-graph capability against PG19's property graph support:</p> | |
| <div class="table-wrap"> | |
| <table> | |
| <thead> | |
| <tr><th>post-graph Feature</th><th>PG19 Impact</th><th>Verdict</th></tr> | |
| </thead> | |
| <tbody> | |
| <tr> | |
| <td>Table creation DDL<br><code>create_vertex_table()</code>, <code>create_edge_table()</code></td> | |
| <td>PG19 property graphs overlay <em>existing</em> tables — they don’t create them. post-graph’s DDL generation remains the foundation.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Multi-tenancy<br><code>realm</code> column, composite PK</td> | |
| <td>No native multi-tenancy in SQL/PGQ. The <code>realm</code> column can be exposed as a property and filtered in <code>WHERE</code> inside patterns, but there’s no built-in isolation.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Schema-per-realm<br>One PG schema per tenant</td> | |
| <td>Each realm’s schema could declare its own property graph. Natural fit — <code>"tenant_a".mygraph</code> vs <code>"tenant_b".mygraph</code>.</td> | |
| <td><span class="tag tag-augment">Augment</span></td> | |
| </tr> | |
| <tr> | |
| <td>Space isolation<br>Logical sub-groups within a realm</td> | |
| <td>Exposed as a property; filterable via <code>WHERE p.space = 'x'</code> in patterns.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Neighbor queries<br><code>get_neighbors()</code></td> | |
| <td>Single-hop <code>GRAPH_TABLE MATCH</code> is a direct replacement. Cleaner syntax, same performance (rewritten to joins).</td> | |
| <td><span class="tag tag-augment">Augment</span></td> | |
| </tr> | |
| <tr> | |
| <td>Recursive traversal<br><code>traverse()</code> via <code>WITH RECURSIVE</code></td> | |
| <td>PG19 cannot do variable-length traversal. Recursive CTE stays.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Shortest path<br><code>shortest_path()</code></td> | |
| <td>Not available in PG19 SQL/PGQ. Recursive CTE stays.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Cycle detection<br>Pre-insert cycle check</td> | |
| <td>Not available. Keep existing <code>shortest_path()</code>-based check.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>pgvector search<br><code>vector_search()</code></td> | |
| <td>Embedding columns can be exposed as properties, but PG19 pattern matching has no distance operators. Dedicated vector search stays.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Audit triggers<br><code>_audit</code> tables</td> | |
| <td>Orthogonal — property graphs are read-only overlays and don’t interact with triggers.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| <tr> | |
| <td>Data history<br><code>_data</code> append-only tables</td> | |
| <td>Data tables could be included as additional vertex tables in the graph with a <code>version</code> label for temporal queries.</td> | |
| <td><span class="tag tag-augment">Augment</span></td> | |
| </tr> | |
| <tr> | |
| <td>CRUD operations<br><code>add_vertex()</code>, <code>add_edge()</code>, etc.</td> | |
| <td>PG19 property graphs are read-only. All mutations go through regular SQL.</td> | |
| <td><span class="tag tag-keep">Keep as-is</span></td> | |
| </tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">03</span> Architecture: Current vs. Proposed</h2> | |
| <div class="diagram"> | |
| <pre><code>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 │ | |
| └──────────────────────┘ └──────────────────────┘</code></pre> | |
| <p class="diagram-caption">The property graph declaration sits alongside existing tables as metadata. All writes continue through regular SQL. The new <code>match()</code> method uses <code>GRAPH_TABLE()</code> for pattern-based reads.</p> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">04</span> Concrete Changes Required</h2> | |
| <h3>New methods on AsyncPostGraph / SQLAlchemyPostGraph</h3> | |
| <div class="table-wrap"> | |
| <table> | |
| <thead><tr><th>Method</th><th>Purpose</th><th>Notes</th></tr></thead> | |
| <tbody> | |
| <tr> | |
| <td><code>create_property_graph(name, realm?)</code></td> | |
| <td>Generate <code>CREATE PROPERTY GRAPH</code> DDL from the client’s known tables</td> | |
| <td>Introspects <code>pg_class</code> / <code>pg_constraint</code> to discover vertex & edge tables and their FK relationships. In schema-per-realm mode, scopes to the realm’s schema.</td> | |
| </tr> | |
| <tr> | |
| <td><code>drop_property_graph(name)</code></td> | |
| <td>Execute <code>DROP PROPERTY GRAPH IF EXISTS</code></td> | |
| <td>Thin wrapper. Needed for cleanup and recreation after schema changes.</td> | |
| </tr> | |
| <tr> | |
| <td><code>refresh_property_graph(name)</code></td> | |
| <td>Drop and recreate after table changes</td> | |
| <td>Since property graphs reference table structure at creation time, adding a new vertex/edge table requires recreation.</td> | |
| </tr> | |
| <tr> | |
| <td><code>match(graph, pattern, columns)</code></td> | |
| <td>Execute a <code>GRAPH_TABLE()</code> query and return results</td> | |
| <td>Takes a pattern string and column projections. Returns list of dicts or model objects. Adds realm filtering automatically.</td> | |
| </tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| <h3>Property graph generation</h3> | |
| <p>The <code>create_property_graph()</code> method would introspect the database to build the DDL. Here's how post-graph's table structure maps to SQL/PGQ declarations:</p> | |
| <pre><code>-- 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) | |
| );</code></pre> | |
| <h3>Label strategy</h3> | |
| <p>Two options for mapping post-graph concepts to PG19 labels:</p> | |
| <div class="feature-grid"> | |
| <div class="feature-card"> | |
| <h4>Option A: Table name = Label</h4> | |
| <p>Each vertex table becomes a label (e.g., <code>people</code> → <code>LABEL person</code>). Each edge table becomes a label. Simple, automatic. Edge <code>relation_type</code> is just a filterable property.</p> | |
| </div> | |
| <div class="feature-card"> | |
| <h4>Option B: relation_type = Label <span class="tag tag-caution">Complex</span></h4> | |
| <p>Map edge <code>relation_type</code> values to separate labels. Requires knowing all relation types at graph creation time — either via introspection (<code>SELECT DISTINCT relation_type</code>) or user declaration. More expressive but harder to keep in sync.</p> | |
| </div> | |
| </div> | |
| <div class="callout callout-note"> | |
| <p><strong>Recommendation:</strong> Start with Option A. The <code>relation_type</code> column is already filterable via <code>WHERE</code> in patterns, so there's no loss of expressiveness. Option B can be added later as an opt-in feature.</p> | |
| </div> | |
| <h3>Multi-tenancy considerations</h3> | |
| <p>The <code>realm</code> column is part of every composite PK and FK. This needs careful handling:</p> | |
| <ul> | |
| <li><strong>Default mode:</strong> The property graph spans all realms. Queries must include <code>WHERE v.realm = 'tenant_a'</code> in every pattern. The <code>match()</code> wrapper injects this automatically.</li> | |
| <li><strong>Schema-per-realm:</strong> Each realm schema declares its own property graph. This is cleaner — no cross-realm leakage possible. <code>create_property_graph()</code> generates one per realm.</li> | |
| </ul> | |
| <h3>Version detection</h3> | |
| <pre><code># 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</code></pre> | |
| <p>All property graph methods would check <code>self._has_property_graphs</code> and raise a clear error on older versions.</p> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">05</span> What a match() Query Looks Like</h2> | |
| <p>Compare the current programmatic API with the proposed pattern-matching surface:</p> | |
| <h3>Finding neighbors (1-hop)</h3> | |
| <div class="table-wrap"> | |
| <table> | |
| <thead><tr><th style="width:50%">Current API</th><th>With PG19 match()</th></tr></thead> | |
| <tbody> | |
| <tr> | |
| <td> | |
| <pre style="border:none;margin:0;"><code>steps = await client.get_neighbors( | |
| "people", realm, vertex_id, | |
| edge_tables=["knows"], | |
| direction="out" | |
| )</code></pre> | |
| </td> | |
| <td> | |
| <pre style="border:none;margin:0;"><code>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} | |
| )</code></pre> | |
| </td> | |
| </tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| <h3>Two-hop traversal</h3> | |
| <div class="table-wrap"> | |
| <table> | |
| <thead><tr><th style="width:50%">Current API</th><th>With PG19 match()</th></tr></thead> | |
| <tbody> | |
| <tr> | |
| <td> | |
| <pre style="border:none;margin:0;"><code># Requires recursive CTE (max_depth=2) | |
| steps = await client.traverse( | |
| "people", realm, start_id, | |
| edge_tables=["knows"], | |
| direction="out", | |
| max_depth=2 | |
| )</code></pre> | |
| </td> | |
| <td> | |
| <pre style="border:none;margin:0;"><code># 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"} | |
| )</code></pre> | |
| </td> | |
| </tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| <div class="callout callout-gap"> | |
| <p><strong>Limitation:</strong> The <code>match()</code> pattern is fixed at 2 hops. For <code>traverse(max_depth=10)</code>, there's no PG19 equivalent — you'd need 10 chained pattern segments. The recursive CTE remains the only viable approach for variable-depth traversal.</p> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">06</span> Migration Path</h2> | |
| <div class="phase"> | |
| <div class="phase-marker">1</div> | |
| <div class="phase-body"> | |
| <h4>Version detection & feature flag</h4> | |
| <p>Add <code>_pg_version</code> / <code>_has_property_graphs</code> to both clients during <code>connect()</code>. Gate all new methods behind this flag. Zero impact on PG < 19 users.</p> | |
| </div> | |
| </div> | |
| <div class="phase"> | |
| <div class="phase-marker">2</div> | |
| <div class="phase-body"> | |
| <h4>Property graph lifecycle methods</h4> | |
| <p>Implement <code>create_property_graph()</code>, <code>drop_property_graph()</code>, and <code>refresh_property_graph()</code>. These introspect existing tables and FK constraints to auto-generate the DDL. Add optional <code>auto_property_graph=True</code> parameter to <code>create_vertex_table()</code> and <code>create_edge_table()</code>.</p> | |
| </div> | |
| </div> | |
| <div class="phase"> | |
| <div class="phase-marker">3</div> | |
| <div class="phase-body"> | |
| <h4>Pattern matching query method</h4> | |
| <p>Implement <code>match(graph, realm, pattern, columns, where)</code>. This wraps <code>GRAPH_TABLE()</code> with automatic realm injection. Returns results as dicts or Vertex/Edge model objects where possible.</p> | |
| </div> | |
| </div> | |
| <div class="phase"> | |
| <div class="phase-marker">4</div> | |
| <div class="phase-body"> | |
| <h4>Optimize get_neighbors() on PG19+</h4> | |
| <p>Optionally rewrite <code>get_neighbors()</code> to use <code>GRAPH_TABLE</code> internally when a property graph exists. This is transparent to callers but lets the query planner use the graph metadata for optimization.</p> | |
| </div> | |
| </div> | |
| <div class="phase"> | |
| <div class="phase-marker">5</div> | |
| <div class="phase-body"> | |
| <h4>Future: track PG20+ additions</h4> | |
| <p>When PostgreSQL adds quantified paths and shortest path to SQL/PGQ, revisit <code>traverse()</code> and <code>shortest_path()</code> to optionally delegate to native graph operations.</p> | |
| </div> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">07</span> Risks & Considerations</h2> | |
| <div class="feature-grid"> | |
| <div class="feature-card"> | |
| <h4><span class="tag tag-caution">Sync</span> Graph staleness</h4> | |
| <p>Property graphs capture table structure at creation time. Adding a column or a new table requires <code>DROP</code> + <code>CREATE</code>. The library must track when to refresh — either eagerly (after every DDL) or lazily (before first <code>match()</code> call).</p> | |
| </div> | |
| <div class="feature-card"> | |
| <h4><span class="tag tag-caution">Perf</span> Planner overhead</h4> | |
| <p>PG19 rewrites <code>GRAPH_TABLE</code> 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.</p> | |
| </div> | |
| <div class="feature-card"> | |
| <h4><span class="tag tag-caution">API</span> Pattern injection</h4> | |
| <p>If users can pass raw pattern strings to <code>match()</code>, SQL injection via the pattern language is possible. The <code>_validate_identifier()</code> approach won't work for GQL patterns. Need a safe parameterization strategy or a builder API.</p> | |
| </div> | |
| <div class="feature-card"> | |
| <h4><span class="tag tag-caution">Scope</span> Namespace collision</h4> | |
| <p>Property graphs share the namespace with tables and views. The graph name must not collide with existing table names. Use a prefix convention like <code>pg_graph_{realm}</code> or let users choose.</p> | |
| </div> | |
| </div> | |
| <!-- ═══════════════════════════════════════════ --> | |
| <h2><span class="section-num">08</span> Summary</h2> | |
| <div class="table-wrap"> | |
| <table> | |
| <thead><tr><th>Category</th><th>Count</th><th>Detail</th></tr></thead> | |
| <tbody> | |
| <tr><td>Features that stay unchanged</td><td>9</td><td>DDL, CRUD, traverse, shortest path, cycle detection, vector search, audit, data history, space isolation</td></tr> | |
| <tr><td>Features to augment</td><td>3</td><td>Schema-per-realm (auto-declare graphs), neighbors (optional GRAPH_TABLE backend), data history (temporal graph queries)</td></tr> | |
| <tr><td>New methods to add</td><td>4</td><td><code>create_property_graph</code>, <code>drop_property_graph</code>, <code>refresh_property_graph</code>, <code>match</code></td></tr> | |
| <tr><td>PG19 gaps vs. post-graph</td><td>5</td><td>Variable-length paths, shortest path, path modes, mutations, multi-tenancy</td></tr> | |
| </tbody> | |
| </table> | |
| </div> | |
| <div class="callout callout-positive"> | |
| <p><strong>Net assessment:</strong> 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.</p> | |
| </div> | |
| <div class="footer"> | |
| <p>post-graph v0.6.2 · PostgreSQL 19 beta 3 · August 2026</p> | |
| </div> | |
| </div> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment