Conversations Index Cleanup

Tracking the diagnosis and remediation of LWLock:BufferMapping pressure on the conversations table that surfaced first as a wave of EU prod webhook timeouts on 2026-05-05.

Update 2026-05-06: the original diagnosis (10 GB of indexes overwhelming shared_buffers) was a contributing factor but not the trigger. The actual root cause is a missing access path: the Zendesk, Freshdesk, and Jira importers issue find_or_initialize_by queries whose WHERE-clause columns don’t match any index on conversations, so Postgres falls back to a Parallel Sequential Scan over the whole 4 GB heap on every webhook. Under cold cache or any autovacuum stall, those seq-scans pile up on BufferMapping. See § Actual root cause (2026-05-06).

TL;DR

• The dominant Zendesk/Freshdesk/Jira webhook lookup runs as a Parallel Seq Scan over the entire conversations heap because its predicate (organization_id, data_source_id, external_id) doesn’t match the unique partial index (organization_data_source_id, external_type, external_id). Cold-cache mean is 14 s in pg_stat_statements; that’s what was timing out at the 60 s statement_timeout on 2026-05-05.
• The Intercom importer (added later) does match the index. The other three were never updated when the index moved to organization_data_source_id. Same fire, three small diffs, mirrors a pattern that already exists.
• The EU incident self-recovered without any of the runbook’s original “ad-hoc index drops” being executed. As of 2026-05-06 the system is healthy on the surface — but the seq-scan timebomb is still armed and will re-fire the next time the heap falls out of shared_buffers.
• Index footprint cleanup (the original “three-phase plan”) is still worth doing as housekeeping — frees ~1.5 GB on US, reduces autovacuum work — but it is no longer the priority and shouldn’t be framed as fixing the incident.

Symptom (2026-05-05)

EU prod, 2026-05-05 ~13:00 UTC

GoodJob dashboard:

• 5,021 discarded ProcessZendeskWebhookEventJob in the last hour
• 5,010 of those are ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
• 4,994 are from organization_id = 33
• 129 discarded CreateConversationPartRelationshipsJob, 120 of which are also QueryCanceled

The error trace points at:

app/models/conversation/import/zendesk.rb:9:in 'import'

which is:

conversation = Conversation.find_or_initialize_by(
  data_source: DataSource.zendesk!,
  external_id: ticket.id
)

Under ActsAsTenant.with_tenant(organization), this issues SELECT … FROM conversations WHERE organization_id = ? AND data_source_id = ? AND external_id = ? LIMIT 1. We initially assumed this hit a unique partial index and was bottlenecked by buffer-mapping contention. It does not hit a unique index — see § Actual root cause below.

Postgres statement_timeout is set to 60 s in config/database.yml; runtimes of 1m 1s confirmed the timeout was firing, not user-side cancellation.

Initial diagnosis (2026-05-05) — what we observed

The data points below are correct; the interpretation in this section is what 2026-05-06 revised. Kept here as the audit trail.

1. Live database state (EU)

pg_stat_activity:

• pid 1992: autovacuum: VACUUM public.conversations running for 1 h 30 m, stuck on LWLock:BufferMapping.
• 19 other backends running long SELECT conversations … queries, several stuck on LWLock:BufferMapping.
• No idle in transaction sessions.
• No real lock contention chains (only routine GoodJob advisory locks, all under 1 s).

Top wait events:

2. Table & index footprint

Bloat:

Autovacuum progress (the stuck process):

phase: vacuuming indexes
heap_blks_total: 522 362
heap_blks_scanned: 522 362        # heap pass done
heap_blks_vacuumed: 0             # waiting on index pass
index_vacuum_count: 0             # no indexes finished after 90+ min
num_dead_tuples: 210 157

3. Performance Insights — historical view

EU prod DB (db-HEVXLD2JB6JGDVN53QRQ7DAJEE, eu-central-1, PI enabled with 7-day retention):

LWLock:BufferMapping spikes had been climbing daily:

Max vCPU is ~5, so the 2026-05-05 peak was 22× over CPU capacity.

Top SQL by load during the spike (filtered to LWLock:BufferMapping):

4. Original (incorrect) interpretation

We initially concluded that the index footprint (10 GB / 80+ indexes on a 4 GB heap) was the structural cause: every BufferMapping slot was contended, autovacuum couldn’t make progress through the bloated index pass, and a query that “should be” a unique-index hit was waiting on locks. The remediation plan was a three-phase index drop.

What that explanation got right:

• The autovacuum was stuck.
• Index bloat is real and was making the autovacuum’s index pass slow.
• Dropping unused indexes is a sensible housekeeping action.

What it got wrong:

• The webhook query was never going to be a unique-index hit, regardless of BufferMapping pressure. It was a Parallel Seq Scan over the whole heap on every call.
• The actual amplifier was: every webhook = 4 GB of buffer pages touched. With 1.18 calls/s and 8+ workers, that saturates shared_buffers and produces exactly the BufferMapping waits we saw, even without a stuck autovacuum.
• The stuck autovacuum was a symptom of the same buffer pressure (every page of every index it tried to read had to fight the seq-scans), not its cause.

Actual root cause (2026-05-06)

Re-checked EU prod on 2026-05-06 with the same diagnostic queries. Headline findings:

The system self-recovered. Nothing was dropped. The fire is out — and that fact alone disproves the “structural index bloat” framing.

EXPLAIN of the actual hot query

ActsAsTenant.with_tenant(Organization.find(33)) do
  ds = DataSource.zendesk!
  sample = "diagnostic-#{SecureRandom.hex(4)}"
  sql = Conversation.where(data_source: ds, external_id: sample).limit(1).to_sql
  pp ActiveRecord::Base.connection.execute("EXPLAIN (ANALYZE, BUFFERS) #{sql}").to_a
end

Limit  (cost=1000.00..539192.35 rows=1 width=3637) (actual time=420.969..422.658 rows=0 loops=1)
  Buffers: shared hit=434464 read=87898
  I/O Timings: shared read=196.952
  ->  Gather  (cost=1000.00..539192.35 rows=1 width=3637)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Seq Scan on conversations  (cost=0.00..538192.25 rows=1 width=3637)
              Filter: ((organization_id = 33) AND (data_source_id = 1) AND (external_id = 'diagnostic-90e115ec'::text))
              Rows Removed by Filter: 727397
              Buffers: shared hit=434464 read=87898
Execution Time: 422.720 ms

Even hot, this query touches 522,362 buffer pages (= the whole 4 GB heap) per call. That matches pg_stat_statements:

The 49 M-call existence check averaging 0.6 ms is the AR uniqueness validator on Conversation:

validates :external_id, uniqueness: { scope: %i[organization_data_source_id external_type] },
                        if: -> { organization_data_source_id.present? && external_id.present? }

It filters by (external_id, organization_data_source_id, external_type) — which exactly matches the unique partial index — and runs in 0.6 ms. Same row, same column set, same data; just queried through a different access path.

The unique index that the importer should be hitting

CREATE UNIQUE INDEX idx_conversations_org_ds_external_type_external_id
  ON public.conversations
  USING btree (organization_data_source_id, external_type, external_id)
  NULLS NOT DISTINCT
  WHERE ((organization_data_source_id IS NOT NULL) AND (external_id IS NOT NULL));

Index leading columns: (organization_data_source_id, external_type, external_id). Importer query columns: (organization_id, data_source_id, external_id) (with organization_id injected by acts_as_tenant).

Zero columns in common that the planner can use as an access path — so it falls back to seq-scan.

Where the bug lives, and why

Likely chronology: the Conversation schema originally indexed (organization_id, data_source_id, external_id) (or had no unique index). When the organization_data_source association was introduced — encoding (organization_id, data_source_id) as a single FK — the unique index migrated to (organization_data_source_id, external_type, external_id). The Intercom importer was written (or rewritten) against the new shape. The Zendesk, Freshdesk, and Jira importers were never updated and silently degraded to seq-scans, then stayed there until the heap got large enough for the seq-scans to time out.

The Zendesk importer’s retry_import_existing fallback (line 32) does pass organization_data_source: — so the cold path hits the index correctly. The hot path doesn’t.

Why org 33 was disproportionately affected

Org 33 is a Zendesk tenant with the largest conversations row count. Every other Zendesk tenant was running the same seq-scan on the same heap; their webhook calls were just less frequent. There is nothing wrong with org 33’s data — they were the canary, not the cause.

Plan

Five workstreams, ordered by urgency. Each can ship independently.

Workstream 1 — Fix the importer queries (high urgency, low risk)

Status: not started.

Change Zendesk, Freshdesk, and Jira importers to issue find_or_initialize_by(organization_data_source: …, external_type: nil, external_id: …) so they match the existing unique partial index. Mirrors the Intercom pattern.

No data migration required:

• The unique index uses NULLS NOT DISTINCT, so existing rows with external_type IS NULL will still match queries that pass external_type: nil.
• Verified on 2026-05-06 that count(*) FILTER (WHERE organization_data_source_id IS NULL) = 0 for Zendesk, Freshdesk, and Jira conversations rows in both EU and US prod. So the new query — which filters by organization_data_source_id — won’t miss legacy rows.

Expected outcome: the EXPLAIN above turns from Parallel Seq Scan into Index Scan using idx_conversations_org_ds_external_type_external_id, with Buffers: shared hit=4 and sub-millisecond execution.

Verification:

• Run the EXPLAIN snippet above before and after the deploy.
• pg_stat_statements mean for the importer query should drop from ~14 s to ~1 ms within minutes of deploy. Reset stats first to make this clean.
• LWLock:BufferMapping peak AAS in PI should flatten on the next mid-day load period.

This is the single change that prevents the next incident.

Workstream 2 — Audit other seq-scan callsites (medium urgency, low risk)

Status: not started.

There are several Conversation.find_by(external_id: …) callsites that filter by external_id without organization_data_source_id. Same anti-pattern, lower call frequency:

• app/models/organization_data_source.rb:1115 (find_by(external_id:, data_source:))
• app/models/workflow_node/run/create_jira_issue_from_ticket.rb:69
• app/models/workflow_node/run/create_jira_issue_comment.rb:54
• app/models/simulation.rb:575
• app/models/sentinel_conversation_message.rb:103
• app/models/workflow_run/tools/get_conversation_by_external_id_tool.rb:18
• app/models/digest_email/tools/get_conversation_details.rb:38
• lib/tasks/conversations.rake:138 (Conversation.where(data_source:, handling_time: nil))

Each should be reviewed and either pass organization_data_source: (preferred) or be confirmed to run rarely enough that a seq-scan is acceptable. The rake task is run by an operator and is fine. The workflow/sentinel/digest paths happen on user actions and should be tightened.

Defer until Workstream 1 lands and stabilises.

Workstream 3 — Decide whether to add organization_id to the unique index (low priority, probably skip)

Status: open question, recommendation: don’t.

Adding organization_id as a leading column to idx_conversations_org_ds_external_type_external_id would force a 4 GB index rebuild and buy nothing — organization_data_source_id is strictly more selective than organization_id (every ODS belongs to exactly one org). The only case where leading with organization_id would help is a query that filters by organization_id but not organization_data_source_id, and we have none of those for external_id lookups.

Document the decision and close.

Workstream 4 — Index footprint cleanup (low urgency, housekeeping)

Status: not started.

The original three-phase plan from this runbook is still valid as housekeeping. It is not firefighting. It frees disk, reduces autovacuum work, and shrinks the structural risk if Workstream 1 ever regresses — but the system was never going to be made healthy by drops alone, and that is now demonstrated.

Do these as a normal-cadence migration, not under incident pressure. Tables retained verbatim from the original analysis below for reference.

Phase 4a — completely safe (drop now)

Six indexes. ~640 MB on US, ~500 MB on EU. No judgment calls.

The last one is a strict column prefix of idx_on_organization_id_interaction_type_started_at__43bf513fd0 ((organization_id, interaction_type, started_at, active_evaluation_id), 85 K+ EU scans) — Postgres can serve any query the prefix handles using the longer one. Free drop.

Phase 4b — strongly recommended (low risk)

Two indexes. ~850 MB on US, ~480 MB on EU.

The compliance and dispute risk-level indexes each have a “short” 3-column variant and a “long” 5-column variant where the short is a strict prefix of the long. Drop the long variants — they’re 10× larger, used 50× less, and the short variants pick up their queries with only a sort cost.

Mild risk: dispute/compliance pagination queries on US gain a sort step. Filtered set is small, probably fine.

Phase 4c — opportunistic (verify with codebase grep, then drop)

Single-column indexes used very rarely on US over the lifetime of the DB. Each candidate should get a quick Grep for where(<column>: …) patterns before dropping to confirm it’s not tied to an active feature.

Keep (for now)

• idx_on_organization_id_interaction_type_compliance__15c7b570ac — 33 987 US scans
• idx_on_organization_id_interaction_type_dispute_ris_1c9318ac66 — 1 487 US scans
• index_conversations_on_translation_state — 211 US scans, decent ratio
• index_conversations_on_transcription_state — 102 US scans, decent ratio
• index_conversations_on_jira_issue_key — small (5 MB), partial index, recent

Workstream 5 — Deprecate external_type column (out of scope for now)

Status: deferred. Tracked here so it doesn’t get lost.

Decision (2026-05-06): we want to merge Intercom conversations and tickets into a single conversations row rather than disambiguating with external_type. That makes external_type dead weight — the unique index can collapse to (organization_data_source_id, external_id).

Pre-requisites before this can happen:

1. Design a dupe-merge strategy for any existing rows where the same (organization_data_source_id, external_id) was inserted with two different external_type values. Need to know how many rows are affected, what to merge into what, and what downstream consumers (events, evaluations, threads) need to be updated.
2. Decide whether IntercomTicket continues to exist as a separate importer or is merged into the Intercom importer.
3. Write a migration: consolidate dupes → drop the column → drop the index → recreate the unique index without external_type.

Do not start until Workstreams 1 and 4 have landed.

Status

Follow-ups (out of scope for this cleanup)

1. Investigate index_conversations_on_data_source_id usage. 1.7 T idx_tup_read for 2 M scans is ~850 K rows per call. Almost certainly another query somewhere doing Conversation.where(data_source: …) without an organization_data_source scope. Workstream 2 will cover most of these; this index in particular is worth a separate look once Workstream 1 lands and the noise floor is lower.
2. Review autovacuum settings on conversations. Even with the access-path fix, dead-tuple churn at this scale may benefit from a lower autovacuum_vacuum_scale_factor for this table.
3. Consider per-table autovacuum cost limits so a heavy autovacuum can finish faster instead of throttling itself.
4. shared_buffers sizing on the EU instance vs. working-set growth — less critical now that the seq-scans are going away, but still worth a sanity check.
5. Monitor for index-bloat regression. Snapshot pg_stat_user_indexes.idx_scan periodically (e.g. nightly cron writing to a small index_usage_snapshots table) so we have freshness signal next time we need it. Postgres 16 gives us last_idx_scan for free; until we upgrade, snapshots are the workaround.
6. Add a guard test that fails CI if a new Conversation.find_by(external_id: …) callsite is introduced without organization_data_source — at minimum a Rubocop pattern matcher or a request-spec-level assertion that webhook lookups produce an Index Scan.