[PostgreSQL / SQL Server sources] Change Data Capture (CDC) implementation questions

[kev-datams]

Hello Airbyte team,

We consider using CDC to retrieve data from both PostgreSQL and SQLServer sources (with 3 days of log retention).

⚠️ As the first sync will be equivalent to a FULL SYNC, our main goal is to absolutely minimize the impact on production DB instances (CPU, RAM, locks, tempdb size, etc).

💡 As we have some huge tables to sync, we count on the Airbyte resumable full refresh capability to be able to:

• minimize the impact on production DB instances
• properly resume the sync in case of failure with no need to restart from scratch

---

❓ Regarding this feature, we have some questions:

1. do you confirm it:

• is available on both PostgreSQL and SQLServer ?
• will properly resume the sync in case of failure with no need to restart from scratch ?

2. how does resumable full refresh concretely operates (technical mechanisms / cursor usage / snapshot isolation / temp disk flush / ...) on:

• PostgreSQL ?
• SQLServer ?

3. considering a first sync lasting a long time (eg: 48 hours), what are the risks on production DB instances ? (CPU, RAM, locks, tempdb size, ...)

4. we observed the LSN commit behaviour parameter on PostgreSQL connector:

• does it mean While reading data value will:
  • regularly flush the tempdb ?
  • reduce the pressure on production DB instances ?
• it looks not available for SQLServer, does it mean it is:
  • internally automatically managed with While reading data behavior ? (if so, how ?)
  • internally automatically managed with After loading data in the destination behavior ? (if so, how ?)
  • other consideration ?

5. if any risk for production DB instance, do you have alternatives / tips / workarounds to suggest ? :ange:

---

I guess above questions are common to many people wishing to synchronize large volumes of data, and the answers might be of great interest to them ! 🙏

Thanks a lot for clarifications 🔥

[kev-datams]

Hello @marcosmarxm, discussion created as discussed, please could you tag people who may help ? 🙏
Thanks a lot 👍

FYI @FVidalCarneiro @juoll-data @ichrzanowska