Estimating Lock-Hold Time for ALTER EXTENSION UPDATE

How to predict, before a maintenance window opens, how long ALTER EXTENSION UPDATE will hold its catalog locks — using historical lock metrics, table cardinality, and the specific catalog rewrites an update performs — so the operation is scheduled to fit rather than aborted mid-flight.

Up: Threshold Tuning for Downtime Windows — this page is the concrete estimation technique behind the threshold that cluster sizes, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline.

Context & When This Applies

The gate that decides whether an upgrade fits its window needs a number: the expected lock-hold duration. That number is not the extension version’s headline “upgrade time” — it is dominated by how long the update’s heaviest statement holds an AccessExclusiveLock on catalog rows and any table it rewrites. This page applies to PostgreSQL 12–17 and any extension whose update script may touch user data (a PostGIS type change forcing a table rewrite, a pgvector opclass change forcing a reindex) or serialize on a hot catalog. It is the measurement that makes threshold tuning for downtime windows quantitative instead of guesswork.

Estimation matters because the failure it prevents is the worst kind: an upgrade that runs past the window, holding a lock while the connection pooler backs up, until it is force-cancelled mid-transaction. A good estimate turns that into either a confident go or a re-plan onto a pg_upgrade or logical replication route.

Estimating lock-hold time from three inputs against a window budget Three inputs feed a lock-hold estimate. Historical lock-acquisition metrics for the extension on a given cluster size give a baseline per-operation cost. Table cardinality of any relation the update rewrites scales that cost, since a table rewrite or reindex is proportional to row count. The catalog-rewrite class of the update — metadata only, table rewrite, or index rebuild — sets the multiplier. The three combine into an estimated lock-hold duration, which is compared against the maintenance window budget: if it fits, promote; if not, re-plan. Historical lock metrics per extension · cluster size Table cardinality rows in any rewritten table Catalog-rewrite class metadata · rewrite · reindex Estimate lock-hold seconds fits window? promote re-plan

Concept: The Lock-Hold Is Dominated by the Heaviest Rewrite

ALTER EXTENSION UPDATE runs a migration script that can range from pure metadata changes to full table rewrites. The lock-hold time is set by the heaviest single statement, because that statement holds its AccessExclusiveLock for its whole duration and everything else queues behind it. Three rewrite classes bracket the estimate. A metadata-only update (new functions, changed function bodies) touches only catalog rows and completes in milliseconds regardless of data size. A table rewrite (a type change that forces ALTER TABLE ... rewrite) is proportional to the row count of every affected table. An index rebuild (a pgvector opclass change) is proportional to the index size and its build cost, which for hnsw is superlinear.

The estimate therefore has the shape t ≈ baseline + rewrite_rows / rewrite_rate, where baseline is the metadata cost measured historically and rewrite_rate is the rows-per-second your cluster sustains for that operation class, itself learned from prior runs. Because rewrite_rate depends on hardware, cache state, and concurrent load, it must come from your cluster’s history — the same lock-acquisition metrics the compatibility validation pipeline already records — not a vendor benchmark. Rehearsing on a pg_basebackup clone is how you obtain a rewrite_rate for an extension you have never upgraded before.

Runnable Implementation

The estimator below reads the catalog to find the tables an update would rewrite, multiplies their live-row counts by a learned per-class rate, and returns a lock-hold estimate with a confidence note.

#!/usr/bin/env python3
"""Estimate ALTER EXTENSION UPDATE lock-hold time from catalog state + history."""
import psycopg2

# Learned from this cluster's history (rows/sec). Replace with measured values.
REWRITE_RATE = {"table_rewrite": 250_000, "index_rebuild_hnsw": 20_000}
BASELINE_SECONDS = {"metadata_only": 0.2, "table_rewrite": 2.0,
                    "index_rebuild_hnsw": 5.0}


def estimate_lock_hold(conn, affected_tables: list, rewrite_class: str) -> dict:
    baseline = BASELINE_SECONDS.get(rewrite_class, 1.0)
    if rewrite_class == "metadata_only":
        return {"rewrite_class": rewrite_class, "estimate_seconds": baseline,
                "confidence": "high", "detail": "catalog-only, size-independent"}

    rate = REWRITE_RATE.get(rewrite_class, 100_000)
    total_rows = 0
    with conn.cursor() as cur:
        for tbl in affected_tables:
            # Live-tuple estimate from the planner stats — no full scan needed.
            cur.execute("SELECT reltuples::bigint FROM pg_class "
                        "WHERE oid = %s::regclass", (tbl,))
            row = cur.fetchone()
            total_rows += int(row[0]) if row and row[0] > 0 else 0

    est = baseline + total_rows / rate
    return {
        "rewrite_class": rewrite_class,
        "affected_rows": total_rows,
        "estimate_seconds": round(est, 1),
        "confidence": "medium" if total_rows else "low",
        "detail": f"baseline {baseline}s + {total_rows} rows / {rate} rows/s",
    }


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=appdb")
    import json
    print(json.dumps(
        estimate_lock_hold(conn, ["public.parcels"], "table_rewrite"), indent=2))

Compare the returned estimate_seconds against the window budget in threshold tuning for downtime windows; if it does not fit, hand off to the route decision in pg_upgrade vs a logical replication upgrade path and keep a checkpoint via snapshot and point-in-time recovery.

Expected Output & Verification

The estimator returns a duration with its derivation:

{
  "rewrite_class": "table_rewrite",
  "affected_rows": 48000000,
  "estimate_seconds": 194.0,
  "confidence": "medium",
  "detail": "baseline 2.0s + 48000000 rows / 250000 rows/s"
}

Verify the estimate against reality by measuring the actual lock-hold during the rehearsal, then feeding it back to recalibrate rewrite_rate:

-- During the rehearsal UPDATE, sample how long the exclusive lock is held.
SELECT a.query,
       now() - a.query_start        AS elapsed,
       l.mode, l.granted
FROM   pg_locks l
JOIN   pg_stat_activity a ON a.pid = l.pid
WHERE  l.mode = 'AccessExclusiveLock'
  AND  a.query ILIKE 'ALTER EXTENSION%'
ORDER  BY elapsed DESC;

If the measured elapsed is materially longer than the estimate, lower rewrite_rate and re-estimate before the production window. Record the calibrated rate in version control and branching so every future estimate for that extension starts from a truthful baseline.

Edge Cases & Gotchas

reltuples is an estimate and goes stale. pg_class.reltuples is only as fresh as the last ANALYZE/autovacuum. A recently bulk-loaded table can report far fewer rows than it holds, producing a dangerously low estimate. Run ANALYZE on the affected tables before estimating, or read pg_stat_user_tables.n_live_tup for a fresher figure.

Lock wait time is separate from lock hold time. The estimator predicts how long the update holds the lock once it starts, not how long it waits to acquire it behind a long-running transaction. Add a lock_timeout so a blocked update fails fast instead of waiting, and size the wait separately from the hold.

A concurrent workload slows the rewrite rate. The learned rewrite_rate assumes the rehearsal’s load profile. Upgrading during peak traffic can halve throughput; either estimate with a peak-adjusted rate or schedule into a genuinely quiet window, and never reuse an off-peak rate for a peak run.

Non-transactional steps do not hold a rollback-able lock. If the update registers a background worker or allocates shared memory, that step commits immediately and is not part of the lock-hold estimate — but it is part of the risk. Surface those steps in async upgrade simulation and treat them under ALTER EXTENSION automation, separately from the lock math.