Related: Redis has a keyspace notification doing something similar that is not very well known, but when it is needed, it is really needed. We are thinking of extending this mechanism in new ways. Similarly I have seen setups where the notifications arriving from Postgres/MySQL are used in order to materialize (and keep updated) a cached view in Redis. To me, it is interesting how certain teams relay on these kind of mechanisms provided by database systems, and other teams like to do things in the client side to have full control, even in the face of having to reimplement some logic.
Listen to Database Changes Through the Postgres WAL
11–20 of 54 posts
Re: Listen to Database Changes Through the Postgres WAL
#12> The problem with Postgres' NOTIFY is that all notifications go through a single queue! > Even if you have 20 database connections making 20 transactions in parallel, all of them need to wait for their turn to lock the notification queue, add their notification, and unlock the queue again. This creates a bottleneck especially in high-throughput databases. We're currently working hard on optimizing LISTEN/NOTIFY: htt…
Here is the Commitfest entry if you want to help with reviewing/development/testing of the patch: https://commitfest.postgresql.org/patch/6078/
Re: Listen to Database Changes Through the Postgres WAL
#13> The problem with Postgres' NOTIFY is that all notifications go through a single queue! > Even if you have 20 database connections making 20 transactions in parallel, all of them need to wait for their turn to lock the notification queue, add their notification, and unlock the queue again. This creates a bottleneck especially in high-throughput databases. We're currently working hard on optimizing LISTEN/NOTIFY: htt…
We use it like this: CREATE TRIGGER notify_events_trg AFTER INSERT ON xxx.events FOR EACH ROW EXECUTE PROCEDURE public.notify_events(); CREATE FUNCTION public.notify_events() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('events', row_to_json(NEW)::text); RETURN NEW; END; $$; And then we have a bunch of triggers like this on many tables: CREATE TRIGGER create_category_event_trg AFTER INSERT OR DELETE…
Thanks for the report. For that use-case (if you have a single application using a single connection with a LISTEN) then it's expected that is should perform well, since then there is only a single backend which will be context-switched to when each NOTIFY signals it.
Re: Listen to Database Changes Through the Postgres WAL
#14Recently released Clojure implementation of the same pattern: https://github.com/eerohele/muutos
Re: Listen to Database Changes Through the Postgres WAL
#15It's worth mentioning debezium https://debezium.io/ It allows to publish all changes from the db to Kafka.
Does it handle the things that the post mentions about the ever-growing WAL, and the fact that some listeners can go offline and need to get back old messages (eg if Kafka crashes?)
Re: Listen to Database Changes Through the Postgres WAL
#16> The problem with Postgres' NOTIFY is that all notifications go through a single queue! > Even if you have 20 database connections making 20 transactions in parallel, all of them need to wait for their turn to lock the notification queue, add their notification, and unlock the queue again. This creates a bottleneck especially in high-throughput databases. We're currently working hard on optimizing LISTEN/NOTIFY: htt…
The post seems to say that NOTIFY is generally not a good idea, then comments here say that NOTIFY can actually work, but it depends on some particular things (which are not easy to know for newcomers to Postgres), makes it a bit complicated to know what is the way to go for a new database
In my case I have an IoT setting, where my devices can change their "DesiredState", and I want to listen on this to push some message to MQTT... but then there might be also other cases where I want to listen to some messages elsewhere (eg do something when there is an alert on a device, or listen to some unrelated object, eg users, etc)
I'm not clear right now what would be the best setting to do this, the tradeoffs, etc
Imagine I have eg 100k to 10M range of devices, that sometimes these are updated in bulks and change their DesiredState 10k at a time, would NOTIFY work in that case? Should I use the WAL/Debezium/etc?
Can you try to "dumb down" in which cases we can use NOTIFY/LISTEN and in which case it's best not to? you're saying something about single-channel/multi-channel/etc but to a newcomer I'm not clear on what all these are
Re: Listen to Database Changes Through the Postgres WAL
#17Earlier quoted context omitted.
We use it like this: CREATE TRIGGER notify_events_trg AFTER INSERT ON xxx.events FOR EACH ROW EXECUTE PROCEDURE public.notify_events(); CREATE FUNCTION public.notify_events() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM pg_notify('events', row_to_json(NEW)::text); RETURN NEW; END; $$; And then we have a bunch of triggers like this on many tables: CREATE TRIGGER create_category_event_trg AFTER INSERT OR DELETE…
Thanks for the report. For that use-case (if you have a single application using a single connection with a LISTEN) then it's expected that is should perform well, since then there is only a single backend which will be context-switched to when each NOTIFY signals it.
Just out of curiosity, could you try to frame in what context this would or would not work? If you have multiple backends with multiple connections for instance? And then if we start with such a "simple" solution and we later need to scale with distributed backends, how should we do this?
Re: Listen to Database Changes Through the Postgres WAL
#18It's worth mentioning debezium https://debezium.io/ It allows to publish all changes from the db to Kafka.
Perhaps the situation has gotten better since I looked a few years ago, but my experience is the Debezium project doesn’t really guarantee exactly-once delivery. Meaning that if row A is replaced by row B, you might see (A, -1), (A, -1), (B, +1), if for example Debezium was restarted at precisely the wrong time. Then if you’re using this stream to try to keep track of what’s in the database, you will think you have negatively many copies of A.
It sounds silly, but caused enormous headaches and problems for the project I was working on (Materialize), one of whose main use cases is creating incrementally maintained live materialized views on top of replicated Postgres (or MySql) data.
Re: Listen to Database Changes Through the Postgres WAL
#19It's worth mentioning debezium https://debezium.io/ It allows to publish all changes from the db to Kafka.
Does it handle the things that the post mentions about the ever-growing WAL, and the fact that some listeners can go offline and need to get back old messages (eg if Kafka crashes?)
Robustness is a key design goal of Debezium. It supports heart beating to address WAL growth issues (wrote about that issue at [1]). If Kafka crashes (or Debezium itself), it will resume consuming the replication slot from where it left off before (applying at-least once semantics, i.e. there can be duplicates in case of an unclean shut-down).
Naturally, if the consumer is down, WAL retained for that replication slot continues to grow until it comes back up again, hence monitoring is key (or the slot gets invalidated at a certain threshold, it will restart with a new initial snapshot).
Disclaimer: I used to lead the Debezium project
[1] https://www.morling.dev/blog/mastering-postgres-replication-...
Re: Listen to Database Changes Through the Postgres WAL
#20It's worth mentioning debezium https://debezium.io/ It allows to publish all changes from the db to Kafka.
Perhaps the situation has gotten better since I looked a few years ago, but my experience is the Debezium project doesn’t really guarantee exactly-once delivery. Meaning that if row A is replaced by row B, you might see (A, -1), (A, -1), (B, +1), if for example Debezium was restarted at precisely the wrong time. Then if you’re using this stream to try to keep track of what’s in the database, you will think you have n…
Debezium generally produces each change event exactly once if there are no unclean connector shut-downs. If that's not the case, I'd consider this a bug which ought to be fixed.
(Disclaimer: I used to lead the Debezium project)