Getting PostgreSQL notifications into an AWS SQS Queue

Question

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.

Justin Howard · Accepted Answer · 2019-01-03 17:19:03Z

4

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).

edited Jan 3, 2019 at 17:19

answered Jan 3, 2019 at 17:11

Justin Howard

5,6421 gold badge25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sflogen Over a year ago

This is an interesting idea - I hadn't thought about writing straight into the the SQS queue so that I could use a FIFO queue. Thanks!

sflogen · Accepted Answer · 2018-10-17 17:36:12Z

Some options I've found:

Continue with the current method

I could keep the current small application that's redirecting events from my PostgreSQL RDS and dumping them into SNS->SQS. I can deploy it in a 1 region/max 1/min 1 auto-scaling group to make sure there is not more than copy running at a time.

Ditch my RDS and use a self hosted database

I could ditch RDS and run PostgreSQL on an EC2 instance and then use PL/Python along with the AWS-SDK to make calls to SNS instead of using pg_notify. I don't like this idea, because I lose the ease of use that comes with RDS.

For now, I'll be sticking with the current method, unless someone has some other ideas that I could explore. I'm sure there will be more options in the future (like when Aurora PostgreSQL adds support for calling Lambdas, like the Aurora MySQL has).

Collectives™ on Stack Overflow

Getting PostgreSQL notifications into an AWS SQS Queue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related