What is performance impact of indexing UpdatedAt/ModifiedAt of frequently updated tables?

Question

I have read that indexing columns that are frequently updated can be bad for performance.

But I was wondering if this is case even with columns that are incremental in nature? For example, given a table which sees lots of updates, UpdatedAt column will get updated very frequently. But it would always update to newer values, which can be appended at end of the indexes. The major part of existing indexes will not see any updates. So for use-cases where there are queries using UpdatedAt column, wouldn't having indexes on this column be of least risk in most scenarios?

Answer 1

indexing columns that are frequently updated can be bad for performance

This is too broad of a statement to be meaningful. Indexed columns can certainly be good for performance when querying, regardless of how frequently they are updated.

It is true that there is an overhead of updating indexes whenever an indexed column in a table is updated. This overhead involves removing the old index entry and adding a new one, which might cause a page split, an addition of a new page, or a page merge (depending on the specific DBMS and storage engine).

It is also true that an index on only the UpdatedAt column (or, for that matter, any column with monotonously increasing values) will not cause page splits. On the other hand, such indexes have a problem of their own -- concurrent sessions will fight for access to the same, last page of the index, which often creates a latch hotspot and therefore hurts performance in its own way.

Finally, an index with UpdatedAt as the leading (or the only) column is unlikely to be very useful, and if it's not the leading column, the benefit of it having monotonously increasing values disappears.

Answer 2

Mustaccio makes all good points. The one other performance implication to consider too is if you're using a database system that stores the clustered index key in its nonclustered indexes.

If your UpdatedAt is part of the clustered index key, then a single update to that field will result in locking and update operations to occur in each subsequent nonclustered index on that table as well.

This means in a table with 1 clustered index and 4 nonclustered indexes, you'll incur 5 write operations to maintain the change of a single field in your table.