Partial index on a datetime column with only the last 3 days

How can I create a partial (decreasing) index with the last 3 days over a datetime column? Must I to delete and recreate my index every day setting the new thershold condition? Is there another way? Thank you.

Hey @Alvaro_71689 I don’t understand what you’re trying to do. Can you elaborate?
In the meantime, see if TTL Indexes help.

There will be many ways but there is no MongoDB functionality that cover the requirement itself.
However, you do not need and you should not to create the index every day - rather schedule only setting flag on documents

db.dogs.createIndex(
{ my_datete_time:1 },
{ partialFilterExpression: { older_than_3_days:false }
)

older_than_3_days - and then insert with false, and schedule to set it to true after expiration.

Ok, I’m sorry my poor english. I will try to explain my environment: I have a collection with a timestamp column (datetime), on a standalone MongoDb vmachine). That virtual machine has only 8 GB Ram. The collection receives about 200 new documents every 20 seconds. My application can graph a subset of my collection with datetime on X-axis. The maximum graphed time is [now() - 3days … now()] (from 3 days ago to now). To minimize the ammount of ram consummed by my index, I would like to create a partial index that matches that condition (now()-3days to now). I want to maintain in memory that index with a maxium of 3 days back. If I delete the index and re-create it, I can apply the condition with

db.mycollection.dropIndex(“myTimestampCol_1_name”);
db.mycollection.createIndex({myTimestampCol: -1}, {partialFilterExpression: {myTimestampCol: {$gte: $substract: [$currentDate , 3600 x 24 x 1000] }} }); // I change the asterisk multiplier as a “x”
(I never tried it)

If it’s a good approax, How can I schedule that index recreation every day at 00:00? Is there a MS-SQLServerJob-like scheduler method?

Thank you.

Hi @Alvaro_71689,

@azor.haf clearly understood your request. So like he said, it currently doesn’t exist. Partial indexes can’t use dynamic date values.

In addition to the alternatives already mentioned, I can think of a few other options to consider:

  • How about using a separate collection for your graph and use a TTL Index to delete records older than 3 days? And all you do is insert data into this collection and the TTL Index will do the rest of keeping this collection small. Plus you won’t need a partial index.
  • If each document you insert are similar in size, you could consider using a separate Capped Collection for your graph. Calculate the size of 3 days worth of data and set the Capped Collection to that size (plus a bit more as a buffer). Plus you won’t need a partial index.
  • Using @azor.haf’s method, all you do is call your server’s scheduler to set the boolean field accordingly. You get the benefit of using the same collection.
  • I think MongoDB Ops Manager does scheduling but it comes at a price and (I believe) is only available for the Enterprise version of MongoDB. Studio 3T also does scheduling.
1 Like