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

The notifier pattern for applications that use Postgres

brandur.org

Post reply on HN
Print view View original

51–56 of 56 posts

Re: The notifier pattern for applications that use Postgres

#51

Post by benesch »

BeefySwain wrote: post #49
benesch wrote: post #45

Earlier quoted context omitted.

We did not originate the Business Source License (BSL/BUSL). It was originally developed by the folks behind MariaDB. Wikipedia has a good article that covers the history: https://en.wikipedia.org/wiki/Business_Source_License Other large projects using the BSL include CockroachDB and (somewhat infamously) Terraform. We're very glad to have been using the BSL for Materialize since our very first release. Relicensing a…

I was actually asking about the automatic timed re-license to Apache :)

Ah, I misunderstood! Yes, we may have invented that. I whipped up the cron job a few years back in response to concerns from our legal team. I’m not aware of any prior art for automatically advancing the change date for the BSL.

Re: The notifier pattern for applications that use Postgres

#52

Post by mcqueenjordan »

hot_gril wrote: post #37
mcqueenjordan wrote: post #10

Other than the space for past notifications and/or having to issue a DELETE, are there significant reasons to prefer this over the typical table-based approach with SKIP LOCKED queries to poll the queue? It seems to me that if the listener dies, notifications in the meantime will be dropped until a listener resubscribes, right? That seems prone to data loss. In the SKIP LOCKED topic-poller style pattern (for example,…

Tbh I didn't know about SKIP LOCKED until now, but it looks like you have to hold a xact open the entire time the worker runs, which can be a problem. What I've done before is timestamp cols for start/end. A worker takes any job whose end time is null and start time is not too recent, which makes retries natural and flexible. A pubsub pattern like pg_notify can definitely make sense depending on the requirements, but…

Yeah, you can avoid holding the xact with the means that you mentioned, e.g. SKIP LOCKED and set some value to PROCESSING, then do your processing, then update to DONE at the end. Or as you mentioned, timestamps.

I think the SKIP LOCKED part is really only useful to avoid contention between two workers querying for new work simultaneously.

Re: The notifier pattern for applications that use Postgres

#53

Post by hot_gril »

mcqueenjordan wrote: post #52
hot_gril wrote: post #37

Earlier quoted context omitted.

Tbh I didn't know about SKIP LOCKED until now, but it looks like you have to hold a xact open the entire time the worker runs, which can be a problem. What I've done before is timestamp cols for start/end. A worker takes any job whose end time is null and start time is not too recent, which makes retries natural and flexible. A pubsub pattern like pg_notify can definitely make sense depending on the requirements, but…

Yeah, you can avoid holding the xact with the means that you mentioned, e.g. SKIP LOCKED and set some value to PROCESSING, then do your processing, then update to DONE at the end. Or as you mentioned, timestamps. I think the SKIP LOCKED part is really only useful to avoid contention between two workers querying for new work simultaneously.

Yeah, I can imagine SKIP LOCKED being faster if used that way. Just hasn't been an issue for me yet, so I haven't tested.

Re: The notifier pattern for applications that use Postgres

#54

Post by jjovan1 »

benesch wrote: post #48
jjovan1 wrote: post #44

Earlier quoted context omitted.

Ok so I was wondering if your solution is faster. I noticed their materialized views are not as fast for real time data.

We haven't benchmarked TimescaleDB, so I can't say. Results tend to vary heavily by workload, too. What I can say is that the research at the heart of Materialize ( https://dl.acm.org/doi/10.1145/2517349.2522738 ) allows us to efficiently maintain computations that are more complex than what a lot of other IVM systems can handle. Your best bet is to run your own benchmark of both systems using data that's representat…

Thanks!

Re: The notifier pattern for applications that use Postgres

#55

Post by orangeisthe »

dajtxx wrote: post #50

I looked into using LISTEN/NOTIFY recently and I must be misunderstanding. How is it different to polling a table? It seemed like it had all the same machinery behind it.

well it's the same difference between polling an api vs setting up a webhook.

Re: The notifier pattern for applications that use Postgres

#56

Post by orangeisthe »

mcqueenjordan wrote: post #10

Other than the space for past notifications and/or having to issue a DELETE, are there significant reasons to prefer this over the typical table-based approach with SKIP LOCKED queries to poll the queue? It seems to me that if the listener dies, notifications in the meantime will be dropped until a listener resubscribes, right? That seems prone to data loss. In the SKIP LOCKED topic-poller style pattern (for example,…

They can be used in conjunction.
Post reply on HN