More on the topic...
Generating detailed summary...
Failed to generate summary. Please try again.
Postgres’s LISTEN/NOTIFY often gets slammed for poor scaling, but the real issue isn’t the feature itself—it’s how Postgres enforces global ordering. When you call NOTIFY inside a transaction, Postgres grabs an exclusive global lock at commit time and holds it until the transaction’s fsync finishes. That lock forces all NOTIFY-bearing transactions to serialize, so if you trigger a notification on every new stream row, you cap out at a few thousand writes per second, even on a beefy server with CPU and I/O to spare.
To work around this, you can treat NOTIFY as a cue rather than the source of truth. Instead of issuing a NOTIFY on every insert, buffer notifications in memory and flush them in batches. One background transaction takes the global lock for a batch, leaving individual stream writes free to use group commit and other Postgres optimizations. You lose perfect durability of each notify call, but you regain durability in the table itself—and you can cover any missed notifications by having readers poll at a low frequency.
With that change, DBOS ran tests hitting 60,000 inserts per second on a single Postgres instance, all while keeping notification-to-read latency in the 15–100 ms range. At peak load, CPU maxes out, proving the database is truly saturated rather than blocked on locks. All benchmark scripts are on GitHub if you want the exact setup.
Questions about this article
what are notify and listen in postgres? its never really addressed.
In PostgreSQL, LISTEN and NOTIFY provide a built-in, lightweight pub/sub mechanism inside the database. You can think of it as a way for one session to “ring a bell” (NOTIFY) on a named channel and for any other sessions that have “hung a sign” on that bell (LISTEN) to be woken up and handed a message.
How it works
• LISTEN <channel> registers your session as a listener on that channel. After you run LISTEN, PostgreSQL remembers “this session wants notifications for channel X.” You only have to do it once; all subsequent NOTIFYs on X will be delivered to your session until you either close your connection or issue UNLISTEN <channel>.
• NOTIFY <channel>[, 'payload'] publishes a notification event on that channel, optionally with up to a 8000-byte text payload. If it’s inside a transaction, the notification is held until commit; if you roll back, it never goes out. Once the transaction commits, PostgreSQL queues the event and delivers it to every session that has previously LISTENed on that same channel. Per the docs, it’s a simple interprocess signal that “table X changed—go check it if you care,” but you can put any text in the payload