Updateinfo.xml Timeouts

[rockythorn]

updateinfo.xml times out for Rocky Linux 8 AppStream (x86_64, aarch64)

The v2 updateinfo endpoint returns HTTP 500 for Rocky Linux 8 AppStream on both x86_64 and
aarch64. This breaks compose tooling (apollo_tree.py) when generating updateinfo for RL8.

Reproduction

GET /api/v3/updateinfo/rocky-linux/8/AppStream/updateinfo.xml?arch=x86_64
GET /api/v3/updateinfo/rocky-linux/8/AppStream/updateinfo.xml?arch=aarch64

Both return HTTP 500 after ~29 seconds, consistent with hitting the 30-second server worker timeout.

Observed response times

Repo	Arch	Status	Size	Time
RL8 AppStream	x86_64	500	0 B	28.9s
RL8 AppStream	aarch64	500	0 B	29.4s
RL8 BaseOS	x86_64	200	6.7 MB	8.4s
RL8 PowerTools	x86_64	200	919 KB	5.9s
RL9 AppStream	x86_64	200	10.9 MB	16.1s
RL9 AppStream	aarch64	200	9.8 MB	13.8s

RL9 AppStream succeeds; RL8 does not. The difference is that RL8 has 11 minor versions
(8.0–8.10) vs RL9's ~5, which significantly increases query and processing load.

Root cause

The primary cause is an unbounded prefetch_related("advisory__packages") in
get_updateinfo_v2 (api_updateinfo.py). This loads all packages for every matching
advisory — across every repo, architecture, and mirror — and then filters them in Python.

For RL8 AppStream without a minor_version filter, the query matches ~3000 advisories across
11 minor versions (~33k advisory_affected_products rows). The prefetch then loads all packages
for each unique advisory, estimated at ~300k rows, of which only ~20k are actually used.

The advisory__packages__repo_name filter in the WHERE clause only restricts which advisories
are returned — it does not filter the prefetch.

Two missing indexes make things worse:

• No index on advisory_packages(advisory_id, repo_name, supported_product_id)
• No composite index on advisory_affected_products(supported_product_id, major_version, arch)

Fix

1. Filter the prefetch (highest impact — pushes filtering to SQL):

from tortoise.queryset import Prefetch

Prefetch(
    "advisory__packages",
    queryset=AdvisoryPackage.filter(
        repo_name=repo,
        supported_product_id=supported_product.id,
    )
)

2. Add a composite index on advisory_packages:

CREATE INDEX advisory_packages_advisory_repo_product_idx
    ON public.advisory_packages
    USING btree (advisory_id, repo_name, supported_product_id);

3. Add a composite index on advisory_affected_products:

CREATE INDEX advisory_affected_products_spid_major_arch_idx
    ON public.advisory_affected_products
    USING btree (supported_product_id, major_version, arch);

Fix 1 alone should resolve the timeout. Fixes 2 and 3 provide additional speedup at the DB level.

[rockythorn]

It may also be possible to patch fix this by increasing the timeout on the Apollo server for this endpoint.

[rockythorn]

Local Benchmark Results

Tested the fix branch against main on a local DB restored from the April 2025 production dump (6,905 advisories, 384,990 packages). Three scenarios were tested:

Scenario	RL8 AppStream x86_64	RL8 AppStream aarch64	RL8 BaseOS x86_64
Production (no fix, no indexes)	❌ 500 @ 29s	❌ 500 @ 29s	✅ 200 @ 8.4s
Local S1: main, no indexes	✅ 200 @ 8.3s	✅ 200 @ 8.3s	✅ 200 @ 1.6s
Local S2: main, indexes only	✅ 200 @ 8.2s	✅ 200 @ 8.2s	✅ 200 @ 1.4s
Local S3: fix branch (Prefetch + indexes)	✅ 200 @ 8.1s	✅ 200 @ 8.1s	✅ 200 @ 1.4s

Why local tests don't show a dramatic difference

Local benchmarks did not reproduce the same timing differences seen in production. We suspect this is primarily due to local hardware being significantly faster than the Kubernetes cluster production runs on — the local machine's NVMe SSD likely masks the I/O cost that would be much more pronounced on production storage under concurrent load.

What EXPLAIN ANALYZE tells us

Running EXPLAIN ANALYZE directly on the filter query gives a more hardware-independent view, showing that the new indexes reduce buffer reads by 12x:

	Buffers read from disk	Buffers from shared cache
Without indexes	5,604	11,529
With indexes	468	3,768

On production storage where data is less likely to be fully cached in memory, that 12x I/O reduction should translate directly into wall-clock time savings. This is the strongest evidence we have that the indexes will resolve the timeout.

Conclusion

The fix branch (fix/updateinfo-appstream-timeout) addresses the issue. The two DB indexes are the critical part of the fix — the Prefetch change additionally reduces the volume of rows loaded into Python memory, which provides a safety margin as the dataset continues to grow.