chore(maven): datastore methods — *store impl over jet (S10 Step 4)

What this MR does

Step 4 of the S10 Maven hosted plan — the Maven format's datastore layer. Adds the concrete jet-backed *store for internal/format/maven, exposed through four narrow consumer interfaces (DownloadStore, DigestStore, UploadStore, ReconcilerStore) that Steps 6–9 re-declare at their use sites; store.go keeps a skeleton copy for the compile-time anchor.

Covers find-or-create on packages/versions/files, soft-delete-filtered lookups (filtered at every join level on the joined FindPackageFile / FindPackageMetadataFile reads), CountLiveVersions / ListLiveVersions, and the release (DO NOTHING + SELECT compare) / mutable (DO UPDATE) upsert split. The Bump* / MarkRepoLastUpdated counter methods ship as TODO(s18-buffered-counters)-marked no-op stubs; the buffered-counter mechanism is owned by S18 (download tracking) / S20 (lifecycle) / S22 (storage accounting).

Two partition-safety / invariant decisions worth a reviewer's eye:

• Inserted is derived via a two-trip INSERT ... ON CONFLICT DO NOTHING RETURNING → SELECT, not RETURNING xmax = 0 (PostgreSQL rejects RETURNING system columns on HASH-partitioned tables, SQLSTATE 0A000; follows the internal/datastore/container_image.go precedent).
• UpsertMutableFile overwrites via ON CONFLICT ... DO UPDATE SET ... = EXCLUDED.*, deliberately not a standalone table.MavenFiles.UPDATE(...), so invariants_test.go diagnostic #1 (every .UPDATE(...) must carry the counter marker) stays truthful — the only AST-matched .UPDATE sites are the four Bump* stubs.

Also wires internal/format/maven into the test:integration CI job (go list allowlist + changes: anchor) so these integration tests actually run in CI.

Tests

• store_integration_test.go — testcontainers integration suite: per-method happy paths, soft-delete filtering at every join level, concurrent find-or-create / multi-file deploy races, the release/snapshot/package upsert split, namespace + repository isolation, and TestStore_CounterStubsAreNoOps pinning the no-op contract.
• invariants_test.go — AST diagnostic #1: every jet .UPDATE(...) carries a TODO(s18-buffered-counters) marker within 5 source lines (fail-closed).

Verified locally: go build ./..., the full unit suite (go test ./...), and go test -tags=integration -race ./internal/format/maven/... all green.

Spec coverage

Spec: docs/specs/S10-maven-hosted.md

Step 4 implements the datastore layer (the jet-backed *store methods). The HTTP-level acceptance criteria (status codes, headers, RFC 9457 envelopes, FIPS handling, range, body limits) are owned by the handler and flow steps in Steps 5-9 and are marked as such below; this table maps each row to the Step 4 datastore tests that back it, or names the owning step / dependency.

Acceptance criteria

#	Criterion	Tests
AC-1	GET primary file resolves; Content-Type + SHA-1/MD5 headers	Datastore part: TestStore_FindPackageFile (row + digest columns the handler serves). HTTP/headers owned by Step 6.
AC-2	HEAD: same headers, bumps last_downloaded_at, not downloads_count	Bump call-sites are TODO-marked no-op stubs (mechanism deferred to S18); marker enforced by TestInvariant_CounterUpdatesCarrySwapMarker. HEAD semantics owned by Step 6.
AC-3	GET .sha1 sidecar synthesized from maven_files.sha1	Datastore part: TestStore_FindPackageFile (sha1 round-trips). Sidecar synthesis owned by Step 6.
AC-4	GET .md5 sidecar per FIPS posture (200 / 404 / 422)	Owned by Step 6 (sidecar synth + FIPS short-circuit). Datastore stores nullable md5 (covered by file seed/round-trip).
AC-5	GET Range -> 206; digests describe whole file	Owned by S06 serve path + Step 6.
AC-6	GET out-of-range Range -> 416	Owned by S06 serve path + Step 6.
AC-7	PUT fresh release primary file -> 201, inserts maven_files row	TestStore_UpsertReleaseFile ("inserts a fresh release file and reports Inserted=true").
AC-8	PUT existing release file, different content -> 409 file_exists	TestStore_UpsertReleaseFile ("different-content re-upload ... reports the conflicting existing row"); 409 mapping owned by Step 8b.
AC-9	PUT existing release file, byte-identical -> 200, no row change	TestStore_UpsertReleaseFile ("byte-identical re-upload reports Inserted=false and does not change the row").
AC-10	PUT snapshot primary file -> 200, overwrites blob attachment	TestStore_UpsertMutableFile ("overwriting a snapshot file reports Inserted=false and swaps the blob attachment").
AC-11	PUT package-level maven-metadata.xml: create (201) / overwrite (200)	TestStore_UpsertMutableFile ("package-level metadata file upserts with a NULL maven_version_id").
AC-12	PUT .sha1/.sha256/.sha512 matching parent -> 204, no row	Datastore part: TestStore_FindPackageFile / TestStore_FindPackageMetadataFile (find-only lookups the sidecar PUT compares against). 204 path owned by Step 7.
AC-13	PUT digest sidecar mismatch -> 409 checksum_mismatch	Owned by Step 7 (compares request body against the columns the find lookups return).
AC-14	PUT digest sidecar, parent missing -> 404	TestStore_FindPackageFile / TestStore_FindPackageMetadataFile ("returns ErrNotFound ..."); 404 mapping owned by Step 7.
AC-15	PUT digest sidecar, malformed hex -> 400 checksum_invalid_format	Owned by Step 7 (body validation, no datastore call).
AC-16	GET .sha256/.sha512 sidecar synthesized from columns	Datastore part: TestStore_FindPackageFile (blob_sha256 + sha512 round-trip). Synthesis owned by Step 6.
AC-17	PUT .md5 sidecar -> 204 regardless of FIPS / match	Owned by Step 7 (no datastore call; .md5 PUT creates no row).
AC-18	FIPS-on: no MD5 header anywhere; .md5 GET/HEAD -> 422	Owned by Step 6 (serve-time FIPS guard). Datastore stores md5 nullable; never persisted to S06.
AC-19	Path-traversal variant matrix (9 forms x {GET,PUT})	Owned by Step 5 handler tests + Step 3 parser. No datastore involvement (rejection precedes any DB lookup).
AC-20	PUT to remote/virtual repo -> 405 Allow: GET, HEAD	TestStore_FindRepository ("returns the repository Kind so PUT can 405 on non-hosted"); 405 mapping owned by Step 5.
AC-21	PUT over max_artifact_size -> 413	Owned by Step 8 (MaxBytesReader). No datastore involvement.
AC-22	Two concurrent SNAPSHOT PUTs both succeed (DO UPDATE, last write wins)	TestStore_UpsertMutableFile (DO UPDATE overwrite path) + TestStore_FindOrCreateVersion ("concurrent find-or-create yields exactly one row").
AC-23	Concurrent release PUTs resolve per caller after conflict resolution	TestStore_UpsertReleaseFile (DO NOTHING + existing-row surface). Per-caller 201/200/409 mapping owned by Step 8b.
AC-24	Concurrent dedup PUTs (same SHA-256) both succeed, distinct attachments	Attachment insert is datastore.BlobStorageAttachmentStore.Create (S06 / OCI-owned); composed by the handler in Step 8b. Not a Step 4 store method.
AC-25	Concurrent multi-file deploy: one package, one version, both files	TestStore_FindOrCreatePackage / TestStore_FindOrCreateVersion (concurrent -> one row) + TestStore_ConcurrentMultiFileDeploy.
AC-26	Classifier files for one release version are distinct rows	TestStore_UpsertReleaseFile ("classifier files for the same version are distinct rows").
AC-27	GET for a soft-deleted file/version/package/repository -> 404	TestStore_FindPackageFile / TestStore_FindPackageMetadataFile (soft-delete at every join level) + TestStore_FindRepository (soft-deleted repo).
AC-28	Maven CLI conformance suite	Owned by Step 11 (conformance CI job). End-to-end; not a datastore unit.
AC-29	mvn/gradle/sbt resolution without checksum/sidecar warnings	Owned by Step 11 (conformance). End-to-end.
AC-30	Every non-success response is RFC 9457 problem+json	Owned by Step 5 (WriteProblem) + diagnostic #2 (http.Error ban, Step 5). No datastore involvement.
AC-31	After soft-delete + Reconcile, package metadata reflects live versions	Datastore part: TestStore_ListLiveVersions (live set, id ASC) + TestStore_FindPackageMetadataFile + TestStore_UpsertMutableFile. Reconcile logic owned by Step 9.
AC-32	Reconcile no-ops when stored metadata already coherent	Owned by Step 9 (reconciler fast-path). Datastore inputs covered by TestStore_ListLiveVersions / TestStore_FindPackageMetadataFile.
AC-33	Reconcile returns ErrNoMetadata when no package-level metadata exists	TestStore_FindPackageMetadataFile ("returns ErrNotFound when the package has no metadata file"); ErrNoMetadata mapping owned by Step 9.

Error cases

Condition	Tests
Path malformed -> 400 bad_request	Owned by Step 3 parser + Step 5 handler. No datastore involvement.
group_id/artifact_id/version grammar violation -> 400	Owned by Step 3 parser. No datastore involvement.
Digest sidecar body not expected hex -> 400 checksum_invalid_format	Owned by Step 7. No datastore call.
Digest sidecar body mismatch -> 409 checksum_mismatch	Owned by Step 7 (compares against columns from the find lookups).
Missing/invalid Basic credential -> 401	Owned by S08 middleware. No datastore involvement.
Authenticated but lacks grant -> 403 forbidden	Owned by S09 middleware. No datastore involvement.
Slug/repo/package/version/file/sidecar-parent missing -> 404 not_found	TestStore_FindRepository, TestStore_FindPackageFile, TestStore_FindPackageMetadataFile (all return datastore.ErrNotFound); 404 mapping owned by Steps 5-7.
.md5 sidecar parent md5 column NULL -> 404 not_found	Owned by Step 6 (sidecar synth). Datastore stores md5 nullable.
Range outside blob -> 416 range_not_satisfiable	Owned by S06 serve path + Step 6.
PUT to remote/virtual repo -> 405 method_not_allowed	TestStore_FindRepository (Kind surfaced for the 405 decision); mapping owned by Step 5.
Release primary file exists, different content -> 409 file_exists	TestStore_UpsertReleaseFile ("different-content re-upload ..."); mapping owned by Step 8b.
Body exceeds max_artifact_size -> 413	Owned by Step 8. No datastore involvement.
Per-package version cap exceeded -> 422 version_limit_exceeded	TestStore_CountLiveVersions (the live-only count the cap reads); 422 mapping owned by Step 8a.
Storage quota exceeded -> 429	Owned by S06 (session-start quota). No Step 4 store method.
Storage/database/hash-state failure -> 500	Owned by Steps 5-9 (error mapping). Store methods wrap DB errors with context (non-ErrNotFound surfaces as a real error).

Security considerations

Concern	Tests
Path traversal (router invariant + handler segment check)	Owned by Step 5 handler + Step 3 parser. No datastore involvement.
Coordinate validation (regex before SQL)	Owned by Step 3 parser. jet parameterization (used by every store method) is the defense-in-depth backstop.
RFC 9457 detail field hygiene	Owned by Step 5 (WriteProblem). Store ErrNotFound wraps carry no client-facing detail.
Information disclosure on 404 (uniform envelope)	Store side: every miss-level (repo/package/version/file) returns the same datastore.ErrNotFound sentinel -- TestStore_FindRepository / FindPackageFile / FindPackageMetadataFile. Uniform envelope owned by Step 5.
Body size limit (MaxBytesReader)	Owned by Step 8 / sidecar steps. No datastore involvement.
Slowloris protection (read-deadline extension)	Owned by Step 8 + S01. No datastore involvement.
maven-metadata.xml publish-path trust boundary	Owned by Step 9 reconciler (DOCTYPE rejection, size cap). Store stores client bytes verbatim.
Sidecar PUT body bound (256 bytes)	Owned by Step 7. No datastore involvement.
Sidecar trust (validate against computed column)	Owned by Step 7 (compares against the columns FindPackageFile / FindPackageMetadataFile return).
FIPS-mode MD5 handling	Owned by Steps 6-8 (serve-time guard, upload-time skip). Store stores md5 nullable; never persisted to S06.
Delivery-mode dispatch deferred to S06	Owned by S06 + Step 6. No datastore involvement.
Audit trail	Owned by S21. No Step 4 store method.
Storage tenancy (path from namespace_id, sha256)	Owned by S06 PathResolver. Every store method is namespace-scoped -- TestStore_* "different namespace" subtests assert the partition key cannot leak.
Background-job amplification bounds	Owned by Step 9/10 (reconciler) + S27 (enqueue dedup). Datastore part: TestStore_CountLiveVersions / TestStore_ListLiveVersions (the 25k version bound input).

Part of #21 (closed).

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

The four buffered-counter stubs (BumpAccessTimestamps, BumpRepoDownloadCount, BumpRepoCounters, MarkRepoLastUpdated) build a jet UPDATE but discard it without dispatch (deferred to S18/S22), so they execute no statement and are not represented here.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
maven.FindRepository	Limit	repositories_namespace_id_name_idx1, maven_repositories_namespace_id_repository_id_idx	1 / 1	16.62	0.019ms	6 / 0	1
maven.ReVerifyRepositoryAlive	Limit	maven_repositories_pkey, repositories_pkey	1 / 1	16.63	0.018ms	8 / 0	1
maven.FindOrCreatePackage (conflict SELECT)	Limit	maven_packages_namespace_id_maven_repository_id_group_i_idx	1 / 1	8.31	0.007ms	3 / 0	1
maven.FindOrCreateVersion (conflict SELECT)	Limit	maven_versions_namespace_id_maven_package_id_version_idx	1 / 1	8.30	0.006ms	3 / 0	1
maven.CountLiveVersions	Aggregate	maven_versions_namespace_id_maven_package_id_last_downl_idx	1 / 1	59.20	0.009ms	3 / 0	1
maven.ListLiveVersions (first page, AfterID=0)	Limit	maven_versions_namespace_id_maven_package_id_last_downl_idx	50 / 50	60.60	0.014ms	3 / 0	1
maven.ListLiveVersions (subsequent page, AfterID>0)	Limit	maven_versions_namespace_id_maven_package_id_last_downl_idx	50 / 49	60.73	0.020ms	3 / 0	1
maven.FindPackageFile	Limit	maven_versions_namespace_id_maven_package_id_version_idx, maven_files_namespace_id_maven_version_id_file_name_idx	1 / 1	17.65	0.023ms	7 / 0	1
maven.FindPackageMetadataFile	Limit	maven_packages_namespace_id_maven_repository_id_group_i_idx, maven_files_namespace_id_maven_package_id_file_name_idx	1 / 1	16.62	0.016ms	6 / 0	1
maven.UpsertReleaseFile (conflict SELECT, version-level)	Limit	maven_files_namespace_id_maven_version_id_file_name_idx	1 / 1	8.30	0.006ms	3 / 0	1
maven.UpsertReleaseFile (conflict SELECT, package-level)	Limit	maven_files_namespace_id_maven_package_id_file_name_idx	1 / 1	8.30	0.005ms	3 / 0	1
maven.FindOrCreatePackage (INSERT … DO NOTHING)	Insert	n/a (Insert)	1 / 1	0.01	0.142ms	41 / 0	1
maven.FindOrCreateVersion (INSERT … DO NOTHING)	Insert	n/a (Insert)	1 / 1	0.01	0.122ms	37 / 0	1
maven.UpsertReleaseFile / UpsertMutableFile (INSERT … DO NOTHING, version-level)	Insert	n/a (Insert)	1 / 1	0.01	0.161ms	33 / 4	1
maven.UpsertReleaseFile / UpsertMutableFile (INSERT … DO NOTHING, package-level)	Insert	n/a (Insert)	1 / 1	0.01	0.145ms	33 / 4	1
maven.UpsertMutableFile (INSERT … DO UPDATE, version-level)	Insert	n/a (Insert)	1 / 1	0.01	0.263ms	92 / 2	1
maven.UpsertMutableFile (INSERT … DO UPDATE, package-level)	Insert	n/a (Insert)	1 / 1	0.01	0.153ms	88 / 0	1

maven.FindRepository

Summary: Plan matches intent: Nested Loop joining an Index Scan on repositories (namespace_id, name) to an Index Scan on maven_repositories (namespace_id, repository_id), the namespace_id literal pruning to one of 64 partitions on both sides; format is a cheap filter on the single name-matched row. plan=actual (1/1), 0.034ms. No anomalies.

Seed shape: namespaces=1, repositories=5000, maven_repositories=5000

Rendered SQL:

SELECT maven_repositories.id AS "maven_repositories.id",
maven_repositories.namespace_id AS "maven_repositories.namespace_id",
maven_repositories.repository_id AS "maven_repositories.repository_id",
repositories.kind AS "repositories.kind"
FROM public.maven_repositories
INNER JOIN public.repositories ON ((repositories.id = maven_repositories.repository_id) AND (repositories.namespace_id = maven_repositories.namespace_id))
WHERE (((maven_repositories.namespace_id = $1::uuid) AND (repositories.name = $2::text)) AND (repositories.format = $3)) AND (repositories.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: namespace_id (seeded), name='review-prep-target', format=1 (maven), LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.56..16.62 rows=1 width=34) (actual time=0.019..0.019 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Nested Loop  (cost=0.56..16.62 rows=1 width=34) (actual time=0.019..0.019 rows=1 loops=1)
         Buffers: shared hit=6
         ->  Index Scan using repositories_p51_namespace_id_name_idx1 on repositories_p51 repositories  (cost=0.28..8.30 rows=1 width=26) (actual time=0.012..0.012 rows=1 loops=1)
               Index Cond: ((namespace_id = '16e799aa-7163-4136-95d6-5b302ac8b1b5'::uuid) AND (name = 'review-prep-target'::text))
               Filter: (format = '1'::smallint)
               Buffers: shared hit=3
         ->  Index Scan using maven_repositories_p51_namespace_id_repository_id_idx on maven_repositories_p51 maven_repositories  (cost=0.28..8.30 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
               Index Cond: ((namespace_id = '16e799aa-7163-4136-95d6-5b302ac8b1b5'::uuid) AND (repository_id = repositories.id))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=706
 Planning Time: 1.221 ms
 Execution Time: 0.032 ms

Timings: planning 1.221ms, execution 0.032ms, total 1.253ms.

maven.ReVerifyRepositoryAlive

Summary: Plan matches intent: the (namespace_id, id) predicate drives an Index Scan on the maven_repositories primary key, joined to the repositories primary key for the format/kind/soft-delete guard, single partition; the FOR SHARE lock is taken on the resolved row. plan=actual (1/1), 0.033ms. No anomalies.

Seed shape: namespaces=1, repositories=5000, maven_repositories=5000

Rendered SQL:

SELECT maven_repositories.id AS "maven_repositories.id"
FROM public.maven_repositories
INNER JOIN public.repositories ON ((repositories.id = maven_repositories.repository_id) AND (repositories.namespace_id = maven_repositories.namespace_id))
WHERE ((((maven_repositories.namespace_id = $1::uuid) AND (maven_repositories.id = $2)) AND (repositories.format = $3)) AND (repositories.kind = $4)) AND (repositories.soft_deleted_at IS NULL)
LIMIT $5
FOR SHARE;

Bound args: namespace_id (seeded), maven_repositories.id (seeded), format=1 (maven), kind=0 (hosted), LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.56..16.63 rows=1 width=28) (actual time=0.018..0.018 rows=1 loops=1)
   Buffers: shared hit=8
   ->  LockRows  (cost=0.56..16.63 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=1)
         Buffers: shared hit=8
         ->  Nested Loop  (cost=0.56..16.62 rows=1 width=28) (actual time=0.015..0.015 rows=1 loops=1)
               Buffers: shared hit=6
               ->  Index Scan using maven_repositories_p01_pkey on maven_repositories_p01 maven_repositories  (cost=0.28..8.30 rows=1 width=42) (actual time=0.007..0.007 rows=1 loops=1)
                     Index Cond: ((id = '5001'::bigint) AND (namespace_id = 'baade1d8-858d-4053-975f-2656b67651fd'::uuid))
                     Buffers: shared hit=3
               ->  Index Scan using repositories_p01_pkey on repositories_p01 repositories  (cost=0.28..8.31 rows=1 width=34) (actual time=0.007..0.007 rows=1 loops=1)
                     Index Cond: ((id = maven_repositories.repository_id) AND (namespace_id = 'baade1d8-858d-4053-975f-2656b67651fd'::uuid))
                     Filter: ((soft_deleted_at IS NULL) AND (format = '1'::smallint) AND (kind = '0'::smallint))
                     Buffers: shared hit=3
 Planning:
   Buffers: shared hit=194
 Planning Time: 0.462 ms
 Execution Time: 0.029 ms

Timings: planning 0.462ms, execution 0.029ms, total 0.491ms.

maven.FindOrCreatePackage (conflict SELECT)

Summary: Conflict-path read after the INSERT … DO NOTHING returns no row. Index Scan on the partial unique index (namespace_id, maven_repository_id, group_id, artifact_id) WHERE soft_deleted_at IS NULL — the same arbiter the INSERT conflicts on — single partition, plan=actual (1/1). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=5000

Rendered SQL:

SELECT maven_packages.namespace_id AS "maven_packages.namespace_id",
maven_packages.id AS "maven_packages.id",
maven_packages.maven_repository_id AS "maven_packages.maven_repository_id",
maven_packages.group_id AS "maven_packages.group_id",
maven_packages.artifact_id AS "maven_packages.artifact_id"
FROM public.maven_packages
WHERE ((((maven_packages.namespace_id = $1::uuid) AND (maven_packages.maven_repository_id = $2)) AND (maven_packages.group_id = $3::text)) AND (maven_packages.artifact_id = $4::text)) AND (maven_packages.soft_deleted_at IS NULL)
LIMIT $5;

Bound args: namespace_id (seeded), maven_repository_id (seeded), group_id='com.example', artifact_id='app', LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.31 rows=1 width=53) (actual time=0.006..0.007 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using maven_packages_p52_namespace_id_maven_repository_id_group_i_idx on maven_packages_p52 maven_packages  (cost=0.28..8.31 rows=1 width=53) (actual time=0.006..0.006 rows=1 loops=1)
         Index Cond: ((namespace_id = '1713ede5-c1d8-4739-a71d-48c5b0e40a7e'::uuid) AND (maven_repository_id = '10001'::bigint) AND (group_id = 'com.example'::text) AND (artifact_id = 'app'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=130
 Planning Time: 0.271 ms
 Execution Time: 0.012 ms

Timings: planning 0.271ms, execution 0.012ms, total 0.283ms.

maven.FindOrCreateVersion (conflict SELECT)

Summary: Conflict-path read mirroring FindOrCreatePackage. Index Scan on the partial unique index (namespace_id, maven_package_id, version) WHERE soft_deleted_at IS NULL, single partition, plan=actual (1/1). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=5000

Rendered SQL:

SELECT maven_versions.namespace_id AS "maven_versions.namespace_id",
maven_versions.id AS "maven_versions.id",
maven_versions.maven_package_id AS "maven_versions.maven_package_id",
maven_versions.version AS "maven_versions.version"
FROM public.maven_versions
WHERE (((maven_versions.namespace_id = $1::uuid) AND (maven_versions.maven_package_id = $2)) AND (maven_versions.version = $3::text)) AND (maven_versions.soft_deleted_at IS NULL)
LIMIT $4;

Bound args: namespace_id (seeded), maven_package_id (seeded), version='tv-target', LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using maven_versions_p32_namespace_id_maven_package_id_version_idx on maven_versions_p32 maven_versions  (cost=0.28..8.30 rows=1 width=40) (actual time=0.005..0.006 rows=1 loops=1)
         Index Cond: ((namespace_id = '4dddf716-52b9-4812-bf52-04758d9ad191'::uuid) AND (maven_package_id = '5301'::bigint) AND (version = 'tv-target'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=70
 Planning Time: 0.156 ms
 Execution Time: 0.011 ms

Timings: planning 0.156ms, execution 0.011ms, total 0.167ms.

maven.CountLiveVersions

Summary: Realistic distribution (100 packages sharing 5000 versions; target package has 50 live versions): Bitmap Index Scan on (namespace_id, maven_package_id, …) feeds the Aggregate, 0.017ms, single partition. The predicate is selective and the index is used; with a degenerate all-under-one-package shape the planner would Seq-Scan the partition (correct at ~100% selectivity). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=100, maven_versions=5000 (target package: 50 live versions)

Rendered SQL:

SELECT COUNT(*)
FROM public.maven_versions
WHERE ((maven_versions.namespace_id = $1::uuid) AND (maven_versions.maven_package_id = $2)) AND (maven_versions.soft_deleted_at IS NULL);

Bound args: namespace_id (seeded), maven_package_id (target package, seeded)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Aggregate  (cost=59.19..59.20 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Bitmap Heap Scan on maven_versions_p32 maven_versions  (cost=4.79..59.07 rows=50 width=0) (actual time=0.005..0.006 rows=50 loops=1)
         Recheck Cond: ((namespace_id = '8ddeca91-348e-4134-99ad-e6260702a43e'::uuid) AND (maven_package_id = '5001'::bigint) AND (soft_deleted_at IS NULL))
         Heap Blocks: exact=1
         Buffers: shared hit=3
         ->  Bitmap Index Scan on maven_versions_p32_namespace_id_maven_package_id_last_downl_idx  (cost=0.00..4.78 rows=50 width=0) (actual time=0.003..0.003 rows=50 loops=1)
               Index Cond: ((namespace_id = '8ddeca91-348e-4134-99ad-e6260702a43e'::uuid) AND (maven_package_id = '5001'::bigint))
               Buffers: shared hit=2
 Planning:
   Buffers: shared hit=83
 Planning Time: 0.199 ms
 Execution Time: 0.017 ms

Timings: planning 0.199ms, execution 0.017ms, total 0.216ms.

maven.ListLiveVersions (first page, AfterID=0)

Summary: First page (AfterID=0). Bitmap Index Scan on (namespace_id, maven_package_id, …) returns the package's live versions, then a quicksort (28 kB) orders by id before the LIMIT. ORDER BY id aligns with no index here, so this small result set is bitmap-scanned and quicksorted. When a package holds many versions the planner instead uses the (id, namespace_id) primary key for an ordered, early-terminating scan — no sort, no full-set materialization (see the keyset note below). No correctness issue.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=100, maven_versions=5000 (target package: 50 live versions)

Rendered SQL:

SELECT maven_versions.namespace_id AS "maven_versions.namespace_id",
maven_versions.id AS "maven_versions.id",
maven_versions.maven_package_id AS "maven_versions.maven_package_id",
maven_versions.version AS "maven_versions.version"
FROM public.maven_versions
WHERE ((maven_versions.namespace_id = $1::uuid) AND (maven_versions.maven_package_id = $2)) AND (maven_versions.soft_deleted_at IS NULL)
ORDER BY maven_versions.id ASC
LIMIT $3;

Bound args: namespace_id (seeded), maven_package_id (target package, seeded), LIMIT 1000 (AfterID=0, first page)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=60.48..60.60 rows=50 width=38) (actual time=0.011..0.014 rows=50 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=60.48..60.60 rows=50 width=38) (actual time=0.011..0.012 rows=50 loops=1)
         Sort Key: maven_versions.id
         Sort Method: quicksort  Memory: 28kB
         Buffers: shared hit=3
         ->  Bitmap Heap Scan on maven_versions_p45 maven_versions  (cost=4.79..59.07 rows=50 width=38) (actual time=0.005..0.006 rows=50 loops=1)
               Recheck Cond: ((namespace_id = '867a8676-b609-4f10-8253-e1feaa2dd247'::uuid) AND (maven_package_id = '5101'::bigint) AND (soft_deleted_at IS NULL))
               Heap Blocks: exact=1
               Buffers: shared hit=3
               ->  Bitmap Index Scan on maven_versions_p45_namespace_id_maven_package_id_last_downl_idx  (cost=0.00..4.78 rows=50 width=0) (actual time=0.003..0.003 rows=50 loops=1)
                     Index Cond: ((namespace_id = '867a8676-b609-4f10-8253-e1feaa2dd247'::uuid) AND (maven_package_id = '5101'::bigint))
                     Buffers: shared hit=2
 Planning:
   Buffers: shared hit=71
 Planning Time: 0.132 ms
 Execution Time: 0.021 ms

Timings: planning 0.132ms, execution 0.021ms, total 0.153ms.

maven.ListLiveVersions (subsequent page, AfterID>0)

Summary: Subsequent page (AfterID>0). Same bitmap + quicksort shape as the first page at the seeded cardinality; the keyset id > AfterID is applied as a post-scan Filter on the heap (Rows Removed by Filter), not an index range seek. When a package holds many versions the planner switches to the (id, namespace_id) primary key, applying id > AfterID as a range bound and early terminating at the LIMIT (no sort). Correct results; see the keyset note below.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=100, maven_versions=5000 (target package: 50 live versions)

Rendered SQL:

SELECT maven_versions.namespace_id AS "maven_versions.namespace_id",
maven_versions.id AS "maven_versions.id",
maven_versions.maven_package_id AS "maven_versions.maven_package_id",
maven_versions.version AS "maven_versions.version"
FROM public.maven_versions
WHERE (((maven_versions.namespace_id = $1::uuid) AND (maven_versions.maven_package_id = $2)) AND (maven_versions.soft_deleted_at IS NULL)) AND (maven_versions.id > $3)
ORDER BY maven_versions.id ASC
LIMIT $4;

Bound args: namespace_id (seeded), maven_package_id (target package, seeded), AfterID (min id of the target package's versions, seeded), LIMIT 1000

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=60.60..60.73 rows=50 width=38) (actual time=0.017..0.020 rows=49 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=60.60..60.73 rows=50 width=38) (actual time=0.016..0.017 rows=49 loops=1)
         Sort Key: maven_versions.id
         Sort Method: quicksort  Memory: 28kB
         Buffers: shared hit=3
         ->  Bitmap Heap Scan on maven_versions_p52 maven_versions  (cost=4.79..59.19 rows=50 width=38) (actual time=0.007..0.010 rows=49 loops=1)
               Recheck Cond: ((namespace_id = '8cd8c864-d043-4988-b8d6-865ee8a0d39b'::uuid) AND (maven_package_id = '5201'::bigint) AND (soft_deleted_at IS NULL))
               Filter: (id > '10001'::bigint)
               Rows Removed by Filter: 1
               Heap Blocks: exact=1
               Buffers: shared hit=3
               ->  Bitmap Index Scan on maven_versions_p52_namespace_id_maven_package_id_last_downl_idx  (cost=0.00..4.78 rows=50 width=0) (actual time=0.004..0.004 rows=50 loops=1)
                     Index Cond: ((namespace_id = '8cd8c864-d043-4988-b8d6-865ee8a0d39b'::uuid) AND (maven_package_id = '5201'::bigint))
                     Buffers: shared hit=2
 Planning:
   Buffers: shared hit=74
 Planning Time: 0.211 ms
 Execution Time: 0.035 ms

Timings: planning 0.211ms, execution 0.035ms, total 0.246ms.

maven.FindPackageFile

Summary: Plan matches intent: Nested Loops resolve maven_packages → maven_versions → maven_files via Index Scans on the maven_versions (namespace_id, maven_package_id, version) and maven_files (namespace_id, maven_version_id, file_name) unique indexes; single partition, plan=actual (1/1), 0.028ms. The Seq Scan on maven_packages is over the single seeded ancestor package (1 row) — correct at that cardinality; with many packages the planner uses the package unique index (see FindPackageMetadataFile). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=5000, maven_files=5000, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

SELECT maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name"
FROM public.maven_files
INNER JOIN public.maven_versions ON ((maven_versions.id = maven_files.maven_version_id) AND (maven_versions.namespace_id = maven_files.namespace_id))
INNER JOIN public.maven_packages ON ((maven_packages.id = maven_versions.maven_package_id) AND (maven_packages.namespace_id = maven_versions.namespace_id))
WHERE ((((((((maven_files.namespace_id = $1::uuid) AND (maven_packages.maven_repository_id = $2)) AND (maven_packages.group_id = $3::text)) AND (maven_packages.artifact_id = $4::text)) AND (maven_versions.version = $5::text)) AND (maven_files.file_name = $6::text)) AND (maven_files.soft_deleted_at IS NULL)) AND (maven_versions.soft_deleted_at IS NULL)) AND (maven_packages.soft_deleted_at IS NULL)
LIMIT $7;

Bound args: namespace_id (seeded), maven_repository_id (seeded), group_id='com.example', artifact_id='app', version='tv-target', file_name='app.jar', LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.56..17.65 rows=1 width=207) (actual time=0.022..0.023 rows=1 loops=1)
   Buffers: shared hit=7
   ->  Nested Loop  (cost=0.56..17.65 rows=1 width=207) (actual time=0.022..0.022 rows=1 loops=1)
         Buffers: shared hit=7
         ->  Nested Loop  (cost=0.28..9.33 rows=1 width=24) (actual time=0.015..0.015 rows=1 loops=1)
               Buffers: shared hit=4
               ->  Seq Scan on maven_packages_p23 maven_packages  (cost=0.00..1.02 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=1)
                     Filter: ((soft_deleted_at IS NULL) AND (namespace_id = '4b709a3a-dcb5-4146-a1ae-6783918e8d49'::uuid) AND (maven_repository_id = '10006'::bigint) AND (group_id = 'com.example'::text) AND (artifact_id = 'app'::text))
                     Buffers: shared hit=1
               ->  Index Scan using maven_versions_p23_namespace_id_maven_package_id_version_idx on maven_versions_p23 maven_versions  (cost=0.28..8.30 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
                     Index Cond: ((namespace_id = '4b709a3a-dcb5-4146-a1ae-6783918e8d49'::uuid) AND (maven_package_id = maven_packages.id) AND (version = 'tv-target'::text))
                     Buffers: shared hit=3
         ->  Index Scan using maven_files_p23_namespace_id_maven_version_id_file_name_idx on maven_files_p23 maven_files  (cost=0.28..8.30 rows=1 width=207) (actual time=0.006..0.006 rows=1 loops=1)
               Index Cond: ((namespace_id = '4b709a3a-dcb5-4146-a1ae-6783918e8d49'::uuid) AND (maven_version_id = maven_versions.id) AND (file_name = 'app.jar'::text))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=347 read=1
 Planning Time: 0.928 ms
 Execution Time: 0.042 ms

Timings: planning 0.928ms, execution 0.042ms, total 0.970ms.

maven.FindPackageMetadataFile

Summary: Plan matches intent: Index Scan on the maven_packages (namespace_id, maven_repository_id, group_id, artifact_id) unique index joins to an Index Scan on the maven_files (namespace_id, maven_package_id, file_name) WHERE … maven_version_id IS NULL partial index, single partition, plan=actual (1/1). Confirms the package lookup uses its index at 5000-package cardinality. No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=5000, maven_files=5000, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

SELECT maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name"
FROM public.maven_files
INNER JOIN public.maven_packages ON ((maven_packages.id = maven_files.maven_package_id) AND (maven_packages.namespace_id = maven_files.namespace_id))
WHERE (((((((maven_files.namespace_id = $1::uuid) AND (maven_packages.maven_repository_id = $2)) AND (maven_packages.group_id = $3::text)) AND (maven_packages.artifact_id = $4::text)) AND (maven_files.file_name = $5::text)) AND (maven_files.maven_version_id IS NULL)) AND (maven_files.soft_deleted_at IS NULL)) AND (maven_packages.soft_deleted_at IS NULL)
LIMIT $6;

Bound args: namespace_id (seeded), maven_repository_id (seeded), group_id='com.example', artifact_id='app', file_name='maven-metadata.xml', LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.56..16.62 rows=1 width=218) (actual time=0.015..0.016 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Nested Loop  (cost=0.56..16.62 rows=1 width=218) (actual time=0.015..0.015 rows=1 loops=1)
         Buffers: shared hit=6
         ->  Index Scan using maven_packages_p05_namespace_id_maven_repository_id_group_i_idx on maven_packages_p05 maven_packages  (cost=0.28..8.31 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=1)
               Index Cond: ((namespace_id = '57d651b3-1f89-457f-bb97-896fc561df7c'::uuid) AND (maven_repository_id = '10008'::bigint) AND (group_id = 'com.example'::text) AND (artifact_id = 'app'::text))
               Buffers: shared hit=3
         ->  Index Scan using maven_files_p05_namespace_id_maven_package_id_file_name_idx on maven_files_p05 maven_files  (cost=0.28..8.30 rows=1 width=218) (actual time=0.006..0.006 rows=1 loops=1)
               Index Cond: ((namespace_id = '57d651b3-1f89-457f-bb97-896fc561df7c'::uuid) AND (maven_package_id = maven_packages.id) AND (file_name = 'maven-metadata.xml'::text))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=200 read=1
 Planning Time: 0.468 ms
 Execution Time: 0.026 ms

Timings: planning 0.468ms, execution 0.026ms, total 0.494ms.

maven.UpsertReleaseFile (conflict SELECT, version-level)

Summary: Conflict-path read for the immutable-release upsert. Index Scan on the version-level partial unique index maven_files (namespace_id, maven_version_id, file_name), single partition, plan=actual (1/1). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=5000, maven_files=5000, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

SELECT maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name"
FROM public.maven_files
WHERE (((maven_files.namespace_id = $1::uuid) AND (maven_files.file_name = $2::text)) AND (maven_files.soft_deleted_at IS NULL)) AND (maven_files.maven_version_id = $3)
LIMIT $4;

Bound args: namespace_id (seeded), file_name='app.jar', maven_version_id (seeded), LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=207) (actual time=0.006..0.006 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using maven_files_p49_namespace_id_maven_version_id_file_name_idx on maven_files_p49 maven_files  (cost=0.28..8.30 rows=1 width=207) (actual time=0.005..0.005 rows=1 loops=1)
         Index Cond: ((namespace_id = 'b5314390-021a-40e1-ab42-098f06e86bba'::uuid) AND (maven_version_id = '25001'::bigint) AND (file_name = 'app.jar'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=92 read=1
 Planning Time: 0.268 ms
 Execution Time: 0.012 ms

Timings: planning 0.268ms, execution 0.012ms, total 0.280ms.

maven.UpsertReleaseFile (conflict SELECT, package-level)

Summary: Conflict-path read, package-level variant. Index Scan on the package-level partial unique index maven_files (namespace_id, maven_package_id, file_name) WHERE maven_version_id IS NULL, single partition, plan=actual (1/1). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=5000, maven_files=5000, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

SELECT maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name"
FROM public.maven_files
WHERE ((((maven_files.namespace_id = $1::uuid) AND (maven_files.file_name = $2::text)) AND (maven_files.soft_deleted_at IS NULL)) AND (maven_files.maven_package_id = $3)) AND (maven_files.maven_version_id IS NULL)
LIMIT $4;

Bound args: namespace_id (seeded), file_name='maven-metadata.xml', maven_package_id (seeded), LIMIT 1

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..8.30 rows=1 width=218) (actual time=0.005..0.005 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using maven_files_p21_namespace_id_maven_package_id_file_name_idx on maven_files_p21 maven_files  (cost=0.28..8.30 rows=1 width=218) (actual time=0.005..0.005 rows=1 loops=1)
         Index Cond: ((namespace_id = '9b4059ec-51e6-4a31-8b91-4ae3d37e3136'::uuid) AND (maven_package_id = '10304'::bigint) AND (file_name = 'maven-metadata.xml'::text))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=92 read=1
 Planning Time: 0.229 ms
 Execution Time: 0.010 ms

Timings: planning 0.229ms, execution 0.010ms, total 0.239ms.

maven.FindOrCreatePackage (INSERT … DO NOTHING)

Summary: INSERT … ON CONFLICT (namespace_id, maven_repository_id, group_id, artifact_id) DO NOTHING into an empty target. Single Insert node; the partial unique index serves as the conflict arbiter (not surfaced as a separate scan node). No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=0

Rendered SQL:

INSERT INTO public.maven_packages (namespace_id, maven_repository_id, group_id, artifact_id)
VALUES ($1::uuid, $2, $3::text, $4::text)
ON CONFLICT (namespace_id, maven_repository_id, group_id, artifact_id) WHERE soft_deleted_at IS NULL DO NOTHING
RETURNING maven_packages.namespace_id AS "maven_packages.namespace_id",
maven_packages.id AS "maven_packages.id",
maven_packages.maven_repository_id AS "maven_packages.maven_repository_id",
maven_packages.group_id AS "maven_packages.group_id",
maven_packages.artifact_id AS "maven_packages.artifact_id";

Bound args: namespace_id (seeded), maven_repository_id (seeded), group_id='com.new', artifact_id='pkg'

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on maven_packages  (cost=0.00..0.01 rows=1 width=112) (actual time=0.142..0.142 rows=1 loops=1)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: unique_maven_packages_ns_id_repo_id_group_id_artifact_id
   Tuples Inserted: 1
   Conflicting Tuples: 0
   Buffers: shared hit=41 dirtied=7 written=4
   ->  Result  (cost=0.00..0.01 rows=1 width=112) (actual time=0.003..0.003 rows=1 loops=1)
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.033 ms
 Trigger for constraint fk_maven_packages_maven_repository_id_maven_repositories on maven_packages_p24: time=0.131 calls=1
 Trigger for constraint fk_maven_packages_namespace_id_namespaces on maven_packages_p24: time=0.014 calls=1
 Execution Time: 0.340 ms

Timings: planning 0.033ms, execution 0.340ms, total 0.373ms.

maven.FindOrCreateVersion (INSERT … DO NOTHING)

Summary: INSERT … ON CONFLICT (namespace_id, maven_package_id, version) DO NOTHING into an empty target. Single Insert node with the partial unique arbiter. No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=0

Rendered SQL:

INSERT INTO public.maven_versions (namespace_id, maven_package_id, version)
VALUES ($1::uuid, $2, $3::text)
ON CONFLICT (namespace_id, maven_package_id, version) WHERE soft_deleted_at IS NULL DO NOTHING
RETURNING maven_versions.namespace_id AS "maven_versions.namespace_id",
maven_versions.id AS "maven_versions.id",
maven_versions.maven_package_id AS "maven_versions.maven_package_id",
maven_versions.version AS "maven_versions.version";

Bound args: namespace_id (seeded), maven_package_id (seeded), version='new-ver'

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on maven_versions  (cost=0.00..0.01 rows=1 width=184) (actual time=0.122..0.122 rows=1 loops=1)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: unique_maven_versions_ns_id_pkg_id_version
   Tuples Inserted: 1
   Conflicting Tuples: 0
   Buffers: shared hit=37 dirtied=9 written=5
   ->  Result  (cost=0.00..0.01 rows=1 width=184) (actual time=0.002..0.002 rows=1 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.029 ms
 Trigger for constraint fk_maven_versions_maven_package_id_maven_packages on maven_versions_p48: time=0.246 calls=1
 Trigger for constraint fk_maven_versions_namespace_id_namespaces on maven_versions_p48: time=0.017 calls=1
 Execution Time: 0.426 ms

Timings: planning 0.029ms, execution 0.426ms, total 0.455ms.

maven.UpsertReleaseFile / UpsertMutableFile (INSERT … DO NOTHING, version-level)

Summary: Shared first trip of UpsertReleaseFile and UpsertMutableFile (version-level). INSERT … ON CONFLICT (namespace_id, maven_version_id, file_name) DO NOTHING into an empty target. Single Insert node; the version-level partial unique index is the arbiter. No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=1, maven_files=0, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

INSERT INTO public.maven_files (namespace_id, maven_package_id, maven_version_id, blob_storage_attachment_id, blob_sha256, sha1, sha512, md5, file_name)
VALUES ($1::uuid, $2, $3, $4, $5::bytea, $6::bytea, $7::bytea, NULL::bytea, $8::text)
ON CONFLICT (namespace_id, maven_version_id, file_name) WHERE (soft_deleted_at IS NULL) AND (maven_version_id IS NOT NULL) DO NOTHING
RETURNING maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name";

Bound args: namespace_id (seeded), maven_package_id, maven_version_id, blob_storage_attachment_id (all seeded), blob_sha256/sha1/sha512 (32/20/64-byte literals), file_name='new.jar'

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on maven_files  (cost=0.00..0.01 rows=1 width=216) (actual time=0.161..0.161 rows=1 loops=1)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: unique_maven_files_ns_id_version_id_file_name
   Tuples Inserted: 1
   Conflicting Tuples: 0
   Buffers: shared hit=33 read=4 dirtied=9 written=5
   ->  Result  (cost=0.00..0.01 rows=1 width=216) (actual time=0.002..0.002 rows=1 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.024 ms
 Trigger for constraint fk_maven_files_blob_storage_attachment_id_bsa on maven_files_p07: time=0.220 calls=1
 Trigger for constraint fk_maven_files_maven_package_id_maven_packages on maven_files_p07: time=0.123 calls=1
 Trigger for constraint fk_maven_files_maven_version_id_maven_versions on maven_files_p07: time=0.212 calls=1
 Trigger for constraint fk_maven_files_namespace_id_namespaces on maven_files_p07: time=0.010 calls=1
 Execution Time: 0.765 ms

Timings: planning 0.024ms, execution 0.765ms, total 0.789ms.

maven.UpsertReleaseFile / UpsertMutableFile (INSERT … DO NOTHING, package-level)

Summary: Shared first trip, package-level. INSERT … ON CONFLICT (namespace_id, maven_package_id, file_name) WHERE maven_version_id IS NULL DO NOTHING into an empty target. Single Insert node with the package-level partial unique arbiter. No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_files=0, blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

INSERT INTO public.maven_files (namespace_id, maven_package_id, maven_version_id, blob_storage_attachment_id, blob_sha256, sha1, sha512, md5, file_name)
VALUES ($1::uuid, $2, NULL::integer, $3, $4::bytea, $5::bytea, $6::bytea, NULL::bytea, $7::text)
ON CONFLICT (namespace_id, maven_package_id, file_name) WHERE (soft_deleted_at IS NULL) AND (maven_version_id IS NULL) DO NOTHING
RETURNING maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name";

Bound args: namespace_id (seeded), maven_package_id, blob_storage_attachment_id (seeded), blob_sha256/sha1/sha512 (32/20/64-byte literals), file_name='maven-metadata.xml'

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on maven_files  (cost=0.00..0.01 rows=1 width=216) (actual time=0.145..0.145 rows=1 loops=1)
   Conflict Resolution: NOTHING
   Conflict Arbiter Indexes: unique_maven_files_ns_id_package_id_file_name_when_ver_null
   Tuples Inserted: 1
   Conflicting Tuples: 0
   Buffers: shared hit=33 read=4 dirtied=9 written=5
   ->  Result  (cost=0.00..0.01 rows=1 width=216) (actual time=0.002..0.002 rows=1 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.020 ms
 Trigger for constraint fk_maven_files_blob_storage_attachment_id_bsa on maven_files_p25: time=0.063 calls=1
 Trigger for constraint fk_maven_files_maven_package_id_maven_packages on maven_files_p25: time=0.102 calls=1
 Trigger for constraint fk_maven_files_maven_version_id_maven_versions on maven_files_p25: time=0.033 calls=1
 Trigger for constraint fk_maven_files_namespace_id_namespaces on maven_files_p25: time=0.008 calls=1
 Execution Time: 0.359 ms

Timings: planning 0.020ms, execution 0.359ms, total 0.379ms.

maven.UpsertMutableFile (INSERT … DO UPDATE, version-level)

Summary: Second trip of the mutable upsert: INSERT … ON CONFLICT … DO UPDATE against 1 conflicting + 49 sibling rows, so the update path fires. Single Insert (on-conflict update) node, last-write-wins SET of the blob/digest columns, single partition. No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=1, maven_versions=50, maven_files=50 (1 conflicting + 49 siblings), blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

INSERT INTO public.maven_files (namespace_id, maven_package_id, maven_version_id, blob_storage_attachment_id, blob_sha256, sha1, sha512, md5, file_name)
VALUES ($1::uuid, $2, $3, $4, $5::bytea, $6::bytea, $7::bytea, NULL::bytea, $8::text)
ON CONFLICT (namespace_id, maven_version_id, file_name) WHERE (soft_deleted_at IS NULL) AND (maven_version_id IS NOT NULL) DO UPDATE
SET blob_storage_attachment_id = excluded.blob_storage_attachment_id,
blob_sha256 = excluded.blob_sha256,
sha1 = excluded.sha1,
sha512 = excluded.sha512,
md5 = excluded.md5
RETURNING maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name";

Bound args: namespace_id (seeded), maven_package_id, maven_version_id, blob_storage_attachment_id (seeded), digests (literals), file_name='snap.jar' (matches the 1 conflicting row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on maven_files  (cost=0.00..0.01 rows=1 width=216) (actual time=0.262..0.263 rows=1 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: unique_maven_files_ns_id_version_id_file_name
   Tuples Inserted: 0
   Conflicting Tuples: 1
   Buffers: shared hit=92 read=2
   ->  Result  (cost=0.00..0.01 rows=1 width=216) (actual time=0.002..0.002 rows=1 loops=1)
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.043 ms
 Trigger for constraint fk_maven_files_blob_storage_attachment_id_bsa on maven_files_p16: time=0.033 calls=1
 Trigger for constraint fk_maven_files_maven_package_id_maven_packages on maven_files_p16: time=0.146 calls=1
 Trigger for constraint fk_maven_files_maven_version_id_maven_versions on maven_files_p16: time=0.209 calls=1
 Trigger for constraint fk_maven_files_namespace_id_namespaces on maven_files_p16: time=0.005 calls=1
 Execution Time: 0.720 ms

Timings: planning 0.043ms, execution 0.720ms, total 0.763ms.

maven.UpsertMutableFile (INSERT … DO UPDATE, package-level)

Summary: Package-level mutable overwrite against 1 conflicting + 49 sibling package-level rows. Single Insert (on-conflict update) node updating the blob/digest columns, single partition. No anomalies.

Seed shape: namespaces=1, repositories=1, maven_repositories=1, maven_packages=50, maven_files=50 (1 conflicting + 49 siblings), blob_storage_blobs=1, blob_storage_attachments=1

Rendered SQL:

INSERT INTO public.maven_files (namespace_id, maven_package_id, maven_version_id, blob_storage_attachment_id, blob_sha256, sha1, sha512, md5, file_name)
VALUES ($1::uuid, $2, NULL::integer, $3, $4::bytea, $5::bytea, $6::bytea, NULL::bytea, $7::text)
ON CONFLICT (namespace_id, maven_package_id, file_name) WHERE (soft_deleted_at IS NULL) AND (maven_version_id IS NULL) DO UPDATE
SET blob_storage_attachment_id = excluded.blob_storage_attachment_id,
blob_sha256 = excluded.blob_sha256,
sha1 = excluded.sha1,
sha512 = excluded.sha512,
md5 = excluded.md5
RETURNING maven_files.namespace_id AS "maven_files.namespace_id",
maven_files.id AS "maven_files.id",
maven_files.maven_package_id AS "maven_files.maven_package_id",
maven_files.maven_version_id AS "maven_files.maven_version_id",
maven_files.blob_storage_attachment_id AS "maven_files.blob_storage_attachment_id",
maven_files.blob_sha256 AS "maven_files.blob_sha256",
maven_files.sha1 AS "maven_files.sha1",
maven_files.sha512 AS "maven_files.sha512",
maven_files.md5 AS "maven_files.md5",
maven_files.file_name AS "maven_files.file_name";

Bound args: namespace_id (seeded), maven_package_id, blob_storage_attachment_id (seeded), digests (literals), file_name='maven-metadata.xml' (matches the 1 conflicting row)

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Insert on maven_files  (cost=0.00..0.01 rows=1 width=216) (actual time=0.152..0.153 rows=1 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: unique_maven_files_ns_id_package_id_file_name_when_ver_null
   Tuples Inserted: 0
   Conflicting Tuples: 1
   Buffers: shared hit=88
   ->  Result  (cost=0.00..0.01 rows=1 width=216) (actual time=0.002..0.002 rows=1 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.027 ms
 Trigger for constraint fk_maven_files_blob_storage_attachment_id_bsa on maven_files_p38: time=0.024 calls=1
 Trigger for constraint fk_maven_files_maven_package_id_maven_packages on maven_files_p38: time=0.220 calls=1
 Trigger for constraint fk_maven_files_namespace_id_namespaces on maven_files_p38: time=0.005 calls=1
 Execution Time: 0.412 ms

Timings: planning 0.027ms, execution 0.412ms, total 0.439ms.

Query notes:

• maven.ListLiveVersions (keyset pagination): the plan depends on the package's live-version count relative to its partition. At low per-package counts both pages plan as a Bitmap Index Scan on (namespace_id, maven_package_id, …) plus a quicksort on id, with the id > AfterID cursor as a post-scan Filter; the sort is trivial. When a package holds many versions and dominates its partition, the planner switches to an ordered Index Scan on the (id, namespace_id) primary key, applying id > AfterID as a range bound and early terminating at the LIMIT — no sort, no full-set materialization. No index covers (namespace_id, maven_package_id, id), so the keyset is never an index range seek on the query's own equality columns; a (namespace_id, maven_package_id, id) WHERE soft_deleted_at IS NULL index would serve that shape directly, but it only wins materially in a narrow case — a package with many more live versions than the page size that is also a small fraction of a large partition (verified empirically: at LIMIT ≈ package size, or when the package dominates its partition, the planner keeps bitmap+sort or the primary-key scan respectively). Correct in all cases; the dedicated index is a targeted optimization, not a fix for a regression.
• maven.CountLiveVersions: under the realistic seed (many packages per partition) the count uses the (namespace_id, maven_package_id, …) index. With every live version under the counted package the predicate matches ~100% of the partition, where a Seq Scan is the planner's correct choice — a seed artifact, not a missing index.
• maven.FindPackageFile: Seq Scan on maven_packages is over the single seeded ancestor row; at production package cardinality the planner uses the package unique index (demonstrated by FindPackageMetadataFile).