Database Migration Debugging and Cross-Database Compatibility

8/12/2022

This post details the upgrade of the operating system on GitLab.com's Postgres database clusters from Ubuntu 16.04 to Ubuntu 20.04. The primary technical challenge addressed is the corruption of Postgres indexes caused by an updated glibc version in Ubuntu 18.10 and later. The chosen approach involves using streaming replication to new Ubuntu 20.04 standby clusters, followed by a planned downtime window to promote the new clusters, reindex affected indexes (specifically B-tree, GiST, and Hash, with a concurrent strategy for GIN indexes), and update DNS. Significant performance improvements were made to the `amcheck` function for faster identification of corrupt B-tree indexes and to the reindexing process itself, reducing the overall downtime required.

8/4/2022

This post details the initial design and planning phase for decomposing the GitLab backend database. It covers the motivation for splitting the monolithic Postgres database, the analysis of write traffic to identify CI tables as a primary candidate for extraction, the proof-of-concept process, and strategies for managing the 'moving target' of application evolution during the decomposition. It also highlights the development of tools like multiple databases documentation, cross-join detection, cross-database transaction detection, query analyzer metrics, and a Rubocop rule to ensure code compatibility. The implementation of loose foreign keys to handle inter-database relationships and the mirroring of `projects` and `namespaces` tables to the CI database are also discussed.

8/4/2022

This post details challenges encountered during the database decomposition migration, including issues with autovacuum locking, unexpected database connections from long-running SSH sessions, missed cron jobs, and Sentry server overload. It also discusses the impact of cascading replication on CI read-only traffic latency and the complexities of incrementally re-balancing PGBouncer connections without saturating the main Postgres database.

8/4/2022

This post details the final migration process for decomposing the GitLab backend database, specifically focusing on the separation of CI-related data. It contrasts a planned zero-downtime approach with the chosen two-hour downtime strategy, highlighting the advantages of a simpler rollback mechanism and the necessity of extensive rehearsals. The post presents the positive results of the migration, including decreased primary database CPU utilization, significant storage savings, reduced dead tuples and vacuuming saturation, and a substantial reduction in average query duration for Sidekiq PGBouncer query pools.

6/23/2022

This post addresses a critical external dependency on PostgreSQL versions, specifically highlighting a use-after-free bug in PostgreSQL 12 and 13 that can cause segmentation faults. It advises users of GitLab EE and CE using external PostgreSQL servers to upgrade to patched versions (12.7+ or 13.3+). Omnibus GitLab instances with bundled PostgreSQL are unaffected. The post links to a GitLab issue for further details.

6/2/2022

This post details the planned split of GitLab.com's single PostgreSQL database into two distinct clusters: 'main' for general features and 'ci' for Continuous Integration-related features. This functional decomposition aims to increase database capacity by approximately 2x, improve performance through independent tuning, and enhance stability by reducing database saturation. The post outlines the background, impact, preparation steps, and the planned execution process, including a scheduled downtime.

9/29/2021

This post details the investigation and resolution of database stalls on GitLab.com caused by PostgreSQL subtransactions (`SAVEPOINT` queries). It explains the symptoms observed (CI/CD runner errors, build backlogs, `LWLocks`, `SubtransControlLock` contention), the underlying mechanism of subtransactions and `SubtransControlLock`, and the debugging process using enhanced observability and application instrumentation. The solution involved identifying and eliminating all `SAVEPOINT` calls from the codebase to prevent `suboverflow` and improve replica performance.

6/1/2021

Introduced a new data migration framework for Elasticsearch indices, inspired by Rails DB migrations. This framework uses time-stamped migration files stored in Elasticsearch, automated background execution via a MigrationWorker, and supports states like 'completed', 'halted', and 'uncompleted'. It includes features like batched migrations (`batched!`), throttle delays (`throttle_delay`), pausing indexing (`pause_indexing!`), and space requirement checks (`space_requirements!`). This framework has been used to migrate issues, comments, and merge requests, improving performance.

1/7/2021

This post details the migration of GitLab.com from Azure to GCP, the upgrade of a major PostgreSQL cluster, and the migration of GitLab.com to Kubernetes. It also covers debugging a bug in the Docker client library, hunting down an NFS bug, debugging a scaling problem on GitLab.com, and fixing a Gitaly service lag. Additionally, it discusses using Prometheus for anomaly detection and alerting, the decision to build GitLab on Rails, the evolution of the GitLab Web IDE, the development of a VS Code extension, challenges with Elasticsearch integration, the origin of the Insights feature, and reimagining technical interviews. Finally, it touches on troubleshooting a Consul outage and escalating privileges in GCP.

9/11/2020

This post details the process and technical considerations for upgrading GitLab.com's main PostgreSQL cluster from version 9.6 to 11. It covers the planning, automation, testing (in isolated environments and staging), and execution of the upgrade using `pg_upgrade`. Key technical aspects include the use of Patroni for High Availability, Consul for leader election, Ansible for automation, and the rollback strategy involving maintaining a 9.6 cluster sample. It also highlights the benefits of PostgreSQL 11 features such as native table partitioning and logical replication, and the challenges of performing a fleet-wide upgrade with minimal downtime.

5/28/2019

This post details the successful upgrade of GitLab to Rails 5. The key technical contributions include: - Implementing a strategy to run the application with both Rails 4 and Rails 5 simultaneously using environment variables (`RAILS5`, `BUNDLE_GEMFILE`) and version-specific Gemfiles. - Developing a helper method `Gitlab.rails5?` to conditionally apply code changes for Rails 5 compatibility. - Documenting the phased upgrade process: enabling dual compatibility, fixing CI issues, manual testing, switching to Rails 5 by default, maintaining Rails 4 compatibility for a period, and finally dropping Rails 4 support. - Highlighting challenges encountered, such as incompatible changes in Active Record/Arel and the need to refactor monkey patches. - Describing the production release strategy, including running CI for both Rails versions during the development cycle and removing Rails 4 code after a successful release.

2/10/2017

This post details a major database outage on GitLab.com caused by accidental data removal from the primary database server. It outlines the incident's timeline, the root cause (an engineer errantly wiping the primary database directory while attempting to restore replication), and the subsequent data loss. The post also critically analyzes the broken recovery procedures, specifically highlighting failures in `pg_dump` backups due to version mismatches and lack of DMARC for cronjob error notifications, and the unsuitability of Azure disk snapshots and LVM snapshots for disaster recovery of the database. It concludes with a commitment to improving operations and recovery procedures to prevent future occurrences.

2/1/2017

This post details a severe database incident on GitLab.com where six hours of data (issues, merge requests, users, comments, snippets) were lost due to a combination of spam attacks, replication lag, and a critical human error during recovery attempts. It highlights the failure of multiple backup and replication techniques (LVM snapshots, regular backups, pg_dump, Azure disk snapshots, S3 backups) and the fragility of the replication procedure. The recovery involved restoring from a staging database backup. The incident underscores the urgent need for robust, reliable, and well-documented backup and replication strategies.

3/9/2015

This post details the process of migrating 9TB of GitLab.com data from dedicated hardware to an AWS data center. It describes the challenges of moving large datasets without downtime, the decision to use DRBD for continuous synchronization, and the complexities of setting up a three-way DRBD replication with IPIP tunnels and IPsec encryption to overcome AWS NAT limitations. It also touches on configuring DRBD for migration, managing LVM and DRBD resizing, and the initial steps of taking the service offline for the final sync.

12/30/2014

Introduced a fix for a MySQL-specific database migration failure where removing a column with a composite index caused MySQL to rebuild the index with only one column, leading to duplicate index errors. The solution involved adding a check to `index_exists?` before `remove_index` in the migration script.