---

Graph: Where a Recursive CTE Stops Being Enough

Every graph query you'd write in Postgres works. The question is at what depth it stops finishing.

Postgres models a graph as an edge table and traverses it with a recursive CTE:

Each hop is an index lookup per row in the current frontier. With an average out-degree of 50, the frontier is 50, then 2,500, then 125,000, then 6.25 million index lookups — each one a potentially random page read, and the intermediate result set has to be materialised and deduplicated. This is Module F-2's random-access penalty compounding geometrically. Two hops is a fast query. Four hops on a social-scale graph is a query that consumes a connection for minutes, which by Module F-13's cascade is how one traversal takes down a service.

A native graph store attacks exactly this. Neo4j's mechanism is index-free adjacency: a node record holds direct pointers to its relationship records, and each relationship points at the next one. Traversing an edge is a pointer dereference, not an index probe. The cost of one hop is therefore proportional to the degree of the node you're standing on, and independent of total graph size. A 6-hop query on a 10-billion-edge graph costs the same per hop as on a 1-million-edge graph, which is not true of the join.

The query language reflects it — depth becomes a parameter rather than a structural rewrite:

Writing that query as SQL is a page of recursive CTE, and changing 6 to 8 in the SQL version changes its runtime by orders of magnitude while the Cypher version changes it linearly-ish in the frontier.

Where a graph store earns its keep, and where it's overkill

It wins when the shape of the connections is the answer, and the depth isn't known when you write the query:

• Fraud rings. "Is this new account connected to a known-fraudulent one through any chain of shared devices, addresses, payment instruments, or referrals?" Nobody knows whether the chain is 2 or 7 long, and the ring structure — a cycle, a hub — is the signal. Detecting it in SQL means guessing a depth.
• Permissions hierarchies. "Does Priya have read access to this document?" where access flows through nested groups, folder inheritance, org-chart delegation, and explicit grants. Variable depth, and the answer must be exact. (Module A-13 covers the authorisation model itself.)
• Recommendations over relationships. "People who bought what you bought also bought" is two hops and a GROUP BY — Postgres handles it. "Products connected to your purchases through shared categories, co-purchase edges, and brand affinity, weighted by path length" is a traversal.

It's overkill when:

• Your depth is fixed and small. A manager_id self-reference on an org table read three levels deep is a recursive CTE with a good index. Do not add a database for it.
• Your workload is really a filtered aggregate over nodes. "Count users in region X created last month" is a relational query. Graph stores do not have the columnar or index machinery to do this well, and you will have made your most common query worse to speed up a rare one.
• Your write volume needs horizontal scaling. This is the honest cost. Index-free adjacency depends on locality, and locality is what sharding destroys — a traversal crossing shards becomes a network hop per edge, which is the thing you paid to avoid. Neo4j scales reads with follower replicas but routes writes to a single leader, so write throughput is a vertical-scaling problem (Module F-9). If your bottleneck is write volume rather than traversal depth, a graph store is the wrong direction.
• You need it for one feature. Which is the usual case, and points at the pattern in the closing section rather than at a migration.

Why this matters in production: the deceptive thing about graph workloads is that they pass code review and pass staging. A 4-hop recursive CTE on a 10,000-row development graph returns in 30 ms. The out-degree in production is 40× higher and the query is a different algorithm at that size. Load-test traversals against production-shaped degree distributions (Module O-5), not production-shaped row counts — the row count is not the variable that matters.

---

Time-Series: The One Workload Whose Write Pattern Is Known in Advance

Metrics, sensor readings, trades, event counters. The workload has properties no other workload has, and every design choice in a time-series store falls out of them:

• Writes are append-only, and almost always at or near the current time.
• Rows are immutable once written. Updates are corrections, and rare. Deletes happen in bulk by age, never by row.
• Reads are almost always time-bounded, and recent data is read far more than old data.
• Old data is read at lower resolution. Nobody wants per-second values from eight months ago; they want hourly averages.
• Adjacent values in the same series are highly similar, which is the property that makes compression extraordinary.

Postgres can absolutely store this — and running it naively is how you find out what the specialty features are for. The naive version is one wide table with a ts column, a B-tree on (series_id, ts), and a nightly DELETE. It fails in a specific, recognisable order: the index grows past memory so writes start hitting random pages, the nightly DELETE creates millions of dead tuples that autovacuum can't keep up with (Module F-13), the table's physical size stops shrinking even as logical retention holds steady, and the dashboard query that averages a month of data scans every raw row every time someone loads the page.

Four features answer those four failures.

1. Time-based partitioning. TimescaleDB's hypertable is a Postgres table transparently split into chunks by time interval; native Postgres declarative partitioning does the same with more manual work. The wins are concrete: each chunk's indexes are small enough to stay in memory, and since writes cluster at the current time, only the newest chunk is hot — so the write path is sequential-ish rather than scattered across a huge index. Queries with a time predicate skip whole chunks at plan time.

2. Retention as a metadata operation, not a DELETE. This is the feature that most justifies the store:

A DELETE of 90 days of rows rewrites visibility information for every one of them and leaves the space for autovacuum to reclaim. Dropping a chunk unlinks files. Same intent, entirely different cost — and the difference between a retention policy that runs in 40 ms and one that is itself the cause of your monthly performance incident.

3. Columnar compression. Once a chunk is old enough that nothing writes to it, the store can rewrite it in a columnar layout: values from the same column batched together, then delta-encoded and compressed. Because consecutive readings from one sensor barely differ, and timestamps advance by a near-constant interval, the compressible redundancy is enormous. Reported ratios vary widely with the data — roughly 5× to 20× is a reasonable expectation for typical numeric metrics, better for slowly-changing values, worse for high-entropy ones. The cost is real and specific: compressed chunks are expensive or restricted to modify row-by-row, so compression is a commitment that this data is now historical. Configure it wrong — compressing chunks that still receive late-arriving data — and backfill becomes painful.

4. Continuous aggregates and downsampling. A continuous aggregate is an incrementally-refreshed materialised view over a hypertable: it stores hourly rollups and updates only the buckets touched by new data, rather than recomputing the whole view.

That last comment is the whole point: retention and resolution become independent knobs. You keep raw data as long as you need to debug, and rollups as long as you need to report — and the rollup is what your dashboards read, so a "last 30 days" panel touches 720 rows per device rather than 260,000.

The other two engines in this space make different trades worth knowing:

The cardinality warning is the one to internalise, because it is the time-series equivalent of the hot partition. A series is identified by its full tag or label set, so cardinality is the product of the distinct values of every tag. Adding user_id as a Prometheus label on a metric with 1 million users does not add a label — it adds a million series, each with its own index entry and in-memory chunk. The store falls over, and the fix is a schema change to something already being scraped. Prometheus is also explicitly not a durable long-term store: it favours availability over completeness and will drop samples rather than block, so it is the wrong home for anything you'll invoice against.

---

Each of These Is a Bet That One Access Pattern Dominates

Module F-14's discipline applies unchanged, and it's what turns this module from a shopping list into a decision. Every specialty store here is fast because it made one access pattern cheap by making the others expensive or impossible. So the question is never "is Cassandra good" — it's does one access pattern dominate this data enough that I want the others to be hard?

Three costs come attached to every answer of "yes":

1. Operational surface. A second datastore means a second thing to back up and test restores for, a second upgrade path, a second failure mode your on-call rotation must learn, a second set of client libraries and connection pools, a second capacity model, and a second line on the invoice (Module O-8). Cassandra in particular needs compaction strategy, repair scheduling, and tombstone management understood by whoever is paged at 3 am — none of which existed as concepts in your Postgres estate. The honest comparison is not "graph query 200× faster" but "graph query 200× faster, against one more system to keep alive".

2. The dual-write question, which F-14 named and which never goes away. If the authoritative record is in Postgres and a derived copy is in the specialty store, something must keep them in agreement. Writing to both from your request handler is the dual-write problem: the second write can fail after the first succeeded, and now the stores disagree with no bounded staleness and no way to tell which is right. The available answers are all the same answers as in F-14 — make the specialty store authoritative for its slice, or drive it from the database's own log via change data capture (Module P-23) with an outbox where you need transactional guarantees (Module A-3). What is not an answer is two writes and a retry.

3. Split-brain in querying. Once data lives in two shapes, some questions require both — and there is no join across them. You'll either denormalise further, run two queries and combine in application code, or discover that a plausible product request is now architecturally expensive. This cost is invisible at design time and shows up as feature velocity a year later.

Which yields a practical order of operations that F-14 would recognise:

1. Measure the query in Postgres first. With the right composite index, partitioning, and EXPLAIN ANALYZE (Module F-13), a surprising fraction of "we need Cassandra" turns out to be "we need the leading column of that index changed".
2. If it's a time-series problem, reach for the Postgres extension before a new engine. TimescaleDB gives you the four features above without giving up SQL, joins, or constraints — a strictly smaller bet.
3. Scope the specialty store to one slice, with a clear boundary and a named owner of the sync mechanism, rather than migrating a domain.
4. Write down the bet. "We are betting reads are always by (customer_id, month). If a query by region becomes important, the cost is a new table plus a full backfill." A future engineer will need that sentence.

---

Where This Shows Up in Your Stack

• PostgreSQL: it is the credible competitor to all three. Declarative partitioning or TimescaleDB covers time-series; WITH RECURSIVE covers shallow graphs; JSONB plus a good composite index covers a lot of document and wide-row work; and pg_trgm or full-text covers what people reach for Elasticsearch to do (Module P-20). Exhaust it before adding an engine — and know its actual limits: writes with fsync-on-commit land around 1K–5K TPS on one node, which is the number that legitimately sends people to a wide-column store.
• Node.js: every specialty client brings its own pool with its own sizing, and Module P-4's arithmetic now runs twice. Cassandra's driver is topology-aware and holds connections to many nodes, so the per-instance connection count is much higher than a single Postgres pool — a serverless deployment that opens both will exhaust something (Module F-10).
• Redis: frequently the actual answer to "we need a specialty store". A leaderboard is a sorted set, a recent-activity feed is a capped list, a per-minute counter is INCR with expiry — 100K+ simple ops/s from one instance, and no new durable system to operate. If the specialty access pattern is small, hot, and regenerable, Redis is a smaller bet than a new database (Module P-12).
• Prometheus / your metrics pipeline: you are already running a time-series database, and the cardinality rule above governs it. Before adding a label, multiply out the series count — this is the most common self-inflicted time-series outage in a normal stack.

---

Summary

• Each specialty store removes a feature to buy a capability — wide-column trades joins and ad-hoc queries for linear write scaling, graph trades horizontal write scaling for depth-independent traversal, time-series trades mutability for compression and time-scoped scans. When the bet is wrong you can't answer the question at all, which is worse than slow.
• Wide-column modelling runs backwards from queries. Partition key selects the node, clustering key orders rows within it, and you can only query by full partition key plus a clustering prefix. ALLOW FILTERING is an incident.
• Storing the same fact in three tables is the intended design — you've swapped read-time joins for write-time fan-out, and the cost is N writes with no cross-table transaction, so idempotent replay from one authoritative event is required.
• Two partition-key failures, both silent for months: unbounded partitions (fix with time bucketing, at the cost of multi-partition reads) and hot partitions (adding nodes doesn't help, because a partition is indivisible). This is why F-14 called the partition key the hardest decision to reverse.
• A DynamoDB GSI is an asynchronous projection, not an index. Eventually consistent with no ConsistentRead option, separately throttled — and GSI throttling propagates back into base-table writes. Cassandra's local secondary indexes fail differently: scatter-gather that worsens as the cluster grows.
• A recursive CTE works to about two hops and degrades geometrically with out-degree. Index-free adjacency makes a hop a pointer dereference whose cost is independent of graph size, which is why fraud rings, nested permissions, and path-weighted recommendations belong in a graph store — and why fixed-depth hierarchies and node aggregates do not.
• Time-series design follows from append-only, immutable, time-bounded, resolution-decaying data. Chunking keeps the hot index small; retention as a chunk DROP avoids the vacuum debt a bulk DELETE creates; columnar compression of roughly 5–20× costs you the ability to modify old chunks; continuous aggregates make retention and resolution independent knobs.
• Cardinality is the time-series hot partition. Series count is the product of tag or label values, so one high-cardinality label multiplies your series and your memory. Prometheus additionally is not a store of record — it drops data to stay available.
• Every addition costs operational surface, a dual-write question, and the inability to join across stores. Measure in Postgres first, prefer an extension to a new engine, scope the store to one slice, and write the bet down with the cost of being wrong.

The next module, CAP and PACELC, takes the distributed stores you've just met and asks the question underneath their configuration flags: when the network partitions, what does each of them give up, and — the part CAP omits and PACELC adds — what does it give up during normal operation, when there is no partition at all and latency is the thing being traded against consistency.

---