[destination-postgres] not finishing loading 30Gb from IBM-DB2

[mosonyi]

Connector Name

destination-postgres

Connector Version

2.0.0

What step the error happened?

During the sync

Relevant information

I’m trying to load a huge table with many attributes and records. Unfortunately, the calculation of _airbyte_meta column takes a lot of time. If I let run the query generated by Airbyte but without _airbyte_meta, the data load into Postgres DB is quick.

Facts

• Airbyte version: 1.3.1, runs in kubernetes
• I created a connection with Full-Sync on only 1 table.
• The source DB Table (IBM-DB2) downloaded size is around 30GB and contains 9.04million records and 27 columns.
• The destination DB is postgres 16.

Steps to reproduce

1. Download from source to airbyte's local object store (which is postgres as airbyte_internal.lz_ipv_rd_raw__stream_tablename) is successful.
2. Insert into destination (into postgres) step starts but it never ends. Airbyte sends the SQL Command to postgres, I can see it in the list of the running processes in Postgresql.

Workaround

If I delete 5 million records from airbyte_internal.lz_ipv_rd_raw__stream_tablename and start the sync-process again, it will be successful.
(I perform a kind of batched load in this case)

Our investigation

I get the SQL query from postgres log:

1. I tried to run it without modification -> No success, it is an infinite process, same as when I run on the airbyte's UI.
2. I edited the SQL query: If I remove the _airbyte_meta the query runs and the data loads.

If you need the original SQL I can send it to you in private, because of sensitive data.

Relevant log output

No relevant log output, while the process seems to be doing nothing.

Contribute

• Yes, I want to contribute

[marcosmarxm]

@mosonyi would be possible to upgrade the connector to the latest version? After 2.1.0 postgres has enabled resumable full refresh which allows to stop and restart from a failed sync.

[mosonyi]

@marcosmarxm I tried today with Postgres v2.4.1 as I found this as the latest version. Unfortunately no change.