Missing record from Postgres follower database

[remisalmon]

Environment

• Airbyte version: v0.35.35-alpha
• OS Version / Instance: AWS EC2 t2.xlarge
• Deployment: Docker
• Source Connector and version: Postgres 0.4.8
• Destination Connector and version: Snowflake 0.4.14
• Severity: Critical?
• Step where error happened: Sync job

Current Behavior

I have a Postgres to Snowflake sync setup with incremental deduped sync (cursor = updated_at timestamp) to run hourly and is sometime failing to sync all the records that were created at or around the timestamp stored in the cursor.

Ex for a sync with missing records:

• From the Airbyte log: Found matching cursor in state. Stream: AirbyteStreamNameNamespacePair{name='xxx', namespace='public'}. Cursor Field: updated_at Value: 2022-03-01T23:03:07.302674Z
• Postgres has 60 records where date_trunc(ms, updated_at::timestamp_ntz) = '2022-03-01T23:03:07.302'::timestamp_ntz
• After the sync has only 21 record where where date_trunc(ms, updated_at::timestamp_ntz) = '2022-03-01T23:03:07.302'::timestamp_ntz

Expected Behavior

All records should be synced to the destination.

Logs

Steps to Reproduce

1. Postgres to Snowflake sync setup with incremental deduped sync
2. Create record in Postgres with a timestamp equal (to the ms?) to the cursor in Airbyte
3. Check for missing records in Snowflake

Are you willing to submit a PR?

No.

[marcosmarxm]

Did you try to get records = or > to the cursor field timestamp?

[remisalmon]

@marcosmarxm unfortunately I do not have access to the Postgres source, only to a copy where the data synced through another ETL process and where updated_at is in the '2022-03-01T23:03:07.302' format. Airbyte has 1 record = to the cursor field timestamp which is expected.

This is a sync that gets ~50k new records/hour, other syncs to Postgres with less frequent updates don't seem to run into this issue.

There is a similar report here: https://airbytehq.slack.com/archives/C01MFR03D5W/p1645769563181569

[remisalmon]

I believe this is due to a race condition where this postgres instance is a follower db in Heroku (https://devcenter.heroku.com/articles/heroku-postgres-follower-databases) and is receiving records from its leader db while Airbyte is querying it.

I have another sync on that same postgres follower db using Stitch which is also showing missing records at around the time of sync.

@marcosmarxm a fix could be to have the option to move back the cursor of a few minutes/seconds at each sync when syncing with incremental deduping but it sounds a little hacky, feel free to close this issue if that's beyond Airbyte's scope.

[marcosmarxm]

I'll the issue open but change the title.

[felix-d]

We have the same problem with Postgres and we believe it's also due to races, which leads to missing rows in our destination datastore BigQuery. We use updatedAt for our cursor, but the current time is defined by transaction start time. If two concurrent transactions insert or update rows at the same time, there's a window for races and the more transactions vary in duration the more this window becomes larger. An option to start the cursor a bit earlier would also help us in this situation.