Evaluate replacing django-mptt with CTE

[jeremystretch]

Proposed Changes

With the upcoming v3.0 release and the recent loss of maintainership for django-mptt, this is a good time to evaluate replacing our usage of MPTT for hierarchical models with an alternate solution, namely PostgreSQL common table expressions (CTE).

When working on #6087 I came across django-tree-queries, which seems like a neat, lightweight implementation of CTE for Django. We should look into using it, or at replicating its approach within NetBox.

Justification

MPTT has serves us pretty well, however it comes with some limitations, notably the need to consistently rebuild the tree with every change. This imposes some additional overhead around things like bulk updates. MPTT also requires a set of database fields to maintain the tree: tree_id, level, lft (left), and rght (right).

A CTE-based approach to conveying recursively hierarchies does not impose this requirement, as the hierarchy is built at query time rather than at write time. It remains to be seen, however, what sort of performance penalty this imposes.

[jeremystretch]

Tagging this for v3.0 because I want to at least make the evaluation prior to the beta release.

[jeremystretch]

Initial testing using django-tree-queries has been promising with regard to performance, however we'll need to devise a suitable replacement for django-mptt's add_related_count() manager method for counting related objects. Replicating the approach directly won't work, because Django's Subquery won't work with the CTE annotations (FieldError: Cannot resolve keyword 'tree_path' into field.).

I've opened feincms/django-tree-queries#21 to raise this issue and see if anyone can offer some guidance.

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Please see our contributing guide.

[jeremystretch]

I'm going to tag this to revisit for v3.2.

[pfcodes]

@jeremystretch came across this post via a google search. were there any pitfalls to switching from mptt to cte, if you went through with doing so?

[arthanson]

Author of django-tree-queries lists his reasons here: https://406.ch/writing/django-tree-queries/. He notes potential issues he doesn't like with PostgreSQL ltree, however this is probably still a viable alternative. See https://github.com/mariocesar/django-ltree and https://github.com/peopledoc/django-ltree-demo (the ltree-demo could just be pulled into the app and updated, i.e. not use any library). Potentially easier to implement the equivalent of add_related_count()...

[arthanson]

Also see #11421

[abhi1693]

There is also a repo which may be used to build our own logic. I just came across this and haven't had a chance to look in depth.

https://github.com/dimagi/django-cte

[jeremystretch]

Wrapping this into #12552.