chore(datastore): npm file and dist-tag management reads

Hayley Swimelarrequested to merge
hswimelar/s17-phase3-step-9-npm-files-dist-tags-reads into main

Why

S17 Phase 3 serves the stored npm rows the S11 publish path writes. Step 9 is the file and dist-tag half: the reads behind the version-detail and package-detail views the monolith slices consume. The existing by-parent listers (NpmFilesByVersion, NpmTagsByPackage) are protocol-shaped, carrying a bare cursor and neither the joined blob size nor the version string, and the npm tarball and packument paths depend on them unchanged, so these are new methods alongside them.

Step 9 of the merged Phase 3 plan, tracked in S17 Phase 3: format artifact reads (#312 - closed) • Hayley Swimelar. Datastore only. The handlers land with Steps 11 and 12, so nothing is wired into handler.go or wire_management.go here.

What (the non-obvious parts)

  • Lists scope to the parent, details verify the chain. Each list filters (namespace_id, parent_id) alone, so it stays on an existing unique index. The chain check runs once per request in the handler's parent read, which is the spec's resolution flow. FindNpmFileByID and FindNpmDistTagByID own that check, joining up to the per-format child row with the soft-delete exclusion at every level.
  • The dist-tag version join carries no soft-delete filter, per the spec. A soft-deleted parent package hides the whole subtree, and filtering the join would drop the row instead and serve a dist-tags list with a hole in it. Two tests pin that.
  • A cursor with an empty key is rejected. File and tag names are non-empty, so an empty boundary served the first page ascending and an empty page descending, both of which read as data rather than as the corrupt cursor they are. The handler still owes a 400 on decode, mirroring decodeListCursor.
  • Digests and checksums stay raw bytes. Step 3's formatDigest and formatChecksum serialize them at the handler.
  • 2433 reviewable LOC, past the 500 ceiling. 704 lines are production code and 1729 are tests. The plan scopes files and dist-tags as one step.

Spec coverage

Requirement Test
File size equals the stored blob size TestNpmFileStore_ListNpmFilesByVersion/returns the published tarball with the size of its blob, TestNpmFileStore_FindNpmFileByID/returns the file with its blob size when the chain matches
Dist-tags resolve version_id and the version string TestNpmTagStore_ListNpmDistTagsByPackage/{returns each dist-tag with the version it binds, resolves each dist-tag to its own version}
Soft-delete exclusion, including soft-deleted parents TestNpmFileStore_ListNpmFilesByVersion/excludes soft-deleted files, TestNpmFileStore_FindNpmFileByID/wraps ErrNotFound when the {file, parent version, parent package} is soft-deleted, TestNpmTagStore_FindNpmDistTagByID/wraps ErrNotFound when the parent package is soft-deleted
No defensive soft-delete filter on the version join TestNpmTagStore_ListNpmDistTagsByPackage/keeps a dist-tag whose version is soft-deleted, TestNpmTagStore_FindNpmDistTagByID/returns a dist-tag whose version is soft-deleted
Chain scoping across repositories and namespaces TestNpmFileStore_FindNpmFileByID/wraps ErrNotFound for a file in another {repository, namespace}, TestNpmTagStore_FindNpmDistTagByID/wraps ErrNotFound for a dist-tag in another {repository, namespace}, both lists' is scoped to the namespace
Keyset pages walk gap-free in both directions, hasMore is exact both suites' walks the ... keyset {ascending, descending} and hasMore is false when the page exactly fills the limit
Every sort is index-backed at a deep page TestNpmFileStore_ListNpmFilesByVersion_DeepPageIsIndexBacked, TestNpmTagStore_ListNpmDistTagsByPackage_DeepPageIsIndexBacked
limit, order, and cursor validation TestNpmFileStore_ListNpmFilesByVersion_ArgumentGuards, TestNpmTagStore_ListNpmDistTagsByPackage_ArgumentGuards, both Find*_ArgumentGuards
Statement shape holds (index-usable predicates, no chain join on the lists) TestListNpmFilesByVersionStmt, TestFindNpmFileByIDStmt, TestListNpmDistTagsByPackageStmt, TestFindNpmDistTagByIDStmt

Test plan

go test ./internal/datastore/
go test -tags=integration ./internal/datastore/ -run 'TestNpmFileStore|TestNpmTagStore'

Integration tests seed through the real NpmPublishCommitter, so the rows under assertion are the rows the service itself writes.

The EXPLAIN cases are worth a second look. npm_files also carries a plain (namespace_id, file_name) index that satisfies the same ORDER BY, so a fixture with every file under one version cannot tell it apart from the version-scoped unique index. The fixture therefore spreads decoy files across 1000 other versions, named to sort immediately on either side of the cursor, and the test asserts the plan seeks (namespace_id, npm_version_id, file_name) with no Rows Removed by Filter.

Context for LLM agents

Design rationale

Per-store sort-order enums, no sort-column enum. The spec gives the files list one sort value (file_name) and the dist-tags list one (name), so the direction is the whole sort vocabulary and a one-member SortColumn enum would be ceremony. NpmFileSortOrder and NpmDistTagSortOrder are per-store rather than shared because Steps 4 to 9 land in parallel and must merge in any order: whichever step defined a shared enum would conflict with the rest.

Cursors carry the sort key with no id tiebreaker. file_name is unique per (namespace_id, npm_version_id) among active rows and name is unique per package, so each is a total keyset on its own. A row-value bound over (key, id) would reintroduce id and force a post-index filter the unique-index order does not need. This is the branch repositoryKeysetBound already takes for name.

Rejected: joining the parent chain in the list queries. It would make each list self-sufficient against a caller that skipped the parent read, at the cost of two extra joins on the driving scan and a plan the optimizer could reorder into a hash join plus a sort. The spec's resolution flow puts the chain check before the artifact query, and the plan assigns chain verification to the detail reads, so the list stays on the partial unique index.

Rejected: a shared generic page-trim helper. The limit+1 probe, the trim, and the row mapping are about eight lines duplicated between the two stores (dupl flags them, annotated). RepositoryStore.List does the same work inline, and five sibling Phase 3 datastore steps land beside these, so an abstraction introduced here would either be duplicated or force a cross-step edit.

The empty-cursor guard diverges from RepositoryStore.List, which accepts a boundary with an empty Name and relies on decodeListCursor to reject it. The divergence is deliberate: a corrupt descending cursor here returns an empty page, which reads as "this version has no files", and a loud store error is easier to diagnose than a plausible empty list. Step 12's handler should still reject it as a 400 so the guard stays unreachable.

Non-goals

  • No handler, route, or wiring changes. Steps 11 and 12 own handler.go and wire_management.go. The stores here have no callers yet, which is why the blast radius of a bug is zero until those steps land.
  • No changes to NpmFilesByVersion or NpmTagsByPackage. The npm protocol path (tarball download, packument generation) depends on their current shapes.
  • No new index. Both orders are backed by existing unique indexes (unique_npm_files_ns_id_version_id_file_name, unique_npm_tags_ns_id_pkg_id_name), so this step does not depend on the Step 2 index migration.
  • No digest or checksum formatting. Step 3 adds formatDigest and formatChecksum in internal/managementapi/artifact.go. These stores return raw bytes so the helpers are not duplicated.
  • No e2e catalog change. The catalogs cover protocol-client journeys. Artifact browsing is a UI journey that lands with the monolith slices consuming these endpoints, per the plan's Testing Strategy.
  • No conformance run. Phase 3 is management-API surface, not Maven, npm, or OCI protocol behavior.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION in .gitlab-ci-other-versions.yml), seeded through the real npm publish committer plus bulk fixtures, then torn down. The seed is deliberately larger than the default: at a few hundred rows the planner picks materialize-and-filter shapes a real namespace never gets. Never-executed partition subplans are elided from the plan text, and every executed node and both timings are verbatim. This MR adds no migration, so there is no ### Migrations section.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.NpmFileStore.ListNpmFilesByVersion.asc Nested Loop npm_files_p38_namespace_id_npm_version_id_file_name_idx 21 / 21 3412.29 0.750ms 82 / 1 1 per npm table, 18 of 64 blob partitions probed
datastore.NpmFileStore.ListNpmFilesByVersion.desc Nested Loop npm_files_p38_namespace_id_npm_version_id_file_name_idx 21 / 21 3412.29 0.773ms 84 / 1 1 per npm table, 18 of 64 blob partitions probed
datastore.NpmFileStore.FindNpmFileByID Nested Loop npm_files_p38_pkey 1 / 1 549.37 0.469ms 14 / 6 1 per npm table, 1 of 64 blob partitions probed
datastore.NpmTagStore.ListNpmDistTagsByPackage.asc Nested Loop npm_tags_p38_namespace_id_npm_package_id_name_idx 21 / 21 35.08 0.104ms 8 / 1 1 per npm table
datastore.NpmTagStore.ListNpmDistTagsByPackage.desc Nested Loop npm_tags_p38_namespace_id_npm_package_id_name_idx 21 / 21 48.11 0.055ms 8 / 0 1 per npm table
datastore.NpmTagStore.FindNpmDistTagByID Nested Loop npm_tags_p38_pkey 1 / 1 25.07 0.046ms 10 / 0 1 per npm table

Query notes: no anomalies. Every list is a keyset Index Scan (forward ascending, Backward descending) on the unique index its ORDER BY matches, with no Sort node and no Rows Removed by Filter, and both detail reads walk the parent chain as primary-key seeks. Each npm table prunes to one partition. blob_storage_blobs is hash-partitioned on sha256 rather than namespace_id (ADR-007), so its Append lists all 64 partitions and run-time pruning executes only the ones a probed digest lands in. Planning dominates execution for the blob-joining queries, around 2ms against 0.8ms, for the same reason, and database/sql statement caching amortizes it per connection.

datastore.NpmFileStore.ListNpmFilesByVersion.asc

Seed shape: npm_files=100201, npm_tags=25201, npm_versions=50001, npm_packages=5001, blob_storage_blobs=200202, blob_storage_attachments=202

Rendered SQL:

SELECT npm_files.namespace_id AS "npm_files.namespace_id",
     npm_files.id AS "npm_files.id",
     npm_files.npm_version_id AS "npm_files.npm_version_id",
     npm_files.blob_storage_attachment_id AS "npm_files.blob_storage_attachment_id",
     npm_files.soft_deleted_at AS "npm_files.soft_deleted_at",
     npm_files.created_at AS "npm_files.created_at",
     npm_files.file_name AS "npm_files.file_name",
     npm_files.blob_sha256 AS "npm_files.blob_sha256",
     blob_storage_blobs.size AS "blob_storage_blobs.size"
FROM public.npm_files
     INNER JOIN public.blob_storage_blobs ON ((blob_storage_blobs.namespace_id = npm_files.namespace_id) AND (blob_storage_blobs.sha256 = npm_files.blob_sha256))
WHERE (((npm_files.namespace_id = $1::uuid) AND (npm_files.npm_version_id = $2::uuid)) AND (npm_files.soft_deleted_at IS NULL)) AND (npm_files.file_name > $3::text)
ORDER BY npm_files.file_name ASC
LIMIT $4;

Bound args: [7787cc12-ea9b-41a3-a1ad-0553128a11da 019faac7-e15e-7eae-8d18-191996bbf2af pkg-0100.tgz 21]

Plan (EXPLAIN (ANALYZE, BUFFERS), never-executed partition subplans elided):

Limit  (cost=0.71..3412.29 rows=21 width=126) (actual time=0.049..0.284 rows=21 loops=1)
  Buffers: shared hit=82 read=1
  ->  Nested Loop  (cost=0.71..17383.51 rows=107 width=126) (actual time=0.048..0.282 rows=21 loops=1)
        Buffers: shared hit=82 read=1
        ->  Index Scan using npm_files_p38_namespace_id_npm_version_id_file_name_idx on npm_files_p38 npm_files  (cost=0.42..195.64 rows=107 width=118) (actual time=0.027..0.035 rows=21 loops=1)
              Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (npm_version_id = '019faac7-e15e-7eae-8d18-191996bbf2af'::uuid) AND (file_name > 'pkg-0100.tgz'::text))
              Buffers: shared hit=19 read=1
        ->  Memoize  (cost=0.29..230.15 rows=64 width=57) (actual time=0.011..0.011 rows=1 loops=21)
              Cache Key: npm_files.blob_sha256
              Cache Mode: logical
              Hits: 0  Misses: 21  Evictions: 0  Overflows: 0  Memory Usage: 4kB
              Buffers: shared hit=63
              ->  Append  (cost=0.28..230.14 rows=64 width=57) (actual time=0.010..0.010 rows=1 loops=21)
                    Buffers: shared hit=63
                    ->  Index Scan using blob_storage_blobs_p03_namespace_id_sha256_idx on blob_storage_blobs_p03 blob_storage_blobs_4  (cost=0.28..3.59 rows=1 width=57) (actual time=0.010..0.010 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p06_namespace_id_sha256_idx on blob_storage_blobs_p06 blob_storage_blobs_7  (cost=0.28..3.63 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p08_namespace_id_sha256_idx on blob_storage_blobs_p08 blob_storage_blobs_9  (cost=0.28..3.51 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p21_namespace_id_sha256_idx on blob_storage_blobs_p21 blob_storage_blobs_22  (cost=0.28..3.66 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=2)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=6
                    ->  Index Scan using blob_storage_blobs_p26_namespace_id_sha256_idx on blob_storage_blobs_p26 blob_storage_blobs_27  (cost=0.28..3.55 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p31_namespace_id_sha256_idx on blob_storage_blobs_p31 blob_storage_blobs_32  (cost=0.28..3.48 rows=1 width=57) (actual time=0.011..0.011 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p33_namespace_id_sha256_idx on blob_storage_blobs_p33 blob_storage_blobs_34  (cost=0.28..3.63 rows=1 width=57) (actual time=0.010..0.010 rows=1 loops=2)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=6
                    ->  Index Scan using blob_storage_blobs_p34_namespace_id_sha256_idx on blob_storage_blobs_p34 blob_storage_blobs_35  (cost=0.28..3.59 rows=1 width=57) (actual time=0.006..0.006 rows=1 loops=2)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=6
                    ->  Index Scan using blob_storage_blobs_p40_namespace_id_sha256_idx on blob_storage_blobs_p40 blob_storage_blobs_41  (cost=0.28..3.70 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p44_namespace_id_sha256_idx on blob_storage_blobs_p44 blob_storage_blobs_45  (cost=0.28..3.74 rows=1 width=57) (actual time=0.011..0.011 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p53_namespace_id_sha256_idx on blob_storage_blobs_p53 blob_storage_blobs_54  (cost=0.28..3.66 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p54_namespace_id_sha256_idx on blob_storage_blobs_p54 blob_storage_blobs_55  (cost=0.28..3.66 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p55_namespace_id_sha256_idx on blob_storage_blobs_p55 blob_storage_blobs_56  (cost=0.28..3.66 rows=1 width=57) (actual time=0.007..0.007 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p56_namespace_id_sha256_idx on blob_storage_blobs_p56 blob_storage_blobs_57  (cost=0.28..3.55 rows=1 width=57) (actual time=0.017..0.017 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p57_namespace_id_sha256_idx on blob_storage_blobs_p57 blob_storage_blobs_58  (cost=0.28..3.59 rows=1 width=57) (actual time=0.010..0.010 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p60_namespace_id_sha256_idx on blob_storage_blobs_p60 blob_storage_blobs_61  (cost=0.28..3.66 rows=1 width=57) (actual time=0.011..0.011 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p61_namespace_id_sha256_idx on blob_storage_blobs_p61 blob_storage_blobs_62  (cost=0.28..3.59 rows=1 width=57) (actual time=0.010..0.010 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p62_namespace_id_sha256_idx on blob_storage_blobs_p62 blob_storage_blobs_63  (cost=0.28..3.51 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
Planning:
  Buffers: shared hit=1381
Planning Time: 6.826 ms
Execution Time: 0.750 ms
         [46 never-executed blob_storage_blobs partition subplans elided]

Timings: planning 6.826ms, execution 0.750ms, total 7.576ms.

datastore.NpmFileStore.ListNpmFilesByVersion.desc

Seed shape: npm_files=100201, npm_tags=25201, npm_versions=50001, npm_packages=5001, blob_storage_blobs=200202, blob_storage_attachments=202

Rendered SQL:

SELECT npm_files.namespace_id AS "npm_files.namespace_id",
     npm_files.id AS "npm_files.id",
     npm_files.npm_version_id AS "npm_files.npm_version_id",
     npm_files.blob_storage_attachment_id AS "npm_files.blob_storage_attachment_id",
     npm_files.soft_deleted_at AS "npm_files.soft_deleted_at",
     npm_files.created_at AS "npm_files.created_at",
     npm_files.file_name AS "npm_files.file_name",
     npm_files.blob_sha256 AS "npm_files.blob_sha256",
     blob_storage_blobs.size AS "blob_storage_blobs.size"
FROM public.npm_files
     INNER JOIN public.blob_storage_blobs ON ((blob_storage_blobs.namespace_id = npm_files.namespace_id) AND (blob_storage_blobs.sha256 = npm_files.blob_sha256))
WHERE (((npm_files.namespace_id = $1::uuid) AND (npm_files.npm_version_id = $2::uuid)) AND (npm_files.soft_deleted_at IS NULL)) AND (npm_files.file_name < $3::text)
ORDER BY npm_files.file_name DESC
LIMIT $4;

Bound args: [7787cc12-ea9b-41a3-a1ad-0553128a11da 019faac7-e15e-7eae-8d18-191996bbf2af pkg-0100.tgz 21]

Plan (EXPLAIN (ANALYZE, BUFFERS), never-executed partition subplans elided):

Limit  (cost=0.71..3412.29 rows=21 width=126) (actual time=0.040..0.261 rows=21 loops=1)
  Buffers: shared hit=84 read=1
  ->  Nested Loop  (cost=0.71..17383.51 rows=107 width=126) (actual time=0.039..0.258 rows=21 loops=1)
        Buffers: shared hit=84 read=1
        ->  Index Scan Backward using npm_files_p38_namespace_id_npm_version_id_file_name_idx on npm_files_p38 npm_files  (cost=0.42..195.64 rows=107 width=118) (actual time=0.017..0.034 rows=21 loops=1)
              Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (npm_version_id = '019faac7-e15e-7eae-8d18-191996bbf2af'::uuid) AND (file_name < 'pkg-0100.tgz'::text))
              Buffers: shared hit=21 read=1
        ->  Memoize  (cost=0.29..230.15 rows=64 width=57) (actual time=0.010..0.010 rows=1 loops=21)
              Cache Key: npm_files.blob_sha256
              Cache Mode: logical
              Hits: 0  Misses: 21  Evictions: 0  Overflows: 0  Memory Usage: 4kB
              Buffers: shared hit=63
              ->  Append  (cost=0.28..230.14 rows=64 width=57) (actual time=0.009..0.009 rows=1 loops=21)
                    Buffers: shared hit=63
                    ->  Index Scan using blob_storage_blobs_p05_namespace_id_sha256_idx on blob_storage_blobs_p05 blob_storage_blobs_6  (cost=0.28..3.59 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=2)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=6
                    ->  Index Scan using blob_storage_blobs_p06_namespace_id_sha256_idx on blob_storage_blobs_p06 blob_storage_blobs_7  (cost=0.28..3.63 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=2)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=6
                    ->  Index Scan using blob_storage_blobs_p07_namespace_id_sha256_idx on blob_storage_blobs_p07 blob_storage_blobs_8  (cost=0.28..3.55 rows=1 width=57) (actual time=0.011..0.011 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p11_namespace_id_sha256_idx on blob_storage_blobs_p11 blob_storage_blobs_12  (cost=0.28..3.66 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p12_namespace_id_sha256_idx on blob_storage_blobs_p12 blob_storage_blobs_13  (cost=0.28..3.55 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p15_namespace_id_sha256_idx on blob_storage_blobs_p15 blob_storage_blobs_16  (cost=0.28..3.55 rows=1 width=57) (actual time=0.007..0.007 rows=1 loops=2)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=6
                    ->  Index Scan using blob_storage_blobs_p16_namespace_id_sha256_idx on blob_storage_blobs_p16 blob_storage_blobs_17  (cost=0.28..3.66 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p18_namespace_id_sha256_idx on blob_storage_blobs_p18 blob_storage_blobs_19  (cost=0.28..3.63 rows=1 width=57) (actual time=0.010..0.010 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p27_namespace_id_sha256_idx on blob_storage_blobs_p27 blob_storage_blobs_28  (cost=0.28..3.59 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p28_namespace_id_sha256_idx on blob_storage_blobs_p28 blob_storage_blobs_29  (cost=0.28..3.66 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p31_namespace_id_sha256_idx on blob_storage_blobs_p31 blob_storage_blobs_32  (cost=0.28..3.48 rows=1 width=57) (actual time=0.012..0.012 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p45_namespace_id_sha256_idx on blob_storage_blobs_p45 blob_storage_blobs_46  (cost=0.28..3.70 rows=1 width=57) (actual time=0.011..0.011 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p47_namespace_id_sha256_idx on blob_storage_blobs_p47 blob_storage_blobs_48  (cost=0.28..3.51 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p48_namespace_id_sha256_idx on blob_storage_blobs_p48 blob_storage_blobs_49  (cost=0.28..3.59 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p52_namespace_id_sha256_idx on blob_storage_blobs_p52 blob_storage_blobs_53  (cost=0.28..3.59 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p54_namespace_id_sha256_idx on blob_storage_blobs_p54 blob_storage_blobs_55  (cost=0.28..3.66 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p56_namespace_id_sha256_idx on blob_storage_blobs_p56 blob_storage_blobs_57  (cost=0.28..3.55 rows=1 width=57) (actual time=0.008..0.008 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
                    ->  Index Scan using blob_storage_blobs_p61_namespace_id_sha256_idx on blob_storage_blobs_p61 blob_storage_blobs_62  (cost=0.28..3.59 rows=1 width=57) (actual time=0.007..0.007 rows=1 loops=1)
                          Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                          Buffers: shared hit=3
Planning:
  Buffers: shared hit=5
Planning Time: 2.274 ms
Execution Time: 0.773 ms
         [46 never-executed blob_storage_blobs partition subplans elided]

Timings: planning 2.274ms, execution 0.773ms, total 3.047ms.

datastore.NpmFileStore.FindNpmFileByID

Seed shape: npm_files=100201, npm_tags=25201, npm_versions=50001, npm_packages=5001, blob_storage_blobs=200202, blob_storage_attachments=202

Rendered SQL:

SELECT npm_files.namespace_id AS "npm_files.namespace_id",
     npm_files.id AS "npm_files.id",
     npm_files.npm_version_id AS "npm_files.npm_version_id",
     npm_files.blob_storage_attachment_id AS "npm_files.blob_storage_attachment_id",
     npm_files.soft_deleted_at AS "npm_files.soft_deleted_at",
     npm_files.created_at AS "npm_files.created_at",
     npm_files.file_name AS "npm_files.file_name",
     npm_files.blob_sha256 AS "npm_files.blob_sha256",
     blob_storage_blobs.size AS "blob_storage_blobs.size"
FROM public.npm_files
     INNER JOIN public.npm_versions ON (((npm_versions.id = npm_files.npm_version_id) AND (npm_versions.namespace_id = npm_files.namespace_id)) AND (npm_versions.soft_deleted_at IS NULL))
     INNER JOIN public.npm_packages ON (((npm_packages.id = npm_versions.npm_package_id) AND (npm_packages.namespace_id = npm_files.namespace_id)) AND (npm_packages.soft_deleted_at IS NULL))
     INNER JOIN public.blob_storage_blobs ON ((blob_storage_blobs.namespace_id = npm_files.namespace_id) AND (blob_storage_blobs.sha256 = npm_files.blob_sha256))
WHERE (((npm_files.namespace_id = $1::uuid) AND (npm_files.id = $2::uuid)) AND (npm_files.soft_deleted_at IS NULL)) AND (npm_packages.npm_repository_id = $3::uuid)
LIMIT $4;

Bound args: [7787cc12-ea9b-41a3-a1ad-0553128a11da 8680744b-6047-4621-9af5-6d96fb40ac47 019faac7-e14e-707f-9fec-c89fcee7069a 1]

Plan (EXPLAIN (ANALYZE, BUFFERS), never-executed partition subplans elided):

Limit  (cost=1.40..549.37 rows=1 width=126) (actual time=0.046..0.048 rows=1 loops=1)
  Buffers: shared hit=14
  ->  Nested Loop  (cost=1.40..549.37 rows=1 width=126) (actual time=0.046..0.047 rows=1 loops=1)
        Buffers: shared hit=14
        ->  Nested Loop  (cost=1.11..17.21 rows=1 width=118) (actual time=0.033..0.034 rows=1 loops=1)
              Buffers: shared hit=11
              ->  Nested Loop  (cost=0.83..16.88 rows=1 width=134) (actual time=0.024..0.024 rows=1 loops=1)
                    Buffers: shared hit=8
                    ->  Index Scan using npm_files_p38_pkey on npm_files_p38 npm_files  (cost=0.42..8.44 rows=1 width=118) (actual time=0.012..0.012 rows=1 loops=1)
                          Index Cond: ((id = '8680744b-6047-4621-9af5-6d96fb40ac47'::uuid) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                          Filter: (soft_deleted_at IS NULL)
                          Buffers: shared hit=4
                    ->  Index Scan using npm_versions_p38_pkey on npm_versions_p38 npm_versions  (cost=0.41..8.43 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=1)
                          Index Cond: ((id = npm_files.npm_version_id) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                          Filter: (soft_deleted_at IS NULL)
                          Buffers: shared hit=4
              ->  Index Scan using npm_packages_p38_pkey on npm_packages_p38 npm_packages  (cost=0.28..0.31 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)
                    Index Cond: ((id = npm_versions.npm_package_id) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (npm_repository_id = '019faac7-e14e-707f-9fec-c89fcee7069a'::uuid))
                    Buffers: shared hit=3
        ->  Append  (cost=0.28..531.52 rows=64 width=57) (actual time=0.010..0.011 rows=1 loops=1)
              Buffers: shared hit=3
              ->  Index Scan using blob_storage_blobs_p16_namespace_id_sha256_idx on blob_storage_blobs_p16 blob_storage_blobs_17  (cost=0.28..8.30 rows=1 width=57) (actual time=0.009..0.009 rows=1 loops=1)
                    Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (sha256 = npm_files.blob_sha256))
                    Buffers: shared hit=3
Planning:
  Buffers: shared hit=520 read=6 written=4
Planning Time: 3.933 ms
Execution Time: 0.469 ms
         [63 never-executed blob_storage_blobs partition subplans elided]

Timings: planning 3.933ms, execution 0.469ms, total 4.402ms.

datastore.NpmTagStore.ListNpmDistTagsByPackage.asc

Seed shape: npm_files=100201, npm_tags=25201, npm_versions=50001, npm_packages=5001, blob_storage_blobs=200202, blob_storage_attachments=202

Rendered SQL:

SELECT npm_tags.namespace_id AS "npm_tags.namespace_id",
     npm_tags.id AS "npm_tags.id",
     npm_tags.npm_package_id AS "npm_tags.npm_package_id",
     npm_tags.npm_version_id AS "npm_tags.npm_version_id",
     npm_tags.name AS "npm_tags.name",
     npm_versions.version AS "npm_versions.version"
FROM public.npm_tags
     INNER JOIN public.npm_versions ON ((npm_versions.id = npm_tags.npm_version_id) AND (npm_versions.namespace_id = npm_tags.namespace_id))
WHERE ((npm_tags.namespace_id = $1::uuid) AND (npm_tags.npm_package_id = $2::uuid)) AND (npm_tags.name > $3::text)
ORDER BY npm_tags.name ASC
LIMIT $4;

Bound args: [7787cc12-ea9b-41a3-a1ad-0553128a11da 019faac7-e15d-7a2e-9c19-882031e7b688 tag-0100 21]

Plan (EXPLAIN (ANALYZE, BUFFERS), never-executed partition subplans elided):

Limit  (cost=0.84..35.08 rows=21 width=84) (actual time=0.077..0.087 rows=21 loops=1)
  Buffers: shared hit=8 read=1 written=1
  ->  Nested Loop  (cost=0.84..261.76 rows=160 width=84) (actual time=0.076..0.086 rows=21 loops=1)
        Buffers: shared hit=8 read=1 written=1
        ->  Index Scan using npm_tags_p38_namespace_id_npm_package_id_name_idx on npm_tags_p38 npm_tags  (cost=0.41..242.89 rows=160 width=73) (actual time=0.064..0.068 rows=21 loops=1)
              Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (npm_package_id = '019faac7-e15d-7a2e-9c19-882031e7b688'::uuid) AND (name > 'tag-0100'::text))
              Buffers: shared hit=4 read=1 written=1
        ->  Memoize  (cost=0.42..7.47 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=21)
              Cache Key: npm_tags.npm_version_id
              Cache Mode: logical
              Hits: 20  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
              Buffers: shared hit=4
              ->  Index Scan using npm_versions_p38_pkey on npm_versions_p38 npm_versions  (cost=0.41..7.46 rows=1 width=43) (actual time=0.008..0.008 rows=1 loops=1)
                    Index Cond: ((id = npm_tags.npm_version_id) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                    Buffers: shared hit=4
Planning:
  Buffers: shared hit=12
Planning Time: 0.216 ms
Execution Time: 0.104 ms

Timings: planning 0.216ms, execution 0.104ms, total 0.32ms.

datastore.NpmTagStore.ListNpmDistTagsByPackage.desc

Seed shape: npm_files=100201, npm_tags=25201, npm_versions=50001, npm_packages=5001, blob_storage_blobs=200202, blob_storage_attachments=202

Rendered SQL:

SELECT npm_tags.namespace_id AS "npm_tags.namespace_id",
     npm_tags.id AS "npm_tags.id",
     npm_tags.npm_package_id AS "npm_tags.npm_package_id",
     npm_tags.npm_version_id AS "npm_tags.npm_version_id",
     npm_tags.name AS "npm_tags.name",
     npm_versions.version AS "npm_versions.version"
FROM public.npm_tags
     INNER JOIN public.npm_versions ON ((npm_versions.id = npm_tags.npm_version_id) AND (npm_versions.namespace_id = npm_tags.namespace_id))
WHERE ((npm_tags.namespace_id = $1::uuid) AND (npm_tags.npm_package_id = $2::uuid)) AND (npm_tags.name < $3::text)
ORDER BY npm_tags.name DESC
LIMIT $4;

Bound args: [7787cc12-ea9b-41a3-a1ad-0553128a11da 019faac7-e15d-7a2e-9c19-882031e7b688 tag-0100 21]

Plan (EXPLAIN (ANALYZE, BUFFERS), never-executed partition subplans elided):

Limit  (cost=0.84..48.11 rows=21 width=84) (actual time=0.028..0.036 rows=21 loops=1)
  Buffers: shared hit=8
  ->  Nested Loop  (cost=0.84..93.13 rows=41 width=84) (actual time=0.028..0.035 rows=21 loops=1)
        Buffers: shared hit=8
        ->  Index Scan Backward using npm_tags_p38_namespace_id_npm_package_id_name_idx on npm_tags_p38 npm_tags  (cost=0.41..75.83 rows=41 width=73) (actual time=0.017..0.018 rows=21 loops=1)
              Index Cond: ((namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid) AND (npm_package_id = '019faac7-e15d-7a2e-9c19-882031e7b688'::uuid) AND (name < 'tag-0100'::text))
              Buffers: shared hit=4
        ->  Memoize  (cost=0.42..8.25 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=21)
              Cache Key: npm_tags.npm_version_id
              Cache Mode: logical
              Hits: 20  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
              Buffers: shared hit=4
              ->  Index Scan using npm_versions_p38_pkey on npm_versions_p38 npm_versions  (cost=0.41..8.24 rows=1 width=43) (actual time=0.008..0.008 rows=1 loops=1)
                    Index Cond: ((id = npm_tags.npm_version_id) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                    Buffers: shared hit=4
Planning:
  Buffers: shared hit=1
Planning Time: 0.139 ms
Execution Time: 0.055 ms

Timings: planning 0.139ms, execution 0.055ms, total 0.194ms.

datastore.NpmTagStore.FindNpmDistTagByID

Seed shape: npm_files=100201, npm_tags=25201, npm_versions=50001, npm_packages=5001, blob_storage_blobs=200202, blob_storage_attachments=202

Rendered SQL:

SELECT npm_tags.namespace_id AS "npm_tags.namespace_id",
     npm_tags.id AS "npm_tags.id",
     npm_tags.npm_package_id AS "npm_tags.npm_package_id",
     npm_tags.npm_version_id AS "npm_tags.npm_version_id",
     npm_tags.name AS "npm_tags.name",
     npm_versions.version AS "npm_versions.version"
FROM public.npm_tags
     INNER JOIN public.npm_packages ON (((npm_packages.id = npm_tags.npm_package_id) AND (npm_packages.namespace_id = npm_tags.namespace_id)) AND (npm_packages.soft_deleted_at IS NULL))
     INNER JOIN public.npm_versions ON ((npm_versions.id = npm_tags.npm_version_id) AND (npm_versions.namespace_id = npm_tags.namespace_id))
WHERE ((npm_tags.namespace_id = $1::uuid) AND (npm_tags.id = $2::uuid)) AND (npm_packages.npm_repository_id = $3::uuid)
LIMIT $4;

Bound args: [7787cc12-ea9b-41a3-a1ad-0553128a11da 019faac7-e167-7098-b622-299265e4b02e 019faac7-e14e-707f-9fec-c89fcee7069a 1]

Plan (EXPLAIN (ANALYZE, BUFFERS), never-executed partition subplans elided):

Limit  (cost=0.98..25.07 rows=1 width=84) (actual time=0.026..0.027 rows=1 loops=1)
  Buffers: shared hit=10
  ->  Nested Loop  (cost=0.98..25.07 rows=1 width=84) (actual time=0.026..0.026 rows=1 loops=1)
        Buffers: shared hit=10
        ->  Nested Loop  (cost=0.57..16.62 rows=1 width=73) (actual time=0.016..0.016 rows=1 loops=1)
              Buffers: shared hit=6
              ->  Index Scan using npm_tags_p38_pkey on npm_tags_p38 npm_tags  (cost=0.29..8.31 rows=1 width=73) (actual time=0.008..0.008 rows=1 loops=1)
                    Index Cond: ((id = '019faac7-e167-7098-b622-299265e4b02e'::uuid) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                    Buffers: shared hit=3
              ->  Index Scan using npm_packages_p38_pkey on npm_packages_p38 npm_packages  (cost=0.28..8.30 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1)
                    Index Cond: ((id = npm_tags.npm_package_id) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
                    Filter: ((soft_deleted_at IS NULL) AND (npm_repository_id = '019faac7-e14e-707f-9fec-c89fcee7069a'::uuid))
                    Buffers: shared hit=3
        ->  Index Scan using npm_versions_p38_pkey on npm_versions_p38 npm_versions  (cost=0.41..8.43 rows=1 width=43) (actual time=0.010..0.010 rows=1 loops=1)
              Index Cond: ((id = npm_tags.npm_version_id) AND (namespace_id = '7787cc12-ea9b-41a3-a1ad-0553128a11da'::uuid))
              Buffers: shared hit=4
Planning:
  Buffers: shared hit=12
Planning Time: 0.287 ms
Execution Time: 0.046 ms

Timings: planning 0.287ms, execution 0.046ms, total 0.333ms.

Edited by Hayley Swimelar

Merge request reports