Prepare async index on checkpoint blobs (workflow_id, channel, id)

What does this MR do and why?

Ai::DuoWorkflows::Workflow#latest_channel_message finds the newest blob for one channel of one workflow session. The only usable index on p_duo_workflows_checkpoint_blobs covers (workflow_id). Postgres filters channel, step_action, project_id, and a thread_ts IN (...) list of 172 values, then sorts to find the newest row. The number of rows it visits grows with the session's total blob count.

This read path sits behind the dw_read_blobs_graphql feature flag, which is off on GitLab.com. There's no user impact today, but the query gets slower as sessions grow.

This MR adds a post-deployment migration that queues a new index, idx_duo_wf_checkpoint_blobs_on_workflow_channel_id, on (workflow_id, channel, id) for asynchronous creation via prepare_partitioned_async_index. It doesn't change db/structure.sql and doesn't touch application code.

Database

On a Database Lab clone of production, each daily partition holds about 1.9M rows in an 836 MB heap, plus 6.7 GB of TOAST that this index never reads. Retention is 30 days, so about 30 of the 58 partitions hold data. A plain, non-concurrent build on one partition took 7.1 seconds on the clone. Concurrent builds across every populated partition would run past the 10-minute budget for a post-deployment migration, so the index goes in asynchronously instead.

The index covers (workflow_id, channel, id). It leaves out thread_ts, because it's matched with an IN list and can't help sort by id. It leaves out the partition key, because that's constant within a partition.

All plans below come from Database Lab (gitlab-production-main), using the session named in the issue (workflow 6405842) and its real 172-value thread_ts list. The after-plans are production data with the index created on that session's partition. databasereview pending

#latest_channel_message

SELECT "p_duo_workflows_checkpoint_blobs".*
FROM "p_duo_workflows_checkpoint_blobs"
WHERE "workflow_id" = 6405842
  AND "workflow_created_at" >= '2026-08-17 09:04:38+00'
  AND "workflow_created_at" < '2026-08-17 09:04:39+00'
  AND "project_id" = 39903947
  AND "thread_ts" IN (<172 values, one per checkpoint in the session>)
  AND "channel" = 'ui_chat_log'
  AND "step_action" = 'conversation'
ORDER BY "id" DESC
LIMIT 1

Before:

 Limit  (cost=328.57..328.58 rows=1 width=405) (actual time=49.579..49.581 rows=1 loops=1)
   Buffers: shared hit=122 read=86
   I/O Timings: read=48.337 write=0.000
   ->  Sort  (cost=328.57..328.58 rows=1 width=405) (actual time=49.576..49.578 rows=1 loops=1)
         Sort Key: p_duo_workflows_checkpoint_blobs.id DESC
         Sort Method: top-N heapsort  Memory: 28kB
         Buffers: shared hit=122 read=86
         I/O Timings: read=48.337 write=0.000
         ->  Index Scan using p_duo_workflows_checkpoint_blobs_20260817_workflow_id_idx on gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 p_duo_workflows_checkpoint_blobs  (cost=0.86..328.56 rows=1 width=405) (actual time=3.337..49.476 rows=121 loops=1)
               Index Cond: (p_duo_workflows_checkpoint_blobs.workflow_id = 6405842)
               Filter: ((p_duo_workflows_checkpoint_blobs.workflow_created_at >= '2026-08-17 09:04:38+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at < '2026-08-17 09:04:39+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.project_id = 39903947) AND (p_duo_workflows_checkpoint_blobs.channel = 'ui_chat_log'::text) AND (p_duo_workflows_checkpoint_blobs.step_action = 'conversation'::text) AND (p_duo_workflows_checkpoint_blobs.thread_ts = ANY ('{...172 values...}'::text[])))
               Rows Removed by Filter: 435
               Buffers: shared hit=119 read=86
               I/O Timings: read=48.337 write=0.000
Total: 54.982 ms (planning 5.333 ms, execution 49.649 ms)

After:

 Limit  (cost=0.98..101.17 rows=1 width=405) (actual time=0.174..0.175 rows=1 loops=1)
   Buffers: shared hit=4 read=4
   I/O Timings: read=0.067 write=0.000
   ->  Index Scan Backward using tmp_blobs_wf_channel_id on gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 p_duo_workflows_checkpoint_blobs  (cost=0.98..101.17 rows=1 width=405) (actual time=0.172..0.172 rows=1 loops=1)
         Index Cond: ((p_duo_workflows_checkpoint_blobs.workflow_id = 6405842) AND (p_duo_workflows_checkpoint_blobs.channel = 'ui_chat_log'::text))
         Filter: ((p_duo_workflows_checkpoint_blobs.workflow_created_at >= '2026-08-17 09:04:38+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at < '2026-08-17 09:04:39+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.project_id = 39903947) AND (p_duo_workflows_checkpoint_blobs.step_action = 'conversation'::text) AND (p_duo_workflows_checkpoint_blobs.thread_ts = ANY ('{...172 values...}'::text[])))
         Buffers: shared hit=4 read=4
         I/O Timings: read=0.067 write=0.000
Total: 8.158 ms (planning 7.915 ms, execution 0.243 ms)

#history_blobs_for

Same query without step_action, ordered id ascending, no LIMIT.

Before:

 Sort  (cost=327.12..327.12 rows=1 width=405) (actual time=3.274..3.284 rows=122 loops=1)
   Sort Key: p_duo_workflows_checkpoint_blobs.id
   Sort Method: quicksort  Memory: 117kB
   Buffers: shared hit=6 read=202
   I/O Timings: read=2.667 write=0.000
   ->  Index Scan using p_duo_workflows_checkpoint_blobs_20260817_workflow_id_idx on gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 p_duo_workflows_checkpoint_blobs  (cost=0.86..327.11 rows=1 width=405) (actual time=0.294..3.192 rows=122 loops=1)
         Index Cond: (p_duo_workflows_checkpoint_blobs.workflow_id = 6405842)
         Filter: ((p_duo_workflows_checkpoint_blobs.workflow_created_at >= '2026-08-17 09:04:38+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at < '2026-08-17 09:04:39+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.project_id = 39903947) AND (p_duo_workflows_checkpoint_blobs.channel = 'ui_chat_log'::text) AND (p_duo_workflows_checkpoint_blobs.thread_ts = ANY ('{...172 values...}'::text[])))
         Rows Removed by Filter: 434
         Buffers: shared hit=3 read=202
         I/O Timings: read=2.667 write=0.000
Total: 8.697 ms (planning 5.360 ms, execution 3.337 ms)

After:

 Index Scan using tmp_blobs_wf_channel_id on gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 p_duo_workflows_checkpoint_blobs  (cost=0.98..100.92 rows=1 width=405) (actual time=0.201..0.381 rows=122 loops=1)
   Index Cond: ((p_duo_workflows_checkpoint_blobs.workflow_id = 6405842) AND (p_duo_workflows_checkpoint_blobs.channel = 'ui_chat_log'::text))
   Filter: ((p_duo_workflows_checkpoint_blobs.workflow_created_at >= '2026-08-17 09:04:38+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at < '2026-08-17 09:04:39+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.project_id = 39903947) AND (p_duo_workflows_checkpoint_blobs.thread_ts = ANY ('{...172 values...}'::text[])))
   Buffers: shared hit=128 read=1
   I/O Timings: read=0.128 write=0.000
Total: 6.089 ms (planning 5.654 ms, execution 0.435 ms)

#chat_log_thread_ts

SELECT DISTINCT thread_ts for one workflow and channel. The plan doesn't change: it keeps the index-only scan on idx_duo_wf_checkpoint_blobs_dedup.

Before:

 Unique  (cost=0.55..3.60 rows=1 width=37) (actual time=1.099..1.323 rows=122 loops=1)
   Buffers: shared hit=22 read=1
   I/O Timings: read=1.025 write=0.000
   ->  Index Only Scan using p_duo_workflows_checkpoint_b_project_id_workflow_id_threa_idx64 on gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 p_duo_workflows_checkpoint_blobs  (cost=0.55..3.60 rows=1 width=37) (actual time=1.098..1.286 rows=122 loops=1)
         Index Cond: ((p_duo_workflows_checkpoint_blobs.project_id = 39903947) AND (p_duo_workflows_checkpoint_blobs.workflow_id = 6405842) AND (p_duo_workflows_checkpoint_blobs.channel = 'ui_chat_log'::text) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at >= '2026-08-17 09:04:38+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at < '2026-08-17 09:04:39+00'::timestamp with time zone))
         Heap Fetches: 0
         Buffers: shared hit=22 read=1
         I/O Timings: read=1.025 write=0.000
Total: 3.491 ms (planning 2.121 ms, execution 1.370 ms)

After:

 Unique  (cost=0.55..3.60 rows=1 width=37) (actual time=0.063..0.241 rows=122 loops=1)
   Buffers: shared hit=23
   I/O Timings: read=0.000 write=0.000
   ->  Index Only Scan using p_duo_workflows_checkpoint_b_project_id_workflow_id_threa_idx64 on gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 p_duo_workflows_checkpoint_blobs  (cost=0.55..3.60 rows=1 width=37) (actual time=0.062..0.214 rows=122 loops=1)
         Index Cond: ((p_duo_workflows_checkpoint_blobs.project_id = 39903947) AND (p_duo_workflows_checkpoint_blobs.workflow_id = 6405842) AND (p_duo_workflows_checkpoint_blobs.channel = 'ui_chat_log'::text) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at >= '2026-08-17 09:04:38+00'::timestamp with time zone) AND (p_duo_workflows_checkpoint_blobs.workflow_created_at < '2026-08-17 09:04:39+00'::timestamp with time zone))
         Heap Fetches: 0
         Buffers: shared hit=23
         I/O Timings: read=0.000 write=0.000
Total: 3.230 ms (planning 2.931 ms, execution 0.299 ms)

To re-run the after-plans, create the index on one partition with Joe's exec: CREATE INDEX tmp_blobs_wf_channel_id ON gitlab_partitions_dynamic.p_duo_workflows_checkpoint_blobs_20260817 USING btree (workflow_id, channel, id). Then run explain, then reset the clone. Joe's exec returns no rows, so instead of the 172 literal thread_ts values, use thread_ts = ANY (ARRAY(SELECT thread_ts FROM p_duo_workflows_checkpoint_headers WHERE workflow_id = 6405842 AND ...)). Postgres runs the ARRAY(...) as a separate InitPlan node, and the main node keeps the same shape.

Follow-up

#626571 (closed) tracks a follow-up migration that creates the index synchronously, which also attaches the per-partition indexes to the parent table. That migration also drops index_duo_wf_checkpoint_blobs_on_workflow_id, the old (workflow_id) index, and updates a comment in workflow.rb. The old index becomes redundant because workflow_id is the leading column of the new index, so fk_duo_wf_checkpoint_blobs_workflow_id's cascade delete still gets an index scan.

Out of scope

The issue calls out the thread_ts IN (...) list as a separate concern. It grows with the session's checkpoint count, and the LATERAL planner fence would address that, not this MR.