Partition checkpoint blobs by workflow_created_at

What does this MR do?

Partitions p_duo_workflows_checkpoint_blobs by a dedicated workflow_created_at column instead of created_at.

The incremental blob read filters by workflow_id + current_thread with no created_at bound, so Postgres opened every retained daily partition and contended on LockManager LWLocks (raised in !243339#note_3529586179).

An earlier revision anchored created_at to workflow.created_at to force single-partition pruning, but that overloaded the column — a checkpoint written hours into a workflow would report the workflow's creation time, and updated_at diverged from created_at (note_3545412358).

Instead, every blob is written with workflow_created_at = workflow.created_at. All of a workflow's blobs share one daily partition, so the read (stacked MR) equality-prunes to it, while created_at / updated_at stay honest.

• workflow_created_at becomes the partition key, the second PK column, and the last column of the dedup index (a partitioned table's unique index must include the partition key). Dedup stays deterministic because the value is constant across re-sends.
• CreateCheckpointService sets workflow_created_at on each blob and no longer anchors created_at on the checkpoint. Checkpoints are otherwise unchanged.

Database

p_duo_workflows_checkpoint_blobs has no data yet (incremental blobs are written on no live path), so the migration recreates the empty table on the new partition key. A partition key cannot be changed in place.

data-deletion is applied but no data is deleted

Migration up

== 20260710120000 RepartitionPDuoWorkflowsCheckpointBlobsByWorkflowCreatedAt: migrating
-- drop_table(:p_duo_workflows_checkpoint_blobs)
   -> 0.0608s
-- create_table(:p_duo_workflows_checkpoint_blobs, {:primary_key=>[:id, :workflow_created_at], :options=>"PARTITION BY RANGE (workflow_created_at)", :if_not_exists=>true})
   -> 0.0051s
-- (sharding-key + data-size check constraints, id trigger)
-- execute("ALTER TABLE ... ADD CONSTRAINT fk_duo_wf_checkpoint_blobs_workflow_id FOREIGN KEY (workflow_id) REFERENCES duo_workflows_workflows (id) ON DELETE CASCADE;")
   -> 0.0005s
== 20260710120000 RepartitionPDuoWorkflowsCheckpointBlobsByWorkflowCreatedAt: migrated (0.1303s)

Migration down

== 20260710120000 RepartitionPDuoWorkflowsCheckpointBlobsByWorkflowCreatedAt: reverting
-- drop_table(:p_duo_workflows_checkpoint_blobs)
   -> 0.0408s
-- create_table(:p_duo_workflows_checkpoint_blobs, {:primary_key=>[:id, :created_at], :options=>"PARTITION BY RANGE (created_at)", :if_not_exists=>true})
   -> 0.0056s
== 20260710120000 RepartitionPDuoWorkflowsCheckpointBlobsByWorkflowCreatedAt: reverted

No query plan here — this MR only changes the table's partition key. The read query that benefits (accumulated_blobs_for bounding workflow_created_at) lands in the stacked read MR with its own plan.

Stacking

Base of the read/graphql stack. Targets master. The read MR (!242398 (closed)) and graphql MR (!243813) build on this branch.

Related

• Issue #605054 (closed)
• Follow-up #605653 (closed) — a purpose-built slim checkpoint table (drops the full checkpoint once blob groups are self-contained).