Postgres Notify for cache busting and more | Hacker News Reader

Postgres Notify for cache busting and more

chrisstucchio.com

76 pointsspindritf32 comments

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.

Reminds me of .NET's SqlCacheDependency - very useful for cache invalidation, it's been around for while.

See http://msdn.microsoft.com/en-us/library/ms178604.ASPX for details if you are interested.

As someone who is looking for better notification system from database, let me tell you that SqlCacheDependency doesn't cut the cake.

1. SqlCacheDependency uses polling. 2. There is another alternative called SqlDependency which doesn't use polling but it is severely restricted.

http://stackoverflow.com/questions/7588572/what-are-the-limi...

I have looked high and low and could not find any mechanism in SQL Server to send notifications based on the status of arbitrary objects like the original article describes. I would love to be proven wrong about this as I need this functionality bad for the current project I am working on.

Yeah, I saw that and got a good laugh because SQL Server has done that for nearly a decade.

listen/notify was available in postgres 7.1 (and may well have predated it): http://www.postgresql.org/docs/7.1/static/sql-listen.html

7.1 was released in 2001.

edit: digging further, I found this:

    Bug fixes:
        [...]
        * the LISTEN/NOTIFY asynchronous notification mechanism now work

that's in the change list for Postgres95 Beta 0.03, released in July 1995: http://www.postgresql.org/docs/9.3/static/release-0-03.html

This is pretty awesome. One of those things you can do when you own the entire stack.

.NET has the same feature for Oracle and several other databases

This approach works well when you want to cache data from an existing integration database and don't have the luxury of rearchitecting the whole thing.

At my previous job, we had a bunch of systems talking to a single database. Some were rails, some were not. We wanted to add memcached to the rails apps via cache-money[1]. That'll handle read-through/write-through caching for vanilla rails stuff, but we had to somehow invalidate the cache for updates that happen outside of ActiveRecord::Base#save (e.g. update_all, or a write from a non-rails system). In the end, we settled upon the same approach[2] as in the article.

The trigger-backed cache invalidator was also written in ruby, and knew from rails/cache-money what key(s) needed to be invalidated when it got a notification. IIRC you didn't need to do anything special when you added a new cache key (e.g. cache user lookups by username), since the invalidator would just know what to do.

[1] https://github.com/nkallen/cache-money

[2] This was actually on 8.4, so unfortunately there was no NOTIFY. Instead, a plpython trigger would send a message to invalidator (persistent tcp conn per pg process), but this had the unfortunate consequences of 1. firing before the commit and 2. firing even in the event of a rollback

> This was actually on 8.4, so unfortunately there was no NOTIFY.

There was actually, listen/notify was available back in 7.1[0][1], maybe earlier.

No payload until 9.0 though, that would usually be done by storing the payload in a dedicated table and querying that when receiving a notification.

[0] http://www.postgresql.org/docs/7.1/static/sql-listen.html

[1] http://www.postgresql.org/docs/7.1/static/sql-notify.html

edit: found the 6.4 docs, it's there: http://www.postgresql.org/docs/6.4/static/sql-listen.html

edit 2: digging in the release notes, LISTEN/NOTIFY was made to work in Postgres95 Beta 0.03: http://www.postgresql.org/docs/6.4/static/release13262.htm (and was apparently present in a non-working state in previous versions)

    while (true) {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT 1");
        rs.close();
        stmt.close();
        org.postgresql.PGNotification notifications[] = pgconn.getNotifications();
        handleNotifications(notifications);
        Thread.sleep(100);
    }

The problem with this approach[1] is there's a 100ms of latency between polling attempts (the sleep call).

LISTEN/NOTIFY is very useful, particularly the transactional nature and not requiring any additional tech stack (e.g. no need for a separate MQ server), but for frequent/high-value/lower latency signalling you're better off using something that doesn't require sleeping/polling.

[1]: That and the lack of exception handling or resource cleanup.

Unfortunately it isn't correct that you don't need a separate MQ. Postgres DB connections are not cheap - the best way to use this pattern is to have a single server listening to PG notifications and publish them to a real MQ.

Also the 100ms of latency is a limitation of JDBC, not of using Postgres. Note the complete lack of sleeping in the python sample code.

> Unfortunately it isn't correct that you don't need a separate MQ. Postgres DB connections are not cheap

That depends on your app size. If you're building something relatively small then having a few additional DB connections vs a dedicated MQ server can be worth it (it's really just extra shared memory for the connection). I do agree though that most folks are better off just using a real MQ server. For anything larger (both app size and app scale) it ends up being much better.

> ... the best way to use this pattern is to have a single server listening to PG notifications and publish them to a real MQ.

Another approach I've been looking at is creating a writable FDW[1] that bridges to an MQ system. That combined with a PG background worker[2] to listen for notifications gives you a transactional system that starts/stop with your database.

[1]: http://wiki.postgresql.org/wiki/Foreign_data_wrappers

[2]: http://www.postgresql.org/docs/9.3/static/bgworker.html

that sounds great. be sure to write about it when you (or someone else) implements it!

> Also the 100ms of latency is a limitation of JDBC

Yeah, JDBC has no provision for asynchronous notification. The alternative is to create/use libpq bindings directly.

> "What happens in this case is that the cache is not invalidated. Because the transaction had not yet committed, the old version of object 12345 was loaded. The cache will now remain out of date until the TTL (if any) has passed."

This seems contrived, does anybody actually do this ?

Why wouldn't the app layer re-warm the cache itself, seeing as how it has the object already in hand ?

Even if the app layer populates the cache, it shouldn't do so before the transaction is committed.

that might not be so easy. not every cache is something like memcached or redis. a caching web proxy (like varnish) can't be warmed that easily.

SQLAlchemy allows use of post-commit hooks, which achieves pretty much the same effect, save for the race conditions.

It is protected from the same race conditions that this apparently protects you from.

What with the silly CSS that won't let me browse the site in landscape mode on my phone? Sigh. Web designers.

FTA:

>I recommend RabbitMQ

I recommend anything but.

Explain, please. I'm considering using it.

When RabbitMQ breaks (and in my experience this happens), you get to ask yourself, "Which will take more time, switching queues or learning Erlang?" Usually the answer is "switching queues."

Operations and configuration-wise, it is _bonkers_, and massively overengineered.

Would you mind providing feedback to the RabbitMQ engineers? These kind of comments aren't terribly helpful to us, besides form an "I don't like it" feeling.

It would be nice to see what you consider to be "massively overengineered" as well.

Would you agree that there's a demand for a simple and reliable MQ? Possibly one that just supports Stomp, possibly written in Golang? That just works and does nothing fancy?

I've actually been using redis pubsub, which is fine for my needs and dead simple.

It depends on what you need and what you mean by simple and reliable.

RabbitMQ is used by the likes of Instagram, so not sure about what's your specific use case.

Reply on news.ycombinator.com