## Summary Deleting a `maven_packages` row makes PostgreSQL prove that no `maven_files` row still references it, through `fk_maven_files_maven_package_id_maven_packages`. No index on `maven_files` can serve that check for the rows a reap deletes, so the check reads the whole partition. The cost grows with partition size. It is paid by whoever first drives the package reap at volume. Nothing drives it today: no chunk driver is in the tree, so the reapers have no caller outside tests. Found while reviewing the Maven hosted reap in !1702. ## The foreign key ```sql ALTER TABLE public.maven_files ADD CONSTRAINT fk_maven_files_maven_package_id_maven_packages FOREIGN KEY (maven_package_id, namespace_id) REFERENCES public.maven_packages (id, namespace_id); ``` The check therefore wants an index that leads with `maven_package_id` and covers every row. ## Why no index serves it `maven_files` carries one parent-level index that mentions `maven_package_id`: ```sql CREATE UNIQUE INDEX unique_maven_files_ns_id_package_id_file_name_when_ver_null ON ONLY public.maven_files USING btree (namespace_id, maven_package_id, file_name) WHERE ((soft_deleted_at IS NULL) AND (maven_version_id IS NULL)); ``` That index is partial on **two** conditions, and both exclude reaped rows: - `soft_deleted_at IS NULL` excludes every tombstoned row, which is the only kind a reap deletes. - `maven_version_id IS NULL` excludes every version-level file, whatever its soft-delete state. The second conjunct is what makes the gap structural. A reap that deleted live rows would still not use this index for version-level files, because they are outside it by shape rather than by state. The other parent-level index on the table leads with a different column and does not carry `maven_package_id`: ```sql CREATE INDEX index_maven_files_on_ns_id_ver_id ON ONLY public.maven_files USING btree (namespace_id, maven_version_id, blob_sha256); ``` ## Measurement Taken on `postgres:17-alpine`, server version 17.10, which matches the CI `GL_PG_CURR_VERSION`. The fixture seeds `maven_files` rows under one namespace, spread over 200 versions of one package, and the delete targets the package row after its files are drained. | Rows in the partition | Package-row delete | Version-row delete | | --- | --- | --- | | 10,000 | 1.998 ms | — | | 200,000 | 16.286 ms | 1.586 ms | The package side is linear in partition size. The version side stays flat, because `index_maven_files_on_ns_id_ver_id` serves the `maven_version_id` check and is not partial. A reader can reproduce this with a seeded partition and `EXPLAIN (ANALYZE)` on a `DELETE` of the parent row. ## Proposal Add a non-partial index on `maven_files (namespace_id, maven_package_id)`. This is a proposal rather than a decision. An index is a migration, and no plan in flight owns one for this table. Whoever picks it up weighs the write cost on the upload path against the reap cost, and decides whether the column order should carry a third column. ## Labels `type::bug` with `severity::4`, and `artifact-registry::database`. The choice was between `type::bug` and `type::feature`. It is `type::bug` because the schema as merged makes a bounded operation unbounded, rather than because a capability is missing. `severity::4` reflects that no code path reaches it today; re-triage upward when a chunk driver lands and the reap runs against real volumes. ## Correction: the measured cost is two unserved checks, not one The summary and the measurement above attribute the cost to the `maven_files` check alone. That is wrong, and the correction raises the price of the fix rather than lowering it. **Two foreign keys reference `maven_packages`**, so deleting one package row fires two referential-integrity checks: ```sql ALTER TABLE public.maven_files ADD CONSTRAINT fk_maven_files_maven_package_id_maven_packages FOREIGN KEY (maven_package_id, namespace_id) REFERENCES public.maven_packages (id, namespace_id); ALTER TABLE public.maven_versions ADD CONSTRAINT fk_maven_versions_maven_package_id_maven_packages FOREIGN KEY (maven_package_id, namespace_id) REFERENCES public.maven_packages (id, namespace_id); ``` **Neither check has a usable index.** On `maven_versions`, every index that leads with `(namespace_id, maven_package_id)` is partial on the live predicate: ```sql CREATE INDEX index_maven_versions_on_ns_id_pkg_id_id ON ONLY public.maven_versions USING btree (namespace_id, maven_package_id, id) WHERE (soft_deleted_at IS NULL); ``` `index_maven_versions_on_ns_id_pkg_id_created_at_id`, `index_maven_versions_on_ns_id_pkg_id_last_downloaded_at`, `index_maven_versions_on_ns_id_pkg_id_size_bytes` and `unique_maven_versions_ns_id_pkg_id_version` carry the same `WHERE (soft_deleted_at IS NULL)`. The two non-partial indexes on the table cannot restrict on the column: ```sql CREATE UNIQUE INDEX unique_maven_versions_id_pkg_id_ns_id ON ONLY public.maven_versions USING btree (id, maven_package_id, namespace_id); CREATE INDEX index_maven_versions_on_ns_id_created_at ON ONLY public.maven_versions USING btree (namespace_id, created_at DESC); ``` The first leads with `id`, so `maven_package_id` is not a leading column. The second does not carry `maven_package_id` at all. A referential-integrity check carries no predicate, so it needs an index over every row. Neither table has one. **So the 16.286 ms in the table above is the sum of two whole-partition scans, not one.** The per-check figure is not separated by that measurement, and a reader pricing the remedy needs to know it is paying for two. **The remedy therefore names two indexes, not one:** a non-partial index leading with `(namespace_id, maven_package_id)` on `maven_files`, and one on `maven_versions`. Both remain proposals; each is a migration. The same class has a third instance in npm, recorded in the comments. The `structural` argument in the section above is Maven-specific: Maven's one candidate index is partial on two conditions, where npm's are partial on one. That difference does not weaken the npm case, because a referential-integrity check carries no predicate in either schema. Recorded as a correction rather than edited into the text above, so that a reader who saw the first version can tell what changed. _This is a bot message 🤖 — /smurfit_