what is the best approach for huge initial syncs? it seems that airbyte source for postgres for example has a single read thread, and for huge tables it may take days to sync, while the timeout is 24 hours

This is a known challenge with Airbyte's database source connectors. Here's what the knowledge sources suggest: Approaches for Large Initial Syncs 1. Split into Multiple Connections (Parallelization) The most commonly recommended workaround is to create one connection per table/stream rather than syncing everything in a single connection. This allows multiple tables to sync in parallel simultaneously. [Sync performance] [Memory issues] 2. Adjust the Initial Load Timeout (CDC) If you're using CDC, the Initial Load Timeout parameter controls the maximum duration for the snapshot phase. For very large databases (>50 GB), it's recommended to set this to 24 hours (the maximum allowed value). [CDC best practices] 3. Use CDC for Incremental Syncs After Initial Load For large Postgres tables, using CDC is recommended as it offers the least latency for high-volume data sync after the initial load is complete. The Postgres source connector also supports checkpointing (via CTID markers), meaning if a sync fails mid-way, it can resume from the last known checkpoint rather than starting over. [Low-latency stack] 4. Increase Job Parallelism (Kubernetes) If you're on Kubernetes, tweaking the Kubernetes configuration to increase job parallelism may help with overall throughput, though this primarily helps when running multiple jobs concurrently rather than speeding up a single table read. [MSSQL slow CDC] Important Caveats • The single-threaded read bottleneck on JDBC-based connectors (Postgres, MySQL, Snowflake, etc.) is a known limitation. The

fetchSize

parameter is dynamically adjusted, but this alone does not produce order-of-magnitude improvements. [Sync performance] [Postgres performance] • Increasing container memory (

JOB_MAIN_CONTAINER_MEMORY_REQUEST

) does not directly improve sync speed. [Postgres performance] • For tables with billions of rows and limited CDC log retention (e.g., 3 days of binlogs), there is currently no fully supported native solution to skip the initial load and start CDC from a specific point, though this has been requested as a feature. [MySQL huge tables] In summary, the most practical approach today is to split large syncs into multiple connections per table and leverage CDC with checkpointing to avoid re-syncing data from scratch in case of failures. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

and what about MongoDB? does it suffer from the same limitation? @kapa.ai

MongoDB's situation is somewhat similar but has its own specific characteristics: MongoDB Large Initial Sync Considerations Checkpointing & Chunking The MongoDB V2 connector does support checkpointing and chunking of data reads, meaning a failed sync can resume from a last known point rather than starting over — this applies to both CDC and Full Refresh modes. [Mongo DB] Initial Load Timeout Like Postgres CDC, MongoDB also has an Initial Load Timeout parameter (default: 8 hours, valid range: 4–24 hours). For large collections, you can increase this up to 24 hours. [MongoDB config] Known Timeout Issues with Large Collections There is a documented case of a 45 million row collection timing out with a

MongoExecutionTimeoutException

, while a 14 million row collection synced successfully. This suggests MongoDB can indeed struggle with very large collections during initial sync. [MongoDB timeout] CDC-Specific Issue: Incremental Sync Stuck at 101 Records A known bug exists where CDC incremental syncs repeatedly load only 101 records due to Debezium timing out on subsequent batches (the timeout is halved after the first batch). The fix is to increase

initial_waiting_seconds

(e.g., from 300 to 1200 seconds). [GitHub issue] Oplog Retention Risk For very large initial syncs, the import duration might exceed the Oplog retention period, which would invalidate the CDC resume token and require a full re-import. It's recommended to configure the Oplog to retain at least 24 hours (ideally a week) of changes. [MongoDB troubleshooting] 16MB Document Size Limit During CDC syncs, large documents can trigger a

BSONObjectTooLarge

error due to MongoDB's 16MB BSON document limit. Switching to Full Refresh or Lookup mode can help. [MongoDB troubleshooting] In summary, MongoDB shares the timeout and large-collection challenges, but also has additional CDC-specific risks around Oplog retention and document size limits. The practical mitigations are: increase

initial_load_timeout_hours

, increase

initial_waiting_seconds

, and ensure your Oplog is sized appropriately. 👍 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 this makes airbyte a very limited solution for real production systems

That's a fair concern to raise. Based on the knowledge sources available to me, I can confirm that large initial sync performance is a known limitation that has been acknowledged by the Airbyte team. [Parallel source processing] However, I'd be cautious about drawing a broad conclusion that Airbyte is unsuitable for production systems, as the knowledge sources don't provide enough information to make that sweeping assessment. What the sources do confirm is: • The single-threaded read bottleneck on large tables is a known issue being tracked, and performance improvements have been on the roadmap. [Parallel source processing] • Practical workarounds exist (splitting connections per table, tuning timeouts, CDC with checkpointing, Oplog sizing for MongoDB), which many production users do employ. • The limitation is most acute for very large tables (1B+ rows) or collections with strict log retention windows — not necessarily typical for all production workloads. Whether Airbyte is suitable for your specific production system depends heavily on your data volumes, sync frequency requirements, and tolerance for the workarounds needed. For use cases involving extremely large tables with tight log retention, the knowledge sources do suggest this is a genuine gap. [MySQL huge tables] [MSSQL slow CDC] If you have a specific use case in mind, I'm happy to help assess what options are available within Airbyte's current capabilities. 👍 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 with mongodb, if I split each collection to a separate connection, and I have a big oplog gap to cover, each connection will separately query the huge oplog gap

You've identified a real and important trade-off. Based on the knowledge sources, I can confirm this concern is valid but I want to be precise about what the sources actually say: What the sources confirm: • MongoDB CDC uses the Replica Set Oplog (via change streams) for incremental syncs. [MongoDB troubleshooting] • There is a documented case (GitHub issue #70222) where two connections using the exact same cluster were running in parallel, and the one syncing the largest database consistently failed with "Saved offset is not valid" errors — while the smaller one succeeded. The reporter specifically noted this led them to believe the issue was related to the size/load of the sync itself, not the oplog on the cluster side. [Saved offset issue] • The recommendation to split connections per table for Postgres CDC explicitly suggests using different CDC slots per connection to avoid contention. [Postgres parallelization] What the sources don't explicitly address: The knowledge sources do not directly discuss whether multiple MongoDB connections sharing the same cluster independently traverse the same oplog gap, or whether there is any shared cursor/state optimization between them. So I cannot confirm or deny the exact mechanics of how oplog traversal is duplicated across connections. Your concern is logically sound — if each connection independently opens a change stream cursor and needs to catch up on the same oplog window, it would multiply the load on the oplog. But I cannot state this with certainty based solely on the available knowledge sources. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

if each connection manages it's own watermarks and runs on its own schedule, what other option there is? @kapa.ai

Based on the knowledge sources available to me, the options are limited. Here's what the sources explicitly mention: Workarounds for Avoiding Full Initial Syncs 1. Manually set the connection state via API — One user attempted to pre-seed the connection state to a specific cursor date to skip the initial full load. However, this did not work — Airbyte still ran a full refresh from scratch on the first sync. [Partial data fetching] 2. Create views with filtered data — The official Airbyte support response to the above was to create database views containing only the subset of data you want (e.g., last 1 month), and sync those views instead of the full collection. This sidesteps the large initial load problem entirely. [Partial data fetching] 3. Load data manually, use Airbyte only for incremental — One forum post discussed loading the initial data via a DB dump directly into the destination, then using Airbyte only for incremental updates. The response was that there is no out-of-box solution for this, but a workaround of loading into a separate table and using dbt to merge was suggested. [Alternate initial loading] To be honest, the knowledge sources don't provide a clean, fully supported solution to your scenario. The view-based approach is the most explicitly endorsed workaround, but it has obvious limitations for MongoDB (views don't reduce oplog traversal). Beyond that, the sources don't offer additional options I can cite with confidence. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.