GoKawiilPostgres LISTEN/NOTIFY has a bad reputation thanks in part to a popular blog post asserting it does not scale. If that were true, it would be a shame, because LISTEN/NOTIFY is a powerful tool, allowing you to use your Postgres database for low-latency durable notifications, streams, and pub/sub. The accusations aren’t wrong: NOTIFY has unintuitive and undocumented performance characteristics arising from its use of a global lock. But “unintuitive behavior” is not the same as “not scalable.” In this blog post, we’ll show how we optimized LISTEN/NOTIFY-backed streams at scale, achieving 60K writes per second on a single Postgres server with millisecond-scale latency.
Low-Latency Streaming with LISTEN/NOTIFY
The basic design of Postgres-backed streams is simple: create a streams table where each stream chunk (for example, an LLM response token) is a new row, then write to streams by inserting into the table.
The tricky part is reading from the stream because you don't know when the next chunk will arrive. One solution is polling: have each reader poll the end of the stream for new chunks. However, polling scales poorly. If the polling interval is set too high, latency is too high for interactive use-cases (e.g., online chats). But if the polling interval is set too low, concurrent pollers overwhelm the database.
The better solution is LISTEN/NOTIFY. This allows readers to block waiting for a notification from a writer that a new chunk has been published to the stream. That way, readers don’t waste resources polling, but wake up immediately when a new stream chunk arrives.
In our initial implementation of LISTEN/NOTIFY-based streams, a trigger on the streams table fired a function that sent a notification every time a new stream chunk was written. Readers waited for these notifications and woke up to a new stream chunk.
This implementation was correct and delivered low latency, but at scale its throughput was poor. Even using a large Postgres database, it could not sustain more than 2.9K stream writes per second. Interestingly, it bottlenecked without visibly consuming any Postgres resource (CPU, memory, or IOPS). As you may have guessed, the root cause was the original “LISTEN/NOTIFY is not scalable” issue: a global lock Postgres takes during NOTIFY. But why does Postgres do that, and how can we optimize it without losing the benefits of Postgres notifications?
The LISTEN/NOTIFY Exclusive Lock
To understand the problem, we’ll need to examine how Postgres LISTEN/NOTIFY actually works.
... continue reading