Skip to content

Add `archived` and `traversal_ids` columns to `sbom_occurrences`

What does this MR do and why?

These columns will contain project.archived and namespaces.traversal_ids. Denormalizing these values will allow us to improve query performance by removing a join on projects and a subquery for all the namespaces under a group.

Relates to: #437635 (closed)

Migrations

$ scripts/db_tasks db:migrate:down VERSION=20240129175101
Running: `bundle exec rake db:migrate:down:main VERSION=20240129175101`
main: == [advisory_lock_connection] object_id: 108400, pg_backend_pid: 124
main: == 20240129175101 AddArchivedAndTraversalIdsColumnsToSbomOccurrences: reverting 
main: -- remove_column(:sbom_occurrences, :traversal_ids, "bigint[]")
main:    -> 0.0020s
main: -- remove_column(:sbom_occurrences, :archived, :boolean)
main:    -> 0.0009s
main: == 20240129175101 AddArchivedAndTraversalIdsColumnsToSbomOccurrences: reverted (0.0145s) 
main: == [advisory_lock_connection] object_id: 108400, pg_backend_pid: 124
$ scripts/db_tasks db:migrate
Running: `bundle exec rake db:migrate:main`
main: == [advisory_lock_connection] object_id: 108280, pg_backend_pid: 157
main: == 20240129175101 AddArchivedAndTraversalIdsColumnsToSbomOccurrences: migrating 
main: -- add_column(:sbom_occurrences, :archived, :boolean)
main:    -> 0.0027s
main: -- add_column(:sbom_occurrences, :traversal_ids, "bigint[]")
main:    -> 0.0013s
main: == 20240129175101 AddArchivedAndTraversalIdsColumnsToSbomOccurrences: migrated (0.0133s) 
main: == [advisory_lock_connection] object_id: 108280, pg_backend_pid: 157
Edited by Brian Williams

Merge request reports