I have a data pipeline that is streaming to a postgres cluster. I have no control over the streaming data source, but full ownership of the destination. I intend to have processes that poll the database for any pertinent data that has changed since the last run, and sync other systems (varying, not databases).
Unfortunately, the pipeline that writes these records does not provide an accurate timestamp for date modified, or any sort of nonce that could track what records have changed since the last polling process synced.
A few extra notes:
- Records soft delete
- The pipeline is replicating a data lake so there is a high volume of data and changes happening at all times
- It is critical that the polling sync processes do not miss any changes
What I have Tried
I have come up with a few approaches to possibly address this, but am not sure what the best approach is for this use case.
Approach 1: Add a
date_modified column to each of the destination tables and a trigger to update the timestamp whenever an update occurs. This seems lightweight and simple.
Approach 2: Logical replication was appealing but there did not seem to be a way to add any sort of metadata that would help (like time replicated).
Approach 3: Using an audit table and triggers that would capture and assign an event ID would work, but the trade off with performance is worrying, and I can't see a difference in using Approach 1 for this specific use case.
What I am Hoping to Have Answered
Based on the above, it seems like the answer is it simply use Approach 1. I can then track the last timestamp that was synchronized using the polling processes and simply pull records that have a later timestamp from the tables.
Are there downsides to Approach 1 that I am not seeing? Or, are there other, betters avenues to accomplish this?