Enable pgvector and Own the Ops, Not Just the Demo

Tier: Core · Workflow area: Vector & AI Features · ~40 minutes

Vector search inside Postgres is the DBA's cleanest claim on the AI estate: no new platform, no data movement, your existing backup, HA and access-control machinery. But the demo hides real operational surface. This playbook walks the enablement in operator order — capacity first, index second, query behavior third — so the feature lands with the same rigor as any other schema change.

When to use

• An application team wants semantic search or RAG over data already in Postgresenabling in-engine beats standing up a separate vector store you would then have to sync, secure and back up.
• You are sizing before committingembeddings are wide. This playbook's storage and memory math tells you what 5 million 1024-dimension vectors actually cost before anyone promises a launch date.
• A pgvector proof-of-concept is heading to productionthe demo worked; now someone has to own maintenance windows, replica lag and backup growth. That someone is you.

When NOT to use

• Billions of vectors or heavy multi-tenant ANN at extreme QPSdedicated vector infrastructure may genuinely fit better. Run the numbers in step 1 before deciding; do not assume either way.
• When the corpus is not in Postgres and never will bepulling data into the engine purely to vectorize it recreates the sync problem in-engine enablement exists to avoid.
• On a write-hot OLTP primary with no headroomHNSW index builds and re-embedding jobs consume memory and I/O. If the instance is already at its limits, capacity work comes first.

The recipe

1. 1.

   Do the capacity math before CREATE EXTENSION. A vector column of dimension d costs roughly 4d + 8 bytes per row; the HNSW index adds the same order again. Have AI build the forecast:

   Copy

   Act as a PostgreSQL capacity planner. Table: 4.2M rows, adding
   vector(1024). Estimate: table growth, HNSW index size (m=16),
   maintenance_work_mem for a sane build, WAL volume during a full
   embedding backfill, and pg_dump size impact. Show formulas,
   then numbers. Flag what changes at 10x rows.
   

   Sanity-check its arithmetic: 4.2M × 1024 dims ≈ 17 GB of raw vector data before index overhead. If the model's numbers disagree with the formula, the formula wins.

2. 2.

   Enable the extension and add the column as a gated change. This is DDL on production — it goes through the pb-10 migration gate like anything else:

   Copy

   CREATE EXTENSION IF NOT EXISTS vector;
   ALTER TABLE products ADD COLUMN embedding vector(1024);
   

   Confirm the version — HNSW needs pgvector 0.5+, and 0.7+ brings halfvec and sparse types worth knowing about:

   Copy

   SELECT extversion FROM pg_extension WHERE extname = 'vector';
   

3. 3.

   Build the HNSW index with memory set deliberately, off-peak. The build wants the graph in memory; starving it makes builds crawl:

   Copy

   SET maintenance_work_mem = '4GB';
   SET max_parallel_maintenance_workers = 4;
   CREATE INDEX CONCURRENTLY idx_products_embedding
     ON products USING hnsw (embedding vector_cosine_ops)
     WITH (m = 16, ef_construction = 64);
   

   CONCURRENTLY avoids blocking writes but roughly doubles build time — the right trade on a live table. Time the build and record peak memory; that number is your maintenance-window budget for every future rebuild.

4. 4.

   Interrogate the plan where vector search meets filters. The classic production surprise: ANN index scans return the ef_search nearest neighbors before your WHERE clause filters them — a selective filter can leave you with 3 results when the app asked for 20:

   Copy

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT id, name FROM products
   WHERE category = 'outdoor' AND in_stock
   ORDER BY embedding <=> $1 LIMIT 20;
   

   If filtered result counts come up short, raise hnsw.ef_search (session-level), or use pgvector 0.8+ iterative index scans which keep fetching candidates until the LIMIT is satisfied. Verify which behavior your version has — do not trust the model's memory of it.

5. 5.

   Answer the replica and backup questions in writing. Ask AI to draft the ops annex, then verify each claim against your setup: HNSW indexes replicate physically and are usable on hot standbys; logical replication rebuilds them on the subscriber; base backups and pg_dump both grow by the table-plus-index figure from step 1. Run one full backup-and-restore timing test with vectors in place before go-live — measured, not estimated.

6. 6.

   Set the vacuum and monitoring baseline. Dead tuples from re-embedding updates bloat both heap and index. Watch pg_stat_user_tables for the vector table, alert on index size growth beyond forecast, and schedule REINDEX CONCURRENTLY guidance for when deletes accumulate — HNSW does not shrink on its own.

Make it yours

• SQL Server 2025the native VECTOR type plus DiskANN indexing plays the same role; memory and maintenance questions map to index build resource governance. See pb-32 for the full translation.
• Oracle 23aiAI Vector Search with in-memory neighbor graph indexes; the capacity math changes (memory-resident HNSW), the discipline does not.
• Aurora/RDS/Cloud SQL Postgrespgvector is available but you do not control maintenance_work_mem ceilings or extension versions; check the version your provider pins before promising HNSW-era features, and watch IOPS billing during builds and backfills.

Quality check

• Storage and memory forecasts are written down and revieweda vector rollout without a capacity doc is a demo wearing a change ticket. The 10x line matters most.
• Index build was timed on production-scale dataa build that takes 40 minutes on staging data may take 7 hours on production rows. The maintenance window is sized from measurement.
• The filtered-query plan was read, not assumedthe ef_search/filtering interaction is the top source of "search is broken" tickets. You have seen the EXPLAIN output for your real query shapes.
• Backup, restore and replica behavior tested once end-to-endone timed restore with vectors in place, one replica failover check. Boring on purpose.