I am using Mongo's TTL indexes on user created posts. Each post has expiresAt field that is a date which the TTL index uses.

Admin's can "highlight" posts, hence making it so that post never expires.

I am not sure how to do this correctly and am considering these 2 methods

1. Setting expiresAt to some big number in the future i.e. 9999 years
2. Deleting or setting expiresAt field to "undefined"

What approach would be the best, ideally removing index on the document as well so it is not stored unnecessarily?

CodePudding user response：

As you suggest, there are multiple ways to approach this. In general, TTL indexes have the following behavior:

If the indexed field in a document is not a date or an array that holds one or more date values, the document will not expire.

If a document does not contain the indexed field, the document will not expire.

This behavior confirms that your second idea (unsetting expiresAt) will work. You additionally mention that the solution should "ideally remov[e the] index on the document as well so it is not stored unnecessarily?" Based on that, I would approach this by using an index that is both TTL and Partial. For example:

db.foo.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 3600, partialFilterExpression: { expiresAt: {$exists:true} } 
})

If we have the following three documents:

test> db.foo.find()
[
  { _id: 1, expiresAt: ISODate("2022-11-27T17:09:23.394Z") },
  { _id: 2, expiresAt: ISODate("2022-11-27T17:09:26.718Z") },
  { _id: 3 }
]

We can see that only the first two are captured in our index, meaning that the third document is not taking up space and also will not expire;

test> db.foo.find().hint({expiresAt:1}).explain("executionStats").executionStats.totalKeysExamined
2

If documents defaulted to having some highlighted field as false, then an alternative approach might be:

db.foo.createIndex(
  { expiresAt: 1 },
  { expireAfterSeconds: 3600, partialFilterExpression: { highlighted: false } 
})

The interesting thing about this approach is that it allows you to retain the original expiration value (in the original field) should it be needed for reference later for some reason:

test> db.foo.find()
[
  {
    _id: 1,
    expiresAt: ISODate("2022-11-27T17:09:23.394Z"),
    highlighted: false
  },
  {
    _id: 2,
    expiresAt: ISODate("2022-11-27T17:09:26.718Z"),
    highlighted: false
  },
  {
    _id: 3,
    expiresAt: ISODate("2022-11-27T17:13:25.929Z"),
    highlighted: true
  }
]
test> db.foo.find().hint({expiresAt:1}).explain("executionStats").executionStats.totalKeysExamined
2