Skip to content

Native: read PostgreSQL claim candidates one status at a time, runs by id #1069

New issue
New issue
Closed
Closed
Native: read PostgreSQL claim candidates one status at a time, runs by id#1069
Labels
agent:serverkind:featureA public product capability or experience is requestedpriority:P0Immediate release or data-integrity riskrepo:serverOwned by the standalone server repository

Description

@rmcdaniel
rmcdaniel
opened on Nov 6, 2026

Delivered — Native Server3.3.0 (2026-11-08)

Implemented by #1088; qualified and published at release source 2a15179f29801eec9f2a2d4e6a9c5f93233f104e / indexcb4b11bd. Acceptance and scope limits. No benchmark speedup is claimed.

Original requirement and evidence

External performance report, topic 18. The reported timings and proposed fixes have not been independently reproduced. The supplied patches are reference drafts for review and current-source qualification.

Problem

Until PostgreSQL first analyzes a new database, it guesses one row for
every scan. With one range over both claimable statuses, the candidate
query:

  • reads every task of that kind in the queue;
  • for each task it keeps, scans the runs table for that task's run.

The cost grows with both tables. In the race, the second rep, still
unanalyzed, ran 20 s. The first ran 14 s and the third, after analysis,
11 s.

Proposal

Use report topic 14's shape on PostgreSQL:

  • UNION ALL with one branch per status, which the dispatch-order index
    returns in claim order;
  • each task's run looked up by id, in a LATERAL subquery with LIMIT 1
    that the planner can't flatten into a join.

The filter, its order and the final limit are unchanged. The six poll
values are bound once and named twice.

No ANALYZE is added, and no planner setting changes. The query stops
depending on the statistics.

Reported benchmark evidence

Reporter-supplied cumulative measurements, not an isolated-effect or independently verified performance claim: 200 sequentially started workflows, five sequential 20ms activities or five local activities each, six PHP worker processes, mean of three repetitions, one machine, fresh SQLite/MySQL8.0/PostgreSQL16 databases with default durability. "Five" and "local" below mean last-close wall time in seconds; S/M/P means SQLite/MySQL/PostgreSQL. Preserve regressions and noise in the reported results.

  • EXPLAIN ANALYZE over 100 ready workflow tasks:
    • unanalyzed: 3.6 ms → 0.08 ms;
    • analyzed: 0.24 ms → 0.06 ms.
  • PostgreSQL five-steps: 15.33 → 11.02. The reps went from
    13.5 / 19.9 / 12.6 to 10.9 / 10.9 / 11.2.

Acceptance

  • Same candidates, same order.
  • An EXPLAIN check, on both an unanalyzed and an analyzed database, shows
    index order with no scan of the runs table per task.

Implementation constraints

A new table, or one that just grew, can no longer flip the
plan.

Dependencies

No new producer dependency is required for source investigation.

Related existing work: #325.

Draft patches and validation boundary

Current main was independently read as b8f4dd13623ce645de77ba5bd89ae8dd8eea2f7c. The supplied draft series targets 3d4d4afaf2b24aaa5443eac154e253b011924738; patches are cumulative reference material, not a current-main application or test result. Review and rebase on maintained source, preserve all existing tests and published compatibility, then establish causal controls and actual conformance before shipping. No patch was applied or executed during intake.

The following sanitized unified diffs identify proposed reference material:

  • 0028-t18-Read-PostgreSQL-s-claim-candidates-a-status-at-a-tim.patch — 3,063 bytes, SHA-256 db12b205cb8b780cc44d1c33794cbf5e11155af58b8000a4fb80eef69aef4922.

Next action: the owning maintainer reviews the candidate against current source and the acceptance criteria above. Preserve durability, lease/fencing, idempotency, fairness and whole-completion/history budgets. New producer capabilities need versioned schemas and real producer/consumer compatibility proof; historical/frozen evidence is not rewritten.

Activity

  1. rmcdaniel
    added
    kind:featureA public product capability or experience is requested
    priority:P0Immediate release or data-integrity risk
    repo:serverOwned by the standalone server repository
    agent:server
    on Nov 6, 2026

  2. rmcdaniel commented on Nov 6, 2026

    @rmcdaniel
    rmcdaniel
    on Nov 6, 2026
    MemberAuthor

    Sanitized reference diff 0028-t18-Read-PostgreSQL-s-claim-candidates-a-status-at-a-tim.patch, part 1/1.

    This is an unverified, cumulative draft from the external report. Mail author/date/commit-message metadata was removed; the unified-diff hunks were retained. No patch was applied or executed, and no build/test or compatibility result is implied. The known gaps and acceptance criteria in this issue remain mandatory.

    Complete sanitized patch: 3,063 bytes, SHA-256 db12b205cb8b780cc44d1c33794cbf5e11155af58b8000a4fb80eef69aef4922. This part: 3,063 bytes, SHA-256 db12b205cb8b780cc44d1c33794cbf5e11155af58b8000a4fb80eef69aef4922. Parts concatenate in order; a part boundary is between complete file diffs.

    diff --git a/rust/src/runtime/store.rs b/rust/src/runtime/store.rs
index 88d0abc9..1c269f73 100644
--- a/rust/src/runtime/store.rs
+++ b/rust/src/runtime/store.rs
@@ -93,10 +93,22 @@ pub(super) struct SignalWrite<'a> {
 }
 /// The runtime-owned message-stream delivery signal (`WorkflowStub::MESSAGE_STREAM_RUNTIME_SIGNAL`).
 const MESSAGE_STREAM_SIGNAL: &str = super::message_streams::INTERNAL_SIGNAL;
-/// A poll's claim candidates in claim order, at most `$limit` of them.
+/// A poll's claim candidates in claim order, at most `$limit` of them. Before
+/// PostgreSQL first analyzes a new database it guesses one row everywhere, and
+/// with one range over both claimable statuses it read every task in the queue
+/// and scanned the runs for each one it kept. Each branch here is one status,
+/// which the dispatch order index returns in claim order, and each looks up
+/// its task's run by id, so the plan doesn't depend on statistics.
 macro_rules! task_candidates_sql {
     ($limit:literal) => {
-        concat!("SELECT t.*,r.workflow_type FROM workflow_tasks t JOIN workflow_runs r ON r.id=t.workflow_run_id WHERE t.namespace=$1 AND t.queue=$2 AND t.task_type=$3 AND t.status IN ('ready','leased') AND r.status IN ('pending','running','waiting') AND (t.status='ready' OR (t.status='leased' AND t.task_type='workflow' AND t.lease_expires_at<=$4)) AND (t.available_at IS NULL OR t.available_at<=$5) AND (COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,'')) IS NULL OR COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,''))=$6) ORDER BY t.priority,t.available_at,t.id LIMIT ", $limit)
+        concat!(
+            "SELECT * FROM ((SELECT t.*,r.workflow_type FROM workflow_tasks t JOIN LATERAL (SELECT r.workflow_type,r.compatibility FROM workflow_runs r WHERE r.id=t.workflow_run_id AND r.status IN ('pending','running','waiting') LIMIT 1) r ON true WHERE t.namespace=$1 AND t.queue=$2 AND t.task_type=$3 AND t.status='ready' AND (t.available_at IS NULL OR t.available_at<=$5) AND (COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,'')) IS NULL OR COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,''))=$6) ORDER BY t.priority,t.available_at,t.id LIMIT ",
+            $limit,
+            ") UNION ALL (SELECT t.*,r.workflow_type FROM workflow_tasks t JOIN LATERAL (SELECT r.workflow_type,r.compatibility FROM workflow_runs r WHERE r.id=t.workflow_run_id AND r.status IN ('pending','running','waiting') LIMIT 1) r ON true WHERE t.namespace=$1 AND t.queue=$2 AND t.task_type=$3 AND t.status='leased' AND t.task_type='workflow' AND t.lease_expires_at<=$4 AND (t.available_at IS NULL OR t.available_at<=$5) AND (COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,'')) IS NULL OR COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,''))=$6) ORDER BY t.priority,t.available_at,t.id LIMIT ",
+            $limit,
+            ")) AS candidates ORDER BY priority,available_at,id LIMIT ",
+            $limit
+        )
     };
 }
 pub(super) const TASK_CANDIDATES_SQL: &str = task_candidates_sql!(100);

  3. rmcdaniel commented on Nov 6, 2026

    @rmcdaniel
    rmcdaniel
    on Nov 6, 2026
    MemberAuthor

    Sanitized rebased reference for report topic 18: 0028-t18-Read-PostgreSQL-s-claim-candidates-a-status-at-a-tim.patch.

    The supplied revision targets Server b8f4dd13623ce645de77ba5bd89ae8dd8eea2f7c. It is cumulative external reference material, not independently qualified against today's maintained source. Mail headers and commit-message/application instructions were removed; the unified diff is retained. The reporter now claims every rebased commit builds and the tip passes SQLite/MySQL/PostgreSQL tests. Those are attributed claims, not a new maintainer test result. Existing acceptance criteria, security findings and completed implementation receipts remain authoritative. Earlier attached patches are retained as historical references.

    Sanitized patch: 3,063 bytes, SHA-256 e78ba433751e8edf1595af11c4ee6a105df544a297145681957d495bfbd05f99. Original-base sanitized identity: db12b205cb8b780cc44d1c33794cbf5e11155af58b8000a4fb80eef69aef4922. No patch was applied or executed during this update.

    diff --git a/rust/src/runtime/store.rs b/rust/src/runtime/store.rs
index 7ad5a108..1c0c8b74 100644
--- a/rust/src/runtime/store.rs
+++ b/rust/src/runtime/store.rs
@@ -93,10 +93,22 @@ pub(super) struct SignalWrite<'a> {
 }
 /// The runtime-owned message-stream delivery signal (`WorkflowStub::MESSAGE_STREAM_RUNTIME_SIGNAL`).
 const MESSAGE_STREAM_SIGNAL: &str = super::message_streams::INTERNAL_SIGNAL;
-/// A poll's claim candidates in claim order, at most `$limit` of them.
+/// A poll's claim candidates in claim order, at most `$limit` of them. Before
+/// PostgreSQL first analyzes a new database it guesses one row everywhere, and
+/// with one range over both claimable statuses it read every task in the queue
+/// and scanned the runs for each one it kept. Each branch here is one status,
+/// which the dispatch order index returns in claim order, and each looks up
+/// its task's run by id, so the plan doesn't depend on statistics.
 macro_rules! task_candidates_sql {
     ($limit:literal) => {
-        concat!("SELECT t.*,r.workflow_type FROM workflow_tasks t JOIN workflow_runs r ON r.id=t.workflow_run_id WHERE t.namespace=$1 AND t.queue=$2 AND t.task_type=$3 AND t.status IN ('ready','leased') AND r.status IN ('pending','running','waiting') AND (t.status='ready' OR (t.status='leased' AND t.task_type='workflow' AND t.lease_expires_at<=$4)) AND (t.available_at IS NULL OR t.available_at<=$5) AND (COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,'')) IS NULL OR COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,''))=$6) ORDER BY t.priority,t.available_at,t.id LIMIT ", $limit)
+        concat!(
+            "SELECT * FROM ((SELECT t.*,r.workflow_type FROM workflow_tasks t JOIN LATERAL (SELECT r.workflow_type,r.compatibility FROM workflow_runs r WHERE r.id=t.workflow_run_id AND r.status IN ('pending','running','waiting') LIMIT 1) r ON true WHERE t.namespace=$1 AND t.queue=$2 AND t.task_type=$3 AND t.status='ready' AND (t.available_at IS NULL OR t.available_at<=$5) AND (COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,'')) IS NULL OR COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,''))=$6) ORDER BY t.priority,t.available_at,t.id LIMIT ",
+            $limit,
+            ") UNION ALL (SELECT t.*,r.workflow_type FROM workflow_tasks t JOIN LATERAL (SELECT r.workflow_type,r.compatibility FROM workflow_runs r WHERE r.id=t.workflow_run_id AND r.status IN ('pending','running','waiting') LIMIT 1) r ON true WHERE t.namespace=$1 AND t.queue=$2 AND t.task_type=$3 AND t.status='leased' AND t.task_type='workflow' AND t.lease_expires_at<=$4 AND (t.available_at IS NULL OR t.available_at<=$5) AND (COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,'')) IS NULL OR COALESCE(NULLIF(t.compatibility,''),NULLIF(r.compatibility,''))=$6) ORDER BY t.priority,t.available_at,t.id LIMIT ",
+            $limit,
+            ")) AS candidates ORDER BY priority,available_at,id LIMIT ",
+            $limit
+        )
     };
 }
 pub(super) const TASK_CANDIDATES_SQL: &str = task_candidates_sql!(100);

  4. rmcdaniel commented on Nov 7, 2026

    @rmcdaniel
    rmcdaniel
    on Nov 7, 2026
    MemberAuthor

    The PostgreSQL candidate-query implementation is normally merged as dc2a9d1050e9ebb15b6a1ffaf2dce667b292bd7d, tree a7479be2500b508a1be2e0fb856f908aae9e6e62. Exact PR #1088 head d0cc49fd passed its original checks and the normal merge queue. All five main workflows passed. Owner acceptance and completed-topic cleanup, independent main acceptance.

    Actual PostgreSQL 16 and 17 controls preserve candidate order and require the scoped ordered indexes on fresh and analyzed databases. The isolated #1064/#1066 changes are absent. Earlier combined-subject failures remain attributed and are not carried as passing evidence.

    This issue stays open for a release containing these schema-11 changes and its exact published-artifact acceptance. The already published native 3.2.0 image predates this change. Server owns that release follow-through after the current ordered P0 source train.

  5. rmcdaniel commented on Nov 8, 2026

    @rmcdaniel
    rmcdaniel
    on Nov 8, 2026
    MemberAuthor

    Published-artifact reconciliation: delivered in Native Server3.3.0.

    Implementation #1088 is an ancestor of immutable release source 2a15179f29801eec9f2a2d4e6a9c5f93233f104e. Its accepted source checks are62success/1intended skip. The integrated #1098 source head 5f937ec8f385c71dc781875f2f38026b63277faa has a byte-identical rust/ tree to this release. I independently read its successful SQLite, MySQL8.0, MariaDB10.11 and PostgreSQL17 native-test job commands: they run the registered all-targets/ignored qualification suites (only the unrelated MySQL bootstrap child is skipped). This supports the specific source controls below; it is not a new benchmark run.

    PostgreSQL16/17 candidate equivalence and fresh/unanalyzed plus analyzed EXPLAIN controls require ordered index ranges and avoid a runs-table scan per task. The later first-batch change retains both-limit plan controls.

    The 3.3.0 release is public at index sha256:cb4b11bd6235a551cf51e7dc6342504fbbcef19959618b376fb5648ca00fc388, with provenance binding M. Published SQLite corpus acceptance passed, as did four-cell MySQL Compose acceptance, the no-input default, and the release’s SQLite/PostgreSQL/MySQL startup/identity/stop smokes. Both registries’3.3/3/latest aliases resolve to D.

    The issue’s functional/source acceptance and release follow-through are met. Closing as completed. No isolated speedup, reporter benchmark reproduction, all-backend installed corpus or installed EXPLAIN rerun is claimed. Earlier rejected/combined subjects remain attributed. Remaining P0 work, including #1099, is excluded from this3.3.0 cut. Schema11 and rollback constraints remain in the tagged release notes.

  6. rmcdaniel
    closed this as completedon Nov 8, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    agent:serverkind:featureA public product capability or experience is requestedpriority:P0Immediate release or data-integrity riskrepo:serverOwned by the standalone server repository

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions