Postgres Orchestrates: Durable Workflows, No External Tools

Database as Orchestrator

The article compellingly argues for leveraging PostgreSQL as a native orchestrator for durable workflows, a paradigm shift that significantly reduces operational overhead and architectural complexity. The core innovation lies in cleverly employing SELECT ... FOR UPDATE SKIP LOCKED to create a robust, concurrent work queue, and using primary key constraints on step checkpoints for automatic idempotency enforcement. The lease-and-sweeper pattern for crash recovery is elegant and efficient, demonstrating how familiar database primitives can solve sophisticated distributed systems problems. This approach consolidates reliability and security into a single, well-understood dependency (Postgres), and makes observability a matter of simple SQL queries, a substantial improvement over exporting data to separate analytics systems.

However, while the article highlights the benefits for I/O-bound automation pipelines with modest concurrency needs, it also implicitly points to limitations. The potential for connection exhaustion with hundreds of polling workers necessitates solutions like PgBouncer. Furthermore, the article acknowledges that for scenarios demanding fan-out across thousands of workers and sub-millisecond dispatch latency, dedicated external orchestrators like Temporal might still be the superior choice. The potential for dead tuple bloat in high-churn queue tables also requires careful tuning of autovacuum and partitioning strategies. While the article presents a strong case for its target use cases, these considerations are crucial for broader adoption and managing expectations regarding scalability and performance under extreme load.

Key Points

• Durable workflows can be implemented directly in PostgreSQL without external orchestrators like Temporal or AWS Step Functions.
• SELECT ... FOR UPDATE SKIP LOCKED enables concurrent work queues with exactly-once processing guarantees.
• Primary key constraints on step outputs enforce idempotency, allowing safe reruns after failures.
• A lease-and-sweeper pattern manages crash recovery by re-enqueuing stalled executions.
• Storing workflow state in Postgres simplifies observability via SQL queries and consolidates reliability/security dependencies.
• This approach is ideal for I/O-bound automation pipelines with modest concurrency, but external orchestrators may be better for extreme scale or sub-millisecond latency needs.

---

📖 Source: Article: Implementing Durable Workflows on Postgres Without an External Orchestrator