title: How to Process PostgreSQL Triggers in a Distributed Environment
body: <p>We're in the process of implementing PostgreSQL Triggers to monitor for inserts/updates/deletes on several tables so that another that app that is listening for these events can keep our relational database in sync with our full-text search database.</p>
  
  <p>Here's what the trigger function looks like:</p>
  
  <pre><code>CREATE FUNCTION notification() RETURNS trigger AS $$
  BEGIN
    PERFORM pg_notify('search', TG_TABLE_NAME || ',id,' || NEW.id);
    RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>And here's how we're adding the trigger to each table:</p>
  
  <pre><code>CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE or DELETE ON foo
  FOR EACH ROW EXECUTE PROCEDURE notification();
  </code></pre>
  
  <p>And here is a very basic example of how we would have a node app (worker) listening for these trigger events:</p>
  
  <pre><code>var pg  = require('pg');
  
  var connString = "postgres://user@localhost/foo_local";
  
  pg.connect(connString, function(err, client, done) {
  
    client.on('notification', function(msg) {
      //get the added / updated / deleted record
      //sync it with the search database
    });
  
    var query = client.query('LISTEN search');
  });
  </code></pre>
  
  <p>Here's my three part question:</p>
  
  <p><strong>Part 1</strong>
  Our app is load balanced across several instances. What happens when the node / worker app, which is also distributed, receives an event? Will all instances of the worker app that are listening receive the triggered event? </p>
  
  <p>If so, that's bad - we don't want all instances of the worker app to process every event because they'd all be doing the same work and that would negate the benefits of having multiple listeners to distribute the load. How do we mitigate this?</p>
  
  <p><strong>Part 2</strong>
  What happens if the worker receives a trigger event, but it is long running? Will PostgreSQL queue the events that have been triggered until the listeners receive them?</p>
  
  <p><strong>Part 3</strong>
  We've got about 5 tables that we want to fire triggers on INSERT / UPDATE / DELETE. We've got a lot of requests, so this would fire a lot of events in a short period of time. We need a worker to listen to these events and process the changed records so that it can send them along to the full-text search database. Is there a better way to architect this to handle the volume?</p>
  
  <p>The other solution our team is considering is abandoning SQL Triggers and just using a message queuing system to shove messages in a data store (SQS or Redis) and then just have workers pick off messages from the queue. We want to avoid this route if we can as it adds more architecture to our platform; however, we're prepared to do it if it's our only option.</p>
  
  <p>Your thoughts would be much appreciated.</p>