What size makes Materialized Paths impractical?

Question

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?

Definitely look into Postgres and the ltree contrib as I suggest in my answer, then. ;-) — Denis de Bernardy, Jun 10 '11 at 00:32
:D Keep an eye out for more questions, and unfortunately switching DBMS is not possible for us :/ — Hailwood, Jun 10 '11 at 00:42

score 1 · Accepted Answer · answered Jun 09 '11 at 23:54

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.)

What size makes Materialized Paths impractical?

1 Answers1