PostgreSQL index rebuild lock timeout after Kubernetes pod restart

Problem Description

During rolling restarts or horizontal pod autoscaling of a PostgreSQL StatefulSet, repeated REINDEX CONCURRENTLY operations abort with lock timeouts or out‑of‑memory errors. The symptoms observed in production are:

• Log entries such as:

2024-07-12 14:03:21.123 UTC [12345] LOG:  index build (concurrently) aborted: could not obtain exclusive lock on relation "orders_pkey"
2024-07-12 14:03:21.124 UTC [12345] ERROR:  lock timeout while attempting to REINDEX CONCURRENTLY
2024-07-12 14:03:21.125 UTC [12345] ERROR:  out of memory while allocating memory for index build (maintenance_work_mem exceeded)

• Read‑only mode is temporarily enforced on affected tables, causing client queries to fail.
• Pod restarts trigger a surge in pg_stat_progress_create_index entries, with each step stuck at phase = 'building' for >30 seconds.
• Metrics show CPU throttling and memory OOM kills during the index rebuild window.

Root Cause Analysis

The failure originates from the interaction of three Kubernetes‑level factors with PostgreSQL’s index rebuild mechanics:

1. Lock acquisition semantics of REINDEX CONCURRENTLY – The command first takes a SHARE UPDATE EXCLUSIVE lock, then later attempts an ACCESS EXCLUSIVE lock to replace the old index. If another session holds a conflicting lock (e.g., a long‑running transaction that started before the pod restart), PostgreSQL will wait up to lock_timeout before aborting. This behavior is documented in the PostgreSQL REINDEX CONCURRENTLY reference.
2. Resource constraints during pod startup – When a pod restarts, the container’s maintenance_work_mem defaults to a low value (often 64 MiB) while the pod’s CPU limit may be hit by the intensive index build. The maintenance_work_mem setting controls the amount of memory PostgreSQL can use for sorting and hashing during index creation. If the process exceeds this limit, the server emits “out of memory” errors and may be OOM‑killed by the kubelet, as seen in the fintech incident report.
3. Persistent Volume I/O latency – During pod initialization the PVC may be in a cold state, causing checkpoint and WAL replay to stall. The checkpoint documentation notes that high I/O latency can extend the time needed to acquire the final exclusive lock, pushing the operation beyond the configured lock_timeout.

Combined, these factors cause the index rebuild to exceed the lock timeout and/or exhaust memory, leading to the observed aborts.

Investigation and Debugging

The following step‑by‑step process reproduces the diagnostic workflow used in the CrunchyData and Zalando GitHub issues.

1. Identify the failing REINDEX statements from the PostgreSQL logs:

2024-07-12 14:03:21.123 UTC [12345] ERROR:  lock timeout while attempting to REINDEX CONCURRENTLY
DETAIL:  Statement: REINDEX INDEX CONCURRENTLY orders_pkey;

2. Check lock wait information using pg_locks and pg_stat_activity:

SELECT pid, locktype, mode, granted, query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT granted AND relation = 'orders_pkey'::regclass;

Typical output shows a long‑running transaction holding a ROW SHARE lock.

 pid | locktype |   mode            | granted |               query
-----+----------+-------------------+---------+---------------------------------
 987 | relation | ROW SHARE         | f       | SELECT * FROM orders WHERE ...

3. Inspect index build progress via pg_stat_progress_create_index:

SELECT pid, relid::regclass, phase, lockers, blocks_total, blocks_done
FROM pg_stat_progress_create_index;

If phase = 'building' and blocks_done stalls, the operation is likely blocked on the exclusive lock.

4. Validate resource limits on the PostgreSQL pod:

kubectl get pod postgres-0 -o yaml | grep -E 'memory|cpu'

Example snippet:

resources:
  limits:
    cpu: "2"
    memory: "2Gi"
  requests:
    cpu: "500m"
    memory: "1Gi"

Compare against the maintenance_work_mem setting inside the container:

psql -c "SHOW maintenance_work_mem;"

Typical output:

 maintenance_work_mem
----------------------
 64MB
(1 row)

5. Capture I/O latency during pod startup with iostat or exec into the container:

kubectl exec -it postgres-0 -- iostat -x 1 5

High await values (>30 ms) indicate PVC warm‑up delays.

Resolution

The fix consists of three coordinated changes:

1. Increase lock_timeout and tune maintenance_work_mem

Set a higher lock timeout (e.g., 5 minutes) to give the index rebuild enough time to acquire the exclusive lock after pod startup, and raise maintenance_work_mem to a value that matches the pod’s memory request.

# postgresql.conf (or ConfigMap)
lock_timeout = '5min'                # default 0 (no timeout)
maintenance_work_mem = '512MB'       # must be ≤ pod memory request
max_parallel_maintenance_workers = 2 # optional, speeds up REINDEX

After updating the ConfigMap, trigger a rolling restart so each pod picks up the new settings.

2. Adjust Kubernetes resource requests

Ensure the pod’s memory request comfortably exceeds maintenance_work_mem plus overhead for shared buffers and WAL buffers.

Parameter	Current	Recommended
memory request	1 Gi	≥ 2 Gi
memory limit	2 Gi	≥ 3 Gi
cpu request	500 m	≥ 1 CPU

Update the StatefulSet spec:

spec:
  template:
    spec:
      containers:
      - name: postgres
        resources:
          requests:
            memory: "2Gi"
            cpu: "1"
          limits:
            memory: "3Gi"
            cpu: "2"

3. Serialize REINDEX operations during rolling updates

Introduce a pre‑stop hook that pauses any automatic REINDEX CONCURRENTLY jobs until the pod reports Ready and the WAL replay has completed.

lifecycle:
  preStop:
    exec:
      command: ["/bin/sh", "-c", "
        echo 'Waiting for WAL replay...';
        while ! pg_isready -U postgres; do sleep 1; done;
        echo 'WAL replay finished, allowing REINDEX';
      "]

Alternatively, use a Kubernetes Job that runs REINDEX CONCURRENTLY after the StatefulSet rollout completes, ensuring only one pod performs the operation at a time.

Validation

After applying the changes, verify the fix with the following steps:

1. Trigger a rolling restart:

kubectl rollout restart statefulset/postgres

Monitor the pod logs for successful index rebuild messages:

kubectl logs -f postgres-0 | grep "index build (concurrently) completed"

Expected output:

2024-07-12 14:10:45.321 UTC [12345] LOG:  index build (concurrently) completed: "orders_pkey"

Check pg_stat_progress_create_index to confirm the phase reaches done within the new timeout.

SELECT relid::regclass, phase, blocks_total, blocks_done
FROM pg_stat_progress_create_index
WHERE relid = 'orders_pkey'::regclass;

All rows should show phase = 'done' and blocks_done = blocks_total.

Confirm no OOM kills:

kubectl get pod postgres-0 -o jsonpath='{.status.containerStatuses[0].lastState.terminated.reason}'

Result should be empty or Completed, not OOMKilled.

Best Practices and Prevention

• Set sensible defaults for lock_timeout (e.g., 2–5 minutes) in environments where rolling restarts are frequent.
• Allocate sufficient maintenance_work_mem based on the largest index size; a rule of thumb is 10 % of the pod’s memory request.
• Monitor pg_stat_progress_create_index and create alerts when phase stays in building longer than lock_timeout.
• Use readiness probes that wait for pg_isready and WAL replay completion before marking the pod as ready.
• Serialize maintenance jobs during rollouts using a Kubernetes Job or a leader‑election sidecar to avoid concurrent REINDEX on multiple replicas.
• Benchmark index rebuilds in a staging environment with realistic PVC latency to tune maintenance_work_mem and max_parallel_maintenance_workers before production deployment.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

1. Why does REINDEX CONCURRENTLY succeed locally but fail after a pod restart?
   Because the local instance typically has ample memory and no I/O cold‑start, while the restarted pod experiences PVC warm‑up latency and tighter resource limits, causing the exclusive lock acquisition to exceed lock_timeout.
2. Can increasing max_parallel_maintenance_workers help?
   It can reduce total build time, but it also raises CPU usage. Ensure the pod’s CPU request can accommodate the additional parallel workers; otherwise you may hit CPU throttling, which can also delay lock acquisition.
3. Is it safe to set lock_timeout to a very high value (e.g., 30 minutes)?
   A high timeout prevents premature aborts but may mask underlying contention. Prefer fixing the root cause (resource limits, long‑running transactions) and keep the timeout to a reasonable bound (5 minutes) to surface genuine deadlocks.
4. How do I know if PVC I/O is the bottleneck?
   During pod startup, run iostat -x inside the container. If await or svctm values are consistently high (>30 ms) and the index build progress stalls, PVC latency is likely contributing to the timeout.
5. Should I use REINDEX instead of REINDEX CONCURRENTLY in a StatefulSet?
   A plain REINDEX acquires an exclusive lock immediately, causing downtime for the table. In a high‑availability setup, REINDEX CONCURRENTLY is preferred despite its complexity, provided the environment is tuned as described.