8 ms·
How to listen to database changes using Postgres triggers in elixir
- andreicek 3y agoGreat article, thank you! Please submit it to https://elixirstatus.com/ https://elixirstatus.com/ as well!
- stevencorona 3y agoI've been using Elixir for the past 5-6 years for my startup. We use pg_notify extensively to broadcast changes between running nodes (basically, use Phoenix.PubSub locally in our apps, with a GenServer to subscribe+re-broadcast using pg_notify). This has been a really elegant and low-complexity way to get distributed pubsub without the complexity of running a distributed erlang cluster (which seems a lil bit painful in a K8S+Continuous Deploy world) There -are- some big downsides to be aware of though. 1. You can't use PgBouncer w/ LISTEN/NOTIFY. This has been really painful because of the high memory overhead of a pgsql connection + elixir keeping a pool of open pgsql connections. The tried and true method of scaling here is to just use PgBouncer. We've kicked the can on this by vastly over-provisioning our pg instance, but this has cost $10s of thousands on the cloud. Of course, it's solvable (dedicated non-pgbouncer connection pool just for LISTEN/NOTIFY, for example), but painful to unwind. 2. The payload has a fixed size limit (8KB, IIRC). This has bitten us a few times! Even though I really like pg_notify, I think that if I were starting over, I'd probably just use Redis Pub/Sub to accomplish the same thing. Tad bit more complex if you're not already running Redis, but without the downsides. (Of course, w/ Redis, you don't get the elegance of firing a notification via a pg trigger)
- timwis 3y agoI believe #2 was the main driver for the supabase team to build their real-time component: https://github.com/supabase/realtime https://github.com/supabase/realtime Background/announcement: https://supabase.com/blog/supabase-realtime-multiplayer-general-availability https://supabase.com/blog/supabase-realtime-multiplayer-gene...
- lawik 3y agoWhich as far as I gather uses Phoenix + Erlang distribution to get Phoenix.PubSub going. Which parent was not wanting to do. Funny full circle.
- chasers 3y agoTriggers can affect throughput quite a bit on busy tables. And we didn't want people schemas polluted with triggers. But also we use Erlang distribution and Phoenix.PubSub because with a global network clients connected to the same node in the same region will get normal Broadcast messages to each other faster. If we ran them through Postgres or Redis the added latency wouldn't work for a global thing.
- the_angry_angel 3y agoFor #1 I've been keeping a keen eye on pgcat [1], in particular the https://github.com/postgresml/pgcat/issues/303 https://github.com/postgresml/pgcat/issues/303 which implies that it should be possible to add support for transaction mode LISTEN/NOTIFY support. [1] https://github.com/postgresml/pgcat https://github.com/postgresml/pgcat
- cpursley 3y agoWalEx gets around these issues.
- gg2222 3y agoHi! Does WalEx continue where it left off after disconnects?
- Dowwie 3y agoHave you contacted the Supavisor team about adding listen/notify support? Supavisor may be your way forward: https://github.com/supabase/supavisor https://github.com/supabase/supavisor
- parthdesai 3y agoDoes GenServer's mailbox introduce any lag/latency? We've found at high loads, GenServer's mailbox can be a bottleneck
- colinchartier 3y agoWe got around this at my company by just pooling all of the LISTEN/NOTIFY streams into a single database connection in software, here's a sample implementation: function software_listen(channel, callback): if not channel_listened(channel): sql("LISTEN " + channel) listeners[channel].append(callback) function on_message(channel, data): for listener in listeners[channel] listener(channel, data) function unlisten(channel, listener): listeners[channel].remove(listener) if len(listeners[channel]) == 0: sql("UNLISTEN " + channel) Here's the actual go implementation we use: https://gist.github.com/ColinChartier/59633c1006407478168b52e2318da8e8 https://gist.github.com/ColinChartier/59633c1006407478168b52...
- chasers 3y agoPhoenix.PubSub is basically a noop service. It really just works. If discovering nodes is difficult in your env, try using a listen/notify libcluster strategy: https://github.com/supabase/supavisor/blob/main/lib/cluster/strategy/postgres.ex https://github.com/supabase/supavisor/blob/main/lib/cluster/...
- stefs 3y agoboo! cows don't have "hooves instead of feet" and they don't lack toes either!
- pjullrich 3y agoI'm sorry, I'm an Elixir developer, not a cow-expert :p
- smcameron 3y agoI thought this was going to be about using audio to literally listen to the database along the lines of "What different sorting algorithms sound like" https://www.youtube.com/watch?v=t8g-iYGHpEA https://www.youtube.com/watch?v=t8g-iYGHpEA
- RobotToaster 3y agoI thought it was going to be about making chiptunes from database changes.
- pjullrich 3y agoI'm sorry to disappoint. Maybe I can interest you in the sounds of a block hole? https://www.youtube.com/watch?v=NWBkZ3bMSV0&ab_channel=CBSNews https://www.youtube.com/watch?v=NWBkZ3bMSV0&ab_channel=CBSNe...
- olavgg 3y agoIf you want to listen to database changes, check out Debezium. Instead of triggers it takes advantage of the more recent CDC functionality that most SQL Servers has implemented today. The difference is that triggers works on every transaction while CDC works on a redo log file. This makes it possible to transfer database changes with minimal performance impact.
- jpalomaki 3y agoLink: https://debezium.io/ https://debezium.io/
- pantulis 3y agoThanks for this! I knew about CDC solutions from vendors like Informatica, but wasnt aware of Debezium.
- 29_29 3y agoDo you have more information on CDC?
- nickpeterson 3y agoMost popular databases have change data capture as a feature. They read backups of the transaction log and extract events they care about (dml operations against subscribed tables) and then store the results into shadow tables. They tend to be relatively low overhead compared with many hand-rolled solutions. One other benefit is they capture all the changes to the underlying data, not just the net changes. It’s important to realize though that CDC records change information but isn’t a mechanism to move it anywhere. You would still have to devise a means to move the data to another system. Debezium is a data movement tool that uses CDC for the underlying tracking.
- AndrewKemendo 3y agoDebezium is an amazing tool for bootstrapping event streams +1 would recommend CDC is such a great concept and is so little used unfortunately
- 3y ago
- moojersey 3y agoDebezium is a cool project, though as a fair warning does come with a fair amount of on-going maintenance. And you should prolly be using Kafka and comfortable with the JVM generally. FWIW, we're (estuary.dev) an open-source and fully managed tool for building CDC pipelines out of Postgres from the WAL.
- xwdv 3y agoThis is exactly why we didn’t use it and instead opted for native triggers.
- wlindley 3y agoListening to your database — is there a way to create an audio stream so you can actually "listen to" reads (a low hum), writes (tinkling wind chimes), and failed transactions (BANG! CRASH! BOOM!)? That would be a useful ambient sound for a DBA's office, wouldn't it?
- mad44 3y agohttp://muratbuffalo.blogspot.com/2016/09/sonification-for-monitoring-and.html http://muratbuffalo.blogspot.com/2016/09/sonification-for-mo... Sonification for monitoring and debugging distributed systems
- aaronmu 3y agoThis is all fun and games but how do you catch-up after a disconnect? Why choose this over logical replication?
- flux293m 3y agoAn approach I moved from this to, was to use the DB trigger to write jobs directly to an Oban oban_jobs table. Oban jobs run almost instantly when they are queued so there's no perceptible difference in speed, but you get all the benefits of resilience that Oban provides, so your webapp connection can go down, such as during a deploy, and still catch up reliably. It's also handy to be able use the oban_jobs table for debugging purposes.
- aaronmu 3y agoVery interesting project, thanks for linking. Was thinking about building something like Oban.Peer a while ago. We're not using Elixir. Might use Oban as an example :-)
- noisy_boy 3y ago> Postgres offers quick and simple Notifications that can help you react to changes in your database without much overhead. They are particularly interesting if you can’t use Phoenix’s PubSub, for example, if another non-Elixir application also makes changes to your database. > PERFORM pg_notify('appointments_canceled_changed', payload); > Be aware that this listener can easily become a bottleneck if you have lots of messages. If you can’t handle the messages quickly enough, the message queue will fill up and crash your application. If you’re worried about this case, you could create one listener per channel or use a PartitionSupervisor to start more handlers and spread out the work. Why not insert into an events table instead of pg_notify? That way the events are recorded within the database itself, can be processed by any component, the state of processing can be saved in the table so even if the component dies, it can resume (and can even fan out the actual processing to workers). Further, you have the record of all events alongwith the flexibility of interacting with the event information with SQL and with partitioning, you can have a clean way to manage performance + ability to easily archive past/processed events.
- paulddraper 3y agoIt depends whether the events are persistent in nature. "Debit account $100" is persistent. "Account balanced changed" is ephermeral. Storing ephemeral events would be a large amount of overhead; pg_notify is far faster as it does not write to storage or WAL.
- deleted 3y ago[deleted]
- bnchrch 3y agoThis was a fun read! It reminds me of a very similar post I put out in 2018 https://by.ben.church/Get-notified-of-user-signups-and-plan-changes-automatically-using-Postgres-and-Phoenix-PubSub/ https://by.ben.church/Get-notified-of-user-signups-and-plan-... But I think Peter did a much better job going through the mechanics and providing a more modernized example. For those that are curious there are pitfalls (that can be worked around) 1. If your DB goes down you may loose messages 2. If you have multiple backends behind a load balancer you may trigger additional events 3. There is a limit to the payload size you can send through these triggers But for those that want to try this approach I do have a library here that does wraps everything Peter layed out: https://github.com/bnchrch/postgrex_pubsub https://github.com/bnchrch/postgrex_pubsub Also if you want something even better I recommend WalEx https://github.com/cpursley/walex https://github.com/cpursley/walex Which is based on WAL logs and doesnt have the same limitations.
- rjbwork 3y agoI actually have this exact problem right now with SQL Server on AWS RDS. Unless I want to pay for standard+ editions in my dev/stage/qa/etc. environments, I can't use the baked in CDC features. And because of the minimum instance sizes for Standard+ edition, it costs ~1700 bucks per month per database. This is fine for production, because I need features like High Availability, but paying a significant premium over web/express in those environments seems like lighting money on fire. We're already tracking changes for the purposes of time travel queries and other auditing purposes using Temporal Tables (SQL:2011 feature). I'm thinking a cron job triggering a lambda every minute should be sufficient to read from the history tables and publish out change data events over a bus. Anyone see any problems with this approach?
- __jem 3y agoI'm not sure how this interacts with temporal tables, but I know that I've run into issues before just trying to track something with a high watermark, in that long running transactions might add records "in the past" behind your watermark that you track, so you need some mechanism to make sure you're capturing those (i.e., you can't just track an auto-inc primary key). Unfortunately, I think CDC is really the best way to handle this.
- rjbwork 3y agoI agree that it is probably the best choice, I'm just not willing to >quadruple my server costs for the feature, unfortunately. I appreciate you taking the time to reply, and I did some further research. It looks like as long as transactions are not taking excessive time to complete, that temporal tables will be sufficient, since the history tables get marked with the transaction begin times. I'll use a sliding window approach and dedupe. EDIT: I also found this https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/s... which seems like it's supported on Express and Web versions and should be a fairly robust solution.
- dagss 3y ago
- RicDan 3y agoPretty cool! not the same but for flyway + java I have a simple github action that on push to develop checks the diff for any *.sql files and mails them to the DA team, as they dont have access to our repo
- SanderNL 3y agoI was hoping for some sort of audio stream to listen to my database changes. High notes for inserts, low rumbles for reads or something. That could be pretty interesting actually.
- alostpuppy 3y agoSome day my friend, someday.