Need a performant solution to find content diffs between non-adjacent repo versions

[acheng-01]

Is your feature request related to a problem? Please describe.
There is no efficient way to compute the net content difference between two non-adjacent repository versions over the REST API.

The existing repository_version_added / repository_version_removed filters only diff a version against its immediate predecessor (a single step). When two versions of interest are not adjacent — for example, two versions that were each published but have other versions created in between — there is no direct way to ask "what content is in version X but not in version Y."

The current workaround is a q= expression filter such as: q=repository_version=<X> AND NOT repository_version=<Y>

On large repositories this is expensive and puts significant load on the database, because the NOT clause is evaluated against the full content set rather than scoped to the change between the two versions.

Describe the solution you'd like
Some way to retrieve the net diff between two arbitrary repository versions where the cost scales with the size of the change rather than the size of the repository. A couple of directions that could work:

• Exposing each version's content unit IDs so clients can fetch two versions and diff the sets themselves
• A base_version parameter on the content filters — allow repository_version_added / repository_version_removed to take a base_version, so the added/removed set is computed between the two specified versions instead of only against the immediate predecessor. This would build on the existing RepositoryVersion.added() / removed() methods, which already accept a base_version argument at the model level. Omitting it would preserve today's single-step behavior.

Describe alternatives you've considered

• Filtering content by repository_version and diffing via q=

[gerrod3]

What is your need for diffing the contend_ids on the client side? Have you tried using the ?repository_version_added= query? Using it with the ?include= filter can reduce the result to just the prn or href of the content in the repository version. I would expect this to be performant -- and if it isn't we should focus on making it so. I'm not sure if we want to expose content_ids since it's an implementation detail.

[acheng-01]

We're not diffing adjacent versions unfortunately. Our use case is having to purge the cache of stale content between two publications from our CDN, hence the diffing. However, our publications are decoupled from version creation, so those two versions are often several increments apart. If I'm understanding correctly, ?repository_version_added= / ?repository_version_removed= resolve to RepositoryVersion.added() / .removed() with no base, so they only give the single-step diff against the immediate predecessor. This means our diffing strategy would miss content changed in the realized (published) versions.

For some context, we are currently running q of repository_version = x AND NOT repository_version = y in the entire content table for content of a specific type. As you can imagine, this has been a very expensive query - one that has recently more frequently been giving us db resource consumption issues.

That said, I did feel iffy about exposing content_ids as well. I do have a PR open to conditionally expose that, but I'd be happy to drop it. One alternative perhaps is to expose a base_version parameter on those filters? For example, ?repository_version_added=<v>&base_version=<v0>. I'm also open to other ideas.

Thanks for the feedback!

[mdellweg]

• Always returning content_ids — rejected because the array can be very large and would unnecessarily inflate every repository version response

To me this even being optional is an invitation to ddos the server. (May even lead to OOM, because content_ids can be up in the 100 000.)

Can you try the "q"-filter on the "content_list" operation?
Like: [...]?q='repository_version=<prn1> AND NOT repository_version=<prn2>' (didn't test this myself, but the api-docs suggest it should be possible.)

https://pulpproject.org/pulpcore/docs/user/guides/use-complex-filters/

[acheng-01]

Thanks, that makes sense.

To clarify, we are already doing what you're suggesting. It is possible, but I believe the query in the back is eating up all the resources in the db and preventing all of our other operations from being able to connect. I'm happy to be wrong, but I think the actual translated SQL of that query becomes:

(SELECT ... FROM rpm_package WHERE content_ptr_id IN (<current version ids>))
INTERSECT
(
  (SELECT ... FROM rpm_package)                              -- FULL TABLE, no WHERE
  EXCEPT
  (SELECT ... FROM rpm_package WHERE content_ptr_id IN (<previous version ids>))
)
ORDER BY col1 LIMIT 100 OFFSET 100

That middle SELECT...EXCEPT...SELECT is running through the entire list of rpm packages in the table. In addition, the specific operations INTERSECT and EXCEPT (translated from AND and NOT respectively) force full sort-or-hash + dedup over those large inputs. This is why we are now trying to move away from using the q filter.

What do you think about my idea of exposing a base_version field into ContentFilter? The RepositoryVersion model's added() and removed() methods already support diffing the current repo version's content from a specified previous version that isn't adjacent. The implementation would look something like the changes I've made in this draft PR: #7844

I think it should be relatively less invasive to the db than exposing content_ids and diffing it ourselves.

[gerrod3]

The base_version filter sounds like a good idea. Have you tested the database query performance? I assume the generated SQL would look like SELECT ... FROM content.pk in (<content_ids>) AND NOT content.pk in (<base_content_ids>). If that's the case, I don't know if Postgres handles the query well with scaling sizes of content_ids. I wonder if it would be better to do the diff in python first before handing the final content_ids to the database. We would love some performance metrics if you have a large system that you can test.

[acheng-01]

Hmm, so you're right about the generated SQL. I wonder if we can address the problem you mentioned by switching the current inline filter query into an unnest subquery when base_version is supplied. It looks like get_content already has this implementation to work around a 65,535-parameter limit.

I had copilot test both scenarios using a seeded dataset of 200k content units, and here are the results. Version pair here means repo version pairs base and current. Each pairing has a 1k content unit diff between them.

Version Pair	Inlined list (current added())	Unnest subquery	Speedup
50k units / 50k units	1,471 ms	116 ms	~12.7x
199k units / 199k units	6,802 ms	521 ms	~13x

I've added the refactor into my draft PR linked in a previous comment for reference.

[acheng-01]

I've also run a benchmark using the q complex query repository_version = x AND NOT repository_version = y on the content_list endpoint using that same seeded data set:

Version pair	q complex query on content table	base_version filter on repo version table	Speedup
50k units / 50k units	1486 ms (plan 130 ms, exec 1143 ms)	99 ms (plan 0.2 ms, exec 142 ms)	~15x
199k units / 199k units	2403 ms (plan 0.5 ms, exec 2881 ms)	479 ms (plan 0.2 ms, exec 686 ms)	~5x