If I am expecting my table to have say, 700,000 rows,
A user can be a vanilla signup (starting a brand new tree)
or
A user can be a invited signup (starting a new branch on an existing tree).
If we are expecting these trees to get, on average, 800 nodes from the first node, Are Materialized paths practical, and at what point do they become impractical?
If we are expecting these trees to get, on average, 800 nodes from the first node, Are Materialized paths practical?
In my experience, they start to be cumbersome when sub-trees grow in the order of 10k nodes (depends on your sub-sub-trees, etc., arguably).
Note, as an aside, that if you switched to PostgreSQL, you could use the ltree contrib to manage your tree paths directly. In the latter case I've never managed to break it. (As in, managed to introduce material performance problems.)