Efficient Way to Determine Records Modified Since Point in Time

Question

Scenario

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

Issue

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?

Answer 1

I have not discounted [logical replication] entirely, but I do not know how (or if it is possible) to add a nonce or modified timestamp to the replicated data that would help me accomplish my use case.

Perhaps we envision the role of Postgres logical replication in this scenario differently. I see it being used at the tail end of the "data pipeline that is streaming to a Postgres cluster", after the changes have been made to the Postgres database, because that's what you ultimately need: replicate changes in the database to "other systems".

You could set up a logical decoder, such as pg_recvlogical, to capture, pre-process, and send the changes further. For example, the aforementioned pg_recvlogical, together with a plugin like wal2json, could produce a continuous stream of JSON objects that represent the changes to the Postgres database in real time, as they happen, instead of polling for the changes.

If for some reason you must use polling, you could save the stream of changes to a file and simply truncate the file once your polling process is done.

In either case there is no need to inject any extra metadata into the change stream.