chore(datastore): add repositories keyset indexes

What

Phase 1 Step 4 of the S17 REST management API: adds the keyset sort indexes that keep repository List pagination index-backed.

Four partial indexes on the HASH-partitioned (64 partitions) repositories table, each adding the id tiebreaker so a ROW(<col>, id) keyset bound stays a single index range instead of a post-scan sort:

• (namespace_id, artifacts_count DESC, id DESC)
• (namespace_id, downloads_count DESC, id DESC)
• (namespace_id, size_bytes DESC, id DESC)
• (namespace_id, last_updated_at DESC NULLS LAST, id DESC)

All four carry WHERE soft_deleted_at IS NULL (active rows only). name already has its unique index and needs none.

Migration safety

Blocking parent-level CREATE INDEX under -- +goose NO TRANSACTION with IF [NOT] EXISTS, structurally identical to the established 20260615120000_add_npm_versions_keyset_index.sql. Safe because repositories is empty in dev and there is no production deployment; the migration header documents the per-partition CONCURRENTLY + ATTACH PARTITION path required if these ever run against populated tables. The indexes are additive, so the new binary and the old binary coexist during a rolling deploy. squawk and pgFormatter are clean.

Review

/review-branch: APPROVE (0 blocking, 0 warning). Non-gating follow-ups:

• The new (…, id) indexes supersede the pre-existing `(namespace_id, )` 2-column sort indexes, which become redundant. **Step 5 drops them** once `List`'s EXPLAIN confirms the new indexes back every sort — recorded in the plan amendment in this MR.
• The existence test's colIdx assertion anchors on the index name rather than the key list (the complementary EXPLAIN test provides the real key-order coverage); a cheap follow-up can tighten it.

ADR-007 alignment (separate handbook MR)

ADR-007 lists these sort indexes without the id tiebreaker. ADRs are synced from the handbook and must not be edited here, so the alignment is a paired follow-up handbook MR, not part of this change.

Spec coverage

Spec: S17 REST management API. This chore index-only migration owns no Acceptance Criterion directly; it backs the keyset-pagination premise of AC #9 and spec Resolution 7.

Plan Step 4 acceptance

Acceptance	Tests
Four indexes exist with the id tiebreaker	TestRepositoriesSchema_KeysetIndexesExistWithTiebreaker (4 subtests)
Indexes carry the soft_deleted_at IS NULL predicate	TestRepositoriesSchema_KeysetIndexesExistWithTiebreaker (all four subtests)
EXPLAIN of ORDER BY <col>, id uses the new index, no post-scan sort	TestRepositoriesSchema_KeysetSortIsIndexBacked (4 subtests)
Migration applies and reverts cleanly	Generic TestMigrations_UpDownUp (down-to-zero residue), not duplicated per migration

Spec rows backed (indirectly)

#	Spec item	Tests
AC-9	List paginates by keyset with a stable id tiebreaker	This step keeps the four keyset sorts index-backed; List behavior is Step 5/8
R-7	Sort limited to indexed columns; every sort index-backed	TestRepositoriesSchema_KeysetSortIsIndexBacked proves index-backing; name keeps its unique index

Security

Concern	Notes
Tenant isolation	The new indexes are namespace_id-leading; the EXPLAIN asserts partition pruning to one repositories_pNN. Query-layer enforcement is Step 5
Injection	namespace_id stays a bound $1 parameter; the only interpolated tokens in the test are column names from a fixed in-test allowlist

Plan

Step 4 of docs/plans/2026-06-22-s17-phase1-repository-crud.md. Part of epic gitlab-org#22342 (closed).

Related to #171 (closed)

Database Review Evidence

Migrations

Note

Timings are from CI (db:migrate matrix, goose verbose) against an empty database, in apply / rollback order per PG version. Production-scale validation via Database Lab is not yet available. See Database review evidence for the matrix rationale and how to read the numbers.

Migration	PG 16	PG 17	PG 18
20260622120000_add_repository_keyset_indexes.sql	OK (492.09ms / 37.78ms)	OK (262.06ms / 99.03ms)	OK (213.03ms / 79.01ms)

Migration notes:

• Applies and rolls back cleanly on all three PG versions, well under the 5-minute boot budget (max 492ms apply on an empty DB). PG 16 is the slowest apply (1.9x PG 17, under the 2x regression threshold) — expected partition-index-creation overhead across 64 partitions. These empty-DB timings do not predict populated-table runtime; the migration header documents the per-partition CONCURRENTLY path required against real data.