Co-Locating Workflow Engines with Application Data in Postgres to Eliminate Distributed State Failures

Integrating workflow metadata and application state within a single PostgreSQL database allows system engineers to execute engine checkpoints and operational writes inside a unified ACID transaction. This architecture guarantees exactly-once execution semantics and simplifies atomic multi-system updates without the operational overhead of separate polling infrastructure.

The Mechanics of Co-Located State

Durable workflow engines typically operate as external systems, maintaining execution state in a separate database from the application's operational data. This architectural separation introduces classic distributed systems failure modes, as state updates across two distinct databases cannot easily participate in a single ACID transaction. When a workflow engine is co-located within the same PostgreSQL database as the application, workflow metadata and application data can be updated within a unified database transaction, eliminating partial state failures.

Achieving Exactly-Once Semantics via Transactional Checkpoints

In standard distributed workflows, fault tolerance is achieved by checkpointing step results. If an execution fails, the engine resumes from the last recorded checkpoint. However, if a node crashes after executing an application database write but before the workflow engine records the checkpoint, the step will re-execute upon recovery. This mismatch requires developers to implement complex, application-level idempotency logic—such as maintaining an applied payments table—to guard against duplicate operations.

Co-location resolves this boundary problem by wrapping the database update and the checkpoint write in the same database transaction. The workflow engine provides a transaction context to the executing step. When the step completes, the engine writes the checkpoint to the metadata tables, and the entire transaction—comprising both application writes and workflow checkpoint updates—commits atomically. If a failure occurs prior to commitment, the database rolls back the entire block, allowing the step to be safely retried from a clean state.

Eliminating Outbox Complexity with Database UDFs

Executing atomic multi-system updates, such as updating a database record and triggering a downstream notification, typically requires the transactional outbox pattern. In a decoupled architecture, this involves writing messages to an outbox table within the application transaction, running separate polling infrastructure to deliver those messages, and maintaining reconciliation processes to correct discrepancies when state drifts.

With co-located database-backed workflows, this operational overhead is bypassed using a PostgreSQL user-defined function (UDF). By executing a UDF like `enqueue_workflow` inside the application's database transaction, the system writes the workflow's metadata—including its name, queue, and input parameters—directly to the database. The queueing mechanism and the primary database update commit atomically, ensuring that a workflow is either reliably enqueued or rolled back entirely. A worker then dequeues and executes the task asynchronously, ensuring reliable, outbox-free execution.