chore(datastore): purge seam, tombstone scan, and namespace page (S20-A plan: 5/21)

Pawel Rozlachrequested to merge
prozlach/s20a-lifecycle-closed-beta-step-5 into main

What this step delivers

The purger's SQL-layer seam, in internal/datastore. Nothing outside the package's own tests calls any part of it.

  • PurgeScope, the purge-level discriminator, with PurgeScopeUnspecified as an invalid zero value. NewPurgeScope rejects an out-of-range value and the zero value at the boundary that admits them. Valid gives the same verdict on a value that never passed through the constructor.
  • tombstoneEligibleExpr, the purger's one input predicate: a row is purgeable when its soft_deleted_at is older than the retention window. The comparison is strict, and it excludes a live row without a second predicate, because a NULL comparison is not true.
  • TombstoneRow, TombstoneCursor, and ReapTotals, the three types the reap steps of this plan will import.
  • Reaper, the interface one format family's reap logic implements. It has no implementation in the tree and no caller. A compile-time assertion against a test stub is the only thing that pins the signature today.
  • LifecycleScanStore.ScanTombstonedRepositories, which returns one bounded page of eligible repository tombstones in one namespace, oldest tombstone first. It takes no keyset cursor. MaxLifecycleScanPageSize bounds its page at 1000 rows.
  • NamespaceStore.ListPage, a keyset page over namespaces ordered by id. It projects namespaceColumns(), so a caller holds every column WriteServiceable reads. MaxNamespacePageSize bounds its page at 1000 rows.
  • Two instrumentQuery name constants in internal/datastore/query_names.go, for the two new statements.

Both maximums are an operator decision, recorded under Knowingly accepted deviations.

Neither store method has a production caller. The purge worker and the sweep worker, which later steps of this plan add, will be the first two callers. This MR adds no migration, no configuration key, no route, and no worker registration. The service boots and dispatches requests as it does on main.

The EXPLAIN assertions read index_repositories_on_ns_id_soft_deleted_at, which !1608 (merged) added.

The branch also carries a docs(plans): commit that corrects stale S22 counts in the plan. See Knowingly accepted deviations.

Spec coverage

Where a criterion is covered only in part here, the row names the later step that owns the rest.

# Criterion Tests
AC-1 repositories row older than the window discovered, newer not; non-zero and zero window TestLifecycleScanStore_ScanTombstonedRepositories/a_tombstone_older_than_a_non-zero_window..., /a_zero_window_makes_a_just-written_tombstone_immediately_eligible, TestTombstoneEligibleExpr. Subtree reap: Steps 8-14
AC-2 soft_deleted_at IS NULL never discovered .../a_row_with_soft_deleted_at_IS_NULL_is_never_returned for repositories. Other tables: Steps 6, 7
AC-3 The three scans use their index and prune to one partition TestLifecycleScanStore_ScanTombstonedRepositories_IsIndexBackedOldestFirst for repositories. The other two: Step 7
AC-8 Frozen-namespace refusal, per lifecycle column Projection half: TestNamespaceStore_ListPage_ProjectsLifecycleColumns (all five columns, off the page). Job refusal: Step 15a
AC-32 Oldest-first; EXPLAIN shows a backwards DESC scan with no Sort ..._IsIndexBackedOldestFirst (three subtests), .../rows_come_back_oldest_tombstone_first, /the_page_is_bounded_by_the_limit_and_keeps_the_oldest, TestScanTombstonedRepositoriesStmt_OrdersOnSoftDeletedAtAlone. Sweep tick: Step 16
AC-38 Frozen namespace not enqueued, re-enumerated once clear Enumerator half: TestNamespaceStore_ListPage_ProjectsLifecycleColumns, TestNamespaceStore_ListPage_CarriesEveryLifecycleState, plus the two subtests that end one enumeration pass, TestNamespaceStore_ListPage/hasMore_is_false_when_the_page_exhausts_the_rows and /hasMore_is_false_when_the_limit_exceeds_the_row_count. Sweep half: Steps 15a, 16
E-13 Retention window changed between enqueue and run Predicate-at-run-time half: TestTombstoneEligibleExpr, .../a_tombstone_older_than_a_non-zero_window.... Job outcome: Step 15
E-14 Enumeration query fails Store half: TestNamespaceStore_ListPage/query_failure_wraps_the_cause_and_names_no_namespace and TestLifecycleScanStore_ScanTombstonedRepositories/query_failure_wraps_the_cause_and_names_no_namespace. Tick error and River retry: Step 16
E-15 Sweep interval elapses before enumeration completes Oldest-first half: .../rows_come_back_oldest_tombstone_first, /the_page_is_bounded_by_the_limit_and_keeps_the_oldest. Tick behavior: Step 16
S-4 Input validation; no purger input is user-controlled TestNewPurgeScope, TestPurgeScope_ZeroValueIsNotAPurgeLevel, TestLifecycleScanStore_ScanTombstonedRepositories_Guards, TestNamespaceStore_ListPage_Guards, .../the_scan_reaches_no_other_namespace's_tombstones. Each guard suite carries a limit above the max page size case for the new maximum
S-5 No injection surface; SQL is jet-built and parameterized TestScanTombstonedRepositoriesStmt_OrdersOnSoftDeletedAtAlone asserts the statement binds exactly two arguments, namespace_id and the LIMIT

e2e scenario catalogs

No scenario is added, and none is affected. The catalog guardrail applies to feat and fix work, and this step's type is chore. The plan assigns the docs/testing/e2e/ edits to Step 18 alone, which owns the destructive repository DELETE. No route, handler, or dispatch path changes here, so no catalog scenario can reach this code.

Diff size (guardrail 18)

Measured at 145caf73, this MR's head, with git diff --numstat 88babb6a...HEAD: 1405 added lines and 13 deleted lines, across nine files. 88babb6a is this branch's merge base with origin/main. Twelve of the deletions are in the plan file and the thirteenth is in internal/datastore/namespaces.go.

Group Files Added
Go source internal/datastore/lifecycle_scan.go, internal/datastore/namespaces.go, internal/datastore/query_names.go 394
Go test internal/datastore/lifecycle_scan_test.go, internal/datastore/lifecycle_scan_integration_test.go, internal/datastore/lifecycle_explain_integration_test.go, internal/datastore/namespaces_integration_test.go, internal/datastore/namespaces_test.go 999
Docs docs/plans/2026-08-11-s20a-lifecycle-closed-beta.md 12 added, 12 deleted

Against the plan's own forecast for this step:

Measure Forecast Measured Over by
Go source ~250 394 58%
Go test ~550 999 82%
Go total ~800 1393 74%
The plan's working ceiling ~900 1393 55%

The documented ceiling is 500 lines, so the Go total is 179% over it. The test-to-source ratio is 2.5 : 1, against the 3.1 : 1 the plan measured across main. Of the 392 added lines in the two source files, 216 are // comment lines, counted with git diff 88babb6a...HEAD -- internal/datastore/lifecycle_scan.go internal/datastore/namespaces.go | grep -c '^+[[:space:]]*//'.

A split does not help, and the plan interrogated this step for one before the work started. Its Seam interrogation outcomes section records Step 5 at ~800 lines and the outcome as negative:

Splitting the interface from its first implementation would leave a step whose only artifact is a compile-time assertion, and splitting the namespace page out would leave the scan with no enumerator to page over. Negative: the whole point of the step is that six later steps import one seam, so a seam delivered in halves is two rebases for every one of them.

Merge order and a deferred obligation

ADR-025 states that every operation that adds or removes a blob attachment first takes a SELECT ... FOR UPDATE lock on the blob_storage_blobs row. Reaper's obligation list does not state that lock. ADR-025 carries status Proposed, the S20-A spec does not reference it, and the spec states no lock obligation on the purger. Open MR !728 assigns the lock on the purger's reap paths to S28 Phase 1. The obligation therefore starts when !728 merges and S28 Phase 1 lands, not with this MR.

TombstoneCursor has no reference on this branch

TombstoneCursor is seam machinery for the version-level scans, which the next step of this plan adds. The plan's Files: list puts the type in this step, so it lands here with no reference to it. The related reason is in the type's own doc comment. ScanTombstonedRepositories takes no cursor, because index_repositories_on_ns_id_soft_deleted_at carries no id column. A ROW(soft_deleted_at, id) bound is therefore not index-resident, and it puts an Incremental Sort above the scan. That sort is the one the acceptance criterion forbids.

Knowingly accepted deviations

All three are operator decisions, named here so a reviewer can tell each one from an oversight.

  • This MR carries a docs(plans): correction to the S20-A plan. docs/plans/README.md prescribes a follow-up plan-amendment MR for a revision to research findings, rather than an in-place edit from a step branch. The operator decided to carry the correction here, with that conflict in view.

  • The correction reached five count sites, plus the working-ceiling rationale. The first authorization covered three sites. The operator decided to keep the wider correction, with the extent in view.

  • This MR adds a maximum page size to each new method: MaxNamespacePageSize on NamespaceStore.ListPage and MaxLifecycleScanPageSize on LifecycleScanStore.ScanTombstonedRepositories. Both are 1000 and both are exported. Each method rejects a larger limit with its own sentinel, before any statement runs. The step's acceptance text asks for a bounded page and a cursor that advances, and it does not ask for this rejection. That criterion sets the deadline for a bound, not its placement. The operator decided to put the maximum in the store.

    The placement has more than one precedent in this package, and the two closest do not point the same way. datastore.MaxCounterDrainBatchSize is exported, and it bounds a background-worker caller rather than a caller behind a handler's page-size clamp. maxRepositoryReconcilePageSize, in internal/datastore/reconcile_repository.go, is the closer match: it caps the page size of RepositoryReconcileStore.ListForReconcile. Its comment names in-tree reconciliation workers as the callers the bound is for, with no handler clamp in front of them. That constant is unexported, and the same comment gives the reason: it stays unexported while every caller that reads it is in this package. On the exported half of this decision, that precedent points the other way.

    No committed assertion changed, because 500 is the largest limit any existing test passes to either method. On ListPage, math.MaxInt now reaches the new guard instead of probeLimit's saturating arm. The query note under Database Review Evidence records both maximums as in force.

Database Review Evidence

Query mode ran. Migration mode did not run, and the reason is that this branch adds no migration. git diff --name-only --diff-filter=ACMR 88babb6a...HEAD -- internal/datastore/migrations/ returns nothing.

This section replaces the evidence of an earlier pass, which ran at head 0cd5bf7e. The branch then moved to head d9c89be7, over a rebase onto origin/main 88babb6a. It also gained the commit that adds a maximum page size to each method. The rebase removed namespaces.organization_id from namespaceColumns(), so the projection of ListPage is now 14 columns. The earlier plans stated a 15-column projection, which this branch does not build. Every number in this section is fresh, at head d9c89be7.

Queries

Note

The plans come from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container. That version matches GL_PG_CURR_VERSION in .gitlab-ci-other-versions.yml. The seed data is synthetic. Each group of plans runs inside a transaction that rolls back, and the container is removed at the end of the run. The numbers reflect moderate cardinality. They do not capture production-scale effects. See Database review evidence for the seed sizing and the anomaly catalog. Expand each row for the seed shape, the rendered SQL, the bound arguments, and the raw plan.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.LifecycleScanStore.ScanTombstonedRepositories.WindowZero Limit repositories_p55_namespace_id_soft_deleted_at_idx 25 / 25 5.81 0.037 ms 15 / 0 1/64
datastore.LifecycleScanStore.ScanTombstonedRepositories.Window7d Limit repositories_p55_namespace_id_soft_deleted_at_idx 25 / 25 7.04 0.030 ms 15 / 0 1/64
datastore.LifecycleScanStore.ScanTombstonedRepositories.Window7dMaxPage Limit over Sort repositories_p55_namespace_id_soft_deleted_at_idx 667 / 667 149.29 0.292 ms 89 / 0 1/64
datastore.LifecycleScanStore.ScanTombstonedRepositories.Window7dMaxPageFullBacklog Limit repositories_p07_namespace_id_soft_deleted_at_idx 1000 / 1000 68.31 0.435 ms 554 / 0 1/64
datastore.NamespaceStore.ListPage.FirstPage Limit pk_namespaces 26 / 26 3.32 0.036 ms 28 / 0 n/a
datastore.NamespaceStore.ListPage.MidKeyset Limit pk_namespaces 26 / 26 5.40 0.029 ms 27 / 0 n/a
datastore.NamespaceStore.ListPage.MaxPage Limit pk_namespaces 1001 / 1001 117.08 0.322 ms 1000 / 0 n/a

No query in this MR reaches more than one partition of a partitioned table, so this pass raises no partition-fan-out warning. namespaces carries no PARTITION BY, which is why the three ListPage rows read n/a.

One row differs in plan shape from the other six. Window7dMaxPage is the scan at the maximum page size, over a namespace whose eligible set is smaller than that page. In that shape the planner materializes a Sort instead of taking the order from the index. The Query notes at the end of this section state the boundary and the two rows that measure each side of it.

datastore.LifecycleScanStore.ScanTombstonedRepositories.WindowZero

Summary: The plan matches the intent of the method. The planner picks a backward walk of one partition of index_repositories_on_ns_id_soft_deleted_at, and the namespace equality prunes 64 partitions to one. Both predicates land in the Index Cond, no Sort node stands above the scan, and no post-scan Filter appears. No anomalies.

Seed shape: namespaces=2, repositories=5500

The target namespace holds 5000 repositories, of which 1000 carry a tombstone. A second namespace holds 500 more repositories in a different partition, so partition pruning has a non-empty partition to exclude.

Rendered SQL:

SELECT repositories.namespace_id AS "tombstone_row.namespace_id",
     repositories.id AS "tombstone_row.id",
     repositories.soft_deleted_at AS "tombstone_row.soft_deleted_at"
FROM public.repositories
WHERE (repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at < (NOW() - INTERVAL '0 MICROSECOND'))
ORDER BY repositories.soft_deleted_at ASC
LIMIT $2;

Bound args: [4005b035-8851-4968-8e35-fe75450de6c1, 25]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.15..5.81 rows=25 width=40) (actual time=0.016..0.025 rows=25 loops=1)
   Buffers: shared hit=15
   ->  Index Scan Backward using repositories_p55_namespace_id_soft_deleted_at_idx on repositories_p55 repositories  (cost=0.15..188.96 rows=834 width=40) (actual time=0.015..0.024 rows=25 loops=1)
         Index Cond: ((namespace_id = '4005b035-8851-4968-8e35-fe75450de6c1'::uuid) AND (soft_deleted_at < (now() - '00:00:00'::interval)))
         Buffers: shared hit=15
 Planning:
   Buffers: shared hit=17
 Planning Time: 0.177 ms
 Execution Time: 0.037 ms

Timings: planning 0.177 ms, execution 0.037 ms, total 0.214 ms.

The scan node reads rows=834 as its estimate and rows=25 as its actual count. This gap is the LIMIT that stops the walk early, not a statistics error. The true eligible set at a zero window is the 1000 tombstones in the namespace, and 834 is the planner's estimate of it.

datastore.LifecycleScanStore.ScanTombstonedRepositories.Window7d

Summary: A non-zero window keeps the same plan shape. The window predicate stays inside the Index Cond and does not move to a heap filter, so the scan reads no ineligible tombstone. The estimate of 667 rows equals the true size of the eligible set. No anomalies.

Seed shape: namespaces=2, repositories=5500

Identical to the row above, in the same transaction. Of the 1000 tombstones in the target namespace, 667 are older than 7 days.

Rendered SQL:

SELECT repositories.namespace_id AS "tombstone_row.namespace_id",
     repositories.id AS "tombstone_row.id",
     repositories.soft_deleted_at AS "tombstone_row.soft_deleted_at"
FROM public.repositories
WHERE (repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at < (NOW() - INTERVAL '7 DAY'))
ORDER BY repositories.soft_deleted_at ASC
LIMIT $2;

Bound args: [4005b035-8851-4968-8e35-fe75450de6c1, 25]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.15..7.04 rows=25 width=40) (actual time=0.014..0.019 rows=25 loops=1)
   Buffers: shared hit=15
   ->  Index Scan Backward using repositories_p55_namespace_id_soft_deleted_at_idx on repositories_p55 repositories  (cost=0.15..183.93 rows=667 width=40) (actual time=0.013..0.018 rows=25 loops=1)
         Index Cond: ((namespace_id = '4005b035-8851-4968-8e35-fe75450de6c1'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
         Buffers: shared hit=15
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.120 ms
 Execution Time: 0.030 ms

Timings: planning 0.120 ms, execution 0.030 ms, total 0.150 ms.

The retention window reaches the SQL as an inline INTERVAL literal, not as a bind parameter. This costs nothing here, because the pgx query mode of this service is QueryExecModeSimpleProtocol and no prepared statement exists at run time.

datastore.LifecycleScanStore.ScanTombstonedRepositories.Window7dMaxPage

Summary: This is the same statement at MaxLifecycleScanPageSize, over a namespace whose eligible set (667 rows) is smaller than that page (1000 rows). The LIMIT can then stop nothing, so the planner drops the ordered index walk and takes a Bitmap Heap Scan plus a Sort. The ordering is no longer index-resident in this shape. The sort is a quicksort over 667 rows and 71 kB, and execution stays at 0.292 ms.

Seed shape: namespaces=2, repositories=5500

Identical to the two rows above, in the same transaction.

Rendered SQL: identical to the Window7d row above.

Bound args: [4005b035-8851-4968-8e35-fe75450de6c1, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=147.62..149.29 rows=667 width=40) (actual time=0.211..0.249 rows=667 loops=1)
   Buffers: shared hit=89
   ->  Sort  (cost=147.62..149.29 rows=667 width=40) (actual time=0.211..0.227 rows=667 loops=1)
         Sort Key: repositories.soft_deleted_at
         Sort Method: quicksort  Memory: 71kB
         Buffers: shared hit=89
         ->  Bitmap Heap Scan on repositories_p55 repositories  (cost=14.99..116.33 rows=667 width=40) (actual time=0.039..0.134 rows=667 loops=1)
               Recheck Cond: ((namespace_id = '4005b035-8851-4968-8e35-fe75450de6c1'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
               Heap Blocks: exact=88
               Buffers: shared hit=89
               ->  Bitmap Index Scan on repositories_p55_namespace_id_soft_deleted_at_idx  (cost=0.00..14.83 rows=667 width=0) (actual time=0.026..0.026 rows=667 loops=1)
                     Index Cond: ((namespace_id = '4005b035-8851-4968-8e35-fe75450de6c1'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.123 ms
 Execution Time: 0.292 ms

Timings: planning 0.123 ms, execution 0.292 ms, total 0.415 ms.

Partition pruning and the two Index Cond predicates survive this switch. What changes is where the order comes from: the index in the other scan rows, a materialized sort here.

datastore.LifecycleScanStore.ScanTombstonedRepositories.Window7dMaxPageFullBacklog

Summary: This is the maximum page size again, over a namespace whose eligible set (5000 rows) is larger than the page. The ordered index walk returns, and the LIMIT stops it after 1000 rows. This row and Window7dMaxPage bracket the boundary. The plan shape follows the size of the eligible set against the page size, not the page size on its own. No anomalies.

Seed shape: namespaces=2, repositories=5500

The target namespace holds 5000 repositories, all tombstoned and all older than 7 days. A second namespace holds 500 more repositories in a different partition.

Rendered SQL: identical to the Window7d row above.

Bound args: [773f0c90-5cf6-46c4-8f7d-653e09a1f8a5, 1000]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.29..68.31 rows=1000 width=40) (actual time=0.036..0.382 rows=1000 loops=1)
   Buffers: shared hit=554
   ->  Index Scan Backward using repositories_p07_namespace_id_soft_deleted_at_idx on repositories_p07 repositories  (cost=0.29..340.41 rows=5000 width=40) (actual time=0.035..0.308 rows=1000 loops=1)
         Index Cond: ((namespace_id = '773f0c90-5cf6-46c4-8f7d-653e09a1f8a5'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)))
         Buffers: shared hit=554
 Planning:
   Buffers: shared hit=26
 Planning Time: 0.418 ms
 Execution Time: 0.435 ms

Timings: planning 0.418 ms, execution 0.435 ms, total 0.853 ms.

The same seed at a page size of 25 gives the same index path, at 0.071 ms of execution and 17 buffer hits. A full page of 1000 rows therefore costs about 6 times the execution of a page of 25, for 40 times the rows.

datastore.NamespaceStore.ListPage.FirstPage

Summary: The first page binds after to uuid.Nil, so the id > $1 predicate matches every row. The planner still walks pk_namespaces rather than a sequential scan plus a sort, because the LIMIT makes an ordered index walk the cheaper path. 26 rows come back for a page size of 25 plus one probe row. No anomalies.

Seed shape: namespaces=5000

Rendered SQL:

SELECT namespaces.id AS "namespaces.id",
     namespaces.created_at AS "namespaces.created_at",
     namespaces.slug AS "namespaces.slug",
     namespaces.platform AS "namespaces.platform",
     namespaces.entity_type AS "namespaces.entity_type",
     namespaces.entity_id AS "namespaces.entity_id",
     namespaces.billing_entity_type AS "namespaces.billing_entity_type",
     namespaces.billing_entity_id AS "namespaces.billing_entity_id",
     namespaces.delivery_mode_override AS "namespaces.delivery_mode_override",
     namespaces.suspended_at AS "namespaces.suspended_at",
     namespaces.disabled_at AS "namespaces.disabled_at",
     namespaces.blocked_at AS "namespaces.blocked_at",
     namespaces.deleted_at AS "namespaces.deleted_at",
     namespaces.purged_at AS "namespaces.purged_at"
FROM public.namespaces
WHERE namespaces.id > $1::uuid
ORDER BY namespaces.id ASC
LIMIT $2;

Bound args: [00000000-0000-0000-0000-000000000000, 26]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..3.32 rows=26 width=160) (actual time=0.012..0.021 rows=26 loops=1)
   Buffers: shared hit=28
   ->  Index Scan using pk_namespaces on namespaces  (cost=0.28..583.68 rows=5000 width=160) (actual time=0.011..0.019 rows=26 loops=1)
         Index Cond: (id > '00000000-0000-0000-0000-000000000000'::uuid)
         Buffers: shared hit=28
 Planning:
   Buffers: shared hit=43
 Planning Time: 0.225 ms
 Execution Time: 0.036 ms

Timings: planning 0.225 ms, execution 0.036 ms, total 0.261 ms.

The projection is 14 columns and the row width is 160 bytes. namespaces.organization_id left namespaceColumns() with the rebase, so a plan that states 15 columns describes an older tree.

datastore.NamespaceStore.ListPage.MidKeyset

Summary: A cursor in the middle of the seeded id range keeps the same plan. The bound reaches pk_namespaces as an Index Cond, so the walk starts at the cursor instead of at the first row. The buffer count stays flat between the two pages, which is the property that makes the page cost independent of the cursor position. No anomalies.

Seed shape: namespaces=5000

The cursor is the 2501st id in id order, captured from the seeded set.

Rendered SQL: identical to the FirstPage row above.

Bound args: [7df1cbba-a25d-4710-8260-b4684f5f97e9, 26]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..5.40 rows=26 width=160) (actual time=0.012..0.020 rows=26 loops=1)
   Buffers: shared hit=27
   ->  Index Scan using pk_namespaces on namespaces  (cost=0.28..487.48 rows=2475 width=160) (actual time=0.011..0.019 rows=26 loops=1)
         Index Cond: (id > '7df1cbba-a25d-4710-8260-b4684f5f97e9'::uuid)
         Buffers: shared hit=27
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.044 ms
 Execution Time: 0.029 ms

Timings: planning 0.044 ms, execution 0.029 ms, total 0.073 ms.

The scan node estimates 2475 rows against 2499 remaining rows, a divergence of 1%.

datastore.NamespaceStore.ListPage.MaxPage

Summary: This is the first page at MaxNamespacePageSize, which fetches 1001 rows for a page of 1000 plus one probe row. The plan keeps the same shape as the smaller pages: one Index Scan on pk_namespaces under a Limit, with no sort. Cost and buffers scale with the page size, and execution stays at 0.322 ms. No anomalies.

Seed shape: namespaces=5000

Rendered SQL: identical to the FirstPage row above.

Bound args: [00000000-0000-0000-0000-000000000000, 1001]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

 Limit  (cost=0.28..117.08 rows=1001 width=160) (actual time=0.010..0.293 rows=1001 loops=1)
   Buffers: shared hit=1000
   ->  Index Scan using pk_namespaces on namespaces  (cost=0.28..583.68 rows=5000 width=160) (actual time=0.010..0.256 rows=1001 loops=1)
         Index Cond: (id > '00000000-0000-0000-0000-000000000000'::uuid)
         Buffers: shared hit=1000
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.053 ms
 Execution Time: 0.322 ms

Timings: planning 0.053 ms, execution 0.322 ms, total 0.375 ms.

The maximum page reads 1000 buffers against 28 for a page of 25. At 160 bytes per row, one maximum page materializes about 160 kB in the result slice.

The scan carries no keyset cursor, and this is what replaces that evidence

ScanTombstonedRepositories takes no keyset cursor, so the usual keyset checks of a paged scan do not apply to it. index_repositories_on_ns_id_soft_deleted_at keys (namespace_id, soft_deleted_at DESC) and carries no id column. The doc comment of the method states that a ROW(soft_deleted_at, id) bound is therefore not index-resident, and that it puts an Incremental Sort above the scan.

This pass measured that claim again on the current tree, with the keyset bound added and id appended to the ORDER BY. The claim holds.

 Limit  (cost=26.46..71.16 rows=25 width=40) (actual time=0.196..0.199 rows=25 loops=1)
   Buffers: shared hit=90
   ->  Incremental Sort  (cost=26.46..185.57 rows=89 width=40) (actual time=0.196..0.197 rows=25 loops=1)
         Sort Key: repositories.soft_deleted_at, repositories.id
         Presorted Key: repositories.soft_deleted_at
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
         Buffers: shared hit=90
         ->  Index Scan Backward using repositories_p53_namespace_id_soft_deleted_at_idx on repositories_p53 repositories  (cost=0.15..182.68 rows=89 width=40) (actual time=0.034..0.156 rows=33 loops=1)
               Index Cond: ((namespace_id = 'e51422a2-384d-4d64-b1a2-ff93262cba6a'::uuid) AND (soft_deleted_at < (now() - '7 days'::interval)) AND (soft_deleted_at >= '2026-07-29 13:35:39.067277+00'::timestamp with time zone))
               Filter: (ROW(soft_deleted_at, id) > ROW('2026-07-29 13:35:39.067277+00'::timestamp with time zone, 'cd520e36-fa64-40d5-a3ba-ec50259a4db4'::uuid))
               Rows Removed by Filter: 135
               Buffers: shared hit=90
 Planning:
   Buffers: shared hit=13
 Planning Time: 0.353 ms
 Execution Time: 0.296 ms

Three costs appear that the shipped shape does not pay:

  • An Incremental Sort node, with Sort Method: quicksort and 27 kB of peak memory.
  • A post-scan Filter on the ROW(...) comparison, with Rows Removed by Filter: 135.
  • An execution time of 0.296 ms against 0.059 ms for the shipped shape at the same page size in the same session, a factor of 5.

The absence of a cursor is therefore a measured trade-off rather than an omission. Progress across passes comes from reaped rows that leave the partial index, which the doc comment of the method states.

What this pass adds to the branch's own EXPLAIN suite

internal/datastore/lifecycle_explain_integration_test.go already asserts the index, the single partition, the backward direction, the two Index Cond predicates, and the absence of a Sort node. This pass confirms every one of those on a bigger and differently shaped fixture, at a page size of 25. The index the plans name, repositories_pNN_namespace_id_soft_deleted_at_idx, is one of the 64 attached children of index_repositories_on_ns_id_soft_deleted_at, which !1608 (merged) added. The attachment was read from pg_inherits.

What Suite This pass
Planner freedom enable_seqscan=off, pinned by explainAnalyzeStmt Planner defaults, so the index win is the planner's own choice
Fixture size 60 tombstones, all eligible 5000 repositories, 1000 tombstones, 667 eligible at a 7-day window
Tenants in the table One namespace Two namespaces in two partitions, so pruning excludes a non-empty partition
Retention window Zero only Zero and 7 days
Page size 25 only 25 and MaxLifecycleScanPageSize, on both sides of the eligible-set boundary
The rejected keyset shape Asserts that no Sort appears in the shipped shape Measures the Incremental Sort and Filter that the keyset shape produces
NamespaceStore.ListPage No EXPLAIN coverage First page, mid-keyset page, and MaxNamespacePageSize page

Planning cost under the simple protocol

The pgx query mode of this service is QueryExecModeSimpleProtocol, so no prepared statement exists and the planner replans on every execution. The table at the top of this section reports PREPARE and EXECUTE timings, which is what the collection recipe prescribes. This pass also measured the direct shape, with the bind values inlined the way the simple protocol sends them.

Statement Planning, first execution in the session Planning, second execution Execution
ScanTombstonedRepositories at a 7-day window, page 25 0.172 ms, 1 buffer hit 0.124 ms, 1 buffer hit 0.073 ms, then 0.059 ms
ListPage at a mid-keyset cursor, page 25 0.419 ms, 43 buffer hits 0.031 ms, 1 buffer hit 0.062 ms, then 0.050 ms

The first execution of ListPage in a session pays for a relation-cache load of 43 buffers. The scan pays no such load, because the seed inserts ahead of it already loaded the one partition it reads. After that load, planning of either statement stays under 0.13 ms and costs one buffer hit. Both statements stay far under the 100 ms budget in every measurement above.

Query notes:

  • LifecycleScanStore.ScanTombstonedRepositories: the plan loses its index-resident order at one page size and one namespace shape. The shape is a request at MaxLifecycleScanPageSize over a namespace whose eligible set is smaller than that page. The planner then takes a Bitmap Heap Scan plus a Sort, because the LIMIT can stop nothing. Window7dMaxPage and Window7dMaxPageFullBacklog measure the two sides of that boundary. With a page of 1000, an eligible set of 667 rows gives the sort, and an eligible set of 5000 rows gives the index walk. The sort input is the whole eligible set. The planner picks this shape only when it estimates that set at or under the page size, so a correct estimate keeps the sort input near the page size. Measured here, the sort is 667 rows and 71 kB of quicksort memory, at 0.292 ms of execution. Two points follow from this, and both are the reviewer's call. First, the doc comment of the method and lifecycle_explain_integration_test.go both state the oldest-first order as index-resident. That statement holds at a page size under the eligible set, which is the regime the suite covers with 25 rows over 60 eligible tombstones. One sentence in the doc comment can state the other regime. Second, a sweep at the maximum page size meets the sort shape on every namespace it nearly drains. A smaller page size for the sweep is one way to keep it in the index-resident regime.
  • This branch closes the unbounded-SELECT concern that the earlier pass raised. MaxNamespacePageSize and MaxLifecycleScanPageSize are both 1000. Each method rejects a larger limit with its own sentinel, before any statement runs. docs/dev/database-query-patterns.md exempts a pagination clause clamped to a server-side maximum, and both methods now carry one. The MaxPage and Window7dMaxPage rows measure the plan at that maximum.

This is a bot message 🤖 — /smurfit

Edited by Pawel Rozlach

Merge request reports