Zero-Downtime Database Migrations: Lessons from GitLab's PostgreSQL Architecture

Running a standard schema migration on a small staging database is trivial: you run ALTER TABLE users ADD COLUMN bio text; and it finishes in 10 milliseconds.

However, when your PostgreSQL database holds billions of rows, multi-terabyte tables, and thousands of concurrent transactions per second (as GitLab.com does), a naive migration can trigger table-wide ACCESS EXCLUSIVE locks. In seconds, transaction queues back up, connection pools exhaust, and your application goes down.

GitLab has pioneered some of the most rigorous, battle-tested patterns for zero-downtime database migrations in the open-source world.

In this guide, we will unpack GitLab’s database architecture rules and show you how to apply these enterprise patterns to your own production systems.

---

1. The PostgreSQL Locking Hierarchy: Understanding the Danger

Every schema alteration in PostgreSQL requests a lock. Understanding which locks conflict with standard reads (SELECT) and writes (INSERT, UPDATE, DELETE) is essential:

Operation	Lock Mode Acquired	Blocks Concurrent Reads?	Blocks Concurrent Writes?
SELECT	AccessShareLock	No	No
UPDATE / DELETE	RowExclusiveLock	No	No
CREATE INDEX CONCURRENTLY	ShareUpdateExclusiveLock	No	No
ALTER TABLE ... ADD COLUMN	AccessExclusiveLock	Yes	Yes
CREATE INDEX (Standard)	ShareLock	No	Yes

The Trap: Lock Queues

Even if an ALTER TABLE statement only takes 2 milliseconds to run, if it is forced to wait behind an active, long-running SELECT query, it waits in the lock queue.

While it waits, every single subsequent query behind it is blocked. Within 30 seconds, your entire application server runs out of database connections.

---

2. Rule 1: Always Use Low Lock Timeouts

In GitLab Core, migrations never wait indefinitely for locks. They configure strict lock timeouts and retry loops:

class AddStatusToDeployments < Gitlab::Database::Migration[2.2]
  # Limit waiting for the lock to 500ms
  SET_LOCK_TIMEOUT = 500

  def change
    # Set statement and lock timeouts for this transaction
    execute("SET LOCAL lock_timeout = '500ms';")
    execute("SET LOCAL statement_timeout = '5s';")

    add_column :deployments, :status, :smallint, default: 0, null: false
  end
end

If the migration cannot acquire the required lock within 500ms, it aborts cleanly, releasing the queue, and can be retried safely without taking down web traffic.

---

3. Rule 2: Never Create Indexes Synchronously

A standard CREATE INDEX locks the target table against writes (INSERT, UPDATE, DELETE) for the entire duration of the index build. On a 100-million-row table, this can lock your database for 30 minutes!

The GitLab Pattern: add_concurrent_index

GitLab provides helper methods that disable DDL transactions and invoke PostgreSQL’s CONCURRENTLY engine:

class AddIndexToIssuesTitle < Gitlab::Database::Migration[2.2]
  disable_ddl_transaction! # Required for concurrent indexing

  def up
    add_concurrent_index :issues, :title, name: 'index_issues_on_title'
  end

  def down
    remove_concurrent_index_by_name :issues, 'index_issues_on_title'
  end
end

CREATE INDEX CONCURRENTLY builds the index in the background using two table scans, allowing concurrent reads and writes to proceed uninhibited.

---

4. Rule 3: Safe Column Deletions (The 3-Phase Strategy)

Dropping a column directly from a live table (ALTER TABLE users DROP COLUMN phone;) instantly breaks existing application workers that are caching table columns in memory.

GitLab enforces a strict 3-phase deployment cycle across separate releases:

Release N (Phase 1):
  Ignore column phone in application models (ActiveRecord / ORM).
  New code no longer reads or writes the column.

Release N+1 (Phase 2):
  Mark column phone as ignored in DB schema.
  Remove column references from all background jobs.

Release N+2 (Phase 3):
  Safely execute: ALTER TABLE users DROP COLUMN phone;
  Zero application crashes.

In code, this is handled via ignore_column:

class User < ApplicationRecord
  # Inform the ORM to stop including this column in queries
  self.ignored_columns += %i[deprecated_token]
end

---

5. Rule 4: Batched Background Migrations (BBM)

What happens when you need to update 50,000,000 rows (e.g., populating a new UUID column)?

Running UPDATE users SET uuid = gen_random_uuid(); in a single transaction will blow up WAL logs, exhaust memory, and lock rows for hours.

GitLab solves this using Batched Background Migrations (BBM):

1. Break the table down into discrete chunks (e.g., batches of 5,000 IDs).
2. Execute the batch update asynchronously inside background worker jobs (Sidekiq).
3. Throttle execution based on database replication lag: if replicas fall behind by more than 8 seconds, the background migration pauses automatically!

class BackfillProjectTokens < Gitlab::Database::Migration[2.2]
  restrict_gitlab_migration gitlab_schema: :gitlab_main

  def up
    queue_batched_background_migration(
      job_class_name: 'BackfillProjectTokens',
      table_name: :projects,
      column_name: :id,
      job_arguments: [],
      batch_size: 5_000,
      sub_batch_size: 250,
      interval: 2.minutes
    )
  end
end

The migration runs smoothly over the course of several hours in the background while users experience 0.00ms of downtime or query slowdown.

---

6. Key Takeaways for High-Scale Applications

1. Guard with Lock Timeouts: Never allow a migration to queue indefinitely; fail fast if locks conflict.
2. Build Indexes Concurrently: Never run CREATE INDEX synchronously on live tables.
3. Phase Destructive Operations: Delete columns and rename tables across at least two consecutive deployment cycles.
4. Batch Data Updates: Throttle heavy data transformations against database replication lag.

---

Resources & Links

• GitLab Database Documentation: docs.gitlab.com/ee/development/database/
• GitLab Profile: gitlab.com/hrlpavan
• GitLab Contributor Profile: contributors.gitlab.com/users/hrlpavan
• GitHub: @hrlpavan

How does your team handle high-concurrency database migrations? Let’s talk database internals in the comments below!