LISTEN & NOTIFY events are supported in Postgres: postgresql.org/docs/16/sql-listen.... - They're postgresql's pub/sub system. It can do everything from respond to record changes (say a job state change, or a declined credit card transaction), INSERT notifications, sending messages between DB clients, etc.
But there are some important considerations:
Notifications don't have delivery guarantee - they're fire and forget.
Payload limit is 8000 bytes; if you’re pushing thicc data, just send IDs and fetch the rest
Only listeners within the same connection will hear it. You need long-lived connections - essentially this means that if you're connection pooling, you need ANOTHER connection to handle events.
If you're doing more than a few thousand inserts a second, it's not really suitable without some serious Pgsql tuning and you might be better off with something like BullMQ.
I use node's pg_listen module running on a dedicated docker container with a single non-pooled connection, and then something like NATS or gRPC to broker messages as needed.
Bonus reading: look into the pg_cron postgres extension for scheduling jobs in postgres directly:
Well we are using kotlin with PostgreSQL, and on jvm at least we had to use a non maintained PostgreSQL specific connector without pooling to use listen notify. It was a few year's ago but are there better connectors now a days? Also listen notify has an 8k transfer limit afaik?
Having said that I like PostgreSQL a lot and always say, let's prove that our system is actually reaching the limits before adding more complexity using maybe load tests so I agree with the essence of the article.
Could you clarify what you mean by
LISTEN/NOTIFY? Is that a feature in postgres?LISTEN & NOTIFY events are supported in Postgres: postgresql.org/docs/16/sql-listen.... - They're postgresql's pub/sub system. It can do everything from respond to record changes (say a job state change, or a declined credit card transaction), INSERT notifications, sending messages between DB clients, etc.
But there are some important considerations:
I use node's pg_listen module running on a dedicated docker container with a single non-pooled connection, and then something like NATS or gRPC to broker messages as needed.
Bonus reading: look into the pg_cron postgres extension for scheduling jobs in postgres directly:
Combined with LISTEN/NOTIFY, it's super powerful.
Well we are using kotlin with PostgreSQL, and on jvm at least we had to use a non maintained PostgreSQL specific connector without pooling to use listen notify. It was a few year's ago but are there better connectors now a days? Also listen notify has an 8k transfer limit afaik?
Having said that I like PostgreSQL a lot and always say, let's prove that our system is actually reaching the limits before adding more complexity using maybe load tests so I agree with the essence of the article.
Thanks for all the valuable insights!