Why we spent the last month eliminating PostgreSQL subtransactions
Sep 29, 2021Grzegorz Bizon
Summary
This post details the debugging and resolution of performance issues caused by PostgreSQL subtransactions, specifically the `SAVEPOINT` SQL query, which was found to cause stalls on database replicas and impact CI/CD job processing. The team developed new instrumentation to track subtransactions and identified that the `SubtransControlLock` was the bottleneck. The fix involved eliminating all `SAVEPOINT` queries from the codebase.
More from the Database Migration Debugging and Cross-Database Compatibility trail
- Seamlessly migrate from Jira to GitLab with Jira2Lab at scale
- We are upgrading the operating system on our Postgres database clusters
- Decomposing the GitLab backend database, Part 1: Designing and planning
- GitLab database decomposition: Final migration and results
- Decomposing the GitLab backend database, Part 3: Challenges and surprises