Improve performance of object type custom field lookups for API responses

[kkthxbye-code]

NetBox version

v3.5.3

Feature type

Data model extension

Proposed functionality

Currently when listing objects (let's use device as an example) with custom fields of type Object or Multiple Objects, if the field is populated a query is made for every single custom field for every single object type custom field.

The lookup is done here:

netbox/netbox/extras/models/customfields.py

Lines 348 to 353 in cdce500

	if self.type == CustomFieldTypeChoices.TYPE_OBJECT:
	model = self.object_type.model_class()
	return model.objects.filter(pk=value).first()
	if self.type == CustomFieldTypeChoices.TYPE_MULTIOBJECT:
	model = self.object_type.model_class()
	return model.objects.filter(pk__in=value)

The reason we do this, is because object type custom fields are implemented like GenericForeignKeys, with the difference being that the type and id of the custom field is stored in the JSONB custom_field_data column.

This is very ineffective and slows API responses to a crawl for larger pagination sizes or with multiple custom fields.

As an example imagine the device model having two custom fields, cf_test1 and cf_test2 both being object type custom fields, both pointing towards the Interface model. Fetching 100 devices in the API results in 422 queries in my test instance. 200 of the queries are for getting the interface matching the ID, and 200 further queries are for getting the device of the interface as the interface serializer contains the device.

We had a quick talk about this on slack, and @jeremystretch suggested that it might be possible to replicate the prefetch logic that exists for generic foreign keys. I don't have enough in-depth knowledge to suggest if this is a viable approach.

An alternative workaround to the immediate problem would be to allow excluding the custom_field field in the API, just as we allow excluding config_context also for performance issues. An even nicer solution would be to allow the user to define exactly which fields are included in the response, however I'm not sure how feasible this is.

Another solution yet again would be to allow the user to disable the deserialization of custom fields in the API response.

Use case

We currently have 6 object type custom fields on the device model. We run a script from another system to get the name and status of all devices of a specific type (1000+ devices). Getting a single page in the API of 1000 devices takes upwards of 90 seconds. The same request in a test environment with the same data, but sans custom fields takes seconds. This is not optimal when the only two fields needed is name and status.

Database changes

Probably not, but might depend on solution chosen.

External dependencies

None

[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. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

[github-actions]

This issue has been automatically closed due to lack of activity. In an effort to reduce noise, please do not comment any further. Note that the core maintainers may elect to reopen this issue at a later date if deemed necessary.