6 ms·

Postgres Notify for cache busting and more

76 points by spindritf 13y ago

pilif 13y ago
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.
- yummyfajitas 13y ago
  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.
- andrewvc 13y ago
  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.
- wulczer 13y ago
  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 13y ago
    It looks like you're right. This was/is a limitation of the node.js postgres adapter I was using.
    - troyk 13y ago
      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 13y ago
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 13y ago
  Yeah, I saw that and got a good laugh because SQL Server has done that for nearly a decade.
  - deleted 13y ago
    [deleted]
  - masklinn 13y ago
    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
- yummyfajitas 13y ago
  This is pretty awesome. One of those things you can do when you own the entire stack.
  - keithwarren 13y ago
    .NET has the same feature for Oracle and several other databases
- solutionyogi 13y ago
  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-limitations-of-sqldependency 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.
sehrope 13y ago
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 13y ago
  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.
  - masklinn 13y ago
    > 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.
  - sehrope 13y ago
    > 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
lucian1900 13y ago
SQLAlchemy allows use of post-commit hooks, which achieves pretty much the same effect, save for the race conditions.
- chrisrhoden 13y ago
  It is protected from the same race conditions that this apparently protects you from.
deleted 13y ago
[deleted]
grandinj 13y ago
What with the silly CSS that won't let me browse the site in landscape mode on my phone? Sigh. Web designers.
jenseng 13y ago
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 13y ago
  > 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)
ris 13y ago
FTA: >I recommend RabbitMQ I recommend anything but.
- dscrd 13y ago
  Explain, please. I'm considering using it.
  - gfodor 13y ago
    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."
  - ris 13y ago
    Operations and configuration-wise, it is _bonkers_, and massively overengineered.
    - dscrd 13y ago
      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 13y ago
        I've actually been using redis pubsub, which is fine for my needs and dead simple.
      - old_sound 13y ago
        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.
    - old_sound 13y ago
      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.
philsnow 13y ago
> "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 13y ago
  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.

76 points by spindritf 13y ago

pilif 13y ago

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.

yummyfajitas 13y ago
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.
andrewvc 13y ago
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.
wulczer 13y ago
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 13y ago
  It looks like you're right. This was/is a limitation of the node.js postgres adapter I was using.
  - troyk 13y ago
    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 13y ago

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 13y ago
Yeah, I saw that and got a good laugh because SQL Server has done that for nearly a decade.
- deleted 13y ago
  [deleted]
- masklinn 13y ago
  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
yummyfajitas 13y ago
This is pretty awesome. One of those things you can do when you own the entire stack.
- keithwarren 13y ago
  .NET has the same feature for Oracle and several other databases
solutionyogi 13y ago
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-limitations-of-sqldependency 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.

sehrope 13y ago

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 13y ago
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.
- masklinn 13y ago
  > 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.
- sehrope 13y ago
  > 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

lucian1900 13y ago

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

chrisrhoden 13y ago
It is protected from the same race conditions that this apparently protects you from.

deleted 13y ago

[deleted]

grandinj 13y ago

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

jenseng 13y ago

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 13y ago
> 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)

ris 13y ago

FTA: >I recommend RabbitMQ I recommend anything but.

dscrd 13y ago
Explain, please. I'm considering using it.
- gfodor 13y ago
  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."
- ris 13y ago
  Operations and configuration-wise, it is _bonkers_, and massively overengineered.
  - dscrd 13y ago
    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 13y ago
      I've actually been using redis pubsub, which is fine for my needs and dead simple.
    - old_sound 13y ago
      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.
  - old_sound 13y ago
    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.

philsnow 13y ago

> "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 13y ago
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.