Remove packages subquery when fetching package files

What does this MR do and why?

Remove packages subquery when fetching package files

Sometime ago the project_id column with the NOT NULL constraint was added to packages_package_files table. We can leverage it and not rely on the packages association.

Changelog: performance

References

Improve cleanup policy execution service (#570760 - closed)

Screenshots or screen recordings

No.

Database analysis

postgres.ai setup

-- Step 1: Select 1000 random packages that don't already have project_id = 17012483
WITH random_packages AS (
    SELECT id 
    FROM packages_packages 
    WHERE status = 0 
      AND project_id != 17012483  -- Exclude packages already in this project
    ORDER BY RANDOM() 
    LIMIT 1000
),
-- Step 2: Update these packages
updated_packages AS (
    UPDATE packages_packages 
    SET project_id = 17012483 
    WHERE id IN (SELECT id FROM random_packages)
    RETURNING id
),
-- Step 3: Delete any existing files for these packages (clean slate)
deleted_files AS (
    DELETE FROM packages_package_files 
    WHERE package_id IN (SELECT id FROM updated_packages)
)
-- Step 4: Insert exactly 3 files per package with matching project_id
INSERT INTO packages_package_files (created_at, updated_at, status, project_id, package_id, file_name, file)
SELECT NOW(),
       NOW(),
       0,  -- status = 0
       17012483,  -- matching project_id
       up.id,
       'bananas-2.3.7.jar',
       'bananas-2.3.7.jar'
FROM updated_packages up
CROSS JOIN generate_series(1, 3);

Before

SELECT "packages_package_files"."package_id",
       "packages_package_files"."file_name"
FROM "packages_package_files"
WHERE "packages_package_files"."status" = 0
  AND "packages_package_files"."package_id" IN
    (SELECT "packages_packages"."id"
     FROM "packages_packages"
     WHERE "packages_packages"."project_id" = XXX
       AND "packages_packages"."status" IN (0,
                                            1,
                                            5))
  AND "packages_package_files"."id" >= XXX
GROUP BY "packages_package_files"."package_id",
         "packages_package_files"."file_name"
HAVING (COUNT(*) > 1);

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43519/commands/132887

After

SELECT "packages_package_files"."package_id",
       "packages_package_files"."file_name"
FROM "packages_package_files"
WHERE "packages_package_files"."status" = 0
  AND "packages_package_files"."project_id" = XXX
  AND "packages_package_files"."id" >= XXX
GROUP BY "packages_package_files"."package_id",
         "packages_package_files"."file_name"
HAVING (COUNT(*) > 1);

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43519/commands/132890

How to set up and validate locally

1. Set cleanup policy for the project to keep only 1 duplicate package file or modify existing one.

   project = Project.first
   
   FactoryBot.create(:packages_cleanup_policy, project: project, keep_n_duplicated_package_files: '1')

2. Create a new package with several duplicate files

   def fixture_file_upload(*args, **kwargs)
     Rack::Test::UploadedFile.new(*args, **kwargs)
   end
   
   project = Project.first
   
   package = FactoryBot.create(:generic_package, project: project)
   
   # Create a few more duplicate files
   3.times do
     FactoryBot.create(:package_file, package: package, file_name: 'my-package.txt')
   end
   
   # Verify how many files were created
   package.reload.package_files.installable.where(file_name: 'my-package.txt') # should return 3

3. Call on the service and verify package files.

   Packages::Cleanup::ExecutePolicyService.new(project.packages_cleanup_policy).execute
   
   # Verify how many installable files the package has
   package.reload.package_files.installable.where(file_name: 'my-package.txt') # should return 1
   
   # Verify how many files were marked for destruction
   package.reload.package_files.pending_destruction # should return 2

MR acceptance checklist

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

Related to #570760 (closed)