Cannot make TTL work correctly

Massimiliano_Ricci1 · 2024-03-04T21:35:13.391Z

Hey everyone, been struggling with setting TTL for my time series collection the whole day, and can’t get what’s wrong after trying ( I believe ) everything.

I have a node.js app, which connects to a containerized - replica set - instance of MongoDB. It is a single node replica set in my local environment. Using mongoose, I create this model:

const mongoose = require('mongoose')
const { Schema } = mongoose

const measurementSchema = new Schema({
	moisture: Number,
	temperature: Number,
	createdAt: {
    type: Date,
    default: new Date()
  },
	metadata: {
		sensorId: String
	}
}, {
	collection: 'measurement',
	timeseries: {
		timeField: 'createdAt',
		metaField: 'metadata',
		granularity: 'minutes',
	},
  expireAfterSeconds: 60
})

module.exports = mongoose.model('measurement', measurementSchema)

Connecting to my instance and running db.runCommand({'listCollections':1}) shows promising results:

{
  name: 'measurement',
  type: 'timeseries',
  options: {
    expireAfterSeconds: Long('60'),
    timeseries: {
      timeField: 'createdAt',
      metaField: 'metadata',
      granularity: 'seconds',
      bucketMaxSpanSeconds: 3600
    }
  },
  info: { readOnly: false }
},
{
  name: 'system.buckets.measurement',
  type: 'collection',
  options: {
    validator: {
      '$jsonSchema': {
        bsonType: 'object',
        required: [Array],
        properties: [Object],
        additionalProperties: false
      }
    },
    clusteredIndex: true,
    expireAfterSeconds: Long('60'),
    timeseries: {
      timeField: 'createdAt',
      metaField: 'metadata',
      granularity: 'seconds',
      bucketMaxSpanSeconds: 3600
    }
  },
...

I then add a new document to the collection…

rs0 [primary] dev> db.measurement.find()
[
  {
    createdAt: ISODate('2024-03-04T20:53:14.000Z'),
    metadata: { sensorId: '1234567890' },
    _id: ObjectId('65e634be808c4c06e01f6c91'),
    moisture: 1000,
    temperature: 27.21,
    __v: 0
  }
]

But TTLMonitor doesn’t seem to pick up that it should delete the document. Now for the interesting part: When I create a document with createdAt: new Date() - 1000 * 60 * 60 * 24 TTL works correctly, and deletes the document.
Thought about some time difference issue, but it looks like that’s not the case to me:

rs0 [primary] dev> db.measurement.find()
[
  {
    createdAt: ISODate('2024-03-04T20:53:14.000Z'),
    metadata: { sensorId: '1234567890' },
    _id: ObjectId('65e634be808c4c06e01f6c91'),
    moisture: 1000,
    temperature: 27.21,
    __v: 0
  }
]
rs0 [primary] dev> db.serverStatus().localTime
ISODate('2024-03-04T21:19:46.747Z')

What I also tried:

Setting the index in a different way in the model (had to use a partialFilterExpression, since MongoServerError[InvalidOptions]: TTL indexes on time-series collections require a partialFilterExpression on the metaField):

measurementSchema.index( {'createdAt': 1 }, {
  partialFilterExpression: {
    'metadata.sensorId': { '$exists': true }
  },
  expireAfterSeconds: 60,
  name: 'ttlindex',
  background: true
})

This successfully created an index on the collection, which I could list with db.measurement.getIndexes() but didn’t solve my issue.
2. Tried reverting back to standalone mongo
3. Tried manually creating the index on my deployed development environment instance on Kubernetes, on Google Cloud. Same result.

Some more info:
Docker image: mongo:7.0.5-jammy
mongoose version: 8.0.4
mongodb driver version: 6.3.0

I feel like I’m missing something really trivial, but cannot figure out what. Sorry for the wall of text

SAMRAT_N_A · 2024-03-21T07:09:43.956Z

Hello @Massimiliano_Ricci1 .
Welcome to Mongodb community.

I suspect the issue you are having could be due to below reson.
Default createdAt value : In your Mongoose schema definition, you’re setting the default value for createdAt to new Date() , which means it will be set to the time when the schema is compiled, not the time when the document is created.

This could be the reason why the TTL index doesn’t work as expected. Try setting the default value using a function instead:

createdAt: {
    type: Date,
    default: Date.now
}