[Feature Request] - Improve Temporal History Retention and Cleanup for Large-Scale Deployments

[bengineerdavis]

Issue

Improve Temporal History Retention and Cleanup for Large-Scale Deployments

Description:

In large-scale Airbyte deployments with many frequent syncs, the Temporal "attempts" table grows significantly (>100's of GBs), leading to API call latency and request timeouts. Currently, there is no configurable mechanism to set a retention period or maximum table depth for the Temporal "attempts" table. This results in degraded performance over time and requires manual intervention by customers to prune the table.

Issue Encountered:

1. Table Growth: The Temporal "attempts" table accumulates data indefinitely, leading to excessive disk usage (e.g., 210 GB across 80,000+ rows for one customer).
2. Performance Impact: API calls to /web_backend/connections/list experience slow response times or timeouts due to large table sizes.
3. Customer Workaround: Customers are forced to manually prune the table to restore performance, which is not ideal for enterprise deployments.

Requested Solution:

1. Retention Period: Introduce a configurable retention period for the Temporal "attempts" table (e.g., via an environment variable or Helm values).
2. Automated Cleanup: Implement a cron job or similar mechanism to automatically prune old entries from the table based on the configured retention period.
   Optional Archiving: Provide an option for customers to export and compress older data for audit purposes before cleanup.
3. API Optimization: Ensure API calls to /web_backend/connections/list are optimized to handle large datasets efficiently, even if table pruning is delayed.

Related Work:

There is some parallel work that is also on-going, which focuses on improving API design and frontend-backend interactions for fetching connection information. While API improvements may mitigate latency issues, the underlying table growth problem remains unresolved without proper pruning mechanisms.

Customer Feedback:

One affected customer, reported:

"The table contained 210 GB worth of data, spread out over 80,000+ rows. After manually deleting most rows and keeping about a month's worth of history, API latency dropped back to normal levels, and system stability improved significantly."

Additional Considerations:

• Deployment Impact: Long-running environments with many connections and frequent syncs are most affected.
• Retention Policy: It may not make sense to retain Temporal run history for more than a few months. Audit logs may already cover long-term review needs.
• Disk Usage: Excessive disk usage due to table growth is undesirable and should be addressed alongside performance improvements.