## Summary On 2026-09-16 at 20:30 UTC, a WAL (write-ahead log) saturation alert fired on GitLab.com's `sec` PostgreSQL database (`patroni-sec`), accompanied by 364 statement timeouts on `INSERT INTO vulnerability_identifiers`. The initially suspected culprit, `vulnerability_identifiers`, was the victim (queued behind a lock) rather than the source. The actual churn source is the join table `vulnerability_occurrence_identifiers`, which is being rewritten by no-op upserts in the security report ingestion pipeline. ## Timeline | Time (UTC) | Event | |---|---| | ~20:30 | WAL saturation begins climbing off a ~11% baseline | | 20:31–20:38 | 364 statement cancellations, SQLSTATE 57014 (`query_canceled`, "canceling statement due to statement timeout"), every one an `INSERT INTO vulnerability_identifiers`, every one from `Security::StoreSecurityReportsByProjectWorker`, all on host `patroni-sec-v18-101-db-gprd` | | ~20:33 | WAL saturation peaks at 68.5%, below the 70% soft SLO | | ~20:35 | Alert's z-score expression reaches ~5.8–6.0 against its `> 3.5` threshold; alert fires | | ~20:37 | `transactions_primary` SLI error ratio peaks at 0.0241%, about 24x the 0.001% baseline | | ~20:40 | Recovery | | 20:40–21:33 | Alert stays latched at 5.8 because the expression uses a `[1h:]` `max_over_time` window | ## Evidence Kibana logs show the 364 statement timeouts, concentrated on a single statement type, during the incident window.  Source: [Kibana, postgres error logs on patroni-sec](https://log.gprd.gitlab.net/app/discover#/?_g=h@e9511ff&_a=h@120cfd9&_tab=h@3ce9259) WAL generation rate saturation peaked at 68.5% (mean 21.3%, weekly p99 42.6%), against a 70% soft SLO and a 90% hard SLO.  Source: [patroni-sec Overview, WAL generation rate saturation](https://dashboards.gitlab.net/d/patroni-sec-main/patroni-sec3a-overview?var-stage=main&orgId=1&from=2026-09-16T19:33:49.726Z&to=2026-09-16T21:35:03.814Z&timezone=utc) The deployed alert expression latched near a z-score of 5.8 for roughly an hour.  Source: [Grafana explore, the deployed alert expression](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22p%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22%28%28%28max_over_time%28rate%28pg_xlog_position_bytes%7Benv%3D%5C%22gprd%5C%22,type%3D%5C%22patroni-sec%5C%22%7D%5B5m%5D%29%5B1h:%5D%29%3E%3D%28100%2A1024%2A1024%29%29-avg_over_time%28rate%28pg_xlog_position_bytes%7Benv%3D%5C%22gprd%5C%22,type%3D%5C%22patroni-sec%5C%22%7D%5B5m%5D%29%5B1d:%5D%29%29%2Fstddev_over_time%28rate%28pg_xlog_position_bytes%7Benv%3D%5C%22gprd%5C%22,type%3D%5C%22patroni-sec%5C%22%7D%5B5m%5D%29%5B1d:%5D%29%20and%20on%28fqdn%29%28pg_replication_is_replica%3D%3D0%29%29%3E3.5%22,%22legendFormat%22:%22%7B%7Bfqdn%7D%7D%22,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22range%22:true,%22instant%22:false%7D%5D,%22range%22:%7B%22from%22:%222026-09-16T20:00:00.000Z%22,%22to%22:%222026-09-16T22:00:00.000Z%22%7D,%22compact%22:false%7D%7D&orgId=1) Dead tuples across all sec tables (1,012 series) show no single obvious culprit at this resolution.  Source: [Grafana explore, dead tuples across all sec tables](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22p%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22pg_stat_user_tables_n_dead_tup%7Btype%3D%5C%22patroni-sec%5C%22,%20env%3D%5C%22gprd%5C%22,%20relname%3D~%5C%22%28analyzer%7Cascp%7Cdast%7Cdependency_list%7Cdependency_management%7Cgovern%7Cgroup_security%7Cpm_malware%7Cproject_security%7Cproject_to_security%7Csbom%7Csecret_detection%7Csecurity%7Cvulnerabilit%29.%2A%5C%22%7D%22,%22legendFormat%22:%22%7B%7Brelname%7D%7D%22,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22range%22:true,%22instant%22:false%7D%5D,%22range%22:%7B%22from%22:%222026-09-16T20:00:00.000Z%22,%22to%22:%222026-09-16T21:00:00.000Z%22%7D,%22compact%22:false%7D%7D&orgId=1) The `transactions_primary` SLI error ratio spike correlates with the WAL pressure window.  Source: [patroni-sec Overview, transactions_primary SLI error ratio](https://dashboards.gitlab.net/d/patroni-sec-main/patroni-sec3a-overview?var-stage=main&orgId=1&from=2026-09-16T20:00:00.000Z&to=2026-09-16T21:00:00.000Z&timezone=utc) Isolating the two identifier tables gives the decisive comparison: `vulnerability_occurrence_identifiers` accumulates dead tuples fast, `vulnerability_identifiers` does not.  Source: [Grafana explore, dead tuples for the two identifier tables](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22p%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22pg_stat_user_tables_n_dead_tup%7Btype%3D%5C%22patroni-sec%5C%22,%20env%3D%5C%22gprd%5C%22,%20relname%3D~%5C%22vulnerability_identifiers%7Cvulnerability_occurrence_identifiers%5C%22%7D%22,%22legendFormat%22:%22%7B%7Brelname%7D%7D%22,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22range%22:true,%22instant%22:false%7D%5D,%22range%22:%7B%22from%22:%222026-09-16T20:00:00.000Z%22,%22to%22:%222026-09-16T21:00:00.000Z%22%7D,%22compact%22:false%7D%7D&orgId=1) ### Decisive measurement (primary host `patroni-sec-v18-101-db-gprd`, 20:00–21:00 UTC) | Series | Behaviour | |---|---| | `vulnerability_occurrence_identifiers` | 3.05M dead tuples at 20:00, rising to 3.43M by 20:11, a cliff to near zero at 20:12 when vacuum completes, flat until ~20:37, then climbing to 1.55M by 21:00 | | `vulnerability_identifiers` | Stays low the whole hour, 137K at 21:00, no spike in the 20:30–20:40 incident window | Two conclusions follow: 1. The join table `vulnerability_occurrence_identifiers` is the churn source, re-accumulating roughly 1,100 dead tuples per second after 20:37. 1. Autovacuum on `vulnerability_occurrence_identifiers` completed at 20:12, about 19 minutes before the first timeout at 20:31 — autovacuum was not running during the incident. This independently reconfirms a correction already recorded on the parent epic: autovacuum is not a necessary precondition for these WAL events. ## Root cause The initially suspected table, `vulnerability_identifiers`, was the victim rather than the source of the WAL pressure. **1. Lock queueing at the first write.** `IngestIdentifiers` is the first task in the 18-entry `SEC_DB_TASKS` list in `ee/app/services/security/ingestion/ingest_report_slice_service.rb`. Its `INSERT ... ON CONFLICT DO UPDATE` is therefore the first statement in each slice transaction to take row-level exclusive locks, on rows shared by concurrent jobs for the same project (the conflict key is `(project_id, fingerprint)`, and re-scans of a project hit an identical identifier set). When commit latency rose under WAL pressure, the queue formed at this first lock-taking statement — which is why 364 cancellations concentrate on one statement instead of spreading across the 18-task chain. The task also runs inside `feature_flagged_transaction_for`, so those locks are held for the whole slice transaction, not just the statement. **2. No-op rewrites on the join table.** `IngestFindingIdentifiers` in `ee/app/services/security/ingestion/tasks/ingest_finding_identifiers.rb` sets `self.unique_by = %i[occurrence_id identifier_id]` and does not set `self.uses`. Because `unique_by` is present, `Gitlab::Ingestion::BulkInsertableTask` routes to `bulk_upsert`, which emits `ON CONFLICT DO UPDATE`. But this table is pure link data: `(occurrence_id, identifier_id)` with no mutable payload. Finding IDs are stable across re-scans, so every re-scan rewrites identical rows, touching 4 indexes each, changing nothing except `updated_at` (which `BulkInsertableTask#timestamps` stamps with `Time.zone.now` on every row). Since `self.uses` is unset, nothing consumes `RETURNING` either, so the update does no useful work at all. **3. Per-slice rather than per-report deduplication.** `Security::Ingestion::IngestReportService` slices findings 50 at a time (`Security::IngestionConstants::COMPONENTS_BATCH_SIZE = 50`) and runs the full task chain per slice. `IngestIdentifiers#report_identifiers` calls `.uniq` only over that slice's finding maps. Identifiers are many-to-many with findings, so a project with N findings re-upserts the same identifier rows up to N/50 times in a single job. **Latent risk.** `IngestFindingIdentifiers` has no sort, unlike `IngestIdentifiers`, which carries an explicit deadlock-avoidance sort with a comment explaining why. The incident produced 57014 (`query_canceled`) and not 40P01 (`deadlock_detected`), so this did not fire here, but it is a gap on the same table. **Related mitigation, not the cause.** https://gitlab.com/gitlab-org/gitlab/-/work_items/629542 caps the `IngestIdentifiers` upsert at 50 rows per statement. It is worth doing, but it is recorded here as a mitigation rather than the root cause. `bulk_upsert!` already sliced at `DEFAULT_BATCH_SIZE = 500` (`app/models/concerns/bulk_insert_safe.rb`), and the statements that actually timed out carried 11 to 130 rows. Since `statement_timeout` measures elapsed time rather than work, a transaction waiting longer than 15s for a row lock times out on whichever statement needs that lock, at any row count. Smaller batches give each statement a fresh timeout window, which helps only when the statement's own work exceeded the limit. The two outstanding queries below would revise this: if these upserts turn out to be rewriting large numbers of rows through bloated indexes, statement size becomes causal after all. ### Why `updated_at` makes the rewrites look unavoidable `vulnerability_identifiers.updated_at` is written on every scan and read by nothing. Verified: no reads anywhere in `ee/app`, `ee/lib`, `app` or `lib` — no ordering, filtering or `touch`; not covered by any of the table's seven indexes; not exposed through GraphQL, a serializer or an entity. It is `NOT NULL` and populated purely because `Gitlab::Ingestion::BulkInsertableTask#timestamps` stamps `created_at` and `updated_at` on every row. As written it means "the last time any scan touched this row," which carries little information for an identifier — a CVE's name and URL do not change. Removing it would not by itself stop the rewrites. Postgres rewrites the row on any `DO UPDATE`, even when every assigned value is byte-identical, so dropping `updated_at` from the `SET` list changes nothing on its own. What it actually does is block the natural SQL remedy, a predicate that skips no-op updates: ```sql ON CONFLICT (project_id, fingerprint) DO UPDATE SET ..., updated_at = excluded.updated_at WHERE (target.external_id, target.external_type, target.name, target.url) IS DISTINCT FROM (excluded.external_id, excluded.external_type, excluded.name, excluded.url) ``` `updated_at` has to be left out of that comparison because it always differs. With the predicate in place the column stops being noise and starts meaning "when this identifier last actually changed." The SQL predicate prevents the write — no new row version, no WAL, no dead tuple — but Postgres still takes a row lock on the conflicting row in order to evaluate it. A Ruby-side pre-filter, the `filter_map` plus `attributes_changed?` pattern already used by `Sbom::Ingestion::Tasks::IngestOccurrences`, keeps unchanged rows out of the statement altogether, so neither the write nor the lock happens. Given this issue has both a WAL problem and a lock-contention problem, prefer the Ruby pre-filter. Keeping the column is the right call either way — dropping it would need a migration on a very large table and buys nothing over the predicate. ## Timed-out slices are dropped, not retried When the `vulnerability_identifiers` insert hits its statement timeout, nothing retries it — the slice's findings are silently discarded and the job still reports success. | Layer | What happens | |---|---| | Statement | Cancelled. No statement-level retry. | | Transaction | All 18 `SEC_DB_TASKS` roll back, so none of that slice's writes land. | | Slice loop | `rescue StandardError` swallows it and the loop continues to later slices. | | Job | No exception escapes, so the worker completes as `done` and `sidekiq_options retry: 3` never engages. | | Scan | Marked with an `IngestionError` processing error, which `without_errors` then filters out of later runs. | The mechanism: - `Security::Ingestion::IngestReportService#ingest_slice`, in `ee/app/services/security/ingestion/ingest_report_service.rb`, wraps the slice call (`IngestReportSliceService.execute(pipeline, slice)`) in `rescue StandardError => error` and calls `process_error`, which calls `Gitlab::ErrorTracking.track_exception`, then `set_ingestion_error!`, then returns an empty array so the caller's `flat_map` doesn't fail on a non-array return value. - `set_ingestion_error!` calls `security_scan.add_processing_error!(INGESTION_ERROR)`, where `INGESTION_ERROR` is `{ type: 'IngestionError', message: 'Ingestion failed for some vulnerabilities' }`. It is guarded by `return if errored`, so a scan records the error once no matter how many of its slices fail. - `Security::Ingestion::IngestReportsService#latest_security_scans` resolves `pipeline.root_ancestor.self_and_descendant_security_scans.without_errors.latest`, and `without_errors` is the scope `where("jsonb_array_length(COALESCE(info->'errors', '[]'::jsonb)) = 0")` on `ee/app/models/security/scan.rb`. **Verified locally**: a throwaway spec against `Security::Ingestion::IngestReportService` raised a real `ActiveRecord::QueryCanceled` from one slice of a two-slice run (`Security::IngestionConstants::COMPONENTS_BATCH_SIZE` stubbed down to split the collection; `Security::Ingestion::IngestReportSliceService.execute` stubbed so the first slice raises and the second returns normally). 5 examples, 0 failures, covering: the timeout does not propagate out of `IngestReportService.execute`; only the surviving slice's ids are returned; the processing error is recorded on `security_scan.processing_errors`; the scan drops out of `Security::Scan.without_errors`; the later slice still executes and ingests successfully. **Scale and scope**: a slice is `Security::IngestionConstants::COMPONENTS_BATCH_SIZE = 50` findings, so each cancellation discards up to 50 findings. The 364 cancellations between 20:31 and 20:38 therefore represent up to roughly 18,000 findings not ingested, not 364 statements that were retried — the "up to" qualifier holds because a slice can hold fewer than 50 findings and the cancellations span multiple scans. The loss is partial, not total: later slices in the same run still ingest, a direct consequence of the `rescue` operating per slice rather than per run. The exclusion is scan-scoped and not permanent: once a scan is marked with `IngestionError`, that scan is skipped if ingestion runs again for the same pipeline, so a re-run does not recover it — but a new pipeline creates fresh scan rows and ingests the findings then. The gap is bounded to "until the next successful scan for that project." **One thing this rules out**: it would be natural to assume cancelled slices retry and reissue the same upserts, adding to the write volume. They do not — the `rescue StandardError` in `ingest_slice` prevents any retry. The write amplification described elsewhere in this issue comes from no-op rewrites (rows rewritten to identical values), not from retried timed-out slices. ### Why the timeouts are a symptom, not a WAL source The timed-out statements contributed almost no WAL. They were cancelled on `IngestIdentifiers`, the first statement in the transaction, while blocked waiting for a row lock. A statement waiting on a lock has not written anything yet, so there are no heap or index changes to log — each one burned 15 seconds of wall clock and produced essentially nothing. The causal direction runs the other way: WAL saturation raises commit `fsync` latency, lock holders keep their row locks longer, and waiters exceed `statement_timeout`. The timeouts are a symptom of the WAL pressure, not its source. The WAL was generated by the *other* transaction in each conflict — the one holding the lock, doing real work across all 18 tasks, and committing. Three ways failures do add WAL, worth separating from what happened here: 1. Rolled-back work is still WAL-logged. Postgres writes WAL as each change happens, not at commit; `ROLLBACK` appends an abort record rather than undoing anything, so the WAL already written stays. A transaction cancelled at task 7 or task 13, after real work, pays full WAL cost for zero durable data. That barely applies in this incident, since every cancellation landed on task 1. 1. Those aborted writes leave dead tuples that still need vacuuming, and vacuum writes WAL of its own. Small here, for the same reason. 1. The dropped work comes back. A discarded slice is not saved work — the findings are re-ingested by the next pipeline's scan, paying the same write cost later. The timeouts shift WAL in time rather than avoiding it, while leaving a data gap in between. There is also a modest second-order effect: a transaction blocked for up to 15 seconds holds its snapshot open, pinning the xmin horizon and briefly delaying vacuum from reclaiming dead tuples. Bounded by the timeout, so minor. Fixing the timeouts will not reduce WAL. The WAL levers are the no-op rewrites — `ON CONFLICT DO NOTHING` on `vulnerability_occurrence_identifiers`, and skipping unchanged rows in `IngestIdentifiers`. The timeout behaviour is a separate, correctness-shaped problem, and the two should be tracked and judged separately. ### Reproducing the lock-wait cancellation locally To test whether those cancellations are lock waits rather than slow inserts, three concurrent `psql` sessions ran against PostgreSQL 17.8 on the GDK `gitlabhq_development_sec` database, using the real `vulnerability_identifiers` and `vulnerability_namespace_statistics` tables. Neither table has foreign keys, so no fixtures were needed. - Session A: `BEGIN;` then `UPDATE vulnerability_namespace_statistics SET total = total WHERE namespace_id = 1;`, then held open. - Session B: `BEGIN;` then `UPDATE vulnerability_identifiers SET name = name WHERE project_id = 1 AND fingerprint = '\x7198...';`, then the same namespace update as Session A, which blocked. Session B acquired the namespace lock only after Session A's transaction ended, so the namespace row serialises the two transactions. - Session C: `INSERT INTO vulnerability_identifiers (...) VALUES (...) ON CONFLICT (project_id, fingerprint) DO UPDATE SET name = EXCLUDED.name, updated_at = now();` on the same key Session B holds. Session C was run three times with different timeout settings: | Timeout setting on session C | Error | | --- | --- | | `statement_timeout` only, at 3s — same settings shape as production | `ERROR: 57014: canceling statement due to statement timeout`, with `CONTEXT: while inserting index tuple (0,1) in relation "vulnerability_identifiers"` | | `lock_timeout = 2s` also set | `ERROR: 55P03: canceling statement due to lock timeout` | | `transaction_timeout = 3s` instead, a PostgreSQL 17 setting | `FATAL: 25P04: terminating connection due to transaction timeout`, which also closes the connection | The 3 second timeout was used only to keep the test quick; production sets `statement_timeout` to 15 seconds and leaves `lock_timeout` and `transaction_timeout` at 0. That configuration matches the first row — SQLSTATE `57014`, not a transaction timeout — and `57014` is what the incident produced: 364 cancellations on `INSERT INTO vulnerability_identifiers`. Proven: - A statement blocked on a `vulnerability_identifiers` row lock, under production's timeout configuration, fails with SQLSTATE `57014` and carries the `CONTEXT: while inserting index tuple ... in relation "vulnerability_identifiers"` line. The incident's error class is therefore consistent with lock-wait cancellation rather than a slow insert. - The root namespace row in `vulnerability_namespace_statistics` does serialise transactions belonging to different projects, because Session B could not proceed until Session A finished. Not proven: - That the namespace statistics task was the lock holder during the 2026-09-16 incident. The reproduction demonstrates one mechanism that produces the observed error; it does not establish that this was the mechanism on the day. Treat it as a candidate pathway, not a confirmed one. Follow-up: a lock-wait cancellation carries that `CONTEXT` line and a merely slow statement does not, so checking whether the production `57014` errors carry the same `CONTEXT` line would confirm or rule out lock contention directly from the logs. The step-by-step causal chain, with the per-project and per-namespace lock detail, is written up in https://gitlab.com/gitlab-org/gitlab/-/work_items/629996. ## What the measurements settled The two outstanding queries have been run against Mimir. Both confirm the hypothesis: these upserts overwhelmingly rewrite rows that already exist, and most of those rewrites are not HOT, so they carry full index maintenance. The following was measured over a one-hour window on 2026-09-20, using `increase(...[1h])` on `pg_stat_user_tables` counters, aggregated `max by (relname)` across the cluster's hosts. These were instant queries: the Explore time picker was set to the last 24 hours, which only fixes the evaluation timestamp, while the `[1h]` range selector is what sets the measurement window. The figures below are therefore per hour, not per day. | Measure | `vulnerability_identifiers` | `vulnerability_occurrence_identifiers` | |---|---|---| | Inserts per hour | 39,166 | 199,259 | | Updates per hour | 903,257 | 2,132,063 | | Updates per insert | 23.1 | 10.7 | | HOT updates per hour | 289,057 | 1,369,714 | | HOT fraction | 32.0% | 64.2% | | Non-HOT updates per hour | 614,200 | 762,349 | | Index entries written per hour by non-HOT updates | 4,299,400 (7 indexes) | 3,049,396 (4 indexes) | Derived from the above: - 96% of row-touches on `vulnerability_identifiers` are updates rather than inserts, and 91% on `vulnerability_occurrence_identifiers`. - Combined, 3,035,320 row updates per hour across the two tables, about 843 per second. - All 2,132,063 updates per hour on `vulnerability_occurrence_identifiers` are no-ops by definition: the table holds only `(occurrence_id, identifier_id)` and has no mutable payload, so an update can never change anything. That is about 592 wasted row rewrites per second. - 7,348,796 index entries written per hour by non-HOT updates across the two tables, about 2,041 per second. Of those, 1,228,400 per hour (about 341 per second) go to the two redundant `partition_id` indexes on `vulnerability_identifiers`. 1. The `ON CONFLICT DO NOTHING` change on `IngestFindingIdentifiers` is the best-evidenced item on the list. It removes 2.1 million pointless row rewrites an hour and the 3.0 million index entries that come with them. 1. Skipping no-op updates in `IngestIdentifiers` is confirmed at a 23:1 update-to-insert ratio. The measurement cannot say what fraction of those updates change a value, but identifiers are stable by nature, so most of them will not. 1. The index work is justified. At a 32% HOT fraction on `vulnerability_identifiers`, roughly two thirds of its rewrites pay full maintenance across all seven indexes, two of which are redundant. **Why HOT is failing**: HOT requires both that no indexed column actually changed and that the page has free space for the new row version. Since the values being written are largely identical to what is already stored, the indexed-column condition should be satisfied, which points at page space as the constraint. No `fillfactor` is set on either table, so both default to 100, leaving no room on the page for a HOT update to land. Lowering it would let more rewrites stay HOT and skip index maintenance entirely. ## Proposed work The list below is ordered by priority, highest first, with the ordering weighing ongoing impact and confidence in the evidence against effort and risk. | # | Change | Status | Problem | Evidence | Effort | MRs | | --- | --- | --- | --- | --- | --- | --- | | 1 | [Stop silently dropping timed-out slices](https://gitlab.com/gitlab-org/gitlab/-/work_items/629988) | open | Correctness | Up to 50 findings discarded per cancellation while the job reports `done` | Small–medium | [!256624](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256624) | | 2 | [`ON CONFLICT DO NOTHING` for `IngestFindingIdentifiers`](https://gitlab.com/gitlab-org/gitlab/-/work_items/629989) | **done** | WAL | 2,132,063 no-op updates/h and 3.0M index entries/h, on a table with no mutable payload | Small | [!256534](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256534) merged | | 3 | [Skip no-op updates in `IngestIdentifiers`](https://gitlab.com/gitlab-org/gitlab/-/work_items/629990) | **done** | WAL | 23.1 updates per insert | Small–medium | [!256535](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256535) merged, [!256114](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256114) merged | | 4 | [Move the aggregate counter tasks out of the slice transaction](https://gitlab.com/gitlab-org/gitlab/-/work_items/629996) | in progress | Timeouts | The namespace task upserts one row per **ancestor** namespace, so unrelated projects under one group serialise on the root row | Medium–large | [!256767](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256767) merged, [!257005](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/257005), [!256593](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256593) | | 5 | [`deduplicate :until_executing` → `:until_executed`](https://gitlab.com/gitlab-org/gitlab/-/work_items/629991) | **done** | Timeouts | Two jobs for the same project can execute concurrently today | Trivial | [!256533](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256533) merged | | 6 | [Add a `concurrency_limit` to the worker](https://gitlab.com/gitlab-org/gitlab/-/work_items/629992) | **done** | Timeouts | Nothing bounds how many transactions contend | Trivial | [!256757](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256757) merged | | 7 | [Lower `fillfactor` to 85–90 on both tables](https://gitlab.com/gitlab-org/gitlab/-/work_items/629994) | open | WAL | HOT only 32.0% and 64.2%; both tables default to 100 | Small, database review | — | | 8 | [Drop the two redundant `partition_id` indexes](https://gitlab.com/gitlab-org/gitlab/-/work_items/629995) | **won't do** | WAL | 1,228,400 wasted index entries/h — but review concluded the indexes are not redundant | Medium, database review | — | | 9 | [Review cross-worker contention from CVS](https://gitlab.com/gitlab-org/gitlab/-/work_items/629993) | open | Timeouts | CVS spans many projects per job and already has dedup and a limit of 10; the limit is unverified | Small | — | | 10 | [Deduplicate identifiers per report rather than per 50-finding slice](https://gitlab.com/gitlab-org/gitlab/-/work_items/629997) | open | WAL | Removes the N/50 re-upsert multiplier | Medium | — | | 11 | [Add the missing deadlock-avoidance sort](https://gitlab.com/gitlab-org/gitlab/-/work_items/629998) | open | Latent risk | Item 2 largely removes the exposure by dropping the exclusive locks | Trivial | — | Five of the eleven are closed: items 2, 3, 5 and 6 shipped, and item 8 was closed as won't do after review found the indexes are not redundant. Item 4 is partly shipped — [!256767](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256767) moved the namespace statistics task into its own transaction, which is the cross-project half; [!256593](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/256593) reorders the two remaining counter tasks within the transaction and [!257005](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/257005) corrects the feature flag actor. Items 7 and 10 are worth re-measuring now that 2 and 3 have shipped, since both lose value once the no-op rewrites stop. ### Plan to remove the lock contention All 18 tasks in `Security::Ingestion::IngestReportSliceService::SEC_DB_TASKS` run inside a single `SecApplicationRecord.transaction`. `Gitlab::Ingestion::BulkInsertableTask` picks the write path with `unique_by.present? && on_conflict != :nothing ? bulk_upsert : bulk_insert`. `bulk_upsert` issues `ON CONFLICT DO UPDATE`, which takes an exclusive lock on every conflicting row and holds it until the transaction commits. `bulk_insert` passes `skip_duplicates: true`, i.e. `ON CONFLICT DO NOTHING`, which takes no exclusive lock on a conflicting row. That gives two collision domains, and only two, because every conflict key in the chain except one is scoped to a project: - **Cross-project — one source.** `IngestVulnerabilityNamespaceStatistics` upserts one row per ancestor namespace, because `Vulnerabilities::NamespaceStatistics::UpdateService` expands `traversal_ids`. Every project in a group hierarchy therefore writes that hierarchy's root namespace row, so unrelated projects under one top-level group serialise on a single row, once per 50-finding slice, held until commit. - **Same-project — everything else.** All remaining conflict keys are project, finding or occurrence scoped. Two different projects can never contend on the same row; these writes collide only when two jobs process the same project concurrently. #### Inventory of writes that hold a row lock to commit | Task | Lock taken on | Path | Domain | Coverage | | --- | --- | --- | --- | --- | | `IngestIdentifiers` | `Vulnerabilities::Identifier`, `(project_id, fingerprint)` | `DO UPDATE` | same-project | item 3, !256535, merged | | `IngestFindingIdentifiers` | `Vulnerabilities::FindingIdentifier`, `(occurrence_id, identifier_id)` | `DO UPDATE` | same-project | item 2, !256534, open | | `IngestFindings` | `Vulnerabilities::Finding`, `uuid` | `DO UPDATE` | same-project | none | | `IngestFindingSignatures` | `Vulnerabilities::FindingSignature`, `(finding_id, algorithm_type, signature_sha)` | `DO UPDATE` | same-project | none | | `IngestFindingEvidence` | `Vulnerabilities::Finding::Evidence`, `vulnerability_occurrence_id` | `DO UPDATE` | same-project | none | | `IngestVulnerabilityFlags` | `Vulnerabilities::Flag`, `(vulnerability_occurrence_id, flag_type, origin)` | `DO UPDATE` | same-project | none | | `IngestRemediations` | `Vulnerabilities::FindingRemediation`, `(vulnerability_occurrence_id, vulnerability_remediation_id)` | `DO UPDATE` | same-project | none | | `IngestFindingRiskScores` | `Vulnerabilities::FindingRiskScore`, `finding_id` | `DO UPDATE` | same-project | none | | `IngestVulnerabilityReads::Upsert` | `Vulnerabilities::Read`, `vulnerability_id` | `DO UPDATE` | same-project | none; gated behind `turn_off_vulnerability_read_create_db_trigger_function` | | `IncreaseCountersTask` | one `security_statistics` row per project | increment | same-project | item 4 | | `IngestVulnerabilityStatistics` | `vulnerability_statistics`, `ON CONFLICT (project_id)` | `DO UPDATE` | same-project | item 4 | | `IngestVulnerabilityNamespaceStatistics` | one row per ancestor namespace | `DO UPDATE` | cross-project | item 4 | | `IngestVulnerabilities` | vulnerabilities, in `mark_resolved_as_detected` and `set_present_on_default_branch` | `update_all` | same-project | none | | `IngestVulnerabilityReads::Update` | vulnerability reads | `update_all` | same-project | none | Three tasks declare no `unique_by` and so take the `DO NOTHING` path, which leaves them low risk: `IngestFindingLinks`, `AttachFindingsToVulnerabilities`, `UpdateFindingEnrichmentVulnerabilities`. Two write nothing to the sec database at all — `HooksExecution` and `MatchAscpComponents` only enqueue workers. #### Phase 1 — remove the same-project overlap Concurrent jobs on one project are the precondition for every same-project collision in the table, and the changes are trivial: - Item 5, `deduplicate :until_executed` with `if_deduplicated: :reschedule_once` on `Security::StoreSecurityReportsByProjectWorker`. Already merged as !256533. - Item 6, add a `concurrency_limit` to that worker, which has none today. If no two jobs for one project ever run concurrently, the nine `DO UPDATE` tasks above cannot contend with each other at all, however many locks each of them takes. That is why this comes first: it settles the domain rather than trimming individual writes inside it. #### Phase 2 — remove the single cross-project source Item 4, move the aggregate counter tasks out of the slice transaction: to `after_commit`, to their own transaction, or to once per job instead of once per slice. !256593 is an interim step that only reorders them to run last in the transaction; that shortens the hold but leaves the serialisation in place. The `ORDER BY namespace_id, traversal_ids` already present in `Vulnerabilities::NamespaceStatistics::UpdateService` is a deliberate lock-ordering measure, which indicates this contention was known before this analysis. #### Phase 3 — reduce the locks taken on the hottest tables This shrinks the blast radius of any overlap that survives phases 1 and 2: - Item 2, `ON CONFLICT DO NOTHING` for `IngestFindingIdentifiers`. Its unique key covers the whole row, so a conflicting insert has nothing to update. - Item 3, skip unchanged rows in `IngestIdentifiers`, which avoids the lock as well as the write. - Item 10, deduplicate identifiers across the whole report rather than per 50-finding slice, which removes a re-upsert multiplier. #### Phase 4 — verify rather than assume Re-audit the seven uncovered `DO UPDATE` tasks once phases 1 to 3 have landed. If same-project overlap is gone, they need no work. If it is not, `IngestFindings` is the next collision point: it is keyed on `uuid`, and it carries the most indexes of any table in the chain and the widest row. Confirm the same before rolling out `turn_off_vulnerability_read_create_db_trigger_function`, since that flag converts the `vulnerability_reads` write into another locking upsert inside this transaction. #### Cutting across the phases - Item 9, review cross-worker contention from continuous vulnerability scanning. `PackageMetadata::GlobalAdvisoryScanWorker` runs `Security::Ingestion::IngestCvsSliceService`, and one CVS job spans many projects, which makes it the only other genuine cross-project collider. It already has `deduplicate :until_executed` and `concurrency_limit -> { 10 }`; the open question is whether 10 is the right number given what it contends with. - Item 11, add the missing deadlock-avoidance sort to `IngestFindingIdentifiers`. This becomes largely moot once item 2 drops the exclusive locks there. #### Item 1 is not part of this Item 1, stop silently dropping timed-out slices, reduces no locking and remediates no contention. It decides whether contention causes silent data loss, which is why it is ranked first despite not appearing in any phase above. ### Separate track The revised WAL spike alert is tracked separately at https://gitlab.com/gitlab-com/runbooks/-/merge_requests/11560. It is not a draft and its pipeline is green; it is waiting on reviewer re-review, with six review threads still open. Nothing in this issue blocks it and it blocks nothing here. ## References - Parent epic: https://gitlab.com/groups/gitlab-org/-/epics/23596 - Sidekiq flow control and the concurrency limiter: https://gitlab.com/gitlab-org/gitlab/-/work_items/629387 - Batching mitigation for the identifier upsert: https://gitlab.com/gitlab-org/gitlab/-/work_items/629542 - Autovacuum health indicator connection fix: https://gitlab.com/gitlab-org/gitlab/-/work_items/628693 - SBOM ingestion write reduction: https://gitlab.com/gitlab-org/gitlab/-/work_items/628686 - Watched-table widening for the SBOM worker: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/255632 - Watched-table widening for the security report worker: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/255634 - Defer delay raised to 5 minutes: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/255973 - Revised WAL spike alert, still in draft: https://gitlab.com/gitlab-com/runbooks/-/merge_requests/11560