EU PostgreSQL saturation from background discovery queries

Incident date: July 20-21, 2026

Environment: EU production

Status: Recovering; background queues remain paused or reduced

Severity: High

Summary

EU production experienced sustained database saturation, API timeouts, queued background work, and PostgreSQL write failures after API2 conversation-redaction discovery began scanning a large, sparsely eligible dataset. The original discovery query created enough temporary database I/O to exhaust local space. A follow-up query removed the temporary-file spill but still read most of the database to find small batches of eligible conversations.

The conversation-part-chunk-sync queue added another broad, parallel read across conversation parts and related detail tables while PostgreSQL was already saturated. Six Rails workers, one API2 worker, normal application traffic, and autovacuum increased contention but were not the initiating cause.

We paused redaction discovery and chunk syncing, cancelled the active redaction discovery backends, and reduced EU Rails workers from 6 to 2. Early recovery metrics showed database connections falling from approximately 190-208 to 152, disk queue depth falling from approximately 36 to 10, and read I/O falling from approximately 3,200 to 2,240 IOPS.

Customer impact

• EU API2 and Rails requests timed out or returned errors.
• API2 p99 latency reached approximately 20 seconds and Rails p99 reached approximately 38 seconds during the investigation window.
• At least 38 API2 target 5xx responses occurred in a 2-minute sample.
• GoodJob and BullMQ work accumulated because workers could not complete database-heavy jobs reliably.
• PostgreSQL writes failed with PG::DiskFull, including ConversationTranscriptionBatchSuccessJob and pg_notify writes.
• Conversation redaction and conversation-part chunk indexing were paused to protect interactive traffic.

No evidence currently indicates permanent customer-data loss. Queued or failed background work must be reviewed before the paused queues resume.

Detection

The incident was detected through customer-visible timeouts, growing background queues, and a Sentry alert from Rails:

PG::DiskFull: could not access status of transaction 0
Could not write to file "pg_notify/BD66" at offset 65536:
No space left on device

AWS Performance Insights and PostgreSQL activity then showed sustained database load dominated by IO:DataFileRead, LWLock:WALWrite, BufferMapping, and concurrent long-running discovery queries.

Timeline

All times are Eastern Time on July 20-21, 2026 unless noted otherwise.

• July 20, 2:31 PM: PR #8400 , which moved conversation redaction to API2, merged.
• July 20, 7:36 PM: Sentry reported PG::DiskFull from ConversationTranscriptionBatchSuccessJob. EU requests and background jobs were already timing out or backing up.
• July 20, 8:37 PM: PR #8423  merged. It replaced the multi-detail-table discovery join and global sort with correlated probes and primary-key ordering.
• Overnight: The revised redaction discovery avoided the previous temporary-file spill, but both discovery steps continued scanning a sparsely eligible dataset for more than 7 hours. Database load remained high.
• July 21, approximately 4:00 AM: We confirmed 30-49 average active sessions in Performance Insights, near-saturated read I/O, database connections above 190, and disk queue depth rising to approximately 36.
• July 21, approximately 4:05 AM: We paused the redaction queues and cancelled the two active redaction discovery leader backends. The redaction queries disappeared from PostgreSQL activity.
• July 21, approximately 4:10 AM: We reduced the EU Rails worker service from 6 tasks to 2. The service reached 2 healthy running tasks.
• July 21, approximately 4:11 AM: A separate conversation-part-chunk-sync query remained active with 2 PostgreSQL parallel workers and substantial DataFileRead waits.
• July 21, approximately 4:12 AM: We paused conversation-part-chunk-sync.
• July 21, approximately 4:13 AM: Early metrics showed connections at approximately 152, disk queue depth near 10, and read I/O near 2,240 IOPS. The database was recovering but had not returned to a confirmed steady state.

Root cause

The incident was caused by unbounded discovery work being run as recurring background jobs against large production tables.

Conversation redaction discovery

The API2 redaction scheduler attempted to discover small batches of conversations requiring redaction. Eligibility was sparse, so PostgreSQL had to inspect a large portion of the conversation and content-detail tables before finding a batch.

The initial implementation joined several large detail tables and sorted qualifying conversations. This produced heavy temporary-file and I/O usage and eventually exhausted the database’s available local space.

PR #8423  fixed the temporary spill by using correlated content probes and scanning in primary-key order. That changed the failure mode but did not bound the amount of work required when few rows were eligible. A LIMIT 100 constrained the number of returned rows, not the number of rows PostgreSQL had to examine. Two discovery steps also ran concurrently, multiplying the scan pressure.

Conversation-part chunk sync

The conversation-part-chunk-sync worker independently searched for changed conversation-part content. Its candidate query unions ordered scans across conversation parts, chats, emails, notes, assignments, call details, and transcript messages before joining the selected IDs back to the source tables.

While redaction and autovacuum had already saturated PostgreSQL, this query used parallel workers and generated additional DataFileRead pressure. It prolonged the incident and slowed recovery, although it was not the original source of the disk-full error.

Contributing factors

• Sparse eligibility: Small result batches gave the appearance of bounded work even though PostgreSQL could scan most of the source tables.
• Overlapping discovery: Multiple redaction discovery steps could run at the same time.
• Shared database: Interactive traffic, Rails jobs, API2 jobs, discovery scans, and autovacuum competed for the same 4-vCPU RDS instance and gp3 volume.
• Worker concurrency: EU had 6 Rails worker tasks plus an API2 worker during the incident.
• No query-level circuit breaker: The discovery paths did not stop after a fixed scan budget or short statement timeout.
• Insufficient early warning: We detected the incident after user-visible timeouts and a disk-full write failure rather than before the database reached saturation.
• Multiple heavy maintenance queues: Pausing redaction did not pause conversation-part chunk syncing, so significant background read pressure remained.

What went well

• Sentry exposed the disk-full failure and the affected Rails job.
• PostgreSQL activity and Performance Insights identified the exact active statements and wait classes.
• Pausing queues was available as a reversible mitigation.
• Cancelling the two redaction backends removed the immediate discovery load without terminating PostgreSQL or restarting the application.
• Reducing Rails workers and pausing chunk syncing produced measurable early recovery.
• The revised redaction query in PR #8423 eliminated the original temporary-file spill, even though it did not fully solve scan amplification.

What did not go well

• We treated a small result LIMIT as a sufficient bound on database work.
• Production validation for PR #8423 used planning-only EXPLAIN; it did not reveal the runtime cost of scanning a sparse production dataset.
• Redaction discovery and chunk syncing lacked database-load-aware throttling.
• The first mitigation targeted redaction only, leaving another I/O-heavy maintenance queue active.
• There was no single runbook listing all background queues that perform large conversation-table scans.

Resolution and recovery

The following mitigations are currently in place:

• conversation-redaction is paused.
• conversation-redaction-tick is paused.
• conversation-part-chunk-sync is paused.
• The active redaction discovery PostgreSQL backends were cancelled.
• EU Rails workers were reduced from 6 to 2.
• Autovacuum was left running because it had reached the index-vacuum phase and terminating it would discard useful recovery work.

Before declaring the incident resolved, confirm that API latency, error rate, average active sessions, read I/O, disk queue depth, and database connections remain at normal levels for at least 30 minutes.

Corrective actions

Required before resuming redaction

• Replace full-table eligibility discovery with a durable cursor or explicit work table populated when source content changes.
• Ensure only one redaction discovery pass can run at a time across both discovery steps.
• Add a short statement timeout and a hard per-run scan budget to discovery queries.
• Validate the replacement with EXPLAIN (ANALYZE, BUFFERS) on a production-shaped snapshot, including the sparse-eligibility case.
• Resume with one worker and a small batch size, then watch database load before increasing concurrency.
• Audit failed and waiting redaction jobs so stale jobs do not create an immediate retry storm.

Required before resuming conversation-part chunk sync

• Profile every branch of candidate_part_ids and confirm that each cursor predicate has a matching index.
• Prevent scheduled runs from overlapping and cap PostgreSQL parallelism for this maintenance path.
• Add a database statement timeout and reduce the initial batch size.
• Resume as a canary and verify read I/O, disk queue depth, and API latency after each batch.

Operational safeguards

• Alert before saturation on database load, disk queue depth, temporary storage pressure, database connections, and sustained read latency.
• Add a background-maintenance runbook listing queue names, pause controls, expected queries, and safe cancellation steps.
• Define separate concurrency budgets for interactive work and database-heavy maintenance jobs.
• Add queue-depth and oldest-job-age dashboards for both GoodJob and BullMQ.
• Review whether increasing the RDS gp3 volume from 300 GiB to at least 400 GiB is worthwhile for additional baseline IOPS and throughput. Treat this as headroom, not a substitute for query fixes.

Lessons

A query is not operationally bounded because it returns a bounded number of rows. For sparse discovery workloads, the system must bound rows examined, elapsed time, and concurrency. Database-heavy maintenance queues also need to be managed as one shared resource class: pausing one queue does not protect production if another queue can issue an equivalent scan.

Follow-up status

This document should be updated after recovery with:

• the confirmed end time and total impact window;
• the number of failed, retried, and delayed jobs;
• the final steady-state latency and database metrics;
• links to the permanent query and scheduling fixes;
• the queue-resume sequence and outcome.