2026-09-15
每日一文 · 长文精读
Article: Implementing Durable Workflows on Postgres Without an External Orchestrator
在 Postgres 上实现持久化工作流，无需外部编排器
作者：Raman Varma · InfoQ 原文
摘要：本文介绍了如何在 Postgres 上实现持久化工作流，无需使用 Temporal 或 AWS Step Functions 等外部编排器。通过使用 SELECT ... FOR UPDATE SKIP LOCKED 将表转换为并发工作队列，利用主键约束保证幂等性，并通过租约和清扫器模式实现崩溃恢复。将工作流状态存储在主数据库中，简化了可观测性并减少了依赖。
Key Takeaways
关键要点
Durable execution does not require a dedicated orchestrator like Temporal or AWS Step Functions; a relational database you already operate can play that role and remove a stateful, external system from your critical path.
持久化执行不需要像 Temporal 或 AWS Step Functions 这样的专用编排器；您已经运营的关系数据库可以扮演这个角色，并将有状态的外部系统从关键路径中移除。
SELECT ... FOR UPDATE SKIP LOCKED turns an ordinary Postgres table into a concurrent work queue where each row is claimed by exactly one worker, with no message broker, leader election, or external lock service.
SELECT ... FOR UPDATE SKIP LOCKED 将普通的 Postgres 表转换为并发工作队列，其中每行恰好由一个 worker 声明，无需消息代理、领导者选举或外部锁服务。
Making step checkpoints a primary-key constraint lets the database enforce idempotency, so a step that reruns after a crash reads its prior result instead of repeating a side effect.
将步骤检查点设为主键约束，让数据库强制幂等性，这样崩溃后重跑的步骤会读取之前的结果，而不是重复副作用。
Crash recovery can be implemented with a simple lease-and-sweeper pattern, where workers heartbeat the rows they own and a periodic query re-enqueues any execution whose lease expired.
崩溃恢复可以通过简单的租约和清扫器模式实现，worker 对自己拥有的行进行心跳，定期查询会重新入队任何租约过期的执行。
Storing workflow state in your primary database makes observability a plain SQL query and collapses reliability and security to one dependency.
将工作流状态存储在主数据库中，使可观测性成为简单的 SQL 查询，并将可靠性和安全性归结为一个依赖。
When we started building Kestrel Workflows (deterministic workflows for automating incident response, cloud provisioning, CI/CD, and developer requests), we hit the problem every automation system eventually hits.
当我们开始构建 Kestrel Workflows（用于自动化事件响应、云配置、CI/CD 和开发者请求的确定性工作流）时，我们遇到了每个自动化系统最终都会遇到的问题。
Our workflows had to be durable and survive all types of failures.
我们的工作流必须是持久的，并能承受各种类型的故障。
A workflow can be defined to trigger on failing Kubernetes workloads, run an AI root cause analysis, generate a fix, wait for a human to approve the fix via Slack, and then open a GitOps pull request.
可以定义一个工作流，在 Kubernetes 工作负载失败时触发，运行 AI 根因分析，生成修复方案，等待人工通过 Slack 批准修复，然后打开 GitOps 拉取请求。
If any of our systems are rescheduled in the middle of such a workflow (in Kubernetes, it most likely will) we couldn't just lose the workflow execution.
如果我们的任何系统在此类工作流中途被重新调度（在 Kubernetes 中很可能发生），我们不能丢失工作流执行。
We needed durable execution.
我们需要持久化执行。
The idea behind durable execution is quite simple and powerful.
持久化执行背后的想法非常简单而强大。
As a program runs, it checkpoints its progress to an external system like a database.
当程序运行时，它将进度检查点到外部系统（如数据库）。
If the process dies, another one reloads the last checkpoint from the external system and continues from the last completed step.
如果进程死亡，另一个进程从外部系统重新加载最后一个检查点，并从最后完成的步骤继续。
It is like autosave from a video game, but for backend code.
这就像视频游戏中的自动保存，但用于后端代码。
The usual advice for implementing durable execution is to use an external orchestrator like Temporal or AWS Step Functions, which act as central orchestrators that coordinate steps in a workflow.
实现持久化执行的通常建议是使用外部编排器，如 Temporal 或 AWS Step Functions，它们充当中央编排器，协调工作流中的步骤。
In this model, a client submits a workflow, the orchestrator persists it in its own data store, and dispatches it to a worker.
在此模型中，客户端提交工作流，编排器将其持久化在自己的数据存储中，并将其分发给 worker。
For every completed step, the workflow sends its results back to the orchestrator, which checkpoints the progress and then dispatches the next step.
对于每个完成的步骤，工作流将其结果发送回编排器，编排器检查点进度，然后分发下一步。
If a worker dies, the orchestrator reassigns its steps to a healthy worker.
如果 worker 死亡，编排器将其步骤重新分配给健康的 worker。
We almost went this way, but reconsidered when we looked at what it would cost us.
我们几乎走了这条路，但当我们看到代价时，重新考虑了。
Using an external orchestrator means having another stateful system to deploy, secure, monitor, and upgrade.
使用外部编排器意味着需要部署、保护、监控和升级另一个有状态系统。
It sits in the critical path of every workflow, so it becomes a single point of failure.
它位于每个工作流的关键路径中，因此成为单点故障。
It needs its own access controls and audit handling because workflow step payloads, which for us include things like infrastructure topology, source code, and logs, now transmit to an external system.
它需要自己的访问控制和审计处理，因为工作流步骤负载（对我们来说包括基础设施拓扑、源代码和日志）现在传输到外部系统。
It brings its own data model, which is usually a key-value store optimized for workflow state rather than arbitrary relational queries, making ad-hoc analysis less straightforward than querying Postgres directly.
它带来了自己的数据模型，通常是针对工作流状态优化的键值存储，而不是任意关系查询，使得临时分析不如直接查询 Postgres 直接。
But here's the thing: Durable execution is fundamentally about a database since it's all about checkpointing state to a durable external system.
但问题是：持久化执行从根本上讲是关于数据库的，因为它都是关于将状态检查点到持久的外部系统。
We already run Postgres as our system of record and have invested heavily in making sure it performs at scale, so we asked ourselves: Why use an external orchestrator at all?
我们已经将 Postgres 作为记录系统，并投入了大量精力确保其大规模性能，所以我们问自己：为什么要使用外部编排器？
Why not make Postgres the orchestrator?
为什么不把 Postgres 作为编排器？
In Kestrel, there is no orchestrator process.
在 Kestrel 中，没有编排器进程。
Each application server runs an embedded durable workflow library and talks straight to Postgres.
每个应用服务器运行一个嵌入式持久化工作流库，并直接与 Postgres 通信。
Every trigger, from PagerDuty alerts to GitHub webhooks, inserts a row into a workflow_executions table:
每个触发器，从 PagerDuty 警报到 GitHub webhooks，都会向 workflow_executions 表插入一行：
Servers poll this table to claim work.
服务器轮询此表以声明工作。
For each completed step, the server checkpoints the output to Postgres itself.
对于每个完成的步骤，服务器将输出检查点到 Postgres 本身。
All coordination happens through the Postgres database.
所有协调都通过 Postgres 数据库进行。
The trick that makes it safe is one clause:
使其安全的技巧是一个子句：
FOR UPDATE SKIP LOCKED is the quiet hero of most Postgres-backed queues.
FOR UPDATE SKIP LOCKED 是大多数 Postgres 支持队列的默默英雄。
It locks the rows a worker grabs and tells every other worker to skip past them rather than block, giving the system exactly-once processing guarantees.
它锁定 worker 抓取的行，并告诉其他 worker 跳过它们而不是阻塞，从而为系统提供恰好一次处理保证。
Two servers can poll the same table at the same time and never hand the same workflow to two executors.
两台服务器可以同时轮询同一张表，永远不会将同一工作流交给两个执行器。
Although this article focuses on Postgres, this queue pattern is not specific to Postgres: MySQL 8.0+, MariaDB 10.6+, and Oracle also support SKIP LOCKED, while Db2 and SQL Server provide equivalent mechanisms for skipping locked rows.
虽然本文重点介绍 Postgres，但这种队列模式并非 Postgres 特有：MySQL 8.0+、MariaDB 10.6+ 和 Oracle 也支持 SKIP LOCKED，而 Db2 和 SQL Server 提供了跳过锁定行的等效机制。
This claim happens inside a short transaction that also flips the status and writes the lease, so the worker owns the row before it does any work:
此声明发生在短事务中，该事务还会翻转状态并写入租约，因此 worker 在执行任何工作之前拥有该行：
This transaction is the entire dispatcher.
此事务就是整个调度器。
You don't need a broker, leader election, or Redis locks with TTLs.
您不需要代理、领导者选举或带 TTL 的 Redis 锁。
A few things that should be called out for production implementations:
对于生产实现，应该指出几点：
The claim transaction should be tiny.
声明事务应该很小。
It should just lock the row, flip the status, and commit.
它应该只锁定行、翻转状态并提交。
If you hold the FOR UPDATE transaction open while the step runs, you will pin a row lock for the duration of potentially long running, external work.
如果在步骤运行时保持 FOR UPDATE 事务打开，您将在可能长时间运行的外部工作期间固定行锁。
LIMIT can be more than 1 to claim a batch of executions and amortize round trips, but the larger the batch is, the larger the blast radius if the worker dies before it finishes them.
LIMIT 可以大于 1 以声明一批执行并分摊往返，但批次越大，如果 worker 在完成之前死亡，爆炸半径就越大。
SKIP LOCKED does not preserve FIFO under contention; for our workflows that is fine and actually desirable, but if you need strict ordering, this pattern won't work.
SKIP LOCKED 在争用下不保持 FIFO；对于我们的工作流来说这没问题，实际上也是可取的，但如果您需要严格排序，这种模式将不起作用。
Step checkpoints make use of a second Postgres primitive: integrity constraints.
步骤检查点利用第二个 Postgres 原语：完整性约束。
Each step writes its output to an operation_outputs table keyed by (execution_id, step_id) as the primary key:
每个步骤将其输出写入 operation_outputs 表，该表以 (execution_id, step_id) 作为主键：
When a step finishes, the worker records the result with an upsert that never overwrites:
当步骤完成时，worker 使用永不覆盖的 upsert 记录结果：
If a recovering worker reruns a step that has already committed, the unique constraint prevents a duplicate checkpoint from being inserted.
如果恢复中的 worker 重跑已经提交的步骤，唯一约束防止插入重复检查点。
The worker reads the existing checkpoint and returns the prior result instead.
worker 读取现有检查点并返回之前的结果。
The database, rather than the application code, enforces idempotency.
数据库而不是应用程序代码强制幂等性。
Before executing a step, the Orchestrator SDK running
在执行步骤之前，运行的 Orchestrator SDK
阅读理解
1. What is the primary purpose of using SELECT ... FOR UPDATE SKIP LOCKED in the workflow queue?
2. How does the system enforce idempotency for step checkpoints?
3. What is a potential drawback of using SKIP LOCKED for the workflow queue?