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