Update Ci catalog Version model to use denormalized `released_at` column

What does this MR do and why?

In #430117 (closed), our objective is to denormalize catalog_resources_versions.released_at so that we can reduce the need to JOIN with the releases table in Ci::Catalog::Resources::Version. This allows us to make more efficient queries that improve performance and allow us to clean up the model code.

In previous MRs, we have already denormalized released_at, established a syncing process, and backfilled the data. Now we can proceed with this final MR to complete #430117 (closed).

In this MR, we refactor the code in Ci::Catalog::Resources::Version so that it utilizes the new released_at column. In doing so, we're able to remove the need to explicitly define the keyset pagination order.

The query plans below show the resulting SQL changes. There are no significant changes to the specs because there are no behavioural or visible changes.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Query plans

1. Sorting query

Ci::Catalog::Resources::Version.order_by_released_at_asc

Before:

SELECT "catalog_resource_versions".*
FROM "catalog_resource_versions"
INNER JOIN "releases" ON "releases"."id" = "catalog_resource_versions"."release_id"
ORDER BY "releases"."released_at" ASC, "releases"."id" ASC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25861/commands/81528

After:

SELECT "catalog_resource_versions".*
FROM "catalog_resource_versions"
ORDER BY "catalog_resource_versions"."released_at" ASC

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25861/commands/81529

2. latest_for_catalog_resources query

Ci::Catalog::Resources::Version.latest_for_catalog_resources(catalog_resources)

Before:

SELECT "catalog_resource_versions".*
FROM (
  VALUES (7),(6),(5),(2),(1),(8),(9),(10)
) catalog_resources(id)
INNER JOIN LATERAL (
  SELECT "catalog_resource_versions".*
  FROM "catalog_resource_versions"
  INNER JOIN releases AS rel ON rel.id = catalog_resource_versions.release_id
  WHERE "catalog_resources"."id" = "catalog_resource_versions"."catalog_resource_id"
  ORDER BY rel.released_at DESC
  LIMIT 1
) catalog_resource_versions ON TRUE;

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25861/commands/81531

After:

SELECT "catalog_resource_versions".*
FROM (
  VALUES (7),(6),(5),(2),(1),(8),(9),(10)
) catalog_resources(id)
INNER JOIN LATERAL (
  SELECT "catalog_resource_versions".*
  FROM "catalog_resource_versions"
  WHERE "catalog_resources"."id" = "catalog_resource_versions"."catalog_resource_id"
  ORDER BY "catalog_resource_versions"."released_at" DESC
  LIMIT 1
) catalog_resource_versions ON TRUE;

Query plan link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25861/commands/81532

changed milestone to %16.9

added Category:Pipeline Composition backend feature flagskipped grouppipeline authoring maintenancerefactor typemaintenance labels

assigned to @lma-git

added devopsverify sectionci labels

added databasereview pending label

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
backend	@mcavoj (UTC+1, 9 hours ahead of author)	@carlad-gl (UTC+1, 9 hours ahead of author)
~"Verify"	Reviewer review is optional for ~"Verify"	@mbobin (UTC+2, 10 hours ahead of author)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 387ec5b4

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 31     | 0      | 0       | 0     | 31    | ✅     |
| Data Stores | 2      | 0      | 0       | 0     | 2     | ✅     |
| Create      | 8      | 0      | 3       | 0     | 11    | ✅     |
| Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
| Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
| Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
| Plan        | 4      | 0      | 0       | 0     | 4     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 52     | 0      | 4       | 0     | 56    | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-package-and-test: test report for 387ec5b4

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Verify      | 138    | 0      | 27      | 0     | 165   | ✅     |
| Create      | 150    | 0      | 19      | 2     | 169   | ✅     |
| Govern      | 6      | 0      | 0       | 0     | 6     | ✅     |
| Plan        | 8      | 0      | 0       | 0     | 8     | ✅     |
| Package     | 0      | 0      | 2       | 0     | 2     | ➖     |
| Data Stores | 4      | 0      | 0       | 0     | 4     | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 314    | 0      | 48      | 2     | 362   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

removed databasereview pending label

changed the description

mentioned in issue #430117 (closed)

added databasereview pending label

Hi @morefice! Could you please do the first backend and database review here? If it looks okay, please send it to a DB maintainer, and @lauraX for backend & ~"Verify" maintainer review. Thank you!

requested review from @morefice

approved this merge request

@morefice, thanks for approving this merge request.

This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break master, a new pipeline will be started shortly.

Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.

added pipeline:mr-approved label

removed review request for @morefice

requested review from @lauraX