Skip to content

Fix an N+1 situation in the PyPI registry

David Fernandez requested to merge 462379-remove-n-plus-one into master

🔭 Context

In GET /api/:version/projects/:id/packages/pypi/si... (#462379), we were made aware of a PyPI registry endpoint doing too many SQL queries.

The endpoint in question is the metadata endpoint. Basically, it will return the state of the registry to package manager clients.

In this case, the state is the list of all available packages and all their versions.

Upon closer inspection, we quickly observed an n+1 situation.

⚙ What does this MR do and why?

  • Preload the metadata association of PyPI packages in the simple index presenter.
  • Update the related spec.

🏁 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

🤷

⚗ How to set up and validate locally

  1. Create a project.
  2. Upload a bunch of PyPI packages. https://gitlab.com/10io/gl_pru can help here.
  3. Access curl "http://<username>:<pat>@gdk.test:8000/api/v4/projects/<project_id>/packages/pypi/simple"

1️⃣ on master 

Packages::Package Load (0.4ms)  SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 317 AND "packages_packages"."status" IN (0, 1) AND "packages_packages"."package_type" = 5 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" != 4 ORDER BY "packages_packages"."id" ASC LIMIT 1 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/models/concerns/each_batch.rb:62:in `each_batch'*/
  ↳ app/models/concerns/each_batch.rb:62:in `each_batch'
  Packages::Package Load (0.2ms)  SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 317 AND "packages_packages"."status" IN (0, 1) AND "packages_packages"."package_type" = 5 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" != 4 AND "packages_packages"."id" >= 10250 ORDER BY "packages_packages"."id" ASC LIMIT 1 OFFSET 1000 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
  ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
  Packages::Package Load (0.2ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 317 AND "packages_packages"."status" IN (0, 1) AND "packages_packages"."package_type" = 5 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" != 4 AND "packages_packages"."id" >= 10250 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:14:in `block in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:14:in `block in links'
  Packages::Pypi::Metadatum Load (0.2ms)  SELECT "packages_pypi_metadata".* FROM "packages_pypi_metadata" WHERE "packages_pypi_metadata"."package_id" = 10250 LIMIT 1 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'
  Packages::Pypi::Metadatum Load (0.1ms)  SELECT "packages_pypi_metadata".* FROM "packages_pypi_metadata" WHERE "packages_pypi_metadata"."package_id" = 10251 LIMIT 1 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'
  Packages::Pypi::Metadatum Load (0.1ms)  SELECT "packages_pypi_metadata".* FROM "packages_pypi_metadata" WHERE "packages_pypi_metadata"."package_id" = 10252 LIMIT 1 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'
  Packages::Pypi::Metadatum Load (0.1ms)  SELECT "packages_pypi_metadata".* FROM "packages_pypi_metadata" WHERE "packages_pypi_metadata"."package_id" = 10253 LIMIT 1 /*application:web,correlation_id:01HY3BM5Z1A1DZP91H16QBS2SA,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:17:in `block (2 levels) in links'

😱

2️⃣ With this MR

Packages::Package Load (0.3ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 317 AND "packages_packages"."status" IN (0, 1) AND "packages_packages"."package_type" = 5 AND "packages_packages"."version" IS NOT NULL AND "packages_packages"."status" != 4 AND "packages_packages"."id" >= 10250 /*application:web,correlation_id:01HY3BTQGVH4PDR1R3JM8082C5,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:16:in `block in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:16:in `block in links'
  Packages::Pypi::Metadatum Load (0.2ms)  SELECT "packages_pypi_metadata".* FROM "packages_pypi_metadata" WHERE "packages_pypi_metadata"."package_id" IN (10250, 10251, 10252, 10253, 10254, 10255, 10256, 10257, 10258, 10259, 10260, 10261, 10262, 10263, 10264, 10265, 10266, 10267, 10268, 10269, 10270, 10271, 10272, 10273, 10274, 10275, 10276, 10277, 10278, 10279, 10280) /*application:web,correlation_id:01HY3BTQGVH4PDR1R3JM8082C5,endpoint_id:GET /api/:version/projects/:id/packages/pypi/simple,db_config_name:main,line:/app/presenters/packages/pypi/simple_index_presenter.rb:16:in `block in links'*/
  ↳ app/presenters/packages/pypi/simple_index_presenter.rb:16:in `block in links'

😻

💾 Database queries

Edited by David Fernandez

Merge request reports