AWS DMS Postgres to OpenSearch Deletes not applyed

It appears you're experiencing an issue with AWS Database Migration Service (DMS) where deletions are not being applied to your OpenSearch domain target, even though they are being detected in the table statistics.

This behavior is actually by design for certain target endpoints in AWS DMS, including OpenSearch (formerly known as Elasticsearch). When using OpenSearch as a target, AWS DMS is configured to only apply inserts and updates by default, while ignoring delete operations.

The reason for this is that OpenSearch is often used for search and analytics purposes, where retaining historical data can be beneficial. Deleting records from OpenSearch could potentially remove valuable information for search or analysis.

If you need to handle deletions in your OpenSearch target, you have a couple of options:

1. Use a transformation rule: You can create an AWS DMS transformation rule that flags delete operations during replication. This approach allows you to keep the record in the target (OpenSearch) even though it has been deleted in the source. You would add an extra column to indicate the operation type (insert, update, or delete) and then handle the "deleted" records in your application logic.

2. Implement a custom solution: You could create a separate process that periodically checks for deleted records in the source and then removes them from OpenSearch. This would require additional development work but would allow you to maintain consistency between your source and target.

It's important to note that these solutions may have performance implications and should be carefully considered based on your specific use case and requirements.

If you absolutely need delete operations to be applied directly in OpenSearch and the above solutions don't meet your needs, you might need to reconsider your architecture or explore alternative tools that support bi-directional replication with full CRUD operations for OpenSearch.
Sources
Filter delete operations with AWS DMS | AWS Database Blog
Monitoring AWS DMS tasks - AWS Database Migration Service