I have a use case in which we use AWS DMS to stream data to our data lakes (S3), from time to time the replication lag gets out of control, leading to over 500Gb in replication slot lag.
It was also observed that when it happens, the wal_sender process is causing an overhead of thousands of write IOps in the RDS instance.
Also, the "Oldest Replication Slot" is increasing, and upon checking the pg_replication_stats view I can see that, it is indeed showing a backend_xmin held to a very old "age". The fact that the replication slot is not advancing is also causing a lot of "VaccumDelays" in the RDS instance.
I figured I would start the troubleshooting with the basics:
1 - The config for max_wal_senders is set doubled than the number max_replication_slots. Check
2 - Networking latency is very low on source and target about 3 milliseconds. Check
3 - No resource contention in the DMS replication instances: Lots of cpu, free memory and free storage. Check
4 - RDS logs show no issues in regards to logical replication. Check
5 - Data is being persisted in S3. Check
What am I missing?
What are the steps for finding the root cause of PostgreSQL high replication lag on async streaming logical replication (FYI I am using test_decoding as the decoder)?
The only reason I can think of is that the DMS client is NOT sending the signal to advance the replication slot after decoding the message and persisting it in the target S3; which then causes the replication slot to be stuck on the same LSN.
I would love to not depend on the AWS support for finding the root cause of this issue, because the AWS DMS support team has proven very inefficient on assessing issues and providing useful responses when requested.
Thanks for your help in advance.
