`Sbom::IngestReportsWorker` writes to the `sec` database inefficiently: each slice opens up to 8 separate transactions instead of one, the slice size is only 10, and no concurrency limit is set. Vulnerability ingestion, a comparable worker in the same codebase, already avoids the transaction problem — proof the SBOM approach isn't the only workable pattern. ## Verified findings ``` Sbom::IngestReportsWorker#perform(pipeline_id) └─ Sbom::Ingestion::IngestReportsService.execute in_lock(per-project, ttl: 30.minutes) └─ ExecutionStrategy::Default#execute 1. per report → IngestReportService.execute └─ occurrence_map_collection.each_slice(BATCH_SIZE = 10) └─ IngestReportSliceService → 8 tasks in sequence 2. set_latest_ingested_sbom_pipeline_id 3. DeleteNotPresentOccurrencesService each_batch(of: 100) 4. publish_ingested_sbom_event ``` - `BATCH_SIZE = 10` at `ee/app/services/sbom/ingestion/ingest_report_service.rb:50`. - 8 tasks run per slice (`TASKS` in `ee/app/services/sbom/ingestion/ingest_report_slice_service.rb`), each one a `bulk_upsert!` or `bulk_insert!` via `Gitlab::Ingestion::BulkInsertableTask`. - `feature_flagged_transaction_for` at `ee/app/models/ee/sec_application_record.rb:16-22` opens a fresh transaction on every call. Nothing wraps the 8 tasks in an outer transaction, so each opens its own. - Deletes already batch at 100 (`ee/app/services/sbom/ingestion/delete_not_present_occurrences_service.rb:8,49`). - Redundant upserts are **not** a lever here: unchanged rows are already skipped. `IngestOccurrences#attributes` skips them via `attributes_changed?`; `IngestComponents` skips via `next` on an existing record. ## Production evidence From an investigation into WAL generation pressure on the `sec` database, window 2026-09-10 22:00 to 2026-09-11 00:00 UTC: - p99 sec-database writes per job: `Sbom::IngestReportsWorker` reached 38,896 writes in a single job at 22:09, versus 587 for the next-highest worker. - Total sec-database writes: 97,352 in the 22:09 minute bucket, against a baseline of roughly 5,000-25,000 per minute. - `patroni-sec` WAL generation saturation peaked at 65.9% at 22:40, against a 70% soft SLO. - About 4,900 slices x 8 tasks is roughly 39,200 statements, against the 38,896 observed — a genuine large first-time ingestion of a ~49,000-component report, not redundant work. - The 22:09 write spike and the 22:40 saturation peak are about 30 minutes apart, so no causal claim is made between them. **Per-job p99 write count**  **Total sec-database write count**  ## Comparison: vulnerability ingestion already does this correctly - `COMPONENTS_BATCH_SIZE = 50` at `ee/lib/security/ingestion_constants.rb`, five times the SBOM slice size. - `run_tasks_in_sec_db` at `ee/app/services/security/ingestion/ingest_slice_base_service.rb:75-88` opens ONE transaction and runs all 18 `SEC_DB_TASKS` for that slice inside it. - 12 of those 18 tasks use the same `Gitlab::Ingestion::BulkInsertableTask` as SBOM does. Their internal `feature_flagged_transaction_for` calls join the already-open outer transaction rather than committing separately. | | Vulnerability ingestion | SBOM ingestion | |---|---|---| | Slice size | 50 | 10 | | Slices needed | 1 | 5 | | Transactions | 1 | up to 40 (8 tasks x 5 slices) | | Commits | 1 | up to 40 | | `set_config` calls | 1 | up to 40 | - A commit record is roughly 40 bytes, against hundreds of bytes per row change, so commit count barely moves `pg_xlog_position_bytes` (which tracks LSN advance). - What transaction count really drives is fsync count, IOPS, and commit latency, plus repeated writes of the same partially-filled 8 KB WAL block — none of which the LSN metric sees, and none of which reaches replicas. ## Proposal 1 — collapse per-slice transactions, then raise the slice size 1. **Wrap the slice in one transaction**, mirroring `IngestSliceBaseService#run_tasks_in_sec_db`, so the per-task `feature_flagged_transaction_for` calls join it instead of committing separately. 2. **Raise the slice size** from 10 toward 50, raised incrementally and measured rather than jumped to a large value. Risks: a single transaction spanning all 8 tasks holds row locks for the whole slice; 10 was a deliberate choice documented in the class comment, so raising it needs justification. ## Proposal 2 — add a concurrency limit Worker principles call for a `concurrency_limit` on every worker; this one has none and inherits the fleet-percentage default. A limit bounds how many jobs run at once, smoothing the aggregate write peak — it does nothing about the cost of a single job. The number should come from measuring this worker's actual concurrency rather than copying the sibling's 200. ## What each proposal wins Figures are for a single ingestion of a report with roughly 49,000 components, the size implied by the observed spike. | | Statements | Commits | fsyncs | |---|---|---|---| | Today (slice 10, 8 transactions per slice) | 39,200 | 39,200 | 39,200 | | Transaction wrap only | 39,200 | 4,900 | 4,900 | | Slice size 50 only | 7,840 | 7,840 | 7,840 | | Both | 7,840 | 980 | 980 | - Proposal 1 together: 5x fewer statements, 40x fewer commits and fsyncs. - WAL bytes: the commit-record saving is about 1.5 MB, against roughly 25 MB (order-of-magnitude estimate, not a measurement) of row and index WAL for 49,000 rows — about 6 percent. - So Proposal 1's real wins are primary disk IOPS, commit latency, transaction IDs consumed, and CPU and lock churn. It is not a meaningful lever on the MB/s saturation metric. - Proposal 2 changes total WAL bytes not at all. It bounds the peak *rate*, and since the alert fires on rate, that is a direct win for the alert. It also bounds replica receive and apply pressure, which is what the 180 MB/s ceiling in the runbook is derived from. - Proposal 2's cost: jobs over the limit are buffered, delaying SBOM ingestion. - Neither proposal reduces the WAL bytes of a genuinely large ingestion — that floor is set by the rows and index entries written. Suggested order: Proposal 1 first, since it directly reduces statement and commit count, then Proposal 2 to bound the aggregate peak. ## Relationship to existing work A merge request already adds a database-health deferral to this worker behind a default-off flag — a reactive backstop that defers jobs once the database is signalling stress. The changes here reduce the load instead. Links: - Merge request adding the health deferral: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/254955 - Feature flag rollout issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/628675 - WAL runbook: https://runbooks.gitlab.com/patroni/primary_db_node_wal_generation_saturation/ - Per-job write-count p99 view: https://log.gprd.gitlab.net/app/r/s/fKE7c - Total sec-database write counts view: https://log.gprd.gitlab.net/app/r/s/zeaJX - Grafana dashboard from the investigation: https://dashboards.gitlab.net/goto/cfxwmpoyla0w0f?orgId=1 🤖 Generated with [Claude Code](https://claude.com/claude-code)