## Summary ## What In https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/25884 the need for a transaction "watchdog", essentially an enforcement mechanism to stop long-held transactions from occurring, was brought up. After an initial implementation in bash/cron to be deployed via chef, discussions turned to working this into a much more elegant and powerful solution. The current result of those efforts lives here: https://gitlab.com/gitlab-com/gl-infra/transaction_watchdog/. ## Why The reason long-held transactions are an issue is that PostgreSQL utilizes the MVCC (Multi-Version Concurrency Control) method for data management, and this means for every row in PostgreSQL there could be multiple copies of that row at any given point. The idea is that a transaction started at a given time will always have a consistent view of the data, regardless of the ensuing updates to rows it interacts with (it will see a "snapshot" of the rows at the time the transaction began). When clients are running read-only workload on replicas, the replicas feed back the oldest transaction id to the primary node (via "hot_standby_feedback" setting) and this tells the primary to delay row cleanup on any open transactions the replicas have running in effort to reduce recovery conflicts on the replicas (and thus, aborted transactions). This introduces the problem where a long-open transaction could cause the primary to get behind on vacuum/cleanup/deletions if a transaction on a replica is held open for an unreasonable amount of time (due to a bug, unaccounted for failure state, etc...). We need a mechanism to detect and cleanup such open transactions. ## The implementation This small go binary is written with the idea in mind that it can run either as a background process/daemon on the database hosts, or be run interactively for live troubleshooting purposes. The purpose of the binary is to connect to the local machine's postgresql instance and check for any transactions that have been held open (active or not) for an excessive amount of time. Initially, the code will simply log open transactions in effort to provide additional data. Once we have hit a threshold of comfort with the logging activity, we can enable the ability of the binary to stop long-open transactions. ## Weekly Status <!-- STATUS NOTE START --> ## Status 2025-08-05 <!-- Create a high level summary (optional) --> :tada: **achievements**: - Deployed chef code update to gitlab-patroni, enabling further testing of transaction_watchdog into gstg after successful tests in db-benchmarking. :issue-blocked: **blockers**: - :arrow_forward: **next**: - Enabling in gstg to report long-running transactions, baseline testing in gstg along with additional code improvements as issues are found. _Copied from https://gitlab.com/groups/gitlab-com/gl-infra/data-access/dbo/-/epics/10#note_2668395690_ <!-- STATUS NOTE END -->