I tried to use Airbyte for CDC pipelines from a Postgres source and it started well with smaller tables, but ran out to some WAL size problems when I needed to add a few bigger tables (~3GB/~34M rows). It looked like Airbyte wouldn't read the WAL until the first sync of this new table ended, resulting on the WAL to increase a lot if this initial sync would take longer than 1 hour or so. Is there a way to have a better control of that operation in terms of WAL size specially during full-refresh syncs? Even if it means somehow not doing this first sync but rather only ingesting the new WAL entries.

Airbyte uses logical replication of the Postgres write-ahead log (WAL) to incrementally capture deletes using a replication plugin. Normally under the CDC mode, the Postgres source will first run a full refresh sync to read the snapshot of all the existing data, and all subsequent runs will only be incremental syncs reading from the WAL. However, occasionally, you may see full refresh syncs after the initial run. This can occur when there are lots of database updates resulting in more WAL files than allowed in the

pg_wal

directory, Postgres will purge or archive the WAL files. To prevent this scenario, you can: - Sync the data source more frequently. The downside is that more computation resources will be consumed, leading to a higher Airbyte bill. - Set a higher

wal_keep_size

. If no unit is provided, it is in megabytes, and the default is

0

. The downside of this approach is that more disk space will be needed. You can see detailed documentation [here](https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-WAL-KEEP-SIZE). Another scenario is when the Postgres connector successfully reads the WAL and acknowledges it to Postgres, but the destination connector fails to consume the data, the Postgres connector will try to read the same WAL again, which may have been removed by Postgres, since the WAL record is already acknowledged. This scenario is rare, because it can happen, and currently there is no way to prevent it. The correct behavior is to perform a full refresh. In some cases, users have reported that adjusting the buffer parameter in the destination (e.g., Snowflake) to a lower value (e.g., 50) can help manage the ingestion of WAL and prevent it from becoming too large. However, this may not be applicable to all destinations and scenarios. For more details, you can refer to the [Airbyte documentation](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting) and [Airbyte forum](https://discuss.airbyte.io/t/3263) for similar issues and solutions.

@Akash Kulkarni do you have any input to help here?

For incremental syncs, Airbyte first snapshots the data associated with a table and then reads from the WAL logs. There isn’t currently a way to read from the WAL before the sync of this new table. How much is your WAL building up, if the corresponding tables are just 3GB? Out of curiosity, do you have any full refresh streams included along with the incremental streams?

I was dealing with a ~20 tables on this replication and the WAL would build up to ~200GB if untouched for an hour or so - which was more or less the time to fully refresh this new table. I didn't have any full refresh streams, only incrementals.