Pass checkpoint blob thread_ts chains as one array

Eduardo Bonetrequested to merge
631359-pass-long-thread-ts-chains-to-the-checkpoint into master

What does this MR do and why?

Planning time for checkpoint blob queries no longer grows with the length of the thread_ts chain. Postgres spent about 0.08 ms of planning per value in the old literal IN list, up to 86 ms at 1,139 values on workflow 8711521, though the cost depends on the partition's thread_ts statistics (see the design notes for details). This MR sends the chain as one array using = ANY(ARRAY(SELECT unnest(...))) for every chain size, without the threshold the issue proposed.

Design notes

Why = ANY(ARRAY(SELECT unnest(...))) over IN (SELECT unnest(...)): the issue's own evidence already showed the ANY form planning in 0.27 ms. Database Lab found the IN-unnest form also picks a nested-loop semi join between 25 and 400 values, at 598 buffers and up to 11.4 ms execution, even though its planning is flat. The ANY form has neither problem: at 5 values it reads 7 buffers, the same as the literal list. Planning stays about 0.2 ms at every size, and buffers grow with the chain length, like the literal list's.

Why no threshold: the issue proposed IN-unnest only above a size threshold, because that form reads the workflow's whole channel on short chains. The ANY form does not have that cost, so one query shape covers every chain length and there is no threshold to tune or test.

Why the planning cost depends on the partition: Postgres estimates each literal IN value against the column's most-common-values list, so planning grows with the list length times the MCV size. The thread_ts MCV list on Database Lab has 1,000 entries on the 2026-09-26 and 2026-09-27 partitions (low-volume weekend days) and 22 to 95 entries on weekday partitions (2026-09-25, 2026-09-28, 2026-09-29). On the 2026-09-28 partition, workflow 8746344 with 1,184 values plans in 2.0 ms with the literal list and 0.2 ms with the ANY form. So the 84 to 86 ms numbers come from partitions with a full MCV list; the ANY form plans in about 0.2 ms whatever the statistics are.

Array encoding: PG::TextEncoder::Array (from the pg gem) builds the literal and binds it through a ? placeholder, so thread_ts values with commas, quotes, or braces are escaped correctly. It is not a comma join.

Callers: Workflow#blobs_by_thread_ts_for serves the internal checkpoint list page (ee/lib/api/ai/duo_workflows/workflows_internal.rb, reconstruct_page!). Workflow#blobs_for_thread_ts serves reconstructed_channel_values, reconstructed_channel, accumulated_blobs_for, and the anchor-miss refetch (unbounded_blobs_by_thread_ts), used by the checkpoint endpoints, the checkpoint event presenter, and trace.jsonl when called with thread=latest or thread=<id>. The default full trace.jsonl (no thread param) goes through Workflow#full_history_blobs, which this MR does not change.

Follow-up, not in this MR: four queries still pass the chain as a literal list, tracked in #631438 (closed). They are Workflow#full_history_blobs (the default full trace.jsonl, which has the longest chain), Workflow#checkpoint_statuses (workflow branches), Workflow#history_blobs_for, and Workflow.history_blobs_for_workflows (message history). They need their own plans, so they stay out of this MR.

Overlap with another MR: !258484 (merged) edits the line next to this change in blobs_by_thread_ts_for. Whichever merges second gets a small rebase conflict.

References

Screenshots or screen recordings

No UI change.

How to set up and validate locally

Backend-only change. Run these steps in a rails console on GDK (gdk rails console or bin/rails console).

  1. Show the SQL shape: run Ai::DuoWorkflows::CheckpointBlob.for_thread_ts(%w[ts-1 ts-2]).to_sql. Expect: ... thread_ts = ANY(ARRAY(SELECT unnest('{ts-1,ts-2}'::text[]))).
  2. Find a workflow with incremental checkpoints: header = Ai::DuoWorkflows::CheckpointHeader.order(:id).last, then workflow = header.workflow. Run ActiveRecord::Base.logger = Logger.new($stdout) then workflow.reconstructed_channel_values(header). Expect: the p_duo_workflows_checkpoint_blobs query uses the ANY form, and the returned hash matches master.
  3. Creating such a workflow needs a Duo Agent Platform session run on GDK with incremental checkpoints enabled. As an alternative, run bin/rspec ee/spec/models/ai/duo_workflows/checkpoint_blob_spec.rb.
Query plans (Database Lab)

Source: Database Lab gitlab-production-main, 2026-09-30 session (clone data as of 2026-09-29 04:24 UTC). Probes ran as DO blocks that build the query server-side with EXECUTE and raise the EXPLAIN summary as an error (Joe hides rows). Each form ran twice; numbers below are the warm run.

SQL (array literal elided; the channel predicate varies), ANY form:

SELECT "p_duo_workflows_checkpoint_blobs".* FROM "p_duo_workflows_checkpoint_blobs" WHERE "p_duo_workflows_checkpoint_blobs"."workflow_id" = 8711521 AND "p_duo_workflows_checkpoint_blobs"."workflow_created_at" = '2026-09-27 13:05:42.680577' AND "p_duo_workflows_checkpoint_blobs"."project_id" = 84861201 AND ("p_duo_workflows_checkpoint_blobs".thread_ts = ANY(ARRAY(SELECT unnest('{… 1,139 thread_ts values …}'::text[])))) AND "p_duo_workflows_checkpoint_blobs"."channel" = 'status' ORDER BY "p_duo_workflows_checkpoint_blobs"."id" ASC

The literal IN form (master) uses the same SQL with "p_duo_workflows_checkpoint_blobs"."thread_ts" IN (... 1,139 values ...) in place of the ANY clause. Workflow 8711521 has 1,139 checkpoints in one group.

Before: channel = 'status' (literal IN list)

Index Scan using index_22d319300b on p_duo_workflows_checkpoint_blobs_20260927 p_duo_workflows_checkpoint_blobs  (cost=3.27..277.98 rows=1 width=368) (actual time=0.067..0.471 rows=506 loops=1)
  Index Cond: ((workflow_id = 8711521) AND (channel = 'status'::text))
  Filter: ((workflow_created_at = '2026-09-27 13:05:42.680577+00'::timestamp with time zone) AND (project_id = 84861201) AND (thread_ts = ANY ('{...}'::text[])))
  Buffers: shared hit=500
Planning Time: 85.826 ms
Execution Time: 0.518 ms

After: channel = 'status' (ANY form)

Sort  (cost=27.91..27.91 rows=1 width=368) (actual time=1.768..1.795 rows=506 loops=1)
  Sort Key: p_duo_workflows_checkpoint_blobs.id
  Sort Method: quicksort  Memory: 107kB
  Buffers: shared hit=598
  InitPlan 1
    ->  ProjectSet  (cost=0.00..5.71 rows=1139 width=32) (actual time=0.001..0.080 rows=1139 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
  ->  Index Scan using p_duo_workflows_checkpoint_b_project_id_workflow_id_threa_idx33 on p_duo_workflows_checkpoint_blobs_20260927 p_duo_workflows_checkpoint_blobs  (cost=0.55..22.19 rows=1 width=368) (actual time=0.529..1.673 rows=506 loops=1)
        Index Cond: ((project_id = 84861201) AND (workflow_id = 8711521) AND (thread_ts = ANY ((InitPlan 1).col1)) AND (channel = 'status'::text) AND (workflow_created_at = '2026-09-27 13:05:42.680577+00'::timestamp with time zone))
        Buffers: shared hit=598
Planning Time: 0.199 ms
Execution Time: 1.844 ms

Before: channel IN (six page-membership channels) (literal IN list)

Sort  (cost=1776.29..1776.30 rows=1 width=368) (actual time=2.590..2.823 rows=2427 loops=1)
  Sort Key: p_duo_workflows_checkpoint_blobs.id
  Sort Method: quicksort  Memory: 1774kB
  Buffers: shared hit=2369
  ->  Index Scan using index_22d319300b on p_duo_workflows_checkpoint_blobs_20260927 p_duo_workflows_checkpoint_blobs  (cost=3.27..1776.28 rows=1 width=368) (actual time=0.085..1.713 rows=2427 loops=1)
        Index Cond: ((workflow_id = 8711521) AND (channel = ANY ('{...}'::text[])))
        Filter: ((workflow_created_at = '2026-09-27 13:05:42.680577+00'::timestamp with time zone) AND (project_id = 84861201) AND (thread_ts = ANY ('{...}'::text[])))
        Buffers: shared hit=2369
Planning Time: 86.070 ms
Execution Time: 2.951 ms

After: channel IN (six page-membership channels) (ANY form)

Sort  (cost=131.28..131.29 rows=1 width=368) (actual time=3.889..4.039 rows=2427 loops=1)
  Sort Key: p_duo_workflows_checkpoint_blobs.id
  Sort Method: quicksort  Memory: 1774kB
  Buffers: shared hit=1350
  InitPlan 1
    ->  ProjectSet  (cost=0.00..5.71 rows=1139 width=32) (actual time=0.001..0.073 rows=1139 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
  ->  Index Scan using p_duo_workflows_checkpoint_b_project_id_workflow_id_threa_idx33 on p_duo_workflows_checkpoint_blobs_20260927 p_duo_workflows_checkpoint_blobs  (cost=0.55..125.56 rows=1 width=368) (actual time=0.479..3.151 rows=2427 loops=1)
        Index Cond: ((project_id = 84861201) AND (workflow_id = 8711521) AND (thread_ts = ANY ((InitPlan 1).col1)) AND (channel = ANY ('{...}'::text[])) AND (workflow_created_at = '2026-09-27 13:05:42.680577+00'::timestamp with time zone))
        Buffers: shared hit=1350
Planning Time: 0.247 ms
Execution Time: 4.158 ms

Before: no channel filter, refetch / membership-less (literal IN list)

Sort  (cost=2298.06..2298.06 rows=1 width=368) (actual time=3.096..3.461 rows=3528 loops=1)
  Sort Key: p_duo_workflows_checkpoint_blobs.id
  Sort Method: quicksort  Memory: 1977kB
  Buffers: shared hit=1476
  ->  Index Scan using p_duo_workflows_checkpoint_b_project_id_workflow_id_threa_idx33 on p_duo_workflows_checkpoint_blobs_20260927 p_duo_workflows_checkpoint_blobs  (cost=0.55..2298.05 rows=1 width=368) (actual time=0.334..2.112 rows=3528 loops=1)
        Index Cond: ((project_id = 84861201) AND (workflow_id = 8711521) AND (thread_ts = ANY ('{...}'::text[])) AND (workflow_created_at = '2026-09-27 13:05:42.680577+00'::timestamp with time zone))
        Buffers: shared hit=1476
Planning Time: 83.983 ms
Execution Time: 3.638 ms

After: no channel filter, refetch / membership-less (ANY form)

Sort  (cost=27.88..27.89 rows=1 width=368) (actual time=3.078..3.300 rows=3528 loops=1)
  Sort Key: p_duo_workflows_checkpoint_blobs.id
  Sort Method: quicksort  Memory: 1977kB
  Buffers: shared hit=1476
  InitPlan 1
    ->  ProjectSet  (cost=0.00..5.71 rows=1139 width=32) (actual time=0.001..0.070 rows=1139 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
  ->  Index Scan using p_duo_workflows_checkpoint_b_project_id_workflow_id_threa_idx33 on p_duo_workflows_checkpoint_blobs_20260927 p_duo_workflows_checkpoint_blobs  (cost=0.55..22.16 rows=1 width=368) (actual time=0.532..2.188 rows=3528 loops=1)
        Index Cond: ((project_id = 84861201) AND (workflow_id = 8711521) AND (thread_ts = ANY ((InitPlan 1).col1)) AND (workflow_created_at = '2026-09-27 13:05:42.680577+00'::timestamp with time zone))
        Buffers: shared hit=1476
Planning Time: 0.210 ms
Execution Time: 3.465 ms

Size sweep, workflow 8711521 (1,139 checkpoints, one group), channel = 'status'. Format: planning ms / execution ms / shared buffers / index (D = dedup idx33, C = index_22d319300b = workflow_channel_id).

n literal IN IN (SELECT unnest) = ANY(ARRAY(SELECT unnest))
25 2.2 / 0.1 / 16 / D 0.2 / 2.4 / 598 / D, nested loop 0.2 / 0.1 / 16 / D
50 4.2 / 0.2 / 30 / D 0.2 / 3.9 / 598 / D, nested loop 0.2 / 0.1 / 30 / D
75 5.9 / 0.2 / 44 / D 0.2 / 5.5 / 598 / D, nested loop 0.2 / 0.2 / 44 / D
100 7.9 / 0.3 / 56 / D 0.2 / 7.1 / 598 / D –
150 11.7 / 0.2 / 500 / C 0.2 / 9.7 / 598 / D, nested loop 0.2 / 0.5 / 82 / D
200 14.2 / 0.2 / 500 / C 0.2 / 11.4 / 598 / D, nested loop 0.2 / 0.5 / 109 / D
400 30.0 / 0.3 / 500 / C 0.2 / 0.7 / 598 / D, hash join 0.2 / 1.1 / 211 / D

Size sweep, workflow 8711521, literal vs ANY, no channel filter and six channels. Format: planning ms / execution ms / shared buffers / rows / index.

n none: literal none: ANY six: literal six: ANY
5 0.7 / 0.0 / 10 / 13 / D 0.2 / 0.1 / 10 / 13 / D 0.8 / 0.1 / 10 / 9 / D 0.2 / 0.0 / 10 / 9 / D
50 5.8 / 0.4 / 68 / 153 / D 0.3 / 0.3 / 68 / 153 / D 4.6 / 0.3 / 65 / 105 / D 0.2 / 0.2 / 65 / 105 / D
150 11.7 / 0.7 / 192 / 464 / D 0.2 / 0.6 / 192 / 464 / D 11.7 / 1.1 / 2369 / 320 / C 0.3 / 0.7 / 181 / 320 / D
400 30.6 / 1.9 / 509 / 1232 / D 0.2 / 1.4 / 509 / 1232 / D 31.4 / 1.5 / 2369 / 843 / C 0.2 / 1.8 / 473 / 843 / D
1139 100.5 / 12.0 / 1476 / 3528 / D 0.3 / 6.8 / 1476 / 3528 / D 90.2 / 3.1 / 2369 / 2427 / C 0.2 / 6.1 / 1350 / 2427 / D

Other workflows, current group chain (format: planning ms / execution ms / shared buffers / rows / index):

  • 8714624 (539 headers, current group of 5): none literal 0.6 / 0.0 / 17 / 26 / D, ANY 0.1 / 0.0 / 17 / 26 / D; status literal 0.6 / 0.0 / 7 / 4 / D, ANY 0.1 / 0.0 / 7 / 4 / D.
  • 8706306 (largest of 2026-09-26, 1,112 headers, one group): none literal 93.5 / 3.8 / 1450 / 3362 / D, ANY 0.2 / 2.6 / 1450 / 3362 / D; status literal 98.5 / 0.6 / 499 / 515 / C, ANY 0.3 / 2.4 / 590 / 515 / D.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eduardo Bonet

Merge request reports