MongoDB Delete old data

Question

We want to clear our Mongo of old records (older than 90 days), we have a replica set with 3 nodes and we have 3.8 terabytes of data (in /var/lib/mongodb) on each node

We want to clear old records using this way

db.Clicks.deleteMany({ “callData.path”: “/info-dubai”, “callData.date”: { $lt: new Date(new Date().getTime() - (86400000 * 90)) } } );

db.Clicks.deleteMany({ “callData.path”: "/info-qatar, “callData.date”: { $lt: new Date(new Date().getTime() - (86400000 * 90)) } }) ;

And after completing these two requests, disk space is not freed up, and i have to run the command for compaction

db.runCommand( { compact: ‘Clicks’ } );

After compaction, the space is freed up, but the cleaning itself takes a very long time and loads I/O quite heavily, and compaction itself also affects performance, please tell me, maybe there is a way to do this more productively? faster and with less load

Answer 1

I would divide the clean-up process into 2 parts: the one-time bulk clean-up on a large amount of qualifying documents and ongoing maintenance of a relatively small amount of qualifying documents.

1. The one-time bulk clean-up on a large amount of qualifying documents

First of all, I would try to do the clean-up in an off-hour to minimize operational impact. Then, I would use an aggregation pipeline to mark the docs that you want to delete. I would set a shouldDelete flag for easy record picking and a deleteBatch to partition the documents evenly into different batches.

db.docs.aggregate([
  {
    "$match": {
      "$expr": {
        "$gt": [
          {
            "$dateDiff": {
              "startDate": "$callDataDate",
              "endDate": "$$NOW",
              "unit": "day"
            }
          },
          90
        ]
      }// ...your other criteria here
      
    }
  },
  {
    "$setWindowFields": {
      "sortBy": {
        "_id": 1
      },
      "output": {
        "deleteBatch": {
          "$documentNumber": {}
        }
      }
    }
  },
  {
    "$set": {
      "shouldDelete": true,
      "deleteBatch": {
        "$mod": [
          "$deleteBatch",
          10 //here I divided by 10 batches
        ]
      }
    }
  },
  {
    "$merge": {
      "into": "docs",
      "on": "_id"
    }
  }
])

Mongo Playground

From then, with the marked collection, you can easily do:

db.docs.deleteMany({"shouldDelete": true, "deleteBatch": <put batch number here>}

By executing deletion in batches, you can minimize the one shot work load and thus potential operational impact. You can potentially avoid wrangling with the compaction too.

2. Ongoing maintenance of a relatively small amount of qualifying documents

I would set up the TTL index for automatically record clean up. Please note that you should only set up the index after the bulk clean-up is done. As it is stated explicitly in the doc:

After you create a TTL index, it might have a very large number of qualifying documents to delete at once. This large workload might cause performance issues on the server. To avoid these issues, plan to create the index during off hours, or delete qualifying documents in batches before you create the index for future documents.