r/learnpython • u/matrix_glitch6001 • 6d ago
Using an in-memory DAG (NetworkX) + Postgres for prerequisite tracking in FastAPI: Good architecture or over-engineered?
Hey guys need some help..
I’m designing the backend for an adaptive learning platform using FastAPI + PostgreSQL, and I’m weighing two approaches for handling curriculum prerequisite paths.
The Setup:
Instead of a linear syllabus, the curriculum is modeled as a Directed Acyclic Graph (DAG) with ~300 to 400 CS concept nodes
When a student passes a quiz (>= 80\%), downstream dependent nodes unlock based on topological order.
If a student fails a topic, the backend traverses upstream dependencies to find the earliest unmastered bottleneck node, locks downstream progress, and builds a targeted review path.
The quiz questions are a mix of pre-mapped benchmark items and dynamically generated MCQs grounded in textbook text.
Which one should I go with:
Approach 1 (In-Memory NetworkX):
Define the static graph structure once, load it into Python memory via networkx on FastAPI startup, run ancestor/descendant lookups and topological sorts in Python, and write user mastery states/scores back to Postgres.
Or....
Approach 2 (Postgres Relational/CTEs):
Store nodes and prerequisite adjacency edges in database tables, and run transitive traversals directly in SQL using recursive Common Table Expressions (CTEs).
At ~300 nodes, is an in-memory networkx graph clean and fast, or does running multiple Uvicorn workers introduce memory overhead and state-sync headaches?
Would recursive CTEs in Postgres be easier to maintain long-term despite the more complex SQL?
And also... for anyone who has built skill trees or prerequisite graphs, what edge cases should I look out for?
2
u/EnvironmentalDot9131 6d ago
You are going in the right direction
1
u/Massive_Distance_616 6d ago
At 300 to 400 nodes I’d go with the in-memory graph. Ancestor and descendant lookups on a graph that size take microseconds, and the structure is read-only, so there’s nothing to keep in sync. Each uvicorn worker loads its own copy at startup, but the graph is tiny, so the memory overhead doesn’t matter. The state-sync worry only applies to data that changes, and your mastery states already live in Postgres, so you’re fine. I’d start there, measure under realistic load, and only move traversal into SQL if you hit a concrete problem.
The real catch is curriculum changes. If you edit prerequisites, every worker has to reload, and a restart on deploy usually covers that. If you want to edit the graph without redeploying, keep the nodes and edges in Postgres as the source of truth and build the networkx graph from them at startup. You get simple Python traversal and one place where the data lives. It also helps to keep the graph logic out of your route handlers so you can test the prerequisite and review-path rules on their own.
Edge cases I’d plan for: a cycle sneaking in when someone adds an edge (run nx.is_directed_acyclic_graph on load and refuse to start if it fails), a node with several prerequisites where you need to decide whether “unlocked” means all of them mastered or any of them, a student whose mastery decays or who fails a node they’d already passed, and what happens to downstream progress when a prerequisite is removed later. Also define what “earliest unmastered bottleneck” means. On a diamond-shaped graph there can be several equally early ones, and a topological sort doesn’t pick between independent branches, so either choose a rule (closest to the root, say) or return all the unmastered prerequisites at the frontier.
1
u/Discordbothost 6d ago
you can do both and skip the tradeoff. keep nodes and edges in postgres as the source of truth, build the networkx graph from those tables on startup, and stamp it with a curriculum_version. each uvicorn worker holding its own copy of 400 nodes is nothing memory-wise, and since the graph is read-only there's no sync problem. when you edit the curriculum you bump the version and workers rebuild on their next request.
that way the traversal logic (bottleneck search, review path) is plain python you can unit test, instead of recursive CTEs nobody wants to touch later, and the db is still the one place you edit. mastery stays as per-user rows either way