Add an option to disable the natural ordering sort #2250

New Issue

adam · 2025-12-29T17:24:06+01:00

adam commented

2025-12-29 17:24:06 +01:00

Originally created by @aruhier on GitHub (Dec 27, 2018).

Environment

Python version: 3.7
NetBox version: 2.5.2

Proposed Functionality

Adding an option to disable the natural ordering sort. It means adding a variable in the settings.py file, and changing the different DCIM models to no override the objects attribute for the NaturalOrderingManager if the option NATURAL_ORDERING is False.

Use Case

In my database, I have more than 100K devices, and more than 4K racks. The listing views of these 2 object types were slower as we added more objects in the database. I searched the cause by using the django-debug-toolbar, and saw that 2s were spent in one query. In the detail, I saw that the sort was taking more than 90% of the query time. Disabling the natural ordering sort reduced the query time to 0.1s.

Natural ordering sort is great, but it seems to not really scale for some usage. Adding an option to disable it (but letting it enabled by default) seems to be a good option.

I also did not find a better way to achieve a natural sort than what you already did, without using a plugin to PostgreSQL.

Database Changes

None

External Dependencies

None

Thanks!

Originally created by @aruhier on GitHub (Dec 27, 2018). ### Environment * Python version: 3.7 * NetBox version: 2.5.2 ### Proposed Functionality Adding an option to disable the natural ordering sort. It means adding a variable in the `settings.py` file, and changing the different DCIM models to no override the `objects` attribute for the `NaturalOrderingManager` if the option `NATURAL_ORDERING` is `False`. ### Use Case In my database, I have more than 100K devices, and more than 4K racks. The listing views of these 2 object types were slower as we added more objects in the database. I searched the cause by using the django-debug-toolbar, and saw that 2s were spent in one query. In the detail, I saw that the sort was taking more than 90% of the query time. Disabling the natural ordering sort reduced the query time to 0.1s. Natural ordering sort is great, but it seems to not really scale for some usage. Adding an option to disable it (but letting it enabled by default) seems to be a good option. I also did not find a better way to achieve a natural sort than what you already did, without using a plugin to PostgreSQL. ### Database Changes None ### External Dependencies None Thanks!

adam closed this issue

2025-12-29 17:24:06 +01:00

adam commented

2025-12-29 17:24:07 +01:00

@jeremystretch commented on GitHub (Dec 28, 2018):

The current implementation employs regular expressions to parse object names into orderable integers at read time. For example, router2 becomes ('router', 2) and router10 becomes ('router', 10), so that router2 comes before router10. This imposes a fairly high overhead as every object matching a query must be sorted before we can select the first n objects. The foremost offender in this regard is interfaces, but it's not usually a problem as queries are typically limited to interfaces belonging to a particular device or virtual machine.

This approach was chosen because it affords the most flexibility: The regular expressions can be modified in new releases without modifying the data or schema. However, as the logic matures and confidence increases that no further changes to the rule set will be needed, we will transition to a set of hard-coded indexes written whenever an object is saved. Ordering will be done using these indexes directly, without the need for regex processing at runtime. This should result in much faster queries. At this stage we're still identifying places where natural ordering should be implemented or improved, and ensuring that we'll be able to cover all reasonable use cases.

@jeremystretch commented on GitHub (Dec 28, 2018): The current implementation employs regular expressions to parse object names into orderable integers at read time. For example, `router2` becomes `('router', 2)` and `router10` becomes `('router', 10)`, so that `router2` comes before `router10`. This imposes a fairly high overhead as every object matching a query must be sorted before we can select the first _n_ objects. The foremost offender in this regard is interfaces, but it's not usually a problem as queries are typically limited to interfaces belonging to a particular device or virtual machine. This approach was chosen because it affords the most flexibility: The regular expressions can be modified in new releases without modifying the data or schema. However, as the logic matures and confidence increases that no further changes to the rule set will be needed, we will transition to a set of hard-coded indexes written whenever an object is saved. Ordering will be done using these indexes directly, without the need for regex processing at runtime. This should result in _much_ faster queries. At this stage we're still identifying places where natural ordering should be implemented or improved, and ensuring that we'll be able to cover all reasonable use cases.

adam commented

2025-12-29 17:24:07 +01:00

@aruhier commented on GitHub (Dec 28, 2018):

Thanks a lot for your answer.

As you bring the argument about flexibility, regarding the really low quantity of code in my PR, I don't get why you closed this issue. I can assure you that only keeping the natural sort on devices objects, in my database storing 150K devices, the sort is really slow (2s to sort instead of 150ms). Restricting the natural sort on what makes sense is of course a great idea, but maybe some users are in my case, where even with that, this ordering does not scale for them, and prefer to have the flexibility of disabling it.

Edit: And the option could be deprecated in a future release, reducing the performance impact until you move to natural ordering by indexes.

@aruhier commented on GitHub (Dec 28, 2018): Thanks a lot for your answer. As you bring the argument about flexibility, regarding the really low quantity of code in my PR, I don't get why you closed this issue. I can assure you that only keeping the natural sort on devices objects, in my database storing 150K devices, the sort is really slow (2s to sort instead of 150ms). Restricting the natural sort on what makes sense is of course a great idea, but maybe some users are in my case, where even with that, this ordering does not scale for them, and prefer to have the flexibility of disabling it. Edit: And the option could be deprecated in a future release, reducing the performance impact until you move to natural ordering by indexes.

Sign in to join this conversation.

Branches Tags

main

update-changelog-comments-docs

feature-removal-issue-type

20911-dropdown

20239-plugin-menu-classes-mutable-state

21097-graphql-id-lookups

feature

fix_module_substitution

20923-dcim-templates

20044-elevation-stuck-lightmode

feature-ip-prefix-link

v4.5-beta1-release

20068-import-moduletype-attrs

20766-fix-german-translation-code-literals

20378-del-script

7604-filter-modifiers-v3

circuit-swap

12318-case-insensitive-uniqueness

20637-improve-device-q-filter

20660-script-load

19724-graphql

20614-update-ruff

14884-script

02496-max-page

19720-macaddress-interface-generic-relation

19408-circuit-terminations-export-templates

20203-openapi-check

fix-19669-api-image-download

7604-filter-modifiers

19275-fixes-interface-bulk-edit

fix-17794-get_field_value_return_list

11507-show-aggregate-and-rir-on-api

9583-add_column_specific_search_field_to_tables

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/netbox#2250