Materialised paths vs. recursive CTEs for comment trees

We store a zero-padded path on each post so a whole thread sorts in tree order with one indexed query. The alternative is a recursive CTE at read time.

Where does each approach break down?

Paths win on read: one index scan, no recursion. The cost is that moving a subtree means rewriting every descendant.

How often do you actually move a subtree though?

CTEs keep writes trivial and handle unbounded depth without a cap.

True, but the query plan degrades badly on hot threads.