Skip to content

Wikantik

Zero-Downtime Schema and pgvector Index Migrations on Live PostgreSQL

From Wikantik · 22 articles · latest

Architecture diagram of zero-downtime PostgreSQL and pgvector HNSW dual-index migration pipeline
Zero-downtime PostgreSQL and pgvector migration pipeline: pairing transactional expand-and-contract DDL with out-of-transaction HNSW dual-index swaps.

A two-line ALTER TABLE or CREATE INDEX that finishes in 12 milliseconds on a laptop can wedge a production PostgreSQL cluster in under ten seconds. The culprit is rarely the DDL operation itself; it is PostgreSQL's FIFO lock queue. When you combine relational schema evolution with pgvector HNSW graph builds—which allocate gigabytes of working memory and scan every row multiple times—a naive migration script turns a routine deploy into an unscheduled outage.

Wikantik's retrieval stack runs on PostgreSQL 18 with pgvector storing 1024-dimensional embeddings in content_chunk_embeddings alongside Apache Lucene BM25, as detailed in Building the Hybrid Retrieval Engine: BM25 Meets pgvector and Reciprocal Rank Fusion. While single-host container deploys can absorb brief startup migrations during an image swap, preparing for zero-downtime rolling deploys and horizontal scaling alongside PgBouncer (The Unfinished Roadmap: Horizontal Scale, PgBouncer, and What Wikantik Still Needs) demands strict online migration discipline.

Why "Instant" DDL Still Stalls Live Traffic

PostgreSQL table locks operate in a strict first-in, first-out queue. Even an operation whose actual metadata change takes microseconds—such as ALTER TABLE ... ADD COLUMN with a constant default in modern PostgreSQL, or DROP COLUMN—must acquire an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode including plain SELECT (ACCESS SHARE).

Suppose a reporting job or slow query holds an ACCESS SHARE lock for 45 seconds. Your migration issues ALTER TABLE and blocks waiting for ACCESS EXCLUSIVE. Every subsequent SELECT, INSERT, and UPDATE from your application pool now queues behind the waiting ALTER TABLE. Within seconds, HikariCP connection pools saturate, Tomcat worker threads pile up, and health checks fail.

Warning

Never execute DDL against a live database without a bounded lock_timeout. If your migration cannot acquire its lock within a couple of seconds, abort immediately and retry rather than trapping application traffic behind a queued lock request.

-- Fail fast instead of piling up the lock queue
SET lock_timeout = '2s';
SET statement_timeout = '15s';

ALTER TABLE content_chunk_embeddings
    ADD COLUMN IF NOT EXISTS embedding_v2 vector(1024);

RESET statement_timeout;
RESET lock_timeout;

Two operational nuances matter here:

  1. lock_timeout bounds waiting, not execution. Once the lock is granted, lock_timeout stops counting. Set statement_timeout as well when running DDL that could unexpectedly scan or rewrite rows.
  2. Session vs. local scope. Inside a BEGIN ... COMMIT block, SET LOCAL lock_timeout scopes cleanly to the transaction. Outside a transaction block—which CREATE INDEX CONCURRENTLY requires—SET LOCAL has no effect, so you must use session-level SET and explicitly RESET afterward.

The Expand-and-Contract Pattern in Wikantik's Schema History

When an application rolls out across multiple containers or supports automated :rollback image swaps, the old application binary and the new database schema inevitably overlap. Dropping or renaming a column in the same release that stops reading it guarantees exceptions on in-flight requests or rolled-back instances.

The expand-and-contract pattern splits breaking schema changes into three independently safe phases:

  1. Expand: Add the new column, table, or constraint (NOT VALID first, followed by VALIDATE CONSTRAINT so writes stay unblocked). Deploy application code that dual-writes both structures while continuing to read the old one.
  2. Backfill and Cutover: Populate historical rows out-of-band in bounded chunks. Flip a configuration flag or deploy code to read from the new structure while verifying parity.
  3. Contract: After the old application code has completely drained and rollback to the previous binary is off the table, drop the legacy index concurrently and remove the obsolete column.

Wikantik's own bin/db/migrations/ ledger reflects this discipline across its 70+ versioned V*.sql files:

  • Least-privilege role separation: Superuser postgres provisions the vector extension (V004), the migrate role owns public tables and executes DDL, and the runtime :app_user (wikantik) holds DML-only grants (SELECT, INSERT, UPDATE, DELETE). Migration V059 even pre-creates monthly audit_log partitions through December 2028 so the application role never needs DDL privileges at runtime.
  • Additive vector evolution (V009 to V032): Embeddings initially landed in content_chunk_embeddings as little-endian float32 BYTEA blobs (V009). When pgvector HNSW support was introduced in V032, the migration added embedding vector(1024) alongside the BYTEA column to support dual-write rather than rewriting the column type in place with ALTER COLUMN ... TYPE.
  • No backfills inside DDL migrations: Wikantik keeps V*.sql files DDL-only and fast, delegating large historical populates to dedicated scripts under bin/db/one-shots/ (such as 2026-05-20-backfill-chunk-embeddings.sh).

Chunked Backfills Without Pool Starvation

Running a single UPDATE content_chunk_embeddings SET ... across hundreds of thousands of rows inside a migration transaction holds row locks for minutes, generates massive WAL spikes, delays VACUUM, and triggers replication lag. Worse, if your backfill calls an external model like Ollama to compute embeddings, holding a JDBC transaction open across HTTP calls will exhaust HikariCP in seconds—the exact connection-lifecycle bug Wikantik fixed in commit a09e0d3b4c during its N+1 profiling cleanup.

Keep backfills keyset-paginated, idempotent, and decoupled from inference I/O:

// Decouple connection checkout from external Ollama HTTP inference
long lastId = 0L;
final int batchSize = 500;

while (true) {
    List<ChunkRow> batch = jdbc.query(
        "SELECT id, chunk_text FROM kg_content_chunks " +
        "WHERE id > ? AND embedding_v2 IS NULL " +
        "ORDER BY id ASC LIMIT ?",
        lastId, batchSize
    );
    if (batch.isEmpty()) {
        break;
    }

    // JDBC connection is already back in HikariCP before HTTP inference runs
    List<VectorUpdate> vectors = ollamaClient.embedBatch(batch);

    jdbc.inTransaction(tx -> {
        tx.execute("SET LOCAL lock_timeout = '2s'");
        tx.batchUpdate(
            "UPDATE content_chunk_embeddings SET embedding_v2 = ?::vector " +
            "WHERE chunk_id = ? AND embedding_v2 IS NULL",
            vectors
        );
    });

    lastId = batch.getLast().id();
    Thread.sleep(50); // Throttle I/O and WAL pressure for live traffic
}

Keyset pagination (WHERE id > ? ORDER BY id LIMIT ?) executes in constant index-scan time regardless of table depth, unlike OFFSET, which degrades linearly as the cursor advances.

Building pgvector HNSW Indexes Without Blocking Writes

A standard CREATE INDEX acquires a SHARE lock on the target table. Reads (SELECT) continue, but every INSERT, UPDATE, and DELETE blocks until the entire index build finishes. On a pgvector HNSW graph—which constructs multi-layer navigable small-world links across high-dimensional vectors—that build can run for tens of minutes on million-row tables.

Always use CREATE INDEX CONCURRENTLY on live tables. It acquires SHARE UPDATE EXCLUSIVE (which conflicts with VACUUM, ANALYZE, and other concurrent DDL on the same table, not normal reads or writes) and builds the index across two table scans while waiting out older snapshots.

Note

CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY cannot execute inside a transaction block. If your migration runner wraps every file in BEGIN ... COMMIT (including psql --single-transaction in bin/db/migrate.sh, or default Flyway/Liquibase execution), concurrent index builds must run in an autocommit/non-transactional migration step or out-of-band operator runbook.

-- Must run in autocommit mode (outside BEGIN ... COMMIT)
SET lock_timeout = '2s';
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX CONCURRENTLY idx_chunk_embeddings_hnsw_v2
    ON content_chunk_embeddings
    USING hnsw (embedding_v2 vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

RESET max_parallel_maintenance_workers;
RESET maintenance_work_mem;
RESET lock_timeout;

While the build runs, monitor its phase and tuple progress without guessing via pg_stat_progress_create_index:

SELECT p.phase, p.blocks_done, p.blocks_total, p.tuples_done, p.tuples_total
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON a.pid = p.pid
WHERE a.query ILIKE '%idx_chunk_embeddings_hnsw_v2%';

The maintenance_work_mem Cliff and halfvec

Unlike B-trees, pgvector builds HNSW graphs primarily in RAM. As soon as the graph exceeds maintenance_work_mem (whose default in PostgreSQL is typically just 64 MB), PostgreSQL logs a warning and spills graph construction to disk:

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 142000 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

Once construction spills to disk, build times routinely jump by an order of magnitude while hammering IOPS. Measure your target graph footprint in staging first, raise maintenance_work_mem per-session above that ceiling (within host RAM headroom), and if memory remains constrained on 1024- or 1536-dimensional embeddings, evaluate halfvec (16-bit floats) via an expression index or column migration to cut graph memory in half.

Cleaning Up Invalid Indexes After a Timeout

If CREATE INDEX CONCURRENTLY fails mid-build—whether from a lock_timeout, a deadlock, or a cancelled session—PostgreSQL leaves behind an invalid index marked indisvalid = false in pg_index. It consumes disk space, slows writes, and never serves queries.

Never mask this by writing CREATE INDEX CONCURRENTLY IF NOT EXISTS: if an invalid index with that name already exists, IF NOT EXISTS silently succeeds and leaves you with a broken index. Always inspect pg_index and drop invalid remnants concurrently before retrying:

-- 1. Verify index validity after every concurrent build
SELECT c.relname AS index_name, i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'content_chunk_embeddings'::regclass;

-- 2. Clean up failed builds before retrying
DROP INDEX CONCURRENTLY IF EXISTS idx_chunk_embeddings_hnsw_v2;

Executing a Zero-Downtime HNSW Dual-Index Swap

Changing HNSW build parameters (m or ef_construction), swapping operator classes, or switching embedding models requires rebuilding the vector index from scratch. Running REINDEX INDEX CONCURRENTLY rebuilds the index in place with the same definition, whereas changing m, ef_construction, or the target column requires a dual-index swap:

  1. Build the candidate index concurrently (idx_chunk_embeddings_hnsw_v2) alongside the existing live index.
  2. Verify indisvalid = true and confirm the query planner selects the expected graph via EXPLAIN (ANALYZE, BUFFERS) at your target hnsw.ef_search setting (Wikantik defaults wikantik.search.dense.pgvector.ef_search to 100 and tunes up to 200–400 when trading CPU for recall).
  3. Soak under production traffic while keeping the original index intact for instant query-flag rollback.
  4. Confirm zero residual usage on the old index using pg_stat_user_indexes (idx_scan stops incrementing across all replicas and readers).
  5. Drop the obsolete index concurrently (DROP INDEX CONCURRENTLY idx_chunk_embeddings_hnsw_v1) with lock_timeout = '2s', then contract the legacy column in a subsequent migration.

Just as Wikantik pruned 16 redundant indexes in V063 after profiling its read paths, removing superseded HNSW graphs matters operationally: every extra HNSW index doubles write amplification on chunk upserts and competes for shared buffers.

Pre-Flight Checklist for Production Vector Migrations

Before merging any migration touching a live PostgreSQL + pgvector table, verify five invariants:

  • Bounded locks: Every DDL session sets lock_timeout (for example, '2s') and retries gracefully on failure.
  • Additive first: New columns and vector types are introduced alongside existing ones; destructive drops ship only after old code drains.
  • Out-of-band backfills: Chunked keyset batches run outside V*.sql DDL migrations and release JDBC connections before calling embedding endpoints.
  • Autocommit index builds: CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY run outside transaction blocks with explicit maintenance_work_mem sizing, pg_stat_progress_create_index visibility, and indisvalid verification.
  • No unindexed writes during cutover: Existing HNSW indexes stay live until the replacement graph is validated under real hnsw.ef_search traffic.

References

Keep reading

Comments