Anatomy of a graph query

What lives in the graph

seed() creates a Core graph named pr_water — the partitionBy schema API is Core-only. One vertex label, one edge label, and two spatial indexes (one active, one legacy showcase).

// executed once by seed() in app.py
system.graph('pr_water').ifNotExists().create()

schema.vertexLabel('asset').ifNotExists()
  .partitionBy('id', Text)      // partition key → O(1) seek by id
  .property('type', Text)        // TreatmentPlant | Tank | Pump | …
  .property('lng', Double)
  .property('lat', Double)
  .property('criticality', Int)  // 1–5
  .property('cost', Int)         // replacement cost, USD
  .property('health', Double)    // synthetic 0.05–0.99
  .property('point', Point)      // DSE geometry — legacy geo path
  .property('geohash', Text)     // tessellation cell @ precision 5
  .create()

schema.edgeLabel('feeds').ifNotExists()
  .from('asset').to('asset').create()  // directed: water flows from → to

// ACTIVE spatial index — a materialized view whose PARTITION KEY
// is the geohash cell. A bbox query becomes `geohash IN (cells)`:
// a native multi-partition read — index-backed and UNCAPPED.
schema.vertexLabel('asset')
  .materializedView('asset_by_geohash').ifNotExists()
  .partitionBy('geohash')          // cell → its assets
  .clusterBy('id', Asc)            // sorted within each cell
  .create()

// LEGACY showcase — Solr search index on the Point column,
// used only by the Geo.inside() demo query (capped, see below)
schema.vertexLabel('asset')
  .searchIndex().ifNotExists().by('point').create()

One schematic — graph · partitions · indexes · DB2 pointers

The four planes of the storage story on one picture: what Gremlin traverses (top left), the CQL partitions it physically becomes, the spatial indexes derived from them, and the pointer relationship that makes every vertex a key into DB2. Solid arrows are the write/storage path; dashed arrows are derivations and lookups.

flowchart TB
  subgraph GV["Gremlin's view · pr_water"]
    direction LR
    TP1(("TP-1")) -->|feeds| TK1(("TK-1"))
    TP1 -->|feeds| TK2(("TK-2"))
    TP1 -->|feeds| P1(("P-1"))
  end

  subgraph D2["IBM DB2 · system of record"]
    direction TB
    AD[("ASSET_DETAIL · PK asset_id (= vertex id)
name · mfr · install · specs · history")]
    AF[("ASSET_FEEDS
authoritative edge list")]
  end

  subgraph PHYS["physical storage · CQL tables"]
    direction TB
    AT["asset — one partition per vertex · PARTITION KEY id
TP-1 → type · lng · lat · health · point · geohash=de2by"]
    ET["asset__feeds__asset — one partition per source
PARTITION KEY out_id · CLUSTERING in_id
TP-1 → TK-1, TK-2, P-1  ⇒  out('feeds') = ONE read"]
  end

  subgraph IDX["spatial indexes"]
    direction LR
    MV["asset_by_geohash — materialized view
PARTITION KEY geohash (cell)
de2bz → TK-2, … · cells IN … · uncapped"]
    SI["search index on point — Solr
legacy · 10-row cap"]
  end

  TP1 -->|"vertex = row"| AT
  TP1 -->|"edge = (out_id, in_id) row"| ET
  AT ==>|"auto-maintained on every write"| MV
  AT -.->|"async Solr sync"| SI
  TP1 -.->|"POINTER · id = asset_id · hydrate on click"| AD
  AD -.->|"lean projection · dsbulk / CDC"| AT
  AF -.->|"loads & rebuilds · dsbulk / CDC"| ET

  classDef gv fill:#07131f,stroke:#7dd3fc,stroke-width:1.5px,color:#eef3f8
  classDef phys fill:#0f0d24,stroke:#a78bfa,stroke-width:1.5px,color:#eef3f8
  classDef idx fill:#171105,stroke:#fbbf24,stroke-width:1.5px,color:#eef3f8
  classDef idxlegacy fill:#171105,stroke:#fbbf24,stroke-width:1px,stroke-dasharray:5 4,color:#93a4b5
  classDef db2 fill:#05140f,stroke:#34d399,stroke-width:1.5px,color:#eef3f8
  class TP1,TK1,TK2,P1 gv
  class AT,ET phys
  class MV idx
  class SI idxlegacy
  class AD,AF db2
  style GV fill:#060d18,stroke:#7dd3fc,stroke-width:1px
  style PHYS fill:#060d18,stroke:#a78bfa,stroke-width:1px
  style IDX fill:#060d18,stroke:#fbbf24,stroke-width:1px
  style D2 fill:#060d18,stroke:#34d399,stroke-width:1px
  linkStyle 5 stroke:#fbbf24,color:#fbbf24
  linkStyle 7 stroke:#34d399,stroke-width:2.5px,color:#34d399
  linkStyle 8,9 stroke:#a78bfa,color:#a78bfa

How the graph gets loaded — and how it would at scale

This PoC loads its 94 elements with sequential Gremlin — measured at ~4 seconds for a full rebuild on this host. That's the right tool for a demo and the wrong one past a few thousand elements; the paths below are the ladder up. The key that unlocks all of them: a Core graph is just CQL tables, so bulk loading the graph is bulk loading tables.

What seed() actually does (this PoC)

# timestamps from a forced RESEED=1 on this host
08:08:56.95  [seed] RESEED=1 -> (re)building at precision 5
08:08:59.17  [seed] building Core graph 'pr_water' ...
             └ drop + create graph + schema + MV
               + search index          ≈ 2.2 s
08:09:01.01  [seed] done: 39 assets, 55 pipes
             └ 39 × addV + 55 × addE     ≈ 1.8 s
               one execute_graph round trip per
               element ≈ 20 ms  ⇒  ~50 elements/s

# each vertex: one bound Gremlin call (id reserved → bind aid)
g.addV('asset').property('id', aid).property('type', t)
 .property('lng', lng).property('lat', lat)…
 .property('point', Geo.point(lng, lat))
 .property('geohash', gh)   # computed client-side, precision 5

# each edge: two partition-key seeks + one insert
g.V().has('asset','id', s).as('s')
 .V().has('asset','id', d).addE('feeds').from('s')

The unlock: the graph is two CQL tables

Straight from cqlsh on the running node — vertices are rows in asset; an edge is a two-column row in asset__feeds__asset, partitioned by the source vertex:

cqlsh> USE pr_water; DESCRIBE TABLES;
asset   asset__feeds__asset

CREATE TABLE pr_water.asset__feeds__asset (
    out_id text,
    in_id  text,
    PRIMARY KEY (out_id, in_id)     -- partitioned by source vertex:
) WITH CLUSTERING ORDER BY (in_id ASC)  -- out('feeds') = ONE partition read

The ladder of load paths

dsbulk, concretely for pr_water

# schema must exist first (run seed()'s schema pass, or paste it in Studio).
# dsbulk is a separate download — it is NOT inside the dse-server image.

# vertices: CSV columns = property names (geohash precomputed! see below)
dsbulk load -g pr_water -v asset \
       -url assets.csv -header true -h my-dse-host

# edges: -from/-to name the endpoint vertex labels;
# CSV maps the endpoint ids (out_id/in_id)
dsbulk load -g pr_water -e feeds -from asset -to asset \
       -url pipes.csv -header true -h my-dse-host

Millions of nodes, each a pointer into DB2

The target architecture (detailed in the companion DSE Graph × DB2 design draft) inverts nothing about this PoC — it just splits the data honestly: every vertex is a pointer, whose id is the primary key of a row in IBM DB2, the system of record. The graph keeps only what a traversal or the map itself consumes — id · type · lng · lat · geohash · health and the feeds topology. Name, manufacturer, install date, inspections, specs, history: DB2 only. That contract is precisely what makes millions of nodes cheap.

the two query paths this page animates are the reason pointer-nodes scale: the MV read touches covering cells (not table size), and adjacency is one partition per frontier vertex — a graph of pointers to a multi-TB DB2 estate stays a few GB of hot topology.

Initial bulk load — DB2 ➜ graph, the million-row seed()

Staying in sync — DB2 keeps writing, the graph keeps up

The lean contract shrinks the sync problem before any tooling enters: work orders, inspections, documents — the bulk of DB2 write traffic — never touch the graph. Only pointer-relevant changes replicate: asset created / moved / decommissioned, pipe added / removed, health rescored.

-- the trickle that must flow DB2 → graph
new asset        → INSERT vertex row (+ geohash)
moved asset      → UPDATE lng/lat/geohash (MV self-maintains)
health rescored  → UPDATE health
new pipe         → INSERT (out_id, in_id) edge row
decommission     → DELETE vertex + edges (out + in, from ASSET_FEEDS)

-- and because Core vertices ARE CQL rows, every one of these
-- is a plain CQL statement — INSERT is a native upsert by
-- primary key, so the consumer is idempotent by construction:
INSERT INTO pr_water.asset
  (id, type, lng, lat, health, geohash, point)
VALUES (?, ?, ?, ?, ?, ?, ?);   -- replay-safe

Runtime at that scale — hydrate on demand

Traversals and region queries keep returning what they return today: pointers. The BFF hydrates detail from DB2 only for what a human actually inspects — SELECT … WHERE asset_id = ? on click (one row), or a single SUM(replacement_cost) WHERE asset_id IN (…) pushed to DB2 when score_impact() should use authoritative costs instead of graph-cached ones. And /api/graph stops meaning "everything": the viewport loads through the same geohash path as a drawn rectangle — the spatial index is also the pagination mechanism.

How the geohash tessellation works

A geohash tessellates the globe hierarchically: each base-32 character appends 5 bits that alternate between longitude and latitude, splitting the current cell into 32 sub-cells. Prefixes nest — every cell starting de30 lies inside cell de30's bounds. Every asset stores its cell at precision 5 (≈ 4.9 km squares over Puerto Rico), and that string is the partition key of asset_by_geohash.

A region query never scans the table. It computes the cells covering the shape's bounding box (geohash_cells_for_bbox), reads exactly those partitions (within(cells) — tier 1), then trims the over-covered candidates to the exact geometry in Python (tier 2). Cost scales with the query's area, not the table's size: the same circle touches the same 12 partitions whether the graph holds 39 assets or 39 million.

The selectivity dial, measured

Finer precision = more partitions to enumerate in the IN clause but fewer candidates to refine; coarser = the reverse. Precision 5 is the sweet spot for this asset density — 12 partition reads, and tier 2 only has to distance-check 17 rows.

The Python functions, end to end

Everything below is verbatim from app.py (trimmed comments). Chips show who calls what.

Entry points — the three read APIs

downstream = g(
    "g.V().has('asset','id', sid)"
    ".repeat(out('feeds').simplePath()).emit().dedup().values('id')",
    sid=asset_id)
return jsonify(start=asset_id, downstream=downstream,
               count=len(downstream),
               impact=score_impact(asset_id, downstream))

lng_pad = radius_deg / max(math.cos(math.radians(lat)), 0.01)
cands = assets_in_bbox(lng - lng_pad, lng + lng_pad,
                       lat - radius_deg, lat + radius_deg)
hits = [c for c in cands
        if gc_degrees(lng, lat, c["lng"], c["lat"]) <= radius_deg]

lngs = [p[0] for p in poly]; lats = [p[1] for p in poly]
candidates = assets_in_bbox(min(lngs), max(lngs),
                            min(lats), max(lats))
hits = [c for c in candidates
        if point_in_polygon(c["lng"], c["lat"], poly)]

Spatial core — tier 1 (index) and tier 2 (exact)

_B32 = "0123456789bcdefghjkmnpqrstuvwxyz"
lat_lo, lat_hi = -90.0, 90.0
lng_lo, lng_hi = -180.0, 180.0
out, ch, bit, even = [], 0, 0, True
while len(out) < precision:
    if even:                      # longitude bit
        mid = (lng_lo + lng_hi) / 2
        if lng >= mid: ch = (ch << 1) | 1; lng_lo = mid
        else:          ch = ch << 1;       lng_hi = mid
    else:                         # latitude bit
        mid = (lat_lo + lat_hi) / 2
        if lat >= mid: ch = (ch << 1) | 1; lat_lo = mid
        else:          ch = ch << 1;       lat_hi = mid
    even = not even; bit += 1
    if bit == 5:
        out.append(_B32[ch]); ch = 0; bit = 0
return "".join(out)

lng_bits = (5 * precision + 1) // 2   # lng gets the extra bit
lat_bits = (5 * precision) // 2
n_lng, n_lat = 1 << lng_bits, 1 << lat_bits
cell_lng, cell_lat = 360.0 / n_lng, 180.0 / n_lat
i0 = int((min_lng + 180.0) // cell_lng)
i1 = int((max_lng + 180.0) // cell_lng)
j0 = int((min_lat + 90.0) // cell_lat)
j1 = int((max_lat + 90.0) // cell_lat)
cells = set()
for i in range(max(0, i0), min(n_lng - 1, i1) + 1):
    cx = -180.0 + (i + 0.5) * cell_lng
    for j in range(max(0, j0), min(n_lat - 1, j1) + 1):
        cy = -90.0 + (j + 0.5) * cell_lat
        cells.add(geohash_encode(cx, cy, precision))
return sorted(cells)

cells = geohash_cells_for_bbox(min_lng, max_lng, min_lat, max_lat)
if not cells:
    return []
return g(
    "g.V().has('asset', 'geohash', within(cells))"
    ".project('id','type','lng','lat','health')"
    ".by('id').by('type').by('lng').by('lat').by('health')",
    cells=cells)

R_KM = 6371.0088
p1, p2 = math.radians(lat1), math.radians(lat2)
dphi = math.radians(lat2 - lat1)
dlam = math.radians(lng2 - lng1)
a = (math.sin(dphi / 2) ** 2
     + math.cos(p1) * math.cos(p2) * math.sin(dlam / 2) ** 2)
return (2 * R_KM * math.asin(math.sqrt(a))) / 111.195

inside = False
n = len(ring); j = n - 1
for i in range(n):
    xi, yi = ring[i]
    xj, yj = ring[j]
    if (yi > lat) != (yj > lat):          # edge straddles the ray
        x_cross = xi + (lat - yi) * (xj - xi) / (yj - yi)
        if lng < x_cross:
            inside = not inside
    j = i
return inside

downstream_cost = sum(ASSET_INDEX[i][5]
                      for i in downstream_ids if i in ASSET_INDEX)
return {
    "headline": "Downstream replacement value at risk",
    "value": downstream_cost,     # TP-1 → 3_735_000
    "unit": "usd",
}

Lifecycle (boot path)

entrypoint.sh runs seed() exactly once (never per gunicorn worker — they'd race the build), which chains: wait_for_graph_ready() — retries through the post-boot code=2200 window → graph_exists_and_seeded() — vertex count and geohash-length check → create schema + MV + 39 vertices + 55 edges if stale. Then gunicorn forks workers, each opening its own driver session.

The Gremlin, three queries

Paste any of these into DSE Studio (localhost:9091 → connect to my-dse, graph pr_water) to watch them natively.

1 · Structural — blast radius (click flow)

g.V().has('asset','id','TP-1')
 .repeat(out('feeds').simplePath())  // follow pipes downstream; no revisits
 .emit()                              // collect EVERY level, not just the last
 .dedup().values('id')                // → 25 ids in 8 waves

2 · Spatial, active path — tessellation index (shape flow)

g.V().has('asset','geohash', within('de2bq','de2br','de2bw','de2bx','de2by','de2bz',
                             'de302','de303','de308','de309','de30b','de30c'))
 .project('id','type','lng','lat','health')
 .by('id').by('type').by('lng').by('lat').by('health')
// multi-partition read on asset_by_geohash — indexed, UNCAPPED → 17 rows

3 · Spatial, legacy showcase — DSE-native geo (capped)

g.V().has('asset','point',
      Geo.inside(Geo.point(-66.25, 18.42), 0.06, Geo.Unit.DEGREES))
 .values('id')
// elegant — but DSE Search caps one graph query at 10 rows at the index
// fetch; .limit() can't lift it. Kept only to demonstrate the trap the
// materialized-view path avoids.

Terms used on this page

Graph & traversal

assetvertex label

The graph's single vertex type: one water-network component — TreatmentPlant, Tank, Pump, Junction, Valve or ServiceConnection. 39 in total.

blast radius

Everything downstream of a failed asset — the vertices reached by repeat(out('feeds')) — plus the business score over them (score_impact: $3.735 M for TP-1).

Core graph

DSE 6.8+ graph engine where every vertex label is a real CQL table (partitionBy schema API, GraphSON3 required). Contrast with the older "Classic" engine. pr_water is Core.

dedup()

Gremlin step that collapses duplicates. Needed because the pipe network is a mesh: several paths can reach the same vertex during the walk.

emit()

Gremlin: yield every vertex visited during repeat(), not just the final frontier — the difference between "all 25 downstream" and "the 2 leaves".

feedsedge label

The single directed edge type: water flows from → to. 55 pipes. Direction is what makes "downstream" meaningful.

GraphSON 3.0

The JSON serialization for Gremlin values on the wire. Mandatory for Core graphs; the driver's graph_graphson3_row_factory decodes it into plain Python values.

Gremlin

Apache TinkerPop's graph traversal language — what DSE Graph executes. A query is a pipeline of steps (has → repeat → emit → dedup) describing a walk.

simplePath()

Gremlin cycle guard: a walk may never revisit a vertex already on its current path. Without it, a looped pipe network could traverse forever.

traversal

One Gremlin query — a declarative description of a walk that the engine compiles into seeks and adjacency reads.

Spatial & the tessellation index

bounding boxbbox

The smallest axis-aligned rectangle enclosing a shape. Tier 1 always works on the bbox (padded by r/cos(lat) for circles) because covering a rectangle with grid cells is trivial and always a superset.

candidate

An asset returned by the tier-1 prefilter: inside a covering cell, so maybe inside the shape. Tier 2 keeps or drops it. The example circle: 17 candidates → 7 hits.

covering cells

The set of tessellation cells whose union contains the query bbox — computed by geohash_cells_for_bbox(), guaranteed to include every cell any in-box asset could be stored under.

geohash

A base-32 string naming a rectangular cell of the globe. Each character adds 5 bits that alternately halve longitude and latitude, so cells nest by prefix: de30b lies inside de30. Asset TK-2's cell at precision 5 is de2bz.

great-circle distance

Shortest distance between two points over the sphere (haversine formula). gc_degrees() expresses it in degrees (km ÷ 111.195) so the same radius number works in DSE, Leaflet and the refine.

Point

DSE's geometry type (Geo.point(lng, lat)) stored per asset — used only by the legacy Geo.inside() path; the active path uses the geohash text column instead.

precisionGEOHASH_PRECISION

The geohash length — the selectivity dial. Deployed at 5 (≈ 4.9 km cells). Finer = more cells, fewer candidates; coarser = the reverse. Changing it triggers an automatic reseed (stored cells must match query cells).

prefilter / refinetier 1 / tier 2

The two-tier pattern of every production geo system: a cheap index-pruned superset first (DSE, by cell), exact-but-bounded geometry second (Python, great-circle or ray-cast). Correctness lives in tier 2; scalability in tier 1.

ray castingeven-odd rule

Point-in-polygon test: shoot a ray from the point and count how many polygon edges it crosses — odd means inside. Works for any simple polygon, convex or concave (point_in_polygon()).

superset guarantee

The invariant tier 1 must uphold: it may return extra assets, never miss one inside the shape. Cells over-cover the bbox and the bbox over-covers the shape, so the guarantee holds by construction.

tessellation

Partitioning space into non-overlapping cells that jointly cover it. The geohash grid is a hierarchical tessellation — same idea as S2 cells or Uber's H3, minus the libraries.

Cassandra storage & indexing

CQL native protocol:9042

Cassandra's binary wire protocol. DSE graph queries ride it too — execute_graph() is a CQL request carrying a Gremlin payload.

materialized viewMV

A server-maintained copy of a table organized under a different partition key. asset_by_geohash re-keys assets by cell so geohash IN (…) becomes a native indexed read. Kept in sync by DSE automatically on every write.

partition key

The column(s) that decide where a row lives in Cassandra. Equality and IN lookups on it are direct seeks — no scan. asset is keyed by id (fast click flow); the MV by geohash (fast region flow).

search indexDSE Search / Solr

DSE's embedded Lucene. Supports rich geo predicates (Geo.inside) but caps a single graph query at 10 rows at the index fetch — the trap that motivated the MV path. Present here on point as a showcase only.

secondary index

Cassandra's per-column index. Serves single-equality lookups only — it cannot answer IN/within() over many values, which is why it couldn't back the tessellation.

uncapped

No arbitrary result limit. The MV partition read returns every matching row — in contrast to the search-index path's silent 10-row truncation.

upsert

Insert-or-replace by key. Cassandra's INSERT is natively an upsert on the primary key — which makes a DB2→graph sync consumer replay-safe: applying the same change twice converges to the same row.

write amplification

Extra physical writes per logical write. Every asset insert also writes its asset_by_geohash MV row (~2×) — the standing cost of a self-maintaining index, and why huge loads create MVs after loading.

Loading & the DB2 source of truth

CDCchange data capture

Streaming a database's row changes as events (InfoSphere Data Replication, Debezium). In the DB2-mastered design, CDC carries the trickle of pointer-relevant changes — new/moved/decommissioned assets, new pipes — from DB2 into the graph.

dsbulkDataStax Bulk Loader

DataStax's high-throughput CSV/JSON loader. Its graph mode (-g -v / -g -e) maps files straight onto a Core graph's vertex and edge tables — the documented way to load graphs too big for Gremlin loops. Separate download; not in the dse-server image.

hydrationlazy

Fetching an entity's full record only when it's actually displayed. Traversals return pointer ids; the BFF hydrates one asset from DB2 on click (WHERE asset_id = ?) instead of shipping master data with every query.

seedseed()

The idempotent boot step: create graph + schema + indexes, insert 39 vertices and 55 edges. Skipped when the graph is already complete and its geohash length matches the configured precision.

system of recordsource of truth

The store whose data wins every disagreement — here IBM DB2, holding the wide asset master rows. The graph is a derived projection of it: disposable, rebuildable, eventually consistent, and never written to directly by business processes.

Serving & container runtime

aardvark-dns

Podman's DNS daemon for container networks. It is what lets the BFF dial my-dse by name — resolving it to its dse-net address (10.89.1.6).

BFFbackend-for-frontend

A small server shaped around exactly what one UI needs — here app.py: it seeds the graph, serves the map, and exposes three read APIs. The UI never talks to DSE directly.

pasta

The user-mode network forwarder rootless Podman uses to publish ports — it carries host :8200 traffic into the container's network namespace.

rootless

Podman running entirely as your user — no daemon, no root. Why port publishing needs pasta and DNS needs aardvark-dns rather than kernel-level plumbing.

WSGIweb server gateway interface

Python's contract between web servers and apps: gunicorn parses HTTP into an environ dict and calls app(environ, start_response); Flask is the app side.