An application that I'm working on uses AFTER CREATE/UPDATE/DELETE triggers to create pg_notify notifications when certain actions occur within the system. Currently, we have a small Node.JS application that LISTENs for the events and then immediately turns around and posts them to an AWS SNS topic, which gets forwarded to our SQS event queue. From that queue, we trigger all sorts of things based on the event (emails, SMSs, lambdas, long running jobs, etc).
This architecture works well, but the Node.JS application that sits in between the PostgreSQL instance and the SNS topic seems a bit fragile. I can't really run two copies in two availability zones, because messages will be duplicated.
I'm looking for a better way to get these Postgres notifications into SQS. Are there any options out there for this? If Postgres Aurora has something, we might consider that.
Use your current strategy of a small application that LISTENs for events. Just introduce a deduplication step between that app and your event subscribers. This will allow you to run several instances of your app.
For example, you could use a FIFO SQS queue. These automatically drop duplicate messages. Since FIFO queues cannot subscribe to SNS, you'd need to put messages directly to the queue instead of through SNS.
Alternatively, you could use DynamoDB to store checksums of your recent messages and if your app encounters a duplicate, drop it manually (make sure to use conditional writes to prevent race conditions).