chore(datastore): id-restricted repository key enumeration (S09 LookupResources plan: 2/3)

João Pereirarequested to merge
jdrpereira/969-lookup-resources-step-2 into main

ListRepositoriesParams gains an optional IDs restriction, honored by both repository page statements -- listRepositoriesStmt and enumerateRepositoryKeysStmt -- through the repositoryListClauses predicate they share.

  • nil is unrestricted, so every existing caller keeps today's answer.
  • A populated slice cuts the page to those ids, duplicates collapsed.
  • An explicitly empty slice is rejected. Unlike Formats and Kinds, where empty means unfiltered, an empty id set must never fall back to the whole namespace.

The restriction only narrows: the namespace scope, the soft-delete exclusion, the format and kind filters and the keyset bound all still apply, and hasMore counts the restricted set.

chore because no route reaches the field. Step 3 of the plan carries the feat.

Notes for the reviewer

  • Diff is 418 added lines: 38 source, 380 test.
  • len(IDs) carries an independent worst-case net, agreed in review: listRepositoriesIDsLimit = 5_000 rejects a set no designed caller can send (the listing filter in !2452 (merged) bounds its own drain at 2,000 first) and exists for a future caller that bounds nothing. Under the simple protocol the ids are interpolated into the statement text, so the cost at the cap is roughly 200 KB of SQL plus its planning, per execution. The net must stay at or above any caller's own ceiling so it never rejects a request that caller allowed; 5,000 is a conservative start, and raising it is a one-constant change.
  • An empty grant set arriving as an append-built nil reads as unrestricted. The datastore cannot tell the two apart; the caller short-circuits an empty set before it gets here.
  • No EXPLAIN test covers the restriction: no index can serve an id set alongside the name sort, so any plan assertion on that shape either cannot fail or pins a planner cost choice. The behavioral suites prove the restriction by results. This deviates from the step's acceptance clause, which assumed an index-backed property that does not exist.

Related to https://gitlab.com/gitlab-org/ops/artifact-registry/-/work_items/969

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 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.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.RepositoryStore.EnumerateRepositoryKeys.IDsPopulated Limit repositories_p57_pkey 10 / 10 58.97 0.042ms 30 / 0 1/64
datastore.RepositoryStore.List.IDsPopulated Limit repositories_p57_pkey 10 / 10 58.97 0.093ms 30 / 0 1/64

Query notes:

  • Both statements plan the same shape on the id-restricted arm, and it is worth recording because a reviewer running EXPLAIN will see a Sort node. At low id counts the planner takes the primary key on the id list and orders the result with a small quicksort (10 rows, 26 kB here). At high id counts it flips to repositories_p57_namespace_id_name_idx, which supplies the name order directly, and the LIMIT stops the scan early — measured at 2000 ids: no Sort node, 3 buffers, 0.085 ms. The choice is cost-based and bounded at both ends, which is the planner behavior the reviewer note above predicts. The unrestricted (IDs nil) arm is unchanged and renders byte-identical SQL to the merge-base.
  • No anomalies otherwise: both plans prune to 1 of 64 partitions, estimates match actuals exactly, and buffer reads are zero. The LIMIT is server-clamped before it reaches the store (parseListQuery in internal/managementapi/list.go), so neither statement is an unbounded SELECT.
datastore.RepositoryStore.EnumerateRepositoryKeys.IDsPopulated

Summary: Plan matches the method's intent: the id list drives an Index Scan on the partition's primary key, with the namespace_id bind pruning to one of 64 partitions and a 10-row quicksort supplying the name order. Actual rows match the estimate (10 / 10), buffer reads are zero, and execution is 0.042 ms at 5000 seeded repositories. No anomalies.

Seed shape: namespaces=1, repositories=5000

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE ((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.id IN ($2::uuid, $3::uuid, $4::uuid, $5::uuid, $6::uuid, $7::uuid, $8::uuid, $9::uuid, $10::uuid, $11::uuid))
ORDER BY repositories.name ASC
LIMIT $12;

Bound args: [00000000-0000-7000-8000-000000000001, 00000000-0000-7000-8000-000000000100, 00000000-0000-7000-8000-000000000600, 00000000-0000-7000-8000-000000001100, 00000000-0000-7000-8000-000000001600, 00000000-0000-7000-8000-000000002100, 00000000-0000-7000-8000-000000002600, 00000000-0000-7000-8000-000000003100, 00000000-0000-7000-8000-000000003600, 00000000-0000-7000-8000-000000004100, 00000000-0000-7000-8000-000000004600, 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=58.94..58.97 rows=10 width=40) (actual time=0.029..0.030 rows=10 loops=1)
   Buffers: shared hit=30
   ->  Sort  (cost=58.94..58.97 rows=10 width=40) (actual time=0.028..0.029 rows=10 loops=1)
         Sort Key: repositories.name
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=30
         ->  Index Scan using repositories_p57_pkey on repositories_p57 repositories  (cost=0.28..58.78 rows=10 width=40) (actual time=0.009..0.022 rows=10 loops=1)
               Index Cond: ((id = ANY ('{00000000-0000-7000-8000-000000000100,00000000-0000-7000-8000-000000000600,00000000-0000-7000-8000-000000001100,00000000-0000-7000-8000-000000001600,00000000-0000-7000-8000-000000002100,00000000-0000-7000-8000-000000002600,00000000-0000-7000-8000-000000003100,00000000-0000-7000-8000-000000003600,00000000-0000-7000-8000-000000004100,00000000-0000-7000-8000-000000004600}'::uuid[])) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
               Filter: (soft_deleted_at IS NULL)
               Buffers: shared hit=30
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.175 ms
 Execution Time: 0.042 ms

Timings: planning 0.175ms, execution 0.042ms, total 0.217ms.

datastore.RepositoryStore.List.IDsPopulated

Summary: Plan matches the method's intent and is identical in shape to the enumeration statement, differing only in projection width: Index Scan on the partition's primary key driven by the id list, one of 64 partitions scanned, and a 10-row quicksort for the name order. Actual rows match the estimate (10 / 10) and execution is 0.093 ms at 5000 seeded repositories. No anomalies.

Seed shape: namespaces=1, repositories=5000

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE ((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.id IN ($2::uuid, $3::uuid, $4::uuid, $5::uuid, $6::uuid, $7::uuid, $8::uuid, $9::uuid, $10::uuid, $11::uuid))
ORDER BY repositories.name ASC
LIMIT $12;

Bound args: [00000000-0000-7000-8000-000000000001, 00000000-0000-7000-8000-000000000100, 00000000-0000-7000-8000-000000000600, 00000000-0000-7000-8000-000000001100, 00000000-0000-7000-8000-000000001600, 00000000-0000-7000-8000-000000002100, 00000000-0000-7000-8000-000000002600, 00000000-0000-7000-8000-000000003100, 00000000-0000-7000-8000-000000003600, 00000000-0000-7000-8000-000000004100, 00000000-0000-7000-8000-000000004600, 11]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=58.94..58.97 rows=10 width=214) (actual time=0.055..0.057 rows=10 loops=1)
   Buffers: shared hit=30
   ->  Sort  (cost=58.94..58.97 rows=10 width=214) (actual time=0.054..0.055 rows=10 loops=1)
         Sort Key: repositories.name
         Sort Method: quicksort  Memory: 26kB
         Buffers: shared hit=30
         ->  Index Scan using repositories_p57_pkey on repositories_p57 repositories  (cost=0.28..58.78 rows=10 width=214) (actual time=0.017..0.037 rows=10 loops=1)
               Index Cond: ((id = ANY ('{00000000-0000-7000-8000-000000000100,00000000-0000-7000-8000-000000000600,00000000-0000-7000-8000-000000001100,00000000-0000-7000-8000-000000001600,00000000-0000-7000-8000-000000002100,00000000-0000-7000-8000-000000002600,00000000-0000-7000-8000-000000003100,00000000-0000-7000-8000-000000003600,00000000-0000-7000-8000-000000004100,00000000-0000-7000-8000-000000004600}'::uuid[])) AND (namespace_id = '00000000-0000-7000-8000-000000000001'::uuid))
               Filter: (soft_deleted_at IS NULL)
               Buffers: shared hit=30
 Planning:
   Buffers: shared hit=649 read=2
 Planning Time: 2.569 ms
 Execution Time: 0.093 ms

Timings: planning 2.569ms, execution 0.093ms, total 2.662ms.

Edited by João Pereira

Merge request reports