0

Assumming we have 3 columns which are relevant for partion : key1 , key2 and lastupdateddate.

when we delete a lot of records from collection (~billion of records) , the index {key1:1,key2:1,lastupdateddate:1} is rebuilding constantly and consumes huge amount of cpu and disk.

We are considering to partition the index. , e.g create

{key1: 1, key2: 1 }, { partialFilterExpression: { lastupdateddate: { $lte: new Date("2024-07-01T00:00:00Z")$gt: new Date("2024-06-01T00:00:00Z") } } }

{key1: 1, key2: 1 }, { partialFilterExpression: { lastupdateddate: { $lte: new Date("2024-06-01T00:00:00Z")$gt: new Date("2024-05-01T00:00:00Z") } } }

and so on.

Would this partial indexes be sufficient to query for the data based on key1 and key2?

Improve this question
6
  • 1
    The index shouldn't rebuild just because you deleted some records. What triggered it to rebuild?
    – Joe
    Commented Aug 17 at 3:09
  • What is your delete pattern? For such amount of data, it could be useful to create (for example) one partition per day. When data is not needed anymore, then drop the entire collection. This will be much faster. Did you consider TTL Index?
    – Wernfried Domscheit
    Commented Aug 17 at 15:37
  • @WernfriedDomscheit the problem is that we need to delete records that has not been updated for some period and not that was created at some time
    – Yulian Oifa
    Commented Aug 18 at 5:25
  • @Joe of course they should...any insertion ,update or deletion of record changes the data which in turn changes indexes based on that data...
    – Yulian Oifa
    Commented Aug 18 at 5:26
  • Add a field which shows the last update time and use TTL index on that.
    – Wernfried Domscheit
    Commented Aug 18 at 5:37

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.