starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

Evaluate replacing django-mptt with CTE #4991

New Issue
Closed
opened 2025-12-29 19:22:57 +01:00 by adam · 9 comments
adam commented 2025-12-29 19:22:57 +01:00
Owner
Copy Link

Originally created by @jeremystretch on GitHub (Jun 10, 2021).

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.

Originally created by @jeremystretch on GitHub (Jun 10, 2021). ### 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)](https://www.postgresql.org/docs/current/queries-with.html). When working on #6087 I came across [django-tree-queries](https://github.com/matthiask/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.
adam added the status: acceptedtype: housekeeping labels 2025-12-29 19:22:57 +01:00
adam closed this issue 2025-12-29 19:22:57 +01:00
adam commented 2025-12-29 19:22:57 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jun 11, 2021):

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

@jeremystretch commented on GitHub (Jun 11, 2021): Tagging this for v3.0 because I want to at least make the evaluation prior to the beta release.
adam commented 2025-12-29 19:22:58 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jun 14, 2021):

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 matthiask/django-tree-queries#21 to raise this issue and see if anyone can offer some guidance.

@jeremystretch commented on GitHub (Jun 14, 2021): 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 matthiask/django-tree-queries#21 to raise this issue and see if anyone can offer some guidance.
adam commented 2025-12-29 19:22:58 +01:00
Author
Owner
Copy Link

@github-actions[bot] commented on GitHub (Oct 11, 2021):

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.

@github-actions[bot] commented on GitHub (Oct 11, 2021): 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](https://github.com/netbox-community/netbox/blob/develop/CONTRIBUTING.md).
adam commented 2025-12-29 19:22:58 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Oct 11, 2021):

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

@jeremystretch commented on GitHub (Oct 11, 2021): I'm going to tag this to revisit for v3.2.
adam commented 2025-12-29 19:22:59 +01:00
Author
Owner
Copy Link

@pfcodes commented on GitHub (Nov 30, 2022):

@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?

@pfcodes commented on GitHub (Nov 30, 2022): @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?
adam commented 2025-12-29 19:22:59 +01:00
Author
Owner
Copy Link

@arthanson commented on GitHub (Jan 5, 2023):

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 commented on GitHub (Jan 5, 2023): 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()...
adam commented 2025-12-29 19:22:59 +01:00
Author
Owner
Copy Link

@arthanson commented on GitHub (Jan 6, 2023):

Also see #11421

@arthanson commented on GitHub (Jan 6, 2023): Also see #11421
adam commented 2025-12-29 19:22:59 +01:00
Author
Owner
Copy Link

@abhi1693 commented on GitHub (Apr 24, 2023):

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

@abhi1693 commented on GitHub (Apr 24, 2023): 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
adam commented 2025-12-29 19:22:59 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (May 10, 2023):

Wrapping this into #12552.

@jeremystretch commented on GitHub (May 10, 2023): Wrapping this into #12552.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label status: accepted type: housekeeping
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#4991
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?