Data Transmission Service:Best practices for synchronizing or migrating MongoDB collections with TTL indexes

Scenario description

During business growth or an architecture upgrade, you may need to migrate a MongoDB instance to a new one. This instance might contain collections that heavily use TTL indexes for data such as user sessions, logs, and temporary cached data. You can use DTS to perform a full and incremental data synchronization. The primary goal is to ensure a smooth and efficient migration with no data loss and minimal impact on your services.

However, the automatic deletion mechanism of TTL indexes can conflict with the DTS data synchronization mechanism. This conflict can lead to task latency and data inconsistency. This occurs for the following reasons:

• Missed DELETE operations during incremental writes reduce efficiency: When the TTL index on the source instance deletes expired data, it generates a DELETE record in the Oplog. DTS then synchronizes this DELETE operation. If the TTL index on the destination instance has already deleted the same data, the DELETE operation from DTS will not find the data to delete. The MongoDB engine then returns an unexpected number of affected rows. This triggers an exception handling process and reduces migration efficiency.

• Data inconsistency caused by asynchronous deletion of expired data: A TTL index does not delete data in real time. Expired data might still exist on the source instance when it has already been deleted on the destination instance. This causes data inconsistency.

  Example:

  The MongoDB Oplog or ChangeStream records only the updated fields for an UPDATE operation. It does not record the full document before and after the update. Therefore, if an UPDATE operation 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.

Best practices

To avoid issues caused by the TTL index on the destination instance, you can temporarily disable its automatic deletion feature for the entire DTS synchronization or migration process. This process includes both the full and incremental stages. After you switch your services to the new instance, you can restore the original TTL settings.

Follow these steps:

1. Prepare for the synchronization or migration: On the source database, run a script to identify all collections with TTL indexes and back up their expiration settings.

   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. Check and modify the destination configuration:

   1. First, check whether the destination instance already contains the collections and TTL indexes to be synchronized or migrated.

   2. If it does not, first use schema migration to synchronize the collections to the destination. Then, connect to the destination database and change the expiration time to the maximum value allowed by the database. This prevents automatic deletion during the synchronization or migration.

   3. If it does, connect to the destination database before you start the DTS task. Change the expiration time of the identified TTL indexes to the maximum value allowed by the database. This prevents automatic deletion during the synchronization or migration.

3. Run and monitor the DTS task: Configure and start the full and incremental DTS synchronization task. Continuously monitor key metrics to ensure that the process is stable and healthy. Because the TTL index on the destination instance is effectively disabled, DTS can synchronize all insert, update, and delete operations from the source without conflicts. This prevents efficiency drops and the risk of data inconsistency.

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

   2. During the process, focus on monitoring the following metrics:

      • DTS task latency: Check the task details page in the DTS console. Ensure that the incremental synchronization latency remains low.

      • Destination instance storage usage: Check the storage usage in the destination MongoDB instance. Because data on the destination instance will not expire, its storage usage will continuously increase. Ensure that you have sufficient storage space.

4. Restore settings after migration: After the DTS synchronization is complete and you have switched your service traffic to the new instance, restore the TTL indexes on the destination database. Use the configuration that you backed up before the migration to restore the original expiration times. After you restore the settings, a MongoDB background thread begins to clean up the expired documents that accumulated during the migration process.

Notes:

• During an incremental synchronization or migration, DTS synchronizes the delete operations that are performed by the source TTL index on expired data.

• After you increase the expiration time for the TTL index on the destination, the destination instance will store data that has already expired on the source if latency occurs during the full or incremental synchronization/migration. Therefore, you must provision enough storage space for the destination MongoDB instance based on the write volume of the source instance.

• If the objects that you are synchronizing have a TTL index with a short expiration time, such as in temporary data or log scenarios, you should not migrate the historical data. In extreme cases, data from a full migration could expire on the destination almost immediately, which would leave the destination collection empty. Instead, you can consider a dual-write strategy for your services or perform only an incremental migration.