PostgreSQL Event Publication System

To maintain a cache of the action counters either by day or by messageid, we could write a trigger. This implements event-driven processing but kills our concurrency and scalability properties.

Trigger for notification

It’s possible for our trigger to notify an external client. This client must be a daemon program, which uses listen to register our messages. Each time a notification is sent, the daemon program processes it as necessary, possibly updating our twcache.counters table. As we have a single daemon program listening to notifications and updating the cache, we now bypass the concurrency issues.

Implement the trigger

Before implementing the client application, we can implement the trigger for notification and use psql as a testing client: