EU Redis OOM and provider sync backlog from Zendesk macro upsert

Incident date: August 24–26, 2026

Environment: EU production

Status: Resolved; recovery backlog drained

Severity: High

Summary

PR #10450 (ENG-2504) moved Zendesk conversation sync into API2. The new persistZendeskMacros upsert used ON CONFLICT (organization_data_source_id, external_id) without the partial-index predicate that Rails created on macros. PostgreSQL rejected every conflicting macro write with “there is no unique or exclusion constraint matching the ON CONFLICT specification”. Zendesk sync jobs failed, retried up to 10 times each, and BullMQ retained completed and failed job payloads in EU Redis.

Over roughly 48 hours the provider-conversation-sync queue accumulated on the order of ~175k BullMQ keys and ~19k waiting jobs. EU Redis (cache.t4g.small, noeviction) reached capacity. API2 workers then crash-looped on Redis OOM command not allowed (RULEBASE-API2-6Z ), which blocked deploys and stopped meaningful queue drain until memory was reclaimed.

PR #10604 (ENG-2567) added WHERE external_id IS NOT NULL to the upsert so it matches index idx_macros_on_organization_data_source_id_external_id. The fix deployed to EU on August 26, 2026. Operational recovery trimmed BullMQ retention backlog, re-enqueued failed jobs, and temporarily raised worker capacity and global sync concurrency while the queue drained.

Downstream symptoms — empty Intercom import shells, stale QA evaluation requests, and evaluation timeouts — were secondary effects of sync and worker stall rather than separate root causes. We found no evidence of permanent customer-data loss.

Customer impact

EU-wide: Conversation sync, QA evaluations, and other API2 BullMQ work delayed while Redis was full and workers could not start reliably.
Zendesk customers on the API2 sync path: Sync failed on tickets whose write plan included macros; repeated retries amplified queue volume.
Intercom customers: Imports continued while sync jobs stalled, leaving conversations with missing parts and empty UI shells until sync caught up.
QA evaluations: Requests timed out or were skipped by audit jobs while workers were unavailable or overloaded.

Detection

Sentry RULEBASE-API2-AY surfaced the Postgres upsert error from persistZendeskMacros (~3.2k events from August 24).
Rising provider-conversation-sync wait depth and near-zero failed→completed conversion pointed to a systemic sync failure rather than provider rate limits.
EU API2 worker deploy failures and RULEBASE-API2-6Z showed Redis OOM command not allowed during worker boot when BullMQ tried to write.
Customer-visible reports (empty conversations, evaluations not finishing) lagged the infrastructure failure because imports and skip-audit rows continued on adjacent paths.

Timeline

All times UTC.

August 24: #10450 API2 Zendesk sync begins failing macro upserts; Sentry AY volume rises.
August 24–25: provider-conversation-sync backlog grows; failed jobs retry and accumulate in Redis.
August 26 (morning): EU Redis near capacity; API2 worker deploys fail with Redis OOM.
August 26 ~12:44: #10604 fix deployed to EU API2 workers.
August 26 (midday): BullMQ retention trimmed; failed sync and QA jobs re-enqueued from preserved payloads.
August 26 (afternoon): EU API2 workers scaled up; global sync concurrency raised to accelerate drain.
August 26 (evening): Queue depth declining; sync and evaluation throughput recovering.

Root cause

Primary: partial-index mismatch in Zendesk macro upsert

Rails defines:


CREATE UNIQUE INDEX idx_macros_on_organization_data_source_id_external_id
  ON macros (organization_data_source_id, external_id)
  WHERE external_id IS NOT NULL;

API2 initially issued:


ON CONFLICT (organization_data_source_id, external_id)
DO UPDATE …

PostgreSQL requires the ON CONFLICT target to match a unique index including partial predicates. Without WHERE external_id IS NOT NULL, the statement fails on every macro upsert during Zendesk sync persist. The sync transaction rolls back; BullMQ retries the job with exponential backoff (10 attempts per providerConversationSyncJobOptions in queue-jobs.ts).

This bug shipped with the API2 Zendesk sync cutover in #10450 . It did not affect Intercom-only persist paths directly, but shared worker and Redis infrastructure still stalled Intercom sync and QA work.

Amplifier: heavy BullMQ retention on a small Redis instance

provider-conversation-sync jobs retain completed and finally failed payloads for 24 hours and 7 days respectively. BullMQ retries update the same job record and move it back to delayed or waiting; only the final failed attempt enters the failed-retention set. The observed ~175k keys therefore reflected the accumulated queued and retained BullMQ state, rather than ten retained payloads per failing job, on cache.t4g.small with noeviction. Once memory was exhausted, all BullMQ producers and consumers failed writes — not only Zendesk sync.

Throughput ceiling during recovery

Drain speed is bounded by PROVIDER_CONVERSATION_SYNC_GLOBAL_CONCURRENCY (cluster-wide Redis limit), not raw worker count alone. Raising worker tasks without raising global concurrency did not materially increase sync throughput.

Contributing factors

API2 ON CONFLICT targets were not validated against existing Postgres unique indexes, including partial predicates.
High-volume queue retention settings were not sized against EU Redis capacity under sustained failure/retry storms.
No alert fired when EU Redis memory crossed a safe threshold before noeviction blocked all writes.
Shared BullMQ Redis serves all API2 queues; one failing job class can exhaust memory for unrelated work.

What went well

Sentry AY pointed directly at the failing upsert before Redis OOM obscured worker logs.
Comparing API2 persist SQL with the Rails macros migration quickly confirmed the index mismatch.
#10604 was a minimal, targeted fix with an obvious post-deploy signal.
BullMQ trim and failed-job retry recovered queue state without flushing Redis entirely.

What did not go well

The partial-index mismatch shipped with the Zendesk sync cutover and ran for ~48 hours before deploy.
Retry amplification turned a deterministic SQL error into infrastructure outage.
Worker health checks did not distinguish “process up” from “unable to connect to BullMQ.”
Recovery capacity (worker floor and global concurrency) was not codified in deployment config before the incident.

Resolution

PR #10604 — add partial-index predicate to macro upsert:


ON CONFLICT (organization_data_source_id, external_id)
WHERE external_id IS NOT NULL
DO UPDATE …

After deploy, we trimmed BullMQ retention backlog in EU Redis, re-enqueued failed sync and QA jobs from preserved payloads, and temporarily raised EU API2 worker count and global sync concurrency until the queue drained.

Production verification

Sentry AY error rate drops after #10604 deploy.
API2 EU workers stay running (no Redis OOM on boot).
provider-conversation-sync failed set stays near zero after retry.
Active sync jobs saturate configured global concurrency.
provider-conversation-sync wait returned to normal levels.
EU Redis memory stable below prior peak after trim and drain.

Corrective actions

Immediate

Ship macro upsert fix (#10604 ).
Reclaim Redis memory from BullMQ retention backlog.
Re-enqueue failed sync and QA jobs.
Scale EU API2 workers and raise global sync concurrency for drain.

Hardening

Add a DB integration test that asserts every API2 ON CONFLICT target matches an existing Postgres unique index, including partial predicates.
Review BullMQ removeOnComplete / removeOnFail for high-volume queues — lower failed retention or move job history off Redis sooner.
Right-size EU Redis or add memory alerting below 85% on noeviction instances.
Codify EU API2 worker minimum capacity and sync concurrency in Flightcontrol so redeploys do not collapse recovery settings.
Raise API2 Postgres pool limits before increasing global sync concurrency further.

2026-07-20 — EU PostgreSQL saturation — separate failure mode (discovery queries), same theme of background work overwhelming shared infrastructure.
2026-07-20 — GoodJob backlog and database saturation — US analogue for worker-pool exhaustion from retry amplification.

Lessons

Partial unique indexes require matching predicates in every upsert. A deterministic SQL error becomes an infrastructure incident when retries retain failure state in a shared, memory-capped queue store.

Queue retention and Redis sizing must assume sustained failure, not just happy-path volume. Global concurrency limits matter as much as worker count for database-heavy sync pipelines.