Project level PyPI metadata endpoint triggering too many SQL queries

🔥 Problem

Found while investigating a red error budget (internal) week.

Looking at this Kibana search (internal), we can observe that /api/:version/projects/:id/packages/pypi/simple/*package_name can sometimes trigger 4000+ SQL queries. 😱

That's a lot for the metadata PyPI endpoint scoped to a package name.

🚒 Solution

1. Investigate the source of those queries.
   • Something is telling me that we might have a n + 1 situation here.
2. Fix that part to avoid that many queries.

🔮 Other considerations

Metadata endpoints can have hard times generating answers because we could be pulling a large amount of data from the database and then format that data in a proper way for the external client.

We have been considering generating those metadata responses in advance so that these metadata requests are sped up. This is Investigate: Improve the performance of package... (#338485 - closed).