Snapshot Too Old Errors for Long-Running Operations

Issue:

Snapshot too old errors may occur during long-running index creation operations or any long DDL operations.

Cause:

Index creation depends on syscatalog history retention to preserve metadata versions during execution.
If the retention window is shorter than the index creation duration or DDL operation, the snapshot may expire. It should be higher than the expected maximum duration of any single transaction in your application.

Resolution

Set syscatalog history retention timestamp_syscatalog_history_retention_interval_sec to a value greater than the longest index creation duration or DDL duration. Always ensure syscatalog history retention exceeds the runtime of long-running DDL operations such as index creation.

The recommended default for timestamp_syscatalog_history_retention_interval_sec is 4 hours. Refer document here

This parameter can be updated from YugabyteDB Anywhere (YBA) by editing GFlags and must be applied to both Master and TServer nodes (YBA UI-> Actions -> Edit Gflag). Increase this value accordingly for environments with long-running DDL operations.

Related doc: 

https://docs.yugabyte.com/stable/manage/backup-restore/time-travel-query/#set-the-history-retention-interval

https://docs.yugabyte.com/stable/reference/configuration/all-flags-yb-tserver/#timestamp-syscatalog-history-retention-interval-sec

https://support.yugabyte.com/hc/en-us/articles/32442077259533-Resolving-Snapshot-Too-Old-Error-in-YugabyteDB

Reference: SUPPORT-870