Production-tested Postgres patterns for graph-shaped data — distilled from GreenRoom, where a deduplicated entity graph of people, venues, works, and typed relationships is the core data asset.
The thesis: for graphs that fit in one database, Postgres is enough. Recursive CTEs traverse, pg_trgm and pgvector resolve entities, materialized views rank, advisory locks coordinate. No graph database, no second system to operate, and your graph queries join directly against the rest of your data.
Every file runs top-to-bottom against the bundled seed data and states its expected output. The seed is a small fictional music scene (53 nodes, ~100 edges, 6 months of events) engineered so each pattern has a right answer — including a decoy long path so shortest-path can visibly win, and near-duplicate names so entity resolution has something real to resolve.
docker compose up -d --wait
# host psql:
export PGPASSWORD=postgres
psql -h localhost -p 5544 -U postgres -d graph -f schema.sql -f seed.sql
psql -h localhost -p 5544 -U postgres -d graph -f patterns/01-shortest-path-bfs.sql
# …or without host psql:
docker exec -i pgp-demo psql -U postgres -d graph < schema.sql
docker exec -i pgp-demo psql -U postgres -d graph < seed.sql
docker exec -i pgp-demo psql -U postgres -d graph < patterns/01-shortest-path-bfs.sqlEach pattern file is independent and rerunnable (files that write clean up after themselves).
01 — Shortest path with parent-pointer BFS.
The naive recursive CTE carries a path array per row and goes exponential in
dense graphs. This one carries only (node, depth, parent), dedups globally
with UNION, collapses to one shortest row per node with DISTINCT ON, and
reconstructs the path with a second tiny recursion over parent pointers. Also:
the JOIN LATERAL two-probe trick that traverses a directed table undirected
without an index-defeating OR.
02 — N-hop neighborhood expansion.
Frontier BFS returning a {nodes, edges} jsonb payload in one round trip —
the query behind "show my extended network." Plus unnest ... WITH ORDINALITY
to turn a stored uuid path into named hops, in order, in SQL.
03 — Canonical-pair upserts.
One edges table holding both directed (manages) and symmetric
(collaborated_with) relationships, kept duplicate-free by two partial
unique indexes: LEAST/GREATEST normalizes the unordered pair for
bidirectional edges, the ordered triple covers directed ones, and each upsert
targets its index with ON CONFLICT ... WHERE.
04 — Fuzzy entity resolution with pg_trgm.
Indexed trigram similarity for point lookups and pairwise dedup sweeps — and a
demonstration of trigram's blind spot: template-similar names ("Session Artist
01/02", 0.800) outscore a true duplicate ("DJ Marrow"/"Marrow", 0.700).
Candidates therefore flow into a review queue whose upsert encodes two rules:
scores only ratchet up, and a human rejected is sticky forever.
05 — Hybrid vector + trigram search.
Fixing pattern 04's blind spot: HNSW-indexed cosine KNN generates candidates
(ORDER BY embedding <=> query — the raw distance, so the index engages),
then a trigram blend re-ranks the small candidate set. The true duplicates
blend to ~0.9 while the template-similar noise collapses to ~0.08.
06 — Momentum scoring in a materialized view.
Trajectory beats totals for discovery. Time-windowed FILTER (WHERE ...)
aggregates in a single pass, recent-vs-baseline trend buckets, and
PERCENT_RANK() OVER (PARTITION BY kind) so entities rank against their peer
cohort — plus the unique index that makes REFRESH CONCURRENTLY legal.
07 — Keyset pagination with a composite cursor.
OFFSET reads-and-discards its way to every deep page and shifts under
concurrent writes. A (created_at, id) row-comparison cursor starts the index
scan exactly where the last page ended — the file proves it with an
Index Only Scan plan and a full-table tiling check (no gaps, no duplicates,
enforced by a PRIMARY KEY mid-walk).
08 — Advisory locks for per-entity serialization.
Two workers computing signals for the same entity must not interleave, but
there may be no row to FOR UPDATE yet. pg_advisory_xact_lock(hashtext(key))
serializes on an application-defined key and self-releases at commit; the
try_ variant lets a redundant second worker skip instead of queue.
- The vector demos use deterministic pseudo-embeddings (
seed.sql) so everything runs offline with no model dependency; near-duplicate nodes get jittered copies of the same vector, which is how real duplicates behave under a real embedding model. - Numbers you'd tune in production — similarity thresholds, blend weights, momentum windows — are called out in comments where they appear. The values here are sensible starting points, not gospel.
- Patterns distilled from production; the schema here is generic and shares nothing with any production system.
MIT