Comment by pilif | Hacker News Reader

Comment by pilif | Hacker News Reader

A couple of notes: First, be mindful that postgres connections are comparably expensive memory-wise and a connection where you use LISTEN on can't be used for anything else. This might or might not be a problem for you (it won't if you have exactly one job using LISTEN, it will if every one of your frontend connection uses LISTEN).

Second, it's my experience that you will never be able to correctly deal with caches if you are trying to clean them. The reason is that when you rely on cleaning out caches, then, whenever you do a write to your database, you have to think what caches might be depending on your write and you'll have to remember to fire events accordingly.

It's much easier if you generate some kind of key or tag based on the information you want to cache and then just look up the value based on the key - like what you'd do with memcache. If your dependencies change, your cache key will change and you will fetch the data again.

This of course has the tendency of being more expensive when reading (because you have to calculate the cache key), but it also has the tendency of being correct with a much greater likelyhood - at least that's the experience I made so far. Of course, YMMV and the increased cost might deter you from going this way.

Why can't you use a connection that has issued LISTEN for anything else? In fact, I'm fairly sure you can use it normally.

We're using NOTIFY to broadcast database changes to services that should react to them and it's quite a nice way to separate concerns: process A just writes to the database, a trigger issues a NOTIFY, process B checks and notices there's been a change.

Edit: I see, the code in the post suggests that the connection needs to be stuck in a select() (I should know, I contributed a largre chunk of psycopg2's async code handling). You just need to use an asynchronous driver and you can happily LISTEN and do queries at the same time (of course you won't get notifies while a query is processing - the Postgres protocol has no concept of multiplexing).

We're using Twisted and happily mixing queries and NOTIFY.

It looks like you're right. This was/is a limitation of the node.js postgres adapter I was using.

We use node-postgres and this is not the case. I think node-postgres has one of the cleanest implementations of LISTEN due to nodejs event callbacks

whenever you do a write to your database, you have to think what caches might be depending on your write and you'll have to remember to fire events accordingly.

That's the beauty of this approach. You don't need to think about it when you write. You only need to think about it in the caching layer.

Say you want to cache the query:

    SELECT * FROM foo AS f INNER JOIN bar AS b ON b.foo_id = f.id WHERE f.id=12345;

The caching layer needs to `LISTEN foo_updates` and `LISTEN bar_updates`.

Anyone doing a write doesn't need to think about this at all if you set up database triggers to send the NOTIFY events.

All true stuff. I'd like to add though, time based expiry is in many ways the best option if you can get away with it. If you can say "this can be 5 minutes out of date" at a product level, a lot of cache complexity simply evaporates.

Even versioning data, as you mentioned, is tricky. Once you add in a few joins ensuring the version is always correct can be hard.

Reply on news.ycombinator.com