Maven package reap: the maven_package_id foreign-key check has no usable index

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 (merged).

The foreign key

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:

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:

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:

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:

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:

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

Edited by Pawel Rozlach