Data Transmission Service:Synchronize or migrate MongoDB collections with TTL indexes

Background

When you migrate a MongoDB instance that contains collections with TTL indexes (for example, user sessions, logs, or cache data), you can use DTS for full and incremental synchronization. The goal is zero data loss with minimal service impact.

However, the TTL auto-deletion mechanism can conflict with DTS synchronization, causing task latency and data inconsistency:

• Redundant DELETEs reduce efficiency: When the source TTL index deletes expired data, it writes a DELETE record to the Oplog. DTS replays this DELETE on the destination. If the destination TTL index already deleted the same data, MongoDB returns an unexpected affected-row count, triggering exception handling and slowing the migration.

• Data inconsistency from asynchronous TTL deletion: TTL indexes do not delete data in real time. Expired data may still exist on the source while the destination has already deleted it, causing inconsistency.

  Example:

  The MongoDB Oplog or ChangeStream records only the updated fields for an UPDATE operation, not the full document. If an UPDATE cannot find the target data on the destination, DTS ignores the operation.

  Timing	Source instance	Destination instance
  1	Service inserts data
  2		DTS synchronizes the INSERT operation
  3	Data has expired but is not yet deleted by the TTL index
  4	Service updates the data (for example, updates the TTL index field to change the expiration time)
  5		TTL index deletes the data
  6		DTS synchronizes the UPDATE, but the data is not found. The operation is ignored.

  As a result, this document is missing from the destination MongoDB instance.

Solution

Temporarily set the destination TTL indexes to the maximum expiration value for the entire synchronization or migration (full and incremental stages). Restore the original TTL settings after you switch traffic to the new instance.

Procedure:

1. Back up TTL index settings: On the source database, run a script to identify all collections with TTL indexes and record their expiration values.

   Click to view the example script

   // Script: Find and print information about all TTL indexes
   const collections = db.getCollectionNames();
   let ttlIndexes = [];
   collections.forEach(collName => {
       const indexes = db.getCollection(collName).getIndexes();
       indexes.forEach(idx => {
           if (idx.hasOwnProperty('expireAfterSeconds')) {
               console.log(`Found TTL index -> Collection: ${collName}, Index name: ${idx.name}, Expires after: ${idx.expireAfterSeconds} seconds`);
               ttlIndexes.push({
                   collection: collName,
                   name: idx.name,
                   expireAfterSeconds: idx.expireAfterSeconds
               });
           }
       });
   });
   // Save the following JSON output to a file for later steps.
   printjson(ttlIndexes);

2. Disable TTL auto-deletion on the destination:

   1. Check whether the destination instance already contains the target collections and TTL indexes.

   2. If not, use schema migration to synchronize the collections first. Then connect to the destination database and set the expiration time to the maximum value to prevent auto-deletion during the process.

   3. If it does, connect to the destination database before starting the DTS task and set the TTL expiration to the maximum value to prevent auto-deletion during the process.

3. Run and monitor the DTS task: Start the full and incremental DTS synchronization task. With TTL auto-deletion disabled on the destination, DTS can replay all operations from the source without conflicts.

   1. Create and start the data synchronization or migration task in the DTS console.

   2. Monitor the following metrics during the process:

      • DTS task latency: Verify that incremental synchronization latency remains low on the task details page.

      • Destination storage usage: Because data on the destination will not expire during migration, storage usage grows continuously. Ensure sufficient storage space is available.

4. Restore TTL settings: After DTS synchronization is complete and traffic has switched to the new instance, restore the original TTL expiration values from your backup. A MongoDB background thread then cleans up expired documents that accumulated during migration.

Notes:

• During incremental synchronization or migration, DTS still replays DELETE operations generated by the source TTL index on expired data.

• With the destination TTL expiration increased, the destination stores data that has already expired on the source if latency occurs during synchronization/migration. Provision sufficient storage on the destination based on the source write volume.

• For collections with very short TTL values (temporary data or logs), avoid migrating historical data. In extreme cases, fully migrated data could expire on the destination almost immediately, leaving the collection empty. Consider a dual-write strategy or incremental-only migration instead.