Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Listen to Database Changes Through the Postgres WAL

peterullrich.com

Post reply on HN
Print view View original

41–50 of 54 posts

Re: Listen to Database Changes Through the Postgres WAL

#41

Post by nightwolf »

chamomeal wrote: post #33
refset wrote: post #14

Recently released Clojure implementation of the same pattern: https://github.com/eerohele/muutos

I saw the post on the clojure subreddit! I’m stoked to try it out. Ever since I saw Martin Kleppman’s “Turning the database inside out” talk I’ve wanted an easy way to hook into a transaction log. Apache samza/kafka is very cool but I’m not going to set it up for personal projects. It’d be VERY cool to make materialized views straight from the log!!

If you do end up trying it out and hit any roadblocks, please don't hesitate to file an issue. I'd be very interested in hearing how it goes!

Re: Listen to Database Changes Through the Postgres WAL

#42

Post by School-Cotton »

gunnarmorling wrote: post #40
School-Cotton wrote: post #35

Earlier quoted context omitted.

The problem is that unclean connector shutdowns are a thing that can happen in real life.

They can happen, yes, although this should be a rather rare event (the most common reason would be misconfiguration, such as a K8s pod with too low memory limits). That said, work towards exactly-once has been done [1], utilizing the support for EOS in Kafka Connect (KIP-618). In particular for Postgres, consumers can detect and reject duplicates really easy though, by tracking a watermark for the {Commit LSN / Event…

> They can happen, yes, although this should be a rather rare event

For our use case, it didn't matter if it was rare or not: the fact that it could happen at all meant we needed to be robust to it, which basically meant storing the entire database in memory.

> We added support for exposing this via the `source.sequence` field a while back upon request by the Materialize team btw.

Yes, I helped work on this! I'm not sure whether Materialize is still using it (it's been years since I've thought about MZ/Debezium integration) but it was helpful, thanks.

Re: Listen to Database Changes Through the Postgres WAL

#45

Post by cpursley »

stackskipton wrote: post #43

The Ops person in me is crying. Please stop doing this via database. Scaling issues can develop and we in Ops catch flak for your bad design choices. Use Kafka/RabbitMQ/Redis PLEASE.

Those can listen to database changes and aren't separate services to set up, keep online and pay for? Neat (and news to me)!

Re: Listen to Database Changes Through the Postgres WAL

#46

Post by stackskipton »

cpursley wrote: post #45
stackskipton wrote: post #43

The Ops person in me is crying. Please stop doing this via database. Scaling issues can develop and we in Ops catch flak for your bad design choices. Use Kafka/RabbitMQ/Redis PLEASE.

Those can listen to database changes and aren't separate services to set up, keep online and pay for? Neat (and news to me)!

Except if you disconnect for any reason, you will miss changes. If you want to break out services into different servers, clients will still be crossing the streams to listen. Database schema changes must be coordinated properly. Basically, you are taking one database to rule them all model which comes with massive drawbacks and inserting additional capabilities into it.

Sure, if you are tiny, will forever remain tiny, then whatever, ignore this Ops person.

Re: Listen to Database Changes Through the Postgres WAL

#47

Post by cpursley »

stackskipton wrote: post #46
cpursley wrote: post #45

Earlier quoted context omitted.

Those can listen to database changes and aren't separate services to set up, keep online and pay for? Neat (and news to me)!

Except if you disconnect for any reason, you will miss changes. If you want to break out services into different servers, clients will still be crossing the streams to listen. Database schema changes must be coordinated properly. Basically, you are taking one database to rule them all model which comes with massive drawbacks and inserting additional capabilities into it. Sure, if you are tiny, will forever remain tin…

Please check out the linked libs, this is not true (and dig deeper into Erlang/Elixir). The schema change issue can be tricky, however.

Re: Listen to Database Changes Through the Postgres WAL

#49

Post by School-Cotton »

chamomeal wrote: post #33
refset wrote: post #14

Recently released Clojure implementation of the same pattern: https://github.com/eerohele/muutos

I saw the post on the clojure subreddit! I’m stoked to try it out. Ever since I saw Martin Kleppman’s “Turning the database inside out” talk I’ve wanted an easy way to hook into a transaction log. Apache samza/kafka is very cool but I’m not going to set it up for personal projects. It’d be VERY cool to make materialized views straight from the log!!

> It’d be VERY cool to make materialized views straight from the log!!

This is what Materialize does. You point it at some PG (or MySQL, or... probably lots more by now) sources, and then you can define arbitrary views on top of those sources using SQL (with the full power of relational logic: fully precise joins, etc.) The views are maintained incrementally, so they update almost instantly when the underlying data updates; you don't have to manually refresh them.

Disclaimer: I worked on Materialize for five years, and it is a commercial, proprietary project. But it is source-available (https://github.com/materializeinc/materialize) under a BSL-style license, and it has fairly generous terms for free usage.

Re: Listen to Database Changes Through the Postgres WAL

#50

Post by tengbretson »

The longer I work the fewer legitimate use cases for LISTEN/NOTIFY I seem to find. At this point, I only really see the value in using it to allow a polling loop to have a longer sleep and to use notify events as interrupts. Even that use case comes with its own set of problems that may make it unsuitable.
Post reply on HN