MongoDB index grows on update

Question

We are using AWS DocumentDB v3.6.0. We want to use MongoDB TTL index functionality to remove items after a period of time. Items are frequently updated until a special event occurs. Therefore we need to update the ttl field often.

db.test.insertOne({_id: "12345",ttl: new Date()})
db.test.createIndex({ttl:-1},{expireAfterSeconds: 3600})

db.stats() yields the following expected result:

[
  {
    "avgObjSize": 51,
    "capped": false,
    "count": 1,
    "indexSizes": {
      "_id_": 16384,
      "ttl_-1": 16384
    },
    "nindexes": 2,
    "ns": "db.test",
    "ok": 1,
    "size": 51,
    "storageSize": 16384,
    "totalIndexSize": 32768
  }
]

However, if we update the ttl timestamp of our item, the ttl_-1 index keeps growing.

for (let i = 1; i < 1000; i++) {
    db.test.updateOne({ _id: "12345" }, { $set: { ttl: new Date() } });

}

db.stats() now yields:

[
  {
    "avgObjSize": 51,
    "capped": false,
    "count": 1,
    "indexSizes": {
      "_id_": 16384,
      "ttl_-1": 90112
    },
    "nindexes": 2,
    "ns": "db.test",
    "ok": 1,
    "size": 51,
    "storageSize": 65536,
    "totalIndexSize": 106496
  }
]

As you can see, the ttl_-1 index is larger than the _id_ index even though we still have only one item. Why?

score 0 · Answer 1 · answered Mar 22 '21 at 15:22

0

In-place data overwrites have various problems. For example, if you perform such a write and lose power, not only does the new data get lost, but you also end up trashing the data that was already in the database.

For this reason it is not surprising that any write would allocate new storage space.

1000 writes (90 KB of data?) isn't enough to conclude that the index is growing without bounds. Try a million, or however many are needed to get the index above, say, 64 MB.

answered Mar 22 '21 at 15:22

D. SM

13,584
3
12
21

Yeah, that example was only to show that it grows. We are already using this index in production with 50m rows. The `_id_` index is 4GB, and the `ttl_-1` index 22GB. – Baumjamin Mar 23 '21 at 08:41

score 0 · Answer 2 · answered Mar 31 '21 at 20:33

0

Have you tried dropping and recreating the index? In your example the _id_ index is insert only (never updated) and does not experience the churn that the ttl_-1 index does. Would be interesting to see the before and after in your simple example as well as full size DocumentDB database.

answered Mar 31 '21 at 20:33

tmcallaghan

1,292
2
10
20

Yes, we tried that. The recreated index is indeed smaller as before (we also did that with a copy of production db, so 50m+ rows). However, the index then grows again. – Baumjamin Apr 07 '21 at 10:26

MongoDB index grows on update

2 Answers2