Comment 5 for bug 1427885

Regarding nested sets, there is one problem - it's not just slower on write, it gets progressively slower the more nodes in the tree since in the usual implementation you need to update the left/right values for every single set in the tree... which potentially amounts to every row in the table. There are some options to use fractional numbers rather than integers but this isn't perfect either.

One case we saw on one of our customers is a hierarchical check:

SELECT SUM(aff.size) FROM "artefact" a JOIN "artefact_file_files" aff ON aff.artefact = a.id WHERE a.path LIKE '%/12345/%' LIMIT 2

Without heading to nested sets, there might be a way to minimise this particular pain point - if artefact 1 has a path of /2/3/1/, a simple two column table could be created and kept up to date when artefact paths change, that records (1, 2), (1, 3) and (1, 1) - meaning that querying that aspect of the hierarchy comes quite a bit cheaper as you can say 'which artefacts are in the same hierarchy'. It's not a complete replacement but for just purely simple 'is in the same hierarchy as' checks, it might be a cheap win.