Limit the number of active versioned pages deployments

What does this MR do and why?

Limit the number of active versioned pages deployments

Context

Gitlab Pages uses an internal api to fetch the list of available pages deployments on a root level namespace. That api returns all the projects with a Pages Deployment on a given root level namespace.

The Problem

With the new Pages Multiple Deployments feature, the number of pages deployments in a root level namespace might increate exponentially, since all the projects might have multiple deployments now (A main version and extra versions, the ones with a non-blank path_prefix). This might cause a problem on the pages internal API, since it's payload can become to large to calculate

Proposed solution (this MR)

To avoid getting a payload that's too huge, we're limiting the number of extra Pages deployments by root level namespace.

Possible questions

• Why do we have different limits on SAAS and Self-managed?
  • As before mentioned, these limits are being introduced to protect the internal API. So, even though the self-managed already manage their infrastructure, we need a limit there to avoid problems in the internal API.
• Are these limits final?
  • This is the first iteration of this solution. We have been discussing internally possible next steps on groupknowledge how to improve to make this feature less restrictive on the user.

Related to: #442947 (closed)

Changelog: added

SQL

• ::PagesDeployment.count_versioned_deployments_for

SELECT
	COUNT(*)
FROM
	"pages_deployments"
WHERE
	"pages_deployments"."project_id" IN (
		SELECT
			"projects"."id"
		FROM
			"projects"
		WHERE
			"projects"."namespace_id" = 1
	)
	AND "pages_deployments"."upload_ready" = TRUE
	AND "pages_deployments"."deleted_at" IS NULL
	AND NOT (
		(
			"pages_deployments"."path_prefix" = ''
			OR "pages_deployments"."path_prefix" IS NULL
		)
	)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27737/commands/86453

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.

Screenshots or screen recordings

changed the limit to 1 for the screenshot

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.