Hi! I’ve just started experimenting with Airbyte. It’s a powerful tool and I want to adopt it for production use for my organization. A couple of questions I have: • Can Airbyte handle big load of data migration ~1TB (from Heroku Postgres to AWS Postgres), and what’s the best way to optimize the migration?  For context, we cannot have downtime for the source and need to move a lot of data over and perform custom dbt transformation after loading the data to the destination. Is this sync feasible and how long does it usually take? If so, what are the machine requirements (for both the Airbyte host and the destination Postgres) to accomplish this as fast as possible?  • How the sync handles schema changes/updates after the initial full refresh?  If we changes the source schema or the transformation logic, will the destination be automatically updated? The next follow up is more a dbt question - but if I change the transformation logic and run full refresh on a big table (~300GB), how long does this usually take and will the table be locked during the refresh? Performance and availability are important for my use case. Would love to get thoughts and recommendations on these. Thanks in advance!

• Can Airbyte handle big load of data migration ~1TB (from Heroku Postgres to AWS Postgres), and what’s the best way to optimize the migration? 

Currently I’m afraid the Postgres to Postgres connection won’t handle this load well. If the 1TB data is all getting synced in one connection, I think it will probably take a few days, and also likely to get stuck along the way. If the 1TB data is distributed across multiple schemas and tables, and multiple connections are set up to sync a subset of them, it may work. However, I don’t think the performance will match your requirement. Our initial benchmark results showed that it can take ~2 hours at least to sync 10GB data. So unfortunately it is pretty much infeasible to transfer 1TB data reliably at this moment.

Are there any alternative recommendations you've found to work? Curious as this is something we're thinking about at my org as well.

Hi @Gerardo Santacruz, the workaround Liren suggests is to create multiple connections on the same source and add a subset of the table to each connection, this can improve parallelism a bit.

Gotcha, thanks for the info. Do you think it’s possible to use

pg_dump

for the data migration/full refresh, and configure airbyte to do incremental refresh only on new data? From the doc it seems like the first sync is always a full refresh, if thats configurable it’d work too

Do you think it’s possible to use 

pg_dump

 for the data migration/full refresh, and configure airbyte to do incremental refresh only on new data?

Yes, this is something we have thought about, but have not had the time to try yet.

Using

pg_dump

will require you to manually set the state of your connection to the latest cursor value you loaded with

pg_dump

. This is done by running some SQL queries against Airbyte database's

state

table. Am I right @Liren Tu (Airbyte)?

Correct. This is probably not something trivial to do now.

How complicated do you think it’ll be? If its just sql queries can we just have a script that do it?