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

41–50 of 56 posts

Re: The notifier pattern for applications that use Postgres

#41

Post by BeefySwain »

benesch wrote: post #39
steeeeeve wrote: post #36

Earlier quoted context omitted.

It's crazy to me that the most updated file in your repository is the license - pushing back the open source date by a day every day.

Those updates are not retroactive. They apply on a go forward basis. Each day's changes become Apache 2.0 licensed on that day four years in the future. For example, v0.28 was released on October 18, 2022, and becomes Apache 2.0 licensed four years after that date (i.e., 2.5 years from today), on October 18, 2026. [0]: https://github.com/MaterializeInc/materialize/blob/76cb6647d...

I love this concept. Did you all come up with this or is there prior art? Is there a name for this concept?

Re: The notifier pattern for applications that use Postgres

#42

Post by zorgmonkey »

riedel wrote: post #33

I was recently very annoyed by my immich server on my NAS doing constant writes to risk. I discovered that this was due to the use of the postgres notify backed socket.io plugin. It turns out while the notify itself does not use the WAL for any information, one needs to trigger a WAL flush for the notify to propagate. In my case this lead toa lot of unnecessary empty WAL writes. If you scale up applications or anyway…

I'm not an expert in postgres performance, but I would think that excessive WAL flushing would also slow down workloads with constant writes.

Re: The notifier pattern for applications that use Postgres

#43

Post by cryptonector »

> [...] was somewhat shocked recently looking into MySQL and SQLite to learn that even in 2024, no equivalent exists.

I'm not sure how one would make `NOTIFY` work on SQLite3. Maybe one could have a system table (`sqlite_temp_notifies`?) that would store notifies, and then have a directory associated with the database where one can create pipes/AF_LOCAL sockets that a process/thread running `NOTIFY` would attempt to write to in a non-blocking manner, then delete the notifies from that system table. The system table would only be needed to make notifies reliable while "connected" to the database and LISTENing (i.e., having registered the pipe/socket).

I agree that that would be very useful.

A couple of things:

- LISTEN/NOTIFY lacks authorization on the channel. Indeed, channels are not even CREATEd. This means you cannot trust NOTIFY payloads if users you don't trust have access to the database.

- NOTIFYs are lost if no client is LISTENing.

In practice this means that NOTIFYs are only good for waking clients that then have to check what's up with a query.

Re: The notifier pattern for applications that use Postgres

#44

Post by jjovan1 »

benesch wrote: post #40
jjovan1 wrote: post #34

Earlier quoted context omitted.

Hey Benesch, is Materialize used by TimescaleDB to create Materialized View? I noticed a similar approach.

Not to my knowledge. I believe TimescaleDB has their own incremental view maintenance engine.

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

Re: The notifier pattern for applications that use Postgres

#45

Post by benesch »

BeefySwain wrote: post #41
benesch wrote: post #39

Earlier quoted context omitted.

Those updates are not retroactive. They apply on a go forward basis. Each day's changes become Apache 2.0 licensed on that day four years in the future. For example, v0.28 was released on October 18, 2022, and becomes Apache 2.0 licensed four years after that date (i.e., 2.5 years from today), on October 18, 2026. [0]: https://github.com/MaterializeInc/materialize/blob/76cb6647d...

I love this concept. Did you all come up with this or is there prior art? Is there a name for this concept?

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 an existing open source project under the BSL can be a painful transition.

Re: The notifier pattern for applications that use Postgres

#46

Post by cryptonector »

koolba wrote: post #6

This post misses the most important part of LISTEN / NOTIFY: transactions The notify piece respects your current transactional state. So of you issue a notify within a transaction, it’s only delivered if the transaction commits. If the transaction rolls back, the notification is discarded. This leads to the common pattern of combining the notification with insertion into a work queue. That solves the “listener was no…

Also, because there are no authorization controls on who can `NOTIFY` on a given channel (indeed, channels aren't even `CREATE`d objects) you can't really rely on the notification payload, so you really do have to go query a work queue.

Re: The notifier pattern for applications that use Postgres

#47

Post by cryptonector »

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,…

One nice thing about `NOTIFY` is that the system is very fast and scales to many `LISTEN`ers that can all get notifications with very little latency. I.e., it's a C10K system.

Because there are no access controls on who can NOTIFY to what channel, you can't rely on the payload, so you really do have to look at a work queue. But if it's just one user, and all you're trying to do is broadcast date fast, then NOTIFY works great.

Re: The notifier pattern for applications that use Postgres

#48

Post by benesch »

jjovan1 wrote: post #44
benesch wrote: post #40

Earlier quoted context omitted.

Not to my knowledge. I believe TimescaleDB has their own incremental view maintenance engine.

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 representative of your workload. We offer a free seven day playground if you'd like to run such a benchmark: https://console.materialize.com/account/sign-up

We also have a community Slack where a number of Materialize employees hang out and answer questions: http://materialize.com/s/chat

Re: The notifier pattern for applications that use Postgres

#49

Post by BeefySwain »

benesch wrote: post #45
BeefySwain wrote: post #41

Earlier quoted context omitted.

I love this concept. Did you all come up with this or is there prior art? Is there a name for this concept?

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 :)
Post reply on HN