Capture a time-ordered sequence of item-level modifications via events when using Amazon Aurora

Question

I'm designing a small platform based on a series of event-based micro-services. The persistence storage I'm targeting is (the managed) Amazon PostgreSQL (Amazon RDS for PostgreSQL) — although I can go with Amazon Aurora as well.

Basically I'm trying to avoid sending events programmatically within the system. Previously I've used Amazon SQS, Axon Framework, etc., to deliver the same functionality, but there were always issues within the application because of the inconsistencies between the database state and the underlying system publishing the messages. For instance, there were cases where database transactions succeeded, but failures happened on the producer of the events. Having multiple infrastructure pieces at that juncture, in my experience, makes the system more difficult to manage state.

Basically, if I can listen to some built-in stream that captures a time-ordered sequence of item-level modifications in the database I would accomplish the same and it would be more reliable. Problem is that I've been searching throughout the documentation but I can't find anything like it so far.

In the end, Amazon DynamoDB Streams delivers something similar to what I'm looking for, but I can't seem to find this in Amazon RDS for PostgreSQL or Amazon Aurora.

I'm open as well to different solutions I haven't considered.

I'm aware of the Transactional Outbox pattern, but I'm trying to use it as a last resort.

• J_H: Please expand on "always issues ... because of inconsistencies". I get it that you want to preserve HappensBefore relationships. What's an example of a bad sequence to avoid? Kafka has a good story on turning at-least-once delivery into exactly-once if you copy its identifiers to external storage. Also, in what ways is transactional outbox a sub-optimal fit for your use case?
• user175557: I expanded the question. Should cover what I was referring to. I didn't single out every issue I've faced but they could be resumed in why the Transactional Outbox exists. The reason I'm trying to use the Transactional Outbox as a last resort is because it would require a custom service polling a database table and processing the data. On the other hand, making use of the AWS "internals" is always more convenient and less error-prone — a Lamba function listening to a predefined stream is more simple, reliable, and scalable than a container continuously polling a data source.
• J_H: I quibble about "it would require a custom service polling a database table...". Primary mechanism would be "custom service listening for event messages" which will prompt it to issue a DB query, and then yes, the backup failure recovery mechanism would be polling the DB, or generating occasional heartbeat messages which is the moral equivalent, so we will eventually issue that query. Also, would you please share a diagram with us? I heard this sequence of events, but I bet you have an alternate scenario in mind.

Answer 1

To the best of my knowledge, Amazon RDS for PostgreSQL and Amazon Aurora don't have a built-in stream for capturing a time-ordered sequence of item-level modifications. However, you can use the following options to achieve similar functionality:

• Amazon Kinesis: You can setup a Kinesis stream and configure it to consume data from your database using AWS Data Streams, change data capture (CDC), or by setting up a database trigger to send changes to Kinesis.

• AWS Lambda: You can set up a Lambda function to listen to database changes and act accordingly. You can use the Transactional Outbox pattern as you mention to trigger the Lambda function from within the database transaction.

• Debezium: This platform allows you to stream changes from your database in real-time. It works with PostgreSQL and other databases.

• Pgoutput: This tool allows you to capture changes in a format that can be easily consumed by other systems.

• user175557: I read that it is possible to expose the PostgreSQL Write-Ahead Logging (WAL) file within AWS. This is initially what I'm going to target. The Kinesis route works with any built-in data stream only, right? I know there are plenty of solutions, like Debezium, but I'm constrained by the Platform Team and the allowed/provided services.
• Sebastian Sotto M.: Yes, using Kinesis to stream changes from the database requires a built-in data stream. If your team allows it though you can use AWS Lambda to process the changes streamed from the WAL. The advantage in this option is that you can implement complex processing logic and can handle any custom data transformation or enrichment you might need.