CI partition sync lock contention on `p_ci_builds`

Overview

GitLab creates the next CI partition (for example ci_builds_118) ahead of time, through the dynamic partition sync (Gitlab::Database::Partitioning.sync_partitions). For p_ci_builds and the tables with a foreign key to it, the ATTACH PARTITION needs a SHARE ROW EXCLUSIVE lock on p_ci_builds. That lock conflicts with every INSERT, UPDATE and DELETE on p_ci_builds, which is the busiest table on the CI database.

While an attach waits for the lock, every new writer on p_ci_builds queues behind it. Runner traffic (POST /api/:version/jobs/request, PUT /api/:version/jobs/:id, artifact uploads) and CI Sidekiq jobs stall, and the rails_primary_sql Apdex of patroni-ci drops. Each attempt gives up after at most 1 s, so a single attempt is short. The damage comes from many attempts in a row, and from several processes attempting at the same time.

This page covers how to recognise it, how to stop it, and how to get the partition created safely afterwards.

Incidents:

INC-15918 / production#23392: partition 118 could not attach, and the rails_primary_sql Apdex of patroni-ci fell to 98.80%.
INC-643: automatic CI partition creation deadlocked against the live workload.

Symptoms

rails_primary_sql Apdex alerts for patroni-ci, in short dips that come back, often during deployments.
On the primary, bursts of dozens to hundreds of backends waiting on a Lock / relation wait event, all blocked by one session running ALTER TABLE ... ATTACH PARTITION or LOCK TABLE p_ci_builds ....
pg_locks_count on patroni-ci shows sharerowexclusivelock and accessexclusivelock rising during each burst and returning to 0 between them. ShareRowExclusiveLock on p_ci_builds is the lock the writers queue behind. AccessExclusiveLock is taken on the brand-new partition tables while each attempt creates and attaches them. It blocks nothing on its own, but it is a reliable sign that an attempt is running.
In Kibana (Rails logs): Failed to create / detach partition(s) with AttemptsExhaustedError, for CI tables.

How the sync works

What one attempt does:

PartitionManager#create creates the new partition tables and attaches them in one transaction, inside WithPartitioningLockRetries.
That makes 20 attempts per table, in about 27 s. The lock_timeout is 0.1–1 s per attempt, with sleeps of 0.05–5 s in between. When all 20 fail, it raises AttemptsExhaustedError and moves on to the next table.
Ten tables in the CI sync have a foreign key to p_ci_builds. Adding that foreign key to the new partition needs the SHARE ROW EXCLUSIVE lock on p_ci_builds. The eleventh table is p_ci_builds itself.

What starts a sync:

db:migrate during every deployment, in the migrations pods.
Database::PartitionManagementWorker, every 6 hours.
Application boot, and anyone running the sync by hand, for example a kubectl exec into a toolbox pod.

These all call the same function. Before gitlab!267572, the only guard was a lease per table. Two processes syncing different CI tables therefore still competed for the same p_ci_builds lock. In INC-15918, three syncs overlapped and caused the worst dip.

Other work that queues on the same lock:

Database::FinalizePendingDetachPartitionsWorker runs LOCK TABLE p_ci_builds, p_ci_job_artifacts IN SHARE ROW EXCLUSIVE MODE, at :05 and :45 from 23:05 to 04:45 UTC. Its longer lock waits are gated by ci_finalize_pending_detach_partitions_daily (gitlab!267391). Lease guards for it are tracked in gitlab#637975.
Autovacuum on the ci_builds_1xx partitions takes SHARE UPDATE EXCLUSIVE, which conflicts with SHARE ROW EXCLUSIVE. PostgreSQL only cancels a blocking regular autovacuum once the waiter runs its deadlock check, after deadlock_timeout (5 s on our clusters). The partition manager gives up after at most 1 s, so autovacuum never steps aside for it, and an anti-wraparound autovacuum never steps aside at all.

Slow partition creation on its own is not new: in earlier months the attach failed for days without causing an incident. Concurrent syncs, the finalizer’s longer waits, and long autovacuums together are what turned it into one.

Diagnose

Run these on the patroni-ci primary. Find it with gitlab-patronictl list, then open sudo gitlab-psql on that node. All queries are read-only.

1. Watch for attach sessions and the queue behind them

SELECT now(),
       count(*) FILTER (WHERE query ILIKE '%ATTACH PARTITION%')        AS attach_sessions,
       count(*) FILTER (WHERE cardinality(pg_blocking_pids(pid)) > 0)  AS blocked_backends,
       count(*) FILTER (WHERE wait_event_type = 'Lock')                AS lock_waiters,
       string_agg(DISTINCT CASE WHEN query ILIKE '%ATTACH PARTITION%'
                  THEN pid || ' started ' || to_char(backend_start, 'HH24:MI:SS') || ' ' || substring(query from 'ALTER TABLE "?([a-z_]+)')
                  END, ', ')                                           AS attach_detail
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();
\watch 2

During a burst, attach_sessions is 1 or more and blocked_backends jumps from about 0 to tens or hundreds. attach_detail names the table being attached. More than one attach session at the same time means several syncs are running together.

2. See what the attach waits on, and what blocks it

SELECT a.pid, substring(a.query from 'ALTER TABLE "?([a-z_]+)') AS attaching,
       l.relation::regclass AS waiting_on, l.mode
FROM pg_stat_activity a JOIN pg_locks l ON l.pid = a.pid AND NOT l.granted
WHERE (a.query ILIKE '%ATTACH PARTITION%' OR a.query ILIKE 'LOCK TABLE%')
  AND a.pid <> pg_backend_pid();
\watch 1

You should see ShareRowExclusiveLock on p_ci_builds. To see which sessions it waits for, check pg_blocking_pids(<pid>) against pg_stat_activity, and look for autovacuum: entries in backend_type and query:

SELECT p.pid, now() - a.xact_start AS age, p.relid::regclass, p.phase, left(a.query, 80)
FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a USING (pid);

The Postgres log does not record these waits. log_lock_waits only logs waits longer than deadlock_timeout (5 s), and every attempt here gives up after 1 s or less.

3. Check how far the next partition got

SELECT id, status, current_from, current_until FROM ci_partitions ORDER BY id DESC LIMIT 3;

Status 2 (current) is the partition CI writes to now, and 0 (preparing) is the next one. The next partition only moves to 1 (ready) once every table has it (Ci::Partitions::SyncService), so CI keeps writing to the current partition until then.

List the tables still missing the next partition. Replace 117 and 118 with the current and next partition IDs:

SELECT p.relname AS parent_missing_next
FROM pg_partitioned_table pt JOIN pg_class p ON p.oid = pt.partrelid
WHERE p.relname LIKE 'p\_ci\_%'
  AND EXISTS     (SELECT 1 FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
                  WHERE i.inhparent = p.oid AND c.relname LIKE '%\_117')
  AND NOT EXISTS (SELECT 1 FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
                  WHERE i.inhparent = p.oid AND c.relname LIKE '%\_118')
ORDER BY 1;

If the list is p_ci_builds and the tables that reference it, this page applies. To confirm which tables reference p_ci_builds:

SELECT c.relname AS referencing_table, k.conname
FROM pg_constraint k JOIN pg_class c ON c.oid = k.conrelid
WHERE k.contype = 'f' AND k.conparentid = 0 AND k.confrelid = 'p_ci_builds'::regclass
ORDER BY 1;

4. Find what starts the syncs

client_addr in pg_stat_activity shows a PgBouncer or pod address, not the job. To find the source, search the Rails logs in Kibana for Syncing dynamic postgres partitions around each burst, and check the pod name. For kubectl exec sessions, the GKE audit logs (pubsub-gke-audit-inf-gprd) show who ran the exec and when.

Mitigate

Disable the partition sync. The partition_manager_sync_partitions ops flag makes sync_partitions return early (partitioning.rb), for deployments, the cron worker and manual runs alike:

/chatops gitlab run feature set partition_manager_sync_partitions false
/chatops gitlab run feature get partition_manager_sync_partitions

A sync that is already running is not stopped. It finishes its remaining attempts, at most about 27 s per table, and then exits. Watch query 1 until attach_sessions stays at 0 through the next deployment window.

If the bursts come from LOCK TABLE statements by the finalizer instead, also disable its flag:

/chatops gitlab run feature set ci_finalize_pending_detach_partitions_daily false

Do not terminate an anti-wraparound autovacuum (autovacuum: VACUUM ... (to prevent wraparound)) to make room for the attach. Transaction ID wraparound is a far worse outage. See check_wraparound.

What disabling the flag affects

The flag also stops drop_detached_partitions and finalize_pending_detach_partitions. It applies to every partitioned table on every database, not only CI.

CI writes are not affected. The next CI partition stays in preparing, and CI keeps writing to the current one. The current partition grows past its usual size until the next one is ready.
Time-based partitions have headroom. Monthly partitions are created 6 months ahead and daily partitions 28 days ahead. Turn the sync back on well within the 28 days.
Retention stops. Old partitions are not detached or dropped, so disk use grows. Tables that rotate to a new partition when the current one is done keep growing on their current partition.

Re-enable and create the partition

Do not re-enable the flag without a plan. The first sync retries the same attaches and can bring the bursts straight back.

Make sure only one process can sync each database at a time (gitlab!267572 must be deployed to gprd).
Keep ci_finalize_pending_detach_partitions_daily disabled until gitlab#637975 is deployed.
Pick a low-traffic window outside the finalizer schedule (23:05–04:45 UTC). Before you start, check query 2’s vacuum query: no autovacuum should be running on a ci_builds_1xx partition.
Tell the EOC, re-enable the flag, and watch query 1 on the primary:
Terminal window
```
/chatops gitlab run feature set partition_manager_sync_partitions true
```
If blocked_backends climbs into the tens and stays there for more than a few seconds, disable the flag again.
When query 3 returns no rows, every table has the next partition, and the next CI partition can become ready. Leave the flag on.