Ask HN: How do/did you use PostgreSQL's LISTEN/NOTIFY in production?

2 points by JoelJacobson 284 days ago | 4 comments

Recently there was an HN thread: "Postgres LISTEN/NOTIFY does not scale", https://news.ycombinator.com/item?id=44490510

We're now working on improving the scalability of LISTEN/NOTIFY in PostgreSQL, and to guide that work, I'd like to better understand how it's used (or was used) in real-world systems. What works well? What doesn't?

The current implementation has some known scalability bottlenecks:

1. Thundering Herd Problem: A NOTIFY wakes up all listening backends in the current database, even those not listening on the notified channel. This is inefficient when many listeners are each listening to their own channels (e.g. in job queues).

2. Commit Lock Contention: NOTIFY operations are serialized behind a heavyweight lock at transaction commit. This can become a bottleneck when many transactions send notifications in parallel.

If you've used LISTEN/NOTIFY in production, I'd love to hear:

- What is/was your use case?

- Does each client listen on its own channel, or do they share channels?

- How many listening backend processes?

- How many NOTIFYs in parallel?

- Are you sending payloads? If so, how large?

- What worked well for you? What didn't?

- Did you hit any scalability limits?

Feedback much appreciated, thanks!

/Joel

JoelJacobson 284 days ago |

I'll add comments to this thread with Github projects that I can find by searching for "postgres listen/notify" on Github.

https://github.com/graphile/worker

    // Line 535 in src/main.ts
    client.query('LISTEN "jobs:insert"; LISTEN "worker:migrate";')

Every worker pool seems to listen on the same shared channels: jobs:insert - all workers get notified when new jobs are added worker:migrate - all workers get notified about database migrations

BenjieGillam 284 days ago | |

Yep; we have a dedicated listener client per pool (and normally something like 1-20 pools, each with 1-100 workers); the workers themselves don’t listen. We also notify on every job or batch job insert, though we would love to rate limit this so we notify at most once per 2 milliseconds.

stop50 284 days ago |

I have ever seen listen/notify in example code and pulp3

JoelJacobson 284 days ago | |

Thanks! I found a link to a GitHub Issue where they described their experienced problems: https://github.com/pulp/pulpcore/issues/6805