Replace the checkpoint header index with a covering index

What does this MR do and why?

Workflow#checkpoint_header_rows plucks id, thread_ts, parent_ts and current_thread for every checkpoint header of a session. #full_ancestor_thread_ts and #current_thread_by_ts walk the result, so every ancestor walk over a session's checkpoints starts with it.

In the read engine's database review, @stomlinson expected fewer than 100 checkpoints per session, with an index change if sessions reach thousands: !247133 (comment 3607759375)

Sessions created on GitLab.com from 2 to 8 October had these header counts:

• p50: 14
• p99: 133
• max: 5,492

On GitLab.com, slow getWorkflowLatestCheckpoint requests pluck every header of the session on each 10-second poll: #631606

For the 5,492-header session, the pluck goes through a BitmapAnd of (workflow_id, thread_ts, id) and the primary key, then the heap. That is 3,702 buffers, 11.6 ms warm, and 3.1 s cold.

This MR replaces index_duo_wf_checkpoint_headers_on_workflow_id_thread_ts_id with idx_duo_wf_checkpoint_headers_on_wf_id_thread_ts_id_covering:

• Same key, (workflow_id, thread_ts, id), so reads that order by thread_ts and the foreign key's cascade delete keep an index.
• Adds INCLUDE (workflow_created_at, parent_ts, current_thread), so the pluck is an Index Only Scan: 227 buffers for the same session.
• Two post-deployment migrations: 20261014010848 adds the new index, and 20261015221500 drops the old one, which has the same key and is now redundant. The drop is its own migration, as @jeanvdw asked in !262167 (comment 4024205311), so each step can be retried or reverted alone.
• No application code changes.

20260812160000 left workflow_created_at out because the partition key prunes. It prunes to the day, but a daily partition holds many sessions, so the plan still applies it as a Filter, and an Index Only Scan needs every column it filters on. An index on (workflow_id, id) INCLUDE (thread_ts, parent_ts, current_thread) was ignored by the planner (3,711 buffers, 11.6 ms).

Where the pluck runs

flowchart LR
  get["Internal checkpoint GETs<br/>(REST, group-bounded blob read)"] --> one
  trace["Workflow trace endpoint<br/>(trace.jsonl)"] --> one["Workflow#checkpoint_header_rows<br/>one session"]
  gql["GraphQL duoMessages<br/>(WorkflowCheckpointEventPresenter)"] --> page["Workflow.checkpoint_header_rows_for<br/>a page of sessions"]
  one --> table[("p_duo_workflows_checkpoint_headers<br/>filters on workflow_id<br/>and workflow_created_at")]
  page --> table

Every entry point walks the ancestor chain, so each starts with one of the two plucks. The page form adds workflow_id, a key column of the new index, to the select list.

References

• Read engine and its database review: !247133 (merged)
• Where both ancestor walks came to share one header pluck: !247392 (merged)
• The GraphQL poller that runs the page form of this pluck on every poll: #631606

Database

p_duo_workflows_checkpoint_headers is insert-only, with daily range partitions on workflow_created_at and 30-day retention. The migrations follow 20260812160000, which created the index they replace.

Query and plans

The pluck for the 5,492-header session:

SELECT "p_duo_workflows_checkpoint_headers"."id", "p_duo_workflows_checkpoint_headers"."thread_ts", "p_duo_workflows_checkpoint_headers"."parent_ts", "p_duo_workflows_checkpoint_headers"."current_thread"
FROM "p_duo_workflows_checkpoint_headers"
WHERE "p_duo_workflows_checkpoint_headers"."workflow_id" = 8987243
  AND "p_duo_workflows_checkpoint_headers"."workflow_created_at" = '2026-10-04 05:22:46.918122'

The same statement for a median session of 14 headers (workflow_id = 9231668, workflow_created_at = '2026-10-08 23:59:42.994024').

Session	Before	After
5,492 headers	Bitmap Heap Scan, 11.6 ms, 3,702 buffers	Index Only Scan, 1.6 ms, 227 buffers
14 headers	Index Scan, 0.10 ms, 19 buffers	Index Only Scan, 0.10 ms, 8 buffers

Cold run of the "before" plan for the 5,492-header session: 3,059 ms, 3,699 pages read from disk.

The page form puts a request's sessions in IN lists. The poller asks for one session, so Rails writes = for each. A duoMessages request over several sessions sends, for the two sessions above:

SELECT "p_duo_workflows_checkpoint_headers"."workflow_id", "p_duo_workflows_checkpoint_headers"."id", "p_duo_workflows_checkpoint_headers"."thread_ts", "p_duo_workflows_checkpoint_headers"."parent_ts", "p_duo_workflows_checkpoint_headers"."current_thread"
FROM "p_duo_workflows_checkpoint_headers"
WHERE "p_duo_workflows_checkpoint_headers"."workflow_id" IN (8987243, 9231668)
  AND "p_duo_workflows_checkpoint_headers"."workflow_created_at" IN ('2026-10-04 05:22:46.918122', '2026-10-08 23:59:42.994024')

Page	Before	After
Both sessions, 5,506 headers	Bitmap Heap Scan and Index Scan, 17.4 ms, 3,721 buffers	Index Only Scan on both partitions, 2.6 ms, 236 buffers

Cold run of the "before" plan for the page: 2,439 ms, 3,719 pages read from disk.

Each after plan uses the new definition built by hand on the two partitions holding these sessions (tmp_hdr_c_20261004, tmp_hdr_c_20261008), with the current parent index dropped, on the same clone as its before plan.

#checkpoint_header_for, which orders by the unchanged key, read 11 buffers both ways (0.071 and 0.125 ms warm) on an earlier Database Lab clone of the same data.

The covering plan's cold time is a bound, not a measurement, because the first run after building the index on the same clone is not cold. It touches 227 buffers in all, so a cold run reads at most 227 pages. At the 0.66 and 0.83 ms per page the two cold runs above show, that is up to about 150 to 190 ms.

The Index Only Scan makes 168 heap fetches, for heap pages not yet marked all-visible. The 227 buffers include them.

Build time, size and write cost

The partition sizes and the insert cost come from the same earlier clone, and the build times from two fresh clones on 19 October with this shape. The table figures come from the database testing job, which ran the earlier version of this change on GitLab.com data on 14 October, with the add and the drop in one migration.

• Build: concurrently, 0.77 to 2.20 s for a 289 MB partition and 3.32 to 3.67 s for a 1,263 MB one. The attached partitions hold 25.9 GB, and the database testing job ran the add and the drop in 328.7 s. The database review guide asks for an asynchronous index when a build takes more than an hour, so the migration builds the index directly.
• Size: 29 MB against 24 MB for today's index on the 289 MB partition, and 127 MB against 98 MB on the 1,263 MB one. The database testing job measured +598.30 MiB for the add and the drop together.
• Write cost: a warm one-row insert touched 259 buffers and wrote 4 WAL records today, and 272 buffers and 4 WAL records with this index, in the same time band. The table takes about 5.1 million header inserts a week.

The job flagged three statements over 100 ms, all from the partitioning helpers: the postgres_partitions lookup (212.97 ms), the DROP INDEX on the parent (141.84 ms), and the CREATE INDEX on the parent (101.54 ms). The last one only attaches the partition indexes already built concurrently.

Migration output

From the db:check-migrations job at this head. Its database has 58 daily partitions. The job rolls back the drop, then the add, then migrates both. The == lines and the DDL lines are shown, and 57 of the 58 partition indexes are elided.

20261015221500, down:

main: == 20261015221500 RemoveIndexOnWorkflowIdThreadTsIdFromPDuoWorkflowsCheckpointHeaders: reverting
main: -- add_index("gitlab_partitions_dynamic.p_duo_workflows_checkpoint_headers_20260916", [:workflow_id, :thread_ts, :id], {:name=>"index_a1c5bdf8f7", :algorithm=>:concurrently})
...
main: -- add_index(:p_duo_workflows_checkpoint_headers, [:workflow_id, :thread_ts, :id], {:name=>"index_duo_wf_checkpoint_headers_on_workflow_id_thread_ts_id"})
main: == 20261015221500 RemoveIndexOnWorkflowIdThreadTsIdFromPDuoWorkflowsCheckpointHeaders: reverted (0.6628s)

20261014010848, down:

main: == 20261014010848 AddCoveringIndexOnPDuoWorkflowsCheckpointHeaders: reverting =
main: -- remove_index(:p_duo_workflows_checkpoint_headers, {:name=>"idx_duo_wf_checkpoint_headers_on_wf_id_thread_ts_id_covering"})
main: == 20261014010848 AddCoveringIndexOnPDuoWorkflowsCheckpointHeaders: reverted (0.1268s)

20261014010848, up:

main: == 20261014010848 AddCoveringIndexOnPDuoWorkflowsCheckpointHeaders: migrating =
main: -- add_index("gitlab_partitions_dynamic.p_duo_workflows_checkpoint_headers_20260916", [:workflow_id, :thread_ts, :id], {:name=>"index_a7ea364270", :include=>[:workflow_created_at, :parent_ts, :current_thread], :algorithm=>:concurrently})
...
main: -- add_index(:p_duo_workflows_checkpoint_headers, [:workflow_id, :thread_ts, :id], {:name=>"idx_duo_wf_checkpoint_headers_on_wf_id_thread_ts_id_covering", :include=>[:workflow_created_at, :parent_ts, :current_thread]})
main: == 20261014010848 AddCoveringIndexOnPDuoWorkflowsCheckpointHeaders: migrated (0.6578s)

20261015221500, up:

main: == 20261015221500 RemoveIndexOnWorkflowIdThreadTsIdFromPDuoWorkflowsCheckpointHeaders: migrating
main: -- remove_index(:p_duo_workflows_checkpoint_headers, {:name=>"index_duo_wf_checkpoint_headers_on_workflow_id_thread_ts_id"})
main: == 20261015221500 RemoveIndexOnWorkflowIdThreadTsIdFromPDuoWorkflowsCheckpointHeaders: migrated (0.0338s)

After the migrations, the job's git diff -- db/structure.sql and git add -A -n db/schema_migrations print nothing.

How to set up and validate locally

1. Run the migrations:

   bin/rails db:migrate

2. In bin/rails dbconsole, run \d p_duo_workflows_checkpoint_headers. It lists idx_duo_wf_checkpoint_headers_on_wf_id_thread_ts_id_covering and no longer lists index_duo_wf_checkpoint_headers_on_workflow_id_thread_ts_id.
3. Roll back the drop, then the add, and check the old index is back and the new one is gone:

   bin/rails db:migrate:down:main VERSION=20261015221500
   bin/rails db:migrate:down:main VERSION=20261014010848

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.

An AI coding assistant wrote this change and ran the plans, the partition builds and sizes, and the insert cost above on Database Lab clones. At this head, 91365e1ce79d, it ran both migrations up, down and up again on a GitLab source checkout, regenerated db/structure.sql with no difference, and ran RuboCop.