*This page may contain information related to upcoming products, features and functionality. It is important to note that the information presented is for informational purposes only, so please do not rely on the information for purchasing or planning purposes. Just like with all projects, the items mentioned on the page are subject to change or delay, and the development, release, and timing of any products, features, or functionality remain at the sole discretion of GitLab Inc.* With the epic [Improve background migrations for primary key conversions](https://gitlab.com/groups/gitlab-org/-/epics/5654) we implemented a batched background migration process to streamline the PK migrations. The goal for this epic is to improve upon this functionality and make it generally available. ### Summary Background migrations are the vehicle for executing all large data updates in GitLab. Any operation that has to update more than a few thousand records has to be performed asynchronously through a background migration, with workers running in the background and executing the update in batches so that the performance of a GitLab instance is not affected. Our current way of performing such operations by scheduling background jobs in regular intervals is static and does not take into account the load of the database server when each job is executed. That means that we have to be very conservative when scheduling such migrations, taking into account the worst case scenario, which results in background migrations taking multiple times more to execute and finish than required. Even worse, in the rare scenario that there is an database incident, the primary database server is overloaded or its resources saturated, we can not respond in real time and the background migration jobs either fail or adversely affect the performance of the database server making things even worse. We have introduced the batched background migrations framework while [addressing the Primary Key overflow risk for tables with an integer PK](https://gitlab.com/groups/gitlab-org/-/epics/4785), in which we had to update more than 8.5 Billion records. It provides mechanisms to adapt in real time to the load of a Database Server, dynamically increasing or decreasing the chunk of data that we process every 2 minutes and allowing for a background migration to pause in case of an incident or automatically be retried if it fails. It also provides us with the ability to add in the future even more advanced stability enhancements like checking the load of the affected table(s) between executions, performing maintenance operations (like manual vacuuming) or constantly throttling the rate of updates for frequently accessed tables. ### Motivation - Gitlab availability and reliability - Perform large data migrations at GitLab.com scale without the risk of causing incidents. - The size of tables is increasing - we need to have a safe way to perform data modifications without compromising the performance of a GitLab instance. - Length of time to completion - we need to be able to ramp up migrations in low load time to speed up the time a migration needs to complete. There are cases when migrations can run more than 20x times faster than how we have to set them to run at the moment. ### Current problems 1. Existing way of performing background migrations by scheduling jobs in Sidekiq is static (batches set at schedule time) - We have to be very conservative when scheduling such migrations, taking into account the worst case scenario, which results in background migrations taking multiple times more to execute and finish than required. - In the case of a database incident, the primary database server is overloaded or its resources saturated, we can not respond in real time and the background migration jobs either fail or adversely affect the performance of the database server making things even worse. 2. In the existing way of performing background migrations, when one or multiple job fail, they have to be retried manually through a new migration that must be submitted by the developer. 3. Existing way of performing background migrations is very hard to debug, monitor and validate that all jobs have successfully completed. 4. We have had a few database related incidents (https://gitlab.com/gitlab-com/gl-infra/production/-/issues/5952#note_748517574, https://gitlab.com/gitlab-com/gl-infra/production/-/issues/4486#note_573068599), that were directly impacted by the rate of updates on specific tables or in which the rate of updates amplified the problem. ### Primary Success criteria We have a single overarching success goal, to make background migrations performant, reliable and less risky to cause incidents. - _(Related to problem \#1)_ We need a framework that **dynamically adapts in real time to changing conditions** on a Database server and which can **automatically halt execution** when resource saturation or other load related issues are identified. It should be able to **resume execution** once the Database server returns back to normal operation. - _(Related to problem \#2)_ We need a framework that can **self monitor**, **retry failed jobs** and change their parameters (batch, sub-batch, etc) if they keep on failing. - _(Related to problem \#2)_ A stable framework for background migrations should **never require manual innervation** under normal circumstances. In case of a bug or other edge cases it **should provide the means to manually fix, update the parameters and retry jobs**. - _(Related to problem \#3)_ We need a framework that can **provide monitoring capabilities** both at the command line and through the administration panels. Debugging and retrying such jobs should be provided through the admin panels as well. - _(Related to problem \#4)_ We need a framework that can **throttle the update rate** of background jobs based on records updated per time, taking into account overall dead tuple statistics for the table at hand, and monitor in real time for other critical queries, heap fetches and other variable parameters that can adversely affect the performance of the background migration and the Database server in general. - _(Related to problem \#4)_ We need a framework that **can allow additional maintenance decisions** based on the previous parameters, even **manually vacuum** the table if required, while taking into account the autovacuum settings for that table and - _(Related to problem \#4)_ We need a framework that can monitor active autovacuum processes and even **automatically pause execution of background jobs while autovacuum is in progress** (potentially important for large tables where autovacuum can take a while and we'd like to unblock it) ### Additional Success criteria - Multiple databases are supported - Easy to use migration helpers for executing batched background migrations are provided. - All migration helpers that perform operations in the background (e.g. related to partitioning, renaming/moving columns, etc) are rewritten using batched background migrations. - Guidelines are available for developers, with examples on how to efficiently implement background migrations. - Documentation is available for instance administrators, SRE and support engineers. - Errors are recorded and stored in a way that is easily accessible by developers, SREs and instance administrators. - (optional) Chatops provides integration with batched background migrations | Feature | Background Migration | Batched Background Migration | [General availability](https://gitlab.com/groups/gitlab-org/-/epics/7415) | | ------ | ------ | ------ | ------ | | Background Migration Tracking | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Documentation for Gitlab engineers | :white_check_mark: | :x: | :white_check_mark: | | Parallelization | :white_check_mark: | :x: | :x: | | Flexible job arguments | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Retries | :x: | :white_check_mark: | :white_check_mark: | | Initial delay | :white_check_mark:| :x: | :x: | | Error logs | :white_check_mark:| :x: | :white_check_mark: | | Admin Panel | :x:| :white_check_mark: | :white_check_mark: | | Helpers (TBA) | :white_check_mark:| :x: | :x: | | Support mutliple databases | :white_check_mark: (in-progress) | :x: | :white_check_mark: | | Remove old job information | :white_check_mark: | :x: | :white_check_mark: | Feature pairing discussion: https://gitlab.com/groups/gitlab-org/-/epics/7339 Data consistency discussion: https://gitlab.com/gitlab-org/gitlab/-/issues/344111 <!-- triage-serverless v3 PLEASE DO NOT REMOVE THIS SECTION --> *This page may contain information related to upcoming products, features and functionality. It is important to note that the information presented is for informational purposes only, so please do not rely on the information for purchasing or planning purposes. Just like with all projects, the items mentioned on the page are subject to change or delay, and the development, release, and timing of any products, features, or functionality remain at the sole discretion of GitLab Inc.* <!-- triage-serverless v3 PLEASE DO NOT REMOVE THIS SECTION -->