Message Queues & Graph-Shaped Data
You'll learn to
- -Design a schema-backed message queue with retry counts and a dead-letter table for permanently failed messages
- -Model graph-shaped data (nodes and edges) in a relational schema, and know when a recursive query is the right tool
This chapter covers two schema shapes that show up constantly in distributed systems: a durable message queue backed by a table (rather than a purely in-memory queue, which loses everything on a crash), and a graph structure represented relationally - both genuinely different problems from anything else in this module.
A Schema-Backed Message Queue
A durable queue needs to track a message's processing status, how many times delivery has been attempted, and - critically - what happens after repeated failures. A message that fails processing 50 times in a row should not retry forever; it should move to a dead-letter table for manual investigation, preventing one poison message from consuming resources indefinitely.
CREATE TABLE message_queue (
id INTEGER PRIMARY KEY,
payload TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
attempt_count INTEGER NOT NULL DEFAULT 0,
max_attempts INTEGER NOT NULL DEFAULT 5,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
next_retry_at TIMESTAMP
);
CREATE TABLE dead_letter_queue ( -- messages that exhausted all retries
id INTEGER PRIMARY KEY,
original_message_id INTEGER NOT NULL,
payload TEXT NOT NULL,
failure_reason TEXT NOT NULL,
moved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- application logic: on failure, increment attempt_count; if it now
-- exceeds max_attempts, move the row into dead_letter_queue instead of retrying againnext_retry_at (rather than retrying immediately on every failure) supports exponential backoff - each failed attempt schedules the next retry further in the future, avoiding hammering a downstream system that is already struggling.
Graph-Shaped Data: Nodes and Edges
A wormhole navigation network - waypoints connected by traversable routes, each with a cost - is a graph, and it maps onto two simple tables: nodes and edges, exactly the same underlying shape as the self-referencing friend graph from the Intermediate Modeling module, generalized to carry edge weights.
CREATE TABLE waypoints (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE wormhole_routes ( -- edges, with a weight
from_waypoint_id INTEGER NOT NULL REFERENCES waypoints(id),
to_waypoint_id INTEGER NOT NULL REFERENCES waypoints(id),
fuel_cost DECIMAL(8, 2) NOT NULL,
PRIMARY KEY (from_waypoint_id, to_waypoint_id)
);When a Recursive Query Is (and Is Not) the Right Tool
"Is there any path from waypoint A to waypoint B" or "list every waypoint reachable within 3 hops" are graph-traversal questions a recursive CTE can answer directly against this structure - the same recursive technique from the hierarchical-data chapter, generalized from a tree (each node has one parent) to a general graph (each node can connect to many others).
WITH RECURSIVE reachable AS (
SELECT to_waypoint_id AS waypoint_id, fuel_cost AS total_cost,
'/' || from_waypoint_id || '/' || to_waypoint_id || '/' AS visited_path
FROM wormhole_routes WHERE from_waypoint_id = :start_id
UNION ALL
SELECT r.to_waypoint_id, reachable.total_cost + r.fuel_cost,
reachable.visited_path || r.to_waypoint_id || '/'
FROM wormhole_routes r
JOIN reachable ON r.from_waypoint_id = reachable.waypoint_id
WHERE reachable.visited_path NOT LIKE '%/' || r.to_waypoint_id || '/%' -- stop before re-entering a visited waypoint
)
SELECT DISTINCT waypoint_id, MIN(total_cost) FROM reachable GROUP BY waypoint_id;That visited_path column is not optional decoration - this chapter just generalized the recursive technique from a tree (one parent per node, no cycles possible) to a general graph, and a wormhole network can absolutely have a cycle (A -> B -> C -> A). Without a guard that refuses to re-enter a waypoint already in the current path, this exact query never terminates: UNION ALL does not deduplicate, so each lap around the cycle produces new rows with an ever-growing total_cost, forever.
The honest caveat worth naming: even with the cycle guard, MIN(total_cost) here only reflects paths this query actually explored, not a provably-optimal shortest path the way Dijkstra's algorithm guarantees - for genuinely complex graph algorithms (true shortest-path at large scale, dense graphs), a recursive SQL query becomes impractical, and a dedicated graph database or an in-memory graph algorithm run outside the database is the more appropriate tool. A relational schema for a graph is a solid choice for moderate-sized graphs and reachability-style queries, not a universal graph-algorithm engine.
Naming that boundary - "a cycle-guarded recursive CTE handles reachability and simple path queries well; true shortest-path at scale would push me toward Dijkstra's algorithm or a dedicated graph database" - shows the same honest-scoping instinct as the LLD case studies in Phase 1.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design the TARS Message Protocol and Wormhole Navigation schemas in the LLD Lab's Interstellar act.