Beta: CachedValue search values may (and do) exceed maximum PostgreSQL B-Tree index row size

[moonrail]

NetBox version

v3.4-beta1

Python version

3.10

Steps to Reproduce

1. Have a NetBox instance with a lot of real-life data
2. Try to migrate data from NetBox 3.3.8 to 3.4-beta1

Expected Behavior

Migrations succeed

Observed Behavior

Migration extras.0083_search fails.

Log:

$ NETBOX_DELETE_LEGACY_DATA=1 netbox/manage.py migrate
netbox.denormalized: DEBUG    Registering denormalized field <class 'dcim.models.cables.CableTermination'>._device
netbox.denormalized: DEBUG    Registering denormalized field <class 'dcim.models.cables.CableTermination'>._rack
netbox.denormalized: DEBUG    Registering denormalized field <class 'dcim.models.cables.CableTermination'>._location
Operations to perform:
  Apply all migrations: admin, auth, circuits, contenttypes, dcim, django_rq, extras, ipam, sessions, social_django, taggit, tenancy, users, virtualization, wireless
Running migrations:
  Applying extras.0083_search...Reindexing 63 models.
Clearing cached values... 0 entries deleted.
Indexing models
  circuits.circuit... 153 entries cached.
  circuits.circuittermination... 234 entries cached.
  circuits.circuittype... 20 entries cached.
  circuits.provider... 17 entries cached.
  circuits.providernetwork... 2 entries cached.
  dcim.cable... 13284 entries cached.
  dcim.consoleport... 6478 entries cached.
  dcim.consoleserverport... 1378 entries cached.
  dcim.device... 39290 entries cached.
  dcim.devicebay... 422 entries cached.
  dcim.devicerole... 68 entries cached.
  dcim.devicetype... 1022 entries cached.
  dcim.frontport... 21296 entries cached.
  dcim.interface... 805260 entries cached.
  dcim.inventoryitem... None found.
  dcim.location... 469 entries cached.
  dcim.manufacturer... 144 entries cached.
  dcim.module... 40 entries cached.
  dcim.modulebay... 136 entries cached.
  dcim.moduletype... 24 entries cached.
  dcim.platform... 212 entries cached.
  dcim.powerfeed... 48 entries cached.
  dcim.poweroutlet... 464 entries cached.
  dcim.powerpanel... 3 entries cached.
  dcim.powerport... 33112 entries cached.
  dcim.rack... 7741 entries cached.
  dcim.rackreservation... 223 entries cached.
  dcim.rackrole... 33 entries cached.
  dcim.rearport... 17705 entries cached.
  dcim.region... 39 entries cached.
  dcim.site... 270 entries cached.
  dcim.sitegroup... 18 entries cached.
  dcim.virtualchassis... 347 entries cached.
  dcim.virtualdevicecontext... None found.
  ipam.aggregate... 647 entries cached.
  ipam.asn... 18 entries cached.
  ipam.fhrpgroup... None found.
  ipam.ipaddress... 159974 entries cached.
  ipam.iprange... None found.
  ipam.l2vpn... None found.
  ipam.prefix... 43320 entries cached.
  ipam.rir... 12 entries cached.
  ipam.role... 9 entries cached.
  ipam.routetarget... 129 entries cached.
  ipam.service... None found.
  ipam.servicetemplate... None found.
  ipam.vlan... 30219 entries cached.
  ipam.vlangroup... 298 entries cached.
  ipam.vrf... 7301 entries cached.
  extras.journalentry... 23594 entries cached.
  tenancy.contact... 24 entries cached.
  tenancy.contactgroup... 2 entries cached.
  tenancy.contactrole... 6 entries cached.
  tenancy.tenant... 14755 entries cached.
  tenancy.tenantgroup... 9 entries cached.
  virtualization.cluster... 257 entries cached.
  virtualization.clustergroup... 161 entries cached.
  virtualization.clustertype... 14 entries cached.
  virtualization.virtualmachine... 25878 entries cached.
  virtualization.vminterface... 13559 entries cached.
  wireless.wirelesslan... None found.
  wireless.wirelesslangroup... None found.
  wireless.wirelesslink... None found.
Completed. Total entries: 1270138
Traceback (most recent call last):
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ProgramLimitExceeded: FEHLER:  Indexzeile benötigt 122744 Bytes, Maximalgröße ist 8191


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/var/dev/netbox/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 249, in apply_migration
    with self.connection.schema_editor(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/base/schema.py", line 164, in __exit__
    self.execute(sql)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/base/schema.py", line 199, in execute
    cursor.execute(sql, params)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: FEHLER:  Indexzeile benötigt 122744 Bytes, Maximalgröße ist 8191

which translates to: ERROR: index row requires 122744 bytes, maximum size is 8191

This seems to be the issue from the perspective of the migration:

• https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/extras/migrations/0083_search.py#L12
• causes run of: https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/extras/management/commands/reindex.py
• causes run of: https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/netbox/search/backends.py#L138

As I am not a database engineer, I'm somewhat guessing my way through here, so in the case, I've missed an obvious configuration value, please have that in mind.

Django seems to use B-Tree indexes in PostgreSQL by default: https://docs.djangoproject.com/en/4.1/ref/models/indexes/#index-options

B-Tree indexes are described in PostgreSQL-docs with a size limit:

"The only limitation is that an index entry cannot exceed approximately one-third of a page (after TOAST compression, if applicable)."

From my understanding of the matter, the size is exceeded as a CachedValue.value may be too large for a B-Tree index row.

[kkthxbye-code]

I'm not sure how we would solve this properly other then truncating the value at some arbitrary length. The alternative would be switching to the actual full text search functionality in postgres (tsvector), but not sure how well that fits most of the data as it tends to be geared towards searching natural languages.

[jeremystretch]

Seems like a fairly niche scenario to have 8KB of data in a searchable field. IMO truncation is an acceptable compromise.

[jeremystretch]

By the way @moonrail thank you for the detailed analysis!