Building durable streams on Postgres

Julius ParkFounding EngineerHatchet

There is an ancient proverb that goes something along the lines of:

No man ever steps in the same stream twice, for it is not the same stream and he is not the same man.

— Heraclitus (probably)

This is obviously a pretty ludicrous statement. The year is 2026, and we have the technology to step in the same stream as many dang times as we would like to. Put that in your pipe and smoke it, Heraclitus.

At Hatchet, our goal is creating useful and ergonomic building blocks for developers building distributed, async-heavy applications. We originally had a streaming feature, but it was not durable, putting the onus on the developer to cache responses and avoid races between workflow starts and subscriptions.

This is not ergonomic, thus we have built durable streams as a more ergonomic solution. Let’s walk through a common use-case for this feature, and the technical decisions that flow from it.

Enter: The Chat

Chat interfaces have become ubiquitous in the age of large language models, to applause and chagrin. Most chat interfaces revolve around the user inputting text, and getting back a textual response.

But LLMs can be slow, and most users (like me) are too dopamine-fried to watch a spinning loading indicator while the matrix multiplies happen. So apps use streaming to send the results back to the user as soon as they’re available, from which we get a few requirements:

1. Text sent from the backend to the user must be in-order. If each chunk streamed represents a word, changing the order of words could drastically alter meaning. Or, in the best case, just be unintelligible.
2. Streams must be durable, such that a user reloading a page, or switching to another chat window, does not result in messages being lost.
3. Latency needs to be low: users should get results as fast as possible.

Of course, streams are useful for things beyond chat, especially in multi-agent coordination. But most of these systems really boil down to the same set of requirements: ordering, durability, and low latency.

Ordering

Ordering is a hard nut to crack and inevitably involves tradeoffs. With Kafka, only a single partition can guarantee ordering, even within the same topic, so chats either share a partition keyed by chat ID, leaving readers to filter out the others, or each get their own, which doesn’t scale to many chats. With s2.dev, message order is guaranteed only when using their Sessions abstraction, which uses a shared HTTP/2 connection to guarantee the ordering matches the client’s side.

Even with sessions, let’s say a network blip drops the connection after a message is stored, but before the client hears back. Should the client retry, and risk writing the message twice? Or should it give up, and risk losing it? S2’s default retry policy allows duplicates, and avoiding them takes conditional appends or deduplicating them later.

In our implementation, the Hatchet engine is responsible for enforcing strict ordering guarantees per topic, with tunable batch timeouts on the client side to tune throughput vs latency. The ordering is enforced by checking that each incoming message is exactly one past the producer’s last stored sequence number.

With those guarantees in mind, we chose to use Postgres as the persistence layer for these messages. Our self-hosted users already have Postgres, and we have loads of experience with it. Hatchet Cloud is a little more bespoke and artisanal, and probably worthy of its own blog post in the future, but the core of the feature is still Postgres.

The obvious solution to building streams on Postgres is using autoincrementing IDs for each message, ensuring natural ordering. But with concurrent writes, this doesn’t quite work. Once messages hit the engine, they might not be read in the correct order because Postgres assigns autoincrementing IDs at insert, but rows only become visible at commit, so readers can see them out of order. For example, these two transactions, despite starting in the correct order, would end up out of order from the perspective of a subscriber:

There are some tricks that can get around this issue by storing the transaction ID of every committed message, then filtering returned messages by the minimum active transaction ID. This is a nice trick, and I wanted it to work, but it has some scalability downsides. Long-running transactions in the database can indefinitely block reading these messages by keeping the minimum active transaction ID stuck, increasing latency. In a system like Hatchet’s, where Postgres is the core DB, that is a real risk.

Instead, we designed it by using a pessimistic locking setup that 1. reserves message IDs in batches and 2. commits those messages in the same transaction. The reservation happens on the v1_stream_topic table—incrementing the last_offset field and holding a row-level lock on that table. The messages are then inserted into v1_stream_message, safe in the knowledge that no other process can steal the lock on the topic.

This does two things: allows batching the messages, which increases throughput, and provides topic-level ordering consistency without having to wait for transactions from other topics before messages are visible.

Dealing with gRPC

Hatchet engines and the SDKs primarily communicate using gRPC. gRPC is great, with some frustrating limitations. One of those is the configurable message size limit. It defaults to 4MB, and can be set higher, but must be set to compatible values for both sides of the channel. This is possible for self-hosted Hatchet, but difficult for our cloud deployments. Setting the limit too high also incurs performance costs, like increased latency.

However, chat sessions may need to send messages larger than 4MB (for example, a generated image, or very large responses). To work around this, we created a side-channel for sending messages where the SDK will publish the large payload to a REST endpoint prior to sending a message over gRPC (with a reference to the large payload).

This made performance for the most common case quite good, while still supporting edge cases. This is a similar approach to that taken by s2.dev, but using Postgres rather than object storage.

Latency

As has been mentioned throughout this article, one of the things we were aiming for was low latency. The primary requirement is that users don’t want to wait.

But because publishes are serialized per topic, the latency of each individual publish impacts the overall throughput of the system. We primarily optimized for this through the techniques elucidated above—using pessimistic locking rather than transaction ID tricks, as well as by thoughtfully partitioning and indexing the message tables. Messages need the lowest latency when they are being read real-time, closing the gap between when the workflow publishes a message and when the user sees it. However, messages that are older can be read in batches, reducing the per-message time cost. We partitioned the messages table by hour, with the primary key including the topic name, namespace, and tenant ID.

We’ve written quite a bit more about optimizing throughput versus latency in our blog post the fastest Postgres inserts.

Topic Cardinality

Besides latency, the other important design constraint was being able to support many concurrent streams. For the chat application use case, you can imagine that there would be N number of chats for each user, each chat having its own stream associated, with many messages. With that in mind, we designed durable streams for many concurrent streams, as well as many concurrent subscribers to a single stream. Related to the ordering constraints mentioned above, we envision a single producer (most likely a Hatchet workflow executing on a worker), sending messages to be consumed by 1 or many subscribers.

While there may be many open subscribers reading from a stream, once a subscriber has caught up with the most recent messages in a stream, we can amortize the database load by only reading new messages from the database once, and passing them out to all connected subscribers for a topic. This allows for a setup where, when reading from a cursor, the stream can quickly catch up by reading messages in batches, before tailing the newest messages. This also makes the per-topic load negligible. We have benchmarked against real deployments and found the marginal latency with 2000 topics vs 5000 topics to be negligible. Of course, many active streams will incur greater load on the database as well as the Hatchet engine, but that is unavoidable.

Conclusion

This post covered some of the more interesting design decisions that went into the backend of this feature, but the goal is to make something that is incredibly simple and delightful to use. To show how simple it is, we created a demo chat application using durable streams and Hatchet workflows. All of the same benefits you get from using Hatchet apply here—simple scalability, concurrency management, and observability.

We’ll let you be the judge of how delightful it is.