Persist count of secrets per group and project (!234604) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Implements persistence of secret counts per group and project namespace as described in issue #597886.

This makes it possible to efficiently count the total number of secrets in a root namespace without querying the OpenBao API on every request. Instead, counts are maintained asynchronously and can be aggregated with a single SQL query per root namespace.

Key changes:

Introduces a new namespace_secret_counts database table with namespace_id (PK), root_namespace_id (indexed FK), and count columns. Both FK columns cascade on delete.
Adds namespace_id_for_secret_count to GroupSecretsManager (returns group_id) and ProjectSecretsManager (returns project_namespace_id) to provide the correct namespace ID for each context.
Enqueues a RefreshNamespaceSecretCountWorker job after a secret is successfully created or deleted. The worker queries OpenBao for the accurate count and upserts the record, avoiding increment/decrement skew.
Adds a ReconcileNamespaceSecretCountsCronWorker scheduled daily at 23 2 * * * to reconcile all counts and prevent long-term drift.
Adds a partial index (id) WHERE status = 1 on group_secrets_managers and project_secrets_managers so the reconcile cron's *.active.each_batch iteration walks only active rows via index range scan instead of filtering in memory after a PK scan.

This unblocks:

#597867 - Emit daily secrets_stored billable event
#599012 - Service Ping metric for secrets_stored

Changelog: added

References

Issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/597886
Parent epic: https://gitlab.com/groups/gitlab-org/-/work_items/21755

Screenshots or screen recordings

N/A - backend-only change (database + workers)

Before	After
Secret counts required querying OpenBao API per namespace	Secret counts are persisted in `namespace_secret_counts` and refreshed asynchronously

How to set up and validate locally

Enable the secrets manager for a group or project.
Create a secret via the API or UI.
Verify a record is upserted in namespace_secret_counts for the corresponding namespace:
```
SELECT * FROM namespace_secret_counts WHERE namespace_id = <namespace_id>;
```
Create a second secret — the count column should be re-fetched from OpenBao and reflect the new total (the worker overwrites count with the value returned by OpenBao; it does not increment).
Delete a secret — the count column should again be re-fetched from OpenBao and reflect the new total (no decrement; full re-fetch on every change).
Trigger the reconcile cron worker manually and confirm counts remain consistent:
```
SecretsManagement::ReconcileNamespaceSecretCountsCronWorker.new.perform
```

Database review — queries and execution plans (click to expand)

This MR introduces one new table (namespace_secret_counts), three model-level scopes that hit the database (active, by_root_namespace, for_namespace_id), two eager-load scopes (with_group, with_project_namespace), one upsert, one delete, and two partial indexes on existing tables (group_secrets_managers, project_secrets_managers). Each is covered below with the generated SQL and an EXPLAIN (ANALYZE, BUFFERS) plan.

Schema summary

Object	Type	Notes
`namespace_secret_counts`	new table, `gitlab_main_org`	PK = `namespace_id` (also FK → `namespaces.id` ON DELETE CASCADE). `root_namespace_id` FK → `namespaces.id` ON DELETE CASCADE, btree-indexed. `count integer NOT NULL DEFAULT 0`. Sharding key `namespace_id`. `table_size: small`.
`index_namespace_secret_counts_on_root_namespace_id`	new index	btree on `(root_namespace_id)`. Supports the `by_root_namespace` consumer query.
`index_group_secrets_managers_active_on_id`	new partial index	btree on `(id) WHERE status = 1`. Supports `GroupSecretsManager.active.each_batch`.
`index_project_secrets_managers_active_on_id`	new partial index	btree on `(id) WHERE status = 1`. Supports `ProjectSecretsManager.active.each_batch`.

Size estimate for `namespace_secret_counts` (local)

Row size: bigint + bigint + integer + 2 × timestamptz ≈ 36 B logical, ~80 B with row header/alignment. Upper bound = 1 row per active group/project secrets manager; today this is in the low thousands cluster-wide. Even at 10⁶ rows the table is < 100 MB on disk — fits the documented table_size: small.

Local seeding used for Q1–Q4:

INSERT INTO namespace_secret_counts (namespace_id, root_namespace_id, count, created_at, updated_at)
SELECT n.id, n.id, (random() * 50)::int, now(), now()
FROM namespaces n
LIMIT 500
ON CONFLICT (namespace_id) DO NOTHING;

Q1 — `RefreshService#upsert_count` (single-row upsert, new table → local)

Source: ee/app/services/secrets_management/namespace_secret_counts/refresh_service.rb

INSERT INTO namespace_secret_counts
  (namespace_id, root_namespace_id, count, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (namespace_id) DO UPDATE
SET count             = EXCLUDED.count,
    root_namespace_id = EXCLUDED.root_namespace_id,
    updated_at        = EXCLUDED.updated_at
RETURNING namespace_id;

created_at is intentionally not in DO UPDATE SET (update_only: %i[count root_namespace_id updated_at]) so re-refreshing a namespace preserves the original insert time.

EXPLAIN (ANALYZE, BUFFERS) — local

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
INSERT INTO namespace_secret_counts (namespace_id, root_namespace_id, count, created_at, updated_at)
VALUES (1, 1, 5, now(), now())
ON CONFLICT (namespace_id) DO UPDATE
  SET count = EXCLUDED.count,
      root_namespace_id = EXCLUDED.root_namespace_id,
      updated_at = EXCLUDED.updated_at;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Insert on public.namespace_secret_counts  (cost=0.00..0.01 rows=0 width=0) (actual time=0.138..0.138 rows=0 loops=1)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: namespace_secret_counts_pkey
   Tuples Inserted: 0
   Conflicting Tuples: 1
   Buffers: shared hit=15
   ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)
         Output: '1'::bigint, '1'::bigint, 5, now(), now()
 Planning:
   Buffers: shared hit=15
 Planning Time: 0.044 ms
 Execution Time: 0.271 ms
(12 rows)

Expected: Insert on namespace_secret_counts → conflict resolved via namespace_secret_counts_pkey, 1 row, single-digit buffer hits.

Q2 — `RefreshService#remove_count` (delete via `for_namespace_id` scope, new table → local)

DELETE FROM namespace_secret_counts WHERE namespace_id = $1;

EXPLAIN (ANALYZE, BUFFERS) — local

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM namespace_secret_counts WHERE namespace_id = 1;
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on public.namespace_secret_counts  (cost=0.27..2.29 rows=0 width=0) (actual time=0.078..0.079 rows=0 loops=1)
   Buffers: shared hit=9 dirtied=1
   ->  Index Scan using namespace_secret_counts_pkey on public.namespace_secret_counts  (cost=0.27..2.29 rows=1 width=6) (actual time=0.056..0.057 rows=1 loops=1)
         Output: ctid
         Index Cond: (namespace_secret_counts.namespace_id = 1)
         Buffers: shared hit=8 dirtied=1
 Planning:
   Buffers: shared hit=81
 Planning Time: 0.714 ms
 Execution Time: 0.267 ms
(10 rows)

Expected: Delete on namespace_secret_counts → Index Scan using namespace_secret_counts_pkey, 1 row.

Q3 — `by_root_namespace` consumer scope (new table → local)

Anticipated downstream consumer (#597867):

SELECT COALESCE(SUM(count), 0)
FROM namespace_secret_counts
WHERE root_namespace_id = $1;

EXPLAIN (ANALYZE, BUFFERS) — local

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT COALESCE(SUM(count), 0)
FROM namespace_secret_counts
WHERE root_namespace_id = 1;
                                                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.29..2.30 rows=1 width=8) (actual time=0.043..0.043 rows=1 loops=1)
   Output: COALESCE(sum(count), '0'::bigint)
   Buffers: shared hit=8 dirtied=1
   ->  Index Scan using index_namespace_secret_counts_on_root_namespace_id on public.namespace_secret_counts  (cost=0.27..2.29 rows=1 width=4) (actual time=0.035..0.036 rows=1 loops=1)
         Output: namespace_id, root_namespace_id, count, created_at, updated_at
         Index Cond: (namespace_secret_counts.root_namespace_id = 1)
         Buffers: shared hit=8 dirtied=1
 Planning:
   Buffers: shared hit=86
 Planning Time: 0.665 ms
 Execution Time: 0.072 ms
(11 rows)

Expected: Aggregate → Bitmap Heap Scan on namespace_secret_counts → Bitmap Index Scan on index_namespace_secret_counts_on_root_namespace_id. Heap fetch is required since count is not in the index. If a real plan against representative data shows heap fetches dominating, switch to INCLUDE (count) in a follow-up.

Q4 — `for_namespace_id` lookup (new table → local)

SELECT * FROM namespace_secret_counts WHERE namespace_id = $1;

EXPLAIN (ANALYZE, BUFFERS) — local

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM namespace_secret_counts WHERE namespace_id = 1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using namespace_secret_counts_pkey on public.namespace_secret_counts  (cost=0.27..2.29 rows=1 width=36) (actual time=0.043..0.043 rows=1 loops=1)
   Output: namespace_id, root_namespace_id, count, created_at, updated_at
   Index Cond: (namespace_secret_counts.namespace_id = 1)
   Buffers: shared hit=6
 Planning:
   Buffers: shared hit=94
 Planning Time: 0.667 ms
 Execution Time: 0.107 ms
(8 rows)

Expected: Index Scan using namespace_secret_counts_pkey, 1 row.

Q5–Q7 — `GroupSecretsManager.active.with_group.each_batch(of: 500)` (existing table → postgres.ai)

Source: ee/app/workers/secrets_management/reconcile_namespace_secret_counts_cron_worker.rb. Generated SQL:

-- Boundary (Q5)
SELECT id FROM group_secrets_managers
WHERE status = 1 AND id >= $1
ORDER BY id ASC LIMIT 1 OFFSET 500;

-- Batch fetch (Q6)
SELECT group_secrets_managers.*
FROM group_secrets_managers
WHERE status = 1 AND id >= $1 AND id < $2
ORDER BY id ASC;

-- Eager-load preload from .with_group (Q7)
SELECT namespaces.*
FROM namespaces
WHERE namespaces.type = 'Group' AND namespaces.id IN ($1, $2, …);

Joe bot — prepare the partial index in the thin clone, then explain:

\exec CREATE INDEX CONCURRENTLY index_group_secrets_managers_active_on_id ON group_secrets_managers USING btree (id) WHERE (status = 1);

\explain SELECT id FROM group_secrets_managers WHERE status = 1 AND id >= 1 ORDER BY id ASC LIMIT 1 OFFSET 500;

\explain SELECT * FROM group_secrets_managers WHERE status = 1 AND id >= 1 AND id < 99999999 ORDER BY id ASC;

\explain SELECT * FROM namespaces WHERE type = 'Group' AND id IN (<500 group_ids from prior batch>);

Command:

explain SELECT id FROM group_secrets_managers WHERE status = 1 AND id >= 1 ORDER BY id ASC LIMIT 1 OFFSET 499;

Q5 plan (postgres.ai)

explain SELECT id FROM group_secrets_managers WHERE status = 1 AND id >= 1 ORDER BY id ASC LIMIT 1 OFFSET 499;

 Limit  (cost=1.65..3.17 rows=1 width=8) (actual time=0.075..0.076 rows=0 loops=1)
   Buffers: shared hit=4 read=1
   ->  Index Only Scan using index_group_secrets_managers_active_on_id on public.group_secrets_managers  (cost=0.13..1.65 rows=1 width=8) (actual time=0.073..0.074 rows=3 loops=1)
         Index Cond: (group_secrets_managers.id >= 1)
         Heap Fetches: 0
         Buffers: shared hit=4 read=1
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Summary
  Time: 0.699 ms
    planning: 0.595 ms
    execution: 0.104 ms
  Shared buffers:
    hits: 4 (~32 KiB) from the buffer pool
    reads: 1 (~8 KiB) from the OS file cache

Notes:

Index Only Scan using index_group_secrets_managers_active_on_id with Heap Fetches: 0 — the new partial index is doing all the work; no heap pages touched.
Inner node actual rows=3 ⇒ only ~3 active group_secrets_managers rows exist on prod today, so the daily cron will issue exactly one each_batch iteration for groups for the foreseeable future.
5 buffer pages total, sub-millisecond execution.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152101

Command:

explain SELECT * FROM group_secrets_managers WHERE status = 1 AND id >= 1 AND id < 99999999 ORDER BY id ASC;

Q6 plan (postgres.ai)

explain SELECT * FROM group_secrets_managers WHERE status = 1 AND id >= 1 AND id < 99999999 ORDER BY id ASC;

 Index Scan using index_group_secrets_managers_active_on_id on public.group_secrets_managers  (cost=0.13..3.15 rows=1 width=98) (actual time=0.035..0.036 rows=3 loops=1)
   Index Cond: ((group_secrets_managers.id >= 1) AND (group_secrets_managers.id < 99999999))
   Buffers: shared hit=5
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Summary
  Time: 0.601 ms
    planning: 0.531 ms
    execution: 0.070 ms
  Shared buffers:
    hits: 5 (~40 KiB) from the buffer pool
    reads: 0

Notes:

Index Scan using index_group_secrets_managers_active_on_id — same partial index used as Q5, this time as a regular index scan (heap fetched for non-id columns since the SELECT projects all columns). No filter on status because the partial-index predicate already implies status = 1.
actual rows=3 matches Q5 (3 active group secrets managers in prod), so the per-iteration scan touches all 3 rows in one shot, sorted by id, with no heap pages beyond the matching tuples themselves.
5 buffer hits, 0.070 ms execution.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152102

Command:

explain SELECT * FROM namespaces WHERE type = 'Group' AND id IN (SELECT id FROM namespaces WHERE type = 'Group' LIMIT 500);

Q7 plan (postgres.ai)

explain SELECT * FROM namespaces WHERE type = 'Group' AND id IN (SELECT id FROM namespaces WHERE type = 'Group' LIMIT 500);

 Nested Loop  (cost=17.75..1815.93 rows=58 width=374) (actual time=59.254..1238.751 rows=500 loops=1)
   Buffers: shared hit=1680 read=1257 dirtied=5
   WAL: records=5 fpi=5 bytes=40001
   ->  HashAggregate  (cost=17.18..22.18 rows=500 width=4) (actual time=55.872..56.723 rows=500 loops=1)
         Group Key: namespaces_1.id
         Buffers: shared hit=368 read=69 dirtied=5
         ->  Limit  (cost=0.43..15.93 rows=500 width=4) (actual time=2.249..55.554 rows=500 loops=1)
               Buffers: shared hit=368 read=69 dirtied=5
               ->  Index Only Scan using index_groups_on_parent_id_id on public.namespaces namespaces_1
                                          (cost=0.43..300638.20 rows=9699204 width=4) (actual time=2.247..55.467 rows=500 loops=1)
                     Heap Fetches: 14
                     Buffers: shared hit=368 read=69 dirtied=5
   ->  Index Scan using namespaces_pkey on public.namespaces
                       (cost=0.57..3.59 rows=1 width=374) (actual time=2.361..2.361 rows=1 loops=500)
         Index Cond: (namespaces.id = namespaces_1.id)
         Filter: ((namespaces.type)::text = 'Group'::text)
         Rows Removed by Filter: 0
         Buffers: shared hit=1312 read=1188
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

This query stresses the worst case: a full 500-id batch (we only have ~3 active managers today, so the real preload is currently 3 PK lookups). Notes:

Outer node: Nested Loop driving 500 Index Scan using namespaces_pkey lookups. PK lookup per id is the optimal access pattern; PostgreSQL would only switch to a Bitmap Index Scan at much larger batch sizes.
Filter: type = 'Group' runs after the PK lookup but Rows Removed by Filter: 0 — every id we pass already belongs to a Group, so the filter never rejects.
Buffers: ~2 940 pages total (1 680 hit + 1 257 read ≈ 23 MiB). Postgres.ai notes that "actual timing values may differ from production because actual caches in DB Lab are smaller" — on prod the namespaces btree and heap are largely buffer-pool-resident, so expect this to be substantially faster (estimated O(50-150 ms) hot vs the 1.2 s cold reading we got here).
Today's actual preload (IN (3 ids)) will touch ~5-10 buffer pages and complete in microseconds; this 500-id measurement is a forward-looking ceiling.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152104

Expected: Q5 = Limit → Index Only Scan using index_group_secrets_managers_active_on_id (no heap fetch). Q6 = Index Scan using index_group_secrets_managers_active_on_id + heap fetch for non-id columns. Q7 = Index Scan using namespaces_pkey, N PK lookups.

Q8–Q10 — `ProjectSecretsManager.active.with_project_namespace.each_batch(of: 500)` (existing table → postgres.ai)

-- Boundary (Q8)
SELECT id FROM project_secrets_managers
WHERE status = 1 AND id >= $1
ORDER BY id ASC LIMIT 1 OFFSET 500;

-- Batch fetch (Q9)
SELECT project_secrets_managers.*
FROM project_secrets_managers
WHERE status = 1 AND id >= $1 AND id < $2
ORDER BY id ASC;

-- Eager-load preload from .with_project_namespace (Q10)
SELECT projects.* FROM projects WHERE id IN ($1, $2, …);
SELECT namespaces.* FROM namespaces
WHERE namespaces.type = 'Project'
  AND namespaces.id IN (<project_namespace_ids>);

Joe bot:

\exec CREATE INDEX CONCURRENTLY index_project_secrets_managers_active_on_id ON project_secrets_managers USING btree (id) WHERE (status = 1);

\explain SELECT id FROM project_secrets_managers WHERE status = 1 AND id >= 1 ORDER BY id ASC LIMIT 1 OFFSET 500;

\explain SELECT * FROM project_secrets_managers WHERE status = 1 AND id >= 1 AND id < 99999999 ORDER BY id ASC;

\explain SELECT * FROM projects WHERE id IN (<500 project_ids>);

\explain SELECT * FROM namespaces WHERE type = 'Project' AND id IN (<500 project_namespace_ids>);

Command:

explain SELECT id FROM project_secrets_managers WHERE status = 1 AND id >= 1 ORDER BY id ASC LIMIT 1 OFFSET 499;

Q8 plan (postgres.ai)

explain SELECT id FROM project_secrets_managers WHERE status = 1 AND id >= 1 ORDER BY id ASC LIMIT 1 OFFSET 499;

 Limit  (cost=3.42..3.62 rows=1 width=8) (actual time=0.053..0.054 rows=0 loops=1)
   Buffers: shared hit=4 read=1
   ->  Index Only Scan using index_project_secrets_managers_active_on_id on public.project_secrets_managers  (cost=0.14..3.42 rows=16 width=8) (actual time=0.050..0.051 rows=16 loops=1)
         Index Cond: (project_secrets_managers.id >= 1)
         Heap Fetches: 0
         Buffers: shared hit=4 read=1
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Notes:

Index Only Scan using index_project_secrets_managers_active_on_id with Heap Fetches: 0 — the new partial index does all the work; no heap pages touched.
Inner node actual rows=16 ⇒ ~16 active project_secrets_managers rows on prod today, so the project-side cron iteration also fits in a single each_batch window for the foreseeable future.
5 buffer pages, 0.054 ms execution.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152107

Command:

explain SELECT * FROM project_secrets_managers WHERE status = 1 AND id >= 1 AND id < 99999999 ORDER BY id ASC;

Q9 plan (postgres.ai)

explain SELECT * FROM project_secrets_managers WHERE status = 1 AND id >= 1 AND id < 99999999 ORDER BY id ASC;

 Sort  (cost=4.60..4.64 rows=16 width=67) (actual time=0.042..0.044 rows=16 loops=1)
   Sort Key: project_secrets_managers.id
   Sort Method: quicksort  Memory: 26kB
   Buffers: shared hit=4
   ->  Seq Scan on public.project_secrets_managers  (cost=0.00..4.28 rows=16 width=67) (actual time=0.011..0.013 rows=16 loops=1)
         Filter: ((project_secrets_managers.id >= 1) AND (project_secrets_managers.id < 99999999) AND (project_secrets_managers.status = 1))
         Rows Removed by Filter: 0
         Buffers: shared hit=1
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Notes:

The planner chose Seq Scan here, not the new partial index. This is correct: the entire project_secrets_managers table fits in a single 8 KiB page (Buffers: shared hit=1), so reading the page sequentially is cheaper (cost=0.00..4.28) than the index startup cost. Rows Removed by Filter: 0 confirms every row already satisfies the predicates.
The partial index index_project_secrets_managers_active_on_id is still doing its job — Q8 above shows the planner picks it for the boundary LIMIT/OFFSET query (where ordered-by-id traversal makes it strictly cheaper) and Q6 confirms the same on the group-side fetch path. Once the table grows beyond ~50–100 rows the planner will flip Q9 to Index Scan using index_project_secrets_managers_active_on_id automatically — no code change needed.
16 active project secrets managers, sorted in 26 KiB of work_mem, 1 buffer hit, 0.044 ms execution. No risk for the cron at any plausible near-term scale.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152109

Command:

explain SELECT * FROM projects WHERE id IN (SELECT project_id FROM project_secrets_managers WHERE status = 1);

Q10 plan — projects (postgres.ai)

explain SELECT * FROM projects WHERE id IN (SELECT project_id FROM project_secrets_managers WHERE status = 1);

 Nested Loop  (cost=0.56..61.52 rows=16 width=745) (actual time=8.649..72.404 rows=16 loops=1)
   Buffers: shared hit=31 read=53 dirtied=1
   WAL: records=1 fpi=1 bytes=7437
   ->  Seq Scan on public.project_secrets_managers  (cost=0.00..4.20 rows=16 width=8) (actual time=0.011..0.041 rows=16 loops=1)
         Filter: (project_secrets_managers.status = 1)
         Rows Removed by Filter: 0
         Buffers: shared hit=1
   ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects
                       (cost=0.56..3.58 rows=1 width=745) (actual time=4.517..4.517 rows=1 loops=16)
         Index Cond: (projects.id = project_secrets_managers.project_id)
         Buffers: shared hit=30 read=53 dirtied=1
         WAL: records=1 fpi=1 bytes=7437
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Notes:

Driver = Seq Scan over the 16-row project_secrets_managers (same shape as Q9). Inner side = 16 PK-equivalent lookups against projects via idx_projects_on_repository_storage_last_repository_updated_at (PostgreSQL picked a multi-column index whose leading id-bearing structure is competitive with projects_pkey; either path resolves to a single page hit per id).
84 buffer pages total (~672 KiB), 72 ms cold. Postgres.ai's cold-cache caveat applies; on prod the projects heap pages are buffer-pool resident and this is microseconds.
Today's preload list = 16 ids; at full batch (500) the cost scales linearly with the loop iterations.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152110

Command:

explain SELECT * FROM namespaces WHERE type = 'Project'
  AND id IN (SELECT project_namespace_id FROM project_secrets_managers psm
             JOIN projects p ON p.id = psm.project_id
             WHERE psm.status = 1);

Q10 plan — namespaces (postgres.ai)

explain SELECT * FROM namespaces WHERE type = 'Project'
  AND id IN (SELECT project_namespace_id FROM project_secrets_managers psm
             JOIN projects p ON p.id = psm.project_id
             WHERE psm.status = 1);

 Nested Loop  (cost=62.13..72.16 rows=9 width=374) (actual time=3.141..47.593 rows=16 loops=1)
   Buffers: shared hit=121 read=43
   ->  HashAggregate  (cost=61.56..61.72 rows=16 width=8) (actual time=0.148..0.177 rows=16 loops=1)
         Group Key: p.project_namespace_id
         Buffers: shared hit=84
         ->  Nested Loop  (cost=0.56..61.52 rows=16 width=8) (actual time=0.047..0.143 rows=16 loops=1)
               Buffers: shared hit=84
               ->  Seq Scan on public.project_secrets_managers psm  (cost=0.00..4.20 rows=16 width=8) (actual time=0.013..0.017 rows=16 loops=1)
                     Filter: (psm.status = 1)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=1
               ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects p
                                  (cost=0.56..3.58 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=16)
                     Index Cond: (p.id = psm.project_id)
                     Buffers: shared hit=83
   ->  Index Scan using namespaces_pkey on public.namespaces
                       (cost=0.57..0.65 rows=1 width=374) (actual time=2.960..2.960 rows=1 loops=16)
         Index Cond: (namespaces.id = p.project_namespace_id)
         Filter: ((namespaces.type)::text = 'Project'::text)
         Rows Removed by Filter: 0
         Buffers: shared hit=37 read=43
 Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Notes:

Inner subquery resolves the project_namespace_id set via the same Q10-projects pattern (84 buffer hits) — this part is joins :project rather than the actual rails preload, but it produces the same set of ids the real preload uses.
Outer node: 16 PK lookups via namespaces_pkey. Filter: type = 'Project' runs post-PK with Rows Removed by Filter: 0 — every project namespace id is, in fact, a Project namespace. No wasted work.
164 buffer pages (~1.3 MiB), 47.6 ms cold. Hot on prod with namespaces heap pages cached: O(few ms).
The actual rails preload that runs at runtime is just SELECT * FROM namespaces WHERE id IN (16 ids) — the projects join here is only because we don't have those ids in hand at chatops time. Real cost ≈ outer-node-only ≈ 80 buffer pages, 47 ms cold.
Postgres.ai session: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/51470/commands/152111

Expected: same shape as Q5–Q7. Both preload steps hit projects_pkey / namespaces_pkey directly.

Migration runtime

db/migrate/20260505131851_create_namespace_secret_counts.rb — small new table, transactional, < 1 s expected.
db/post_migrate/20260505131852_add_fks_to_namespace_secret_counts.rb — add_concurrent_foreign_key × 2, validation runs separately, no app-blocking lock.
db/post_migrate/20260505131853_add_active_partial_index_to_secrets_managers.rb — add_concurrent_index × 2, partial. Build wall time and pg_relation_size('index_…') to be reported from postgres.ai once \exec'd.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited May 06, 2026 by Dmytro Biryukov

Persist count of secrets per group and project

What does this MR do and why?

References

Screenshots or screen recordings

How to set up and validate locally

Schema summary

Size estimate for namespace_secret_counts (local)

Q1 — RefreshService#upsert_count (single-row upsert, new table → local)

Q2 — RefreshService#remove_count (delete via for_namespace_id scope, new table → local)

Q3 — by_root_namespace consumer scope (new table → local)

Q4 — for_namespace_id lookup (new table → local)

Q5–Q7 — GroupSecretsManager.active.with_group.each_batch(of: 500) (existing table → postgres.ai)

Q8–Q10 — ProjectSecretsManager.active.with_project_namespace.each_batch(of: 500) (existing table → postgres.ai)

Migration runtime

MR acceptance checklist

Merge request reports

Size estimate for `namespace_secret_counts` (local)

Q1 — `RefreshService#upsert_count` (single-row upsert, new table → local)

Q2 — `RefreshService#remove_count` (delete via `for_namespace_id` scope, new table → local)

Q3 — `by_root_namespace` consumer scope (new table → local)

Q4 — `for_namespace_id` lookup (new table → local)

Q5–Q7 — `GroupSecretsManager.active.with_group.each_batch(of: 500)` (existing table → postgres.ai)

Q8–Q10 — `ProjectSecretsManager.active.with_project_namespace.each_batch(of: 500)` (existing table → postgres.ai)