What are the reasons why cdc syncs won't ingest records except for occasional "burst" syncs that ingest millions of recrods in bulk that would take a few hours to run? This leads to a lot more retained wal

Thinking completed Based on the provided Airbyte knowledge sources, this behavior—where regular incremental CDC syncs ingest 0 (or very few) records, followed by occasional massive "burst" syncs ingesting millions of records over several hours—typically points to Postgres WAL recycling triggering unexpected full refresh resyncs, often compounded by low transaction activity or LSN acknowledgment issues. Here are the primary reasons and mechanics behind this pattern: 1. Invalidation of the CDC Position Causing an Automatic Full Resync When an incremental sync runs, Airbyte relies on the Write-Ahead Log (WAL) to read changes starting from its last saved Log Sequence Number (LSN). If the source database purges or archives WAL segments that Airbyte still needs, the connector loses its place [troubleshooting]. • The Burst Symptom: If your connection is configured with Invalid CDC Position Behavior set to

Re-sync Data (Automatic Recovery)

, Airbyte will automatically initiate a full snapshot resync without failing the job [cdc-best-practices]. This causes a massive "burst" sync where it re-snapshots and transfers all records across the tables (often millions of rows taking hours) [2675; cdc-best-practices]. • What you see in the logs: Saved offset is before Replication slot's confirmed_flush_lsn, Airbyte will trigger sync from scratch [2675; troubleshooting] 2. Low-Activity or Filtered Tables (No Heartbeats) Debezium (the engine Airbyte uses for CDC) requires continuous change events to advance the replication slot's

confirmed_flush_lsn

. • The Problem: If the tables being replicated have low transaction volume, or if updates occur on tables outside the publication/catalog, events are filtered out. Without committed events, no offset is acknowledged to Postgres [2675]. • Resulting WAL Bloat: Because the slot's

confirmed_flush_lsn

never advances, Postgres retains all WAL segments, leading to growing disk usage [2675; postgres-troubleshooting]. Eventually, Postgres may hit limits or purge logs, repeating the cycle of LSN loss and full resyncs [2675; troubleshooting]. • What you see in the logs: Received 10001 events which were all filtered out, so no offset could be committed. This prevents the replication slot from acknowledging the processed WAL offsets... [2675] 3. Sync Frequency Mismatched with WAL Retention If the sync frequency is too infrequent relative to database activity or WAL retention settings: • High-volume batch updates or low

wal_keep_size

can purge WAL segments before the next scheduled sync runs [troubleshooting]. • If WAL retention is shorter than your sync intervals, Airbyte is continually forced back into full snapshot mode rather than performing incremental reads [cdc-best-practices; troubleshooting]. 4. Known Bugs with LSN Advancement Older versions of the

source-postgres

connector suffered from bugs preventing the replication slot LSN from properly advancing: • Missing LSN Fallback: Following the initial snapshot, missing offset fields prevented

advanceReplicationSlot()

from advancing the slot, causing unbounded WAL growth [pull/79671]. • Race Condition: Closing the replication stream immediately after updating status discarded in-flight LSN updates before Postgres processed them [pull/79671]. • State vs. Confirmed Flush Mismatch: Bugs in connector versions (such as v3.6.x/v3.7.x) caused Airbyte to save an LSN behind the slot's

confirmed_flush_lsn

, throwing

Saved offset is before replication slot's confirmed lsn

errors [55846]. Recommended Solutions 1. Implement a CDC Heartbeat: Create an

airbyte_heartbeat

table, add it to your publication, and configure the Debezium heartbeat query (e.g.,

INSERT INTO airbyte_heartbeat (text) VALUES ('heartbeat')

) in the connector settings. This regularly generates dummy transactions so the replication slot can advance its LSN even during quiet periods [55846; postgres-troubleshooting]. 2. Change "Invalid CDC Position Behavior": Set this setting to Fail Sync instead of Re-sync Data. This prevents Airbyte from automatically running an expensive multi-hour full snapshot whenever an LSN issue occurs, allowing you to investigate the root cause first [cdc-best-practices]. 3. Align Sync Frequency & WAL Retention: ◦ Ensure your sync frequency is well within your WAL retention window (e.g., sync multiple times per day if retention is short, or maintain at least 3–7 days retention) [cdc-best-practices; cdc-best-practices]. ◦ Increase

wal_keep_size

on your Postgres server to prevent the database from aggressively purging WAL segments needed by Airbyte [troubleshooting]. 1. Upgrade Connector Versions: Ensure you are on the latest versions of the Postgres source connector, as several LSN flushing and offset-tracking issues have been addressed in recent releases [pull/79671; 55846]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai I don't think it matters, but my hearbeat query looks like this that's different than yours. Would this still work? INSERT INTO public.airbyte_heartbeat (id, timestamp, text) VALUES (1, CURRENT_TIMESTAMP, 'heartbeat') ON CONFLICT (id) DO UPDATE SET timestamp = EXCLUDED.timestamp, text = EXCLUDED.text; My sync would fail during low transaction volume hours. Maybe adding a high transaction volume table to the publication helps...

Thinking completed Yes, that query will still work. The purpose of the heartbeat query is simply to artificially produce a write event on a table that is tracked by your publication. Updating a single existing row via

ON CONFLICT ... DO UPDATE

generates a write transaction in PostgreSQL just as an

INSERT

does, allowing the connector to process the event, advance the Log Sequence Number (LSN), and acknowledge it to the replication slot [postgres-troubleshooting; 55846]. For this query to function properly with CDC, make sure that: 1. The

airbyte_heartbeat

table is included in your PostgreSQL publication (e.g.,

ALTER PUBLICATION <publicationName> ADD TABLE public.airbyte_heartbeat;

) [postgres-troubleshooting; 55846]. 2. The database user configured in Airbyte has permissions to write to that table (

INSERT

and

UPDATE

) [postgres-troubleshooting; 55846]. 3. The table has a valid replication identity (default with a primary key is standard) [cloud-sql-postgres]. Adding a high-transaction volume table to the publication If your sync is still failing during low-transaction hours despite the heartbeat configuration, adding a high-transaction table is indeed a documented alternative [postgres-troubleshooting]. According to the Airbyte documentation, the easiest read-only fix for WAL advancement issues during low volume is to add one or more tables with high transaction volumes to the Airbyte publication [postgres-troubleshooting]:

ALTER PUBLICATION <publicationName> ADD TABLE <high_volume_table>;

Key considerations for this approach: • You do not need to sync this table: You only need to add it to the PostgreSQL publication; you do not have to select or enable it in your Airbyte connection stream settings [postgres-troubleshooting]. • Advances the LSN: The frequent changes on this table ensure that the LSN continues moving forward so the sync does not fail from stagnation [postgres-troubleshooting]. • Disk consumption trade-off: The documentation cautions that adding a high-volume table to the publication can greatly increase WAL disk consumption on PostgreSQL [postgres-troubleshooting]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Is it that any table added to publication increases size on disk?

Thinking completed The knowledge sources do not explicitly state that adding any table to a publication automatically increases disk size by itself. However, based on the provided documents: 1. Transaction Volume Matters: Adding a table with high transaction volumes to the publication can "greatly increase disk consumption" because of the volume of WAL records generated and retained while waiting to be processed [advanced-wal-disk-consumption-and-heartbeat-action-query]. 2. Replication Identities: Tables using

REPLICA IDENTITY FULL

(used in rare cases for large field values or TOAST data) log more row data to the WAL than

DEFAULT

, which can also impact disk usage [cloud-sql-postgres]. 3. Small/Low-Activity Tables: Adding a small, low-write table (such as a heartbeat table) is specifically recommended as a way to avoid the large disk consumption caused by adding high-volume tables [advanced-wal-disk-consumption-and-heartbeat-action-query]. In short, simply adding a table to a publication does not inherently cause massive disk growth; the disk increase is tied to the frequency and volume of write transactions occurring on that table that must be logged and held in the WAL [advanced-wal-disk-consumption-and-heartbeat-action-query]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Is wal on replication slots, are they ones coming from the publication tables?

Thinking completed No, the Write-Ahead Log (WAL) retained on the database server is not restricted only to the tables in your publication. Based on the provided sources: • WAL contains changes across the whole database: The Postgres WAL logs changes occurring on the server, including "changes for tables outside the publication" [issue 55846]. • Replication slots hold back all WAL: A logical replication slot retains WAL segments on the server based on its acknowledged position (the Log Sequence Number, or LSN). If the replication slot does not acknowledge processed LSN offsets, it causes a "growing backlog of non-removeable WAL segments on the database server" regardless of which tables the changes belong to [forum post 2675]. • Filtering and backlog: When transactions happen on tables that are not part of the publication (or not included in the sync catalog), the connector filters out those events. Because these filtered events do not advance or commit an offset, the replication slot cannot acknowledge the processed WAL, preventing Postgres from clearing the retained WAL segments [forum post 2675; issue 55846]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Would having a full refresh table in the same connector help? When there are not many records to ingest

Thinking completed The knowledge sources do not explicitly mention whether configuring a stream with the "Full Refresh" sync mode in the same connector would help advance the replication slot or resolve WAL retention issues during low-volume periods. However, based on how CDC works in the provided documents, it is unlikely to help: • Full Refresh does not use the replication stream: Full refreshes retrieve data using standard

SELECT

queries rather than reading from the Write-Ahead Log (WAL) [tutorial cdc]. • WAL advancement relies on CDC events: The replication slot's Log Sequence Number (LSN) only advances when change events are processed from the WAL for tables included in the publication [troubleshooting; issue 2675]. A standard

SELECT

query on a Full Refresh table does not generate or process CDC WAL events. To keep the replication slot advancing during low-volume periods, the recommended approaches in the documentation are to: 1. Use an artificially generated heartbeat query on a dedicated heartbeat table that is added to the publication [troubleshooting; issue 55846]. 2. Add an existing table with high transaction volume directly to the PostgreSQL publication [troubleshooting]. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.