

Postgres Notify for cache busting and more - spindritf
http://www.chrisstucchio.com/blog/2013/postgres_external_triggers.html

======
pilif
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.

~~~
wulczer
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.

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

~~~
troyk
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

------
polskibus
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](http://msdn.microsoft.com/en-
us/library/ms178604.ASPX) for details if you are interested.

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

~~~
masklinn
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](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](http://www.postgresql.org/docs/9.3/static/release-0-03.html)

------
jenseng
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](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

~~~
masklinn
> 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](http://www.postgresql.org/docs/7.1/static/sql-listen.html)

[1] [http://www.postgresql.org/docs/7.1/static/sql-
notify.html](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](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](http://www.postgresql.org/docs/6.4/static/release13262.htm)
(and was apparently present in a non-working state in previous versions)

------
sehrope

        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.

~~~
yummyfajitas
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.

~~~
sehrope
> 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](http://wiki.postgresql.org/wiki/Foreign_data_wrappers)

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

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

------
philsnow
> "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.

~~~
ibotty
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.

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

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

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

------
ris
FTA:

>I recommend RabbitMQ

I recommend anything but.

~~~
dscrd
Explain, please. I'm considering using it.

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

~~~
dscrd
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?

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

