> Is there some other pattern that emulates WITH RECURSIVE
You could use an association matrix.
Or cached paths, but that has a list of disadvantages that make me consider it an anti-pattern these days (I maintain a legacy system that uses this model a lot).
> without blowing up the cost of writes?
It is a long time since I went over the relative performance of common operations on these structures, so I'll not try compare them (some research for you, dear reader!).
The performance of updates to an association matrix depend largely on the depth of the forest: when adding a new node you insert one row per level it is away from its trees root. Removing or moving a leaf node is the same amount of deletes or updates respectively. Moving a branch node can be pretty expensive as you have to make changes to the matrix for all the nodes under it.
You could use an association matrix.
Or cached paths, but that has a list of disadvantages that make me consider it an anti-pattern these days (I maintain a legacy system that uses this model a lot).
> without blowing up the cost of writes?
It is a long time since I went over the relative performance of common operations on these structures, so I'll not try compare them (some research for you, dear reader!).
The performance of updates to an association matrix depend largely on the depth of the forest: when adding a new node you insert one row per level it is away from its trees root. Removing or moving a leaf node is the same amount of deletes or updates respectively. Moving a branch node can be pretty expensive as you have to make changes to the matrix for all the nodes under it.