Add released_at column to catalog_resource_versions

What does this MR do and why?

In order to avoid JOIN-ing the catalog_resource_versions and releases tables, this MR denormalizes releases.released_at in catalog_resource_versions. This will improve the performance of queries in Ci::Catalog::Resource::Version, which will improve our overall ability to scale the Global Ci Catalog search feature.

This MR:

• Adds released_at column to catalog_resource_versions.
  • This column has the same data type and NOT NULL constraint as releases.released_at, with one exception: it defaults the value to 1970-01-01. This default value allows us to:
    • (i) create the column without needing to backfill right now (it will be done in a later step)
    • (ii) satisfy the new migration guideline that requires a default value to be specified for NOT NULL columns, and
    • (iii) easily distinguish non-updated columns from updated ones (this is why it wasn't just set to default = NOW()).
• Adds an index on released_at and catalog_resource_id, which will improve the queries such as this that sort a catalog resource's versions by released_at.
  • Also removes the existing index index_catalog_resource_versions_on_catalog_resource_id since it is not necessary and causes a duplicate index error in https://gitlab.com/gitlab-org/gitlab/-/jobs/5537396210.

NOTE:

• This new column will not be utilized until the syncing process and data backfill (later steps in #430117 (closed)) are implemented.

Resolves Step 1 in #430117 (closed).

Query plan

Up

main: == [advisory_lock_connection] object_id: 185520, pg_backend_pid: 75474
main: == 20231114231330 AddReleasedAtToCatalogResourceVersions: migrating ===========
main: -- add_column(:catalog_resource_versions, :released_at, :datetime_with_timezone, {:default=>"1970-01-01", :null=>false})
main:    -> 0.0021s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0854s
main: -- indexes(:catalog_resource_versions)
main:    -> 0.0022s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:catalog_resource_versions, {:algorithm=>:concurrently, :name=>"index_catalog_resource_versions_on_catalog_resource_id"})
main:    -> 0.0040s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:catalog_resource_versions, [:catalog_resource_id, :released_at], {:name=>"index_catalog_resource_versions_on_resource_id_and_released_at", :algorithm=>:concurrently})
main:    -> 0.0012s
main: -- add_index(:catalog_resource_versions, [:catalog_resource_id, :released_at], {:name=>"index_catalog_resource_versions_on_resource_id_and_released_at", :algorithm=>:concurrently})
main:    -> 0.0025s
main: == 20231114231330 AddReleasedAtToCatalogResourceVersions: migrated (0.1127s) ==

main: == [advisory_lock_connection] object_id: 185520, pg_backend_pid: 75474
ci: == [advisory_lock_connection] object_id: 185820, pg_backend_pid: 75476
ci: == 20231114231330 AddReleasedAtToCatalogResourceVersions: migrating ===========
ci: -- add_column(:catalog_resource_versions, :released_at, :datetime_with_timezone, {:default=>"1970-01-01", :null=>false})
ci:    -> 0.0016s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- indexes(:catalog_resource_versions)
ci:    -> 0.0020s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:catalog_resource_versions, {:algorithm=>:concurrently, :name=>"index_catalog_resource_versions_on_catalog_resource_id"})
ci:    -> 0.0025s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:catalog_resource_versions, [:catalog_resource_id, :released_at], {:name=>"index_catalog_resource_versions_on_resource_id_and_released_at", :algorithm=>:concurrently})
ci:    -> 0.0012s
ci: -- add_index(:catalog_resource_versions, [:catalog_resource_id, :released_at], {:name=>"index_catalog_resource_versions_on_resource_id_and_released_at", :algorithm=>:concurrently})
ci:    -> 0.0016s
ci: == 20231114231330 AddReleasedAtToCatalogResourceVersions: migrated (0.0246s) ==

ci: == [advisory_lock_connection] object_id: 185820, pg_backend_pid: 75476

Down

main: == [advisory_lock_connection] object_id: 185200, pg_backend_pid: 77200
main: == 20231114231330 AddReleasedAtToCatalogResourceVersions: reverting ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0992s
main: -- indexes(:catalog_resource_versions)
main:    -> 0.0024s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:catalog_resource_versions, {:algorithm=>:concurrently, :name=>"index_catalog_resource_versions_on_resource_id_and_released_at"})
main:    -> 0.0013s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:catalog_resource_versions, :catalog_resource_id, {:name=>"index_catalog_resource_versions_on_catalog_resource_id", :algorithm=>:concurrently})
main:    -> 0.0013s
main: -- add_index(:catalog_resource_versions, :catalog_resource_id, {:name=>"index_catalog_resource_versions_on_catalog_resource_id", :algorithm=>:concurrently})
main:    -> 0.0013s
main: -- remove_column(:catalog_resource_versions, :released_at)
main:    -> 0.0005s
main: == 20231114231330 AddReleasedAtToCatalogResourceVersions: reverted (0.1218s) ==

main: == [advisory_lock_connection] object_id: 185200, pg_backend_pid: 77200
ci: == [advisory_lock_connection] object_id: 185220, pg_backend_pid: 77630
ci: == 20231114231330 AddReleasedAtToCatalogResourceVersions: reverting ===========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0904s
ci: -- indexes(:catalog_resource_versions)
ci:    -> 0.0029s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- remove_index(:catalog_resource_versions, {:algorithm=>:concurrently, :name=>"index_catalog_resource_versions_on_resource_id_and_released_at"})
ci:    -> 0.0015s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:catalog_resource_versions, :catalog_resource_id, {:name=>"index_catalog_resource_versions_on_catalog_resource_id", :algorithm=>:concurrently})
ci:    -> 0.0013s
ci: -- add_index(:catalog_resource_versions, :catalog_resource_id, {:name=>"index_catalog_resource_versions_on_catalog_resource_id", :algorithm=>:concurrently})
ci:    -> 0.0012s
ci: -- remove_column(:catalog_resource_versions, :released_at)
ci:    -> 0.0004s
ci: == 20231114231330 AddReleasedAtToCatalogResourceVersions: reverted (0.1195s) ==

ci: == [advisory_lock_connection] object_id: 185220, pg_backend_pid: 77630

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #430117 (closed)