Broken/corrupt SharePoint list column indexes across multiple tenant

What type of issue is this?

Question

What SharePoint development model, framework, SDK or API is this about?

SharePoint CSOM

Target SharePoint environment

SharePoint Online

What browser(s) / client(s) have you tested

• 💥 Internet Explorer
• 💥 Microsoft Edge
• 💥 Google Chrome
• 💥 FireFox
• 💥 Safari
• mobile (iOS/iPadOS)
• mobile (Android)
• not applicable
• other (enter in the "Additional environment details" area below)

Additional environment details

No response

Issue description

As part of the software we create, we use SharePoint Online lists for storing data on the customer Microsoft 365 tenant. Some of them are exceeding the 5000 item threshold already for a long time. Back in the days we added column indexes to work around possible issues with these large lists abd ti be still able to query data by using those indexed columns, as long as the results are below the list view treshold.

This has worked perfectly fine for multiple years on all the tenants we connected to in the past, because we're respecting the known rules for these large lists. Now since a couple of weeks some tenants are starting to show failures in these lists. The failures we see:

• Duplicate values can be added without any warning for indexed columns, even though unique values are enforced for these columns.
• UI Filtering or searching by the indexed columns does not return (all) results. Only recent items are returned.
• CSOM and REST queries filtering on Identifier fail to find (all) existing items. Only recent items are returned

It looks like the indexes are reset on a certain point in time as new items added are indexed and returned but older items seem to not be part of the index.

The only solution we currently see is removing the column index from the list, wait untill it's fully removed (can take up to 5 minutes, depending the list item count), and re-add the index. After these steps it's fully working again and we haven't seen it happen again on these 'repaired' lists. The biggest disadvantage is that indexes for columns which enforce unique values cannot be removed. Then you first have to disable the unique value enforcement. But this setting can only be re-enabled if the list contains less than the 5000 listitems. Our only solution to this is to temporarily recycle items from this list untill there are less than 5000 items, then fix the index and restore all items again.

Is this a known/by design issue? As this has never occured during the past 10+ years, it looks like something has recently changed or reset in this area.

The issue is also mentioned here by my colleague