Remove Query taking around 3 mins to delete 50000 records

We have a MongoDB collection with a size of 200 GB, and we need to delete records based on a specific condition using a query. The query we’re using looks like this:

“$and”: [
{
“user_id”: {
“$oid”: “64e87bfa2c7fb2a959e1ad4f”
}
},
{
“$or”: [
{
“createdAt”: {
“$lt”: {
“$date”: “2023-10-10T11:05:12.447Z”
}
}
},
{
“createdAt”: {
“$eq”: null
}
},
{
“createdAt”: {
“$exists”: false
}
}
]
}
]
We’ve also created a compound index on user_id and createdAt to improve the query’s performance. However, the deletion process is taking a significant amount of time, approximately 3 minutes to delete 50,000 records.

Is there a more efficient way to delete these records, or are there any optimizations we can make to speed up the deletion process? We’d like to reduce the time it takes to delete records from our collection.

Thanks in advance for any advice or suggestions!

The fact that these are bad or good numbers depends on a lot of things.

What is the mongod configuration? RAM, disks, …

What are other indexes?

What else do you have running on this system?

Please share the explain plan.

One way to reduce the load one the server but that could increase the running time is to do 3 deleteMany, one with createdAt:null, one with createdAt:$exist:false and one with the createAt:$lt. An $exist:false might involve a collection scan, which is probably very slow on a 200GB collection. Could you update your model to make sure createAt always exists at least with a null.

If this is a non-frequent use-case, it might be better to reduce the CPU spike rather than making the use-case faster, aka throttling.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.