Handle updates to denormalized data via PostgreSQL triggers

[jeremystretch]

NetBox Version

v4.5.2

Python Version

3.12

Area(s) of Concern

• User Interface
• REST API
• GraphQL API
• Python ORM
• Other

Details

There are several instances throughout NetBox where we store denormalized data from related objects locally performance reasons. For example, the region and site group for the site to which a prefix is assigned are stored locally in the ipam_prefix table to enable efficient filtering. These stored values must be updated automatically in response to changes on the related object.

Currently, this is handled in Python via the register and the update_denormalized_fields() signal handler. When the post_save signal fires for an object, any registered denormalized fields are updated in a synchronous (blocking) operation.

We could move this logic from the application code to a series of PostgreSQL triggers which live in the database itself. This would remove the dependence on application-level signal handling and likely yield better performance in general. This approach comprises two components:

• PostgreSQL functions to update database records
• PostgreSQL triggers to execute the function is response to changes

While it's feasible to create functions and triggers using raw SQL in migrations, we should consider using a library such as django-pgtrigger to simplify their management.