Index and Collection Operations

09:14 on a Tuesday: legal requires every vector belonging to tenant Globex — 2.1 million of them — out of the store within the hour. The request arrives as one sentence, and there are three different operations hiding behind it. Which one you pick depends on what the store does cheaply and what your queries must guarantee in the meantime.

Per-id deletes work when you have the ids (the tenant's rows in Postgres) and the store has no instant bulk delete. Run them in batches so no single statement holds a lock while autovacuum and replicas fall behind:

DO $$
DECLARE n int;
BEGIN
  LOOP
    WITH doomed AS (
      SELECT id FROM tickets
      WHERE tenant_id = 'globex'
      ORDER BY id
      LIMIT 5000                    -- bounded work per statement
    )
    DELETE FROM tickets WHERE id IN (SELECT id FROM doomed);
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    PERFORM pg_sleep(0.1);          -- let replicas and autovacuum keep up
  END LOOP;
END $$;

The loop terminates on a zero-row delete, so it is safe to re-run after a crash; the sleep is what keeps a 2.1M-row purge from turning into a replication-lag incident. On Pinecone the same intent is one call — index.delete(delete_all=True, namespace="tenant:globex") — followed immediately by a describe_index_stats() check that the namespace count reads zero.

Soft delete is the fallback when neither exists: bulk deletes are slow or unsupported, and the requirement is that queries stop returning the data, not that bytes vanish now.

write path:   metadata.deleted = true        (upsert same id, cheap)
query path:   WHERE deleted != true          (runs server-side, in the filter)
late path:    batch purge by flag            (off-hours, once counts settle)

The flag only works if every query carries it — see the iterative-scan discussion in Vector Search with pgvector for why a filter that falls off an ANN query fails silently, returning flagged rows without an error.

The failure modes, in the order they become expensive:

• delete_all on the wrong namespace or index. Irreversible, and the call succeeds. Require a namespace argument from config, never a literal, and print the target before executing.
• Ids you do not own. A purge scoped by id list is only as complete as the list; the durable version scopes by tenant key on the source table and verifies counts on both sides.
• Soft-delete flag missing from one query path. Deleted data reappears in exactly the surface nobody tests. Assert it in the retrieval contract tests, not just in code review.
• Secondary copies. Cached results, reranker inputs, exported files, and backups still hold the vectors after the index is clean. Deletion is a system property, not a storage property.

The provider deprecates the embedding model behind your 12 million rows, or a newer model measurably wins on your eval set. Either way the migration has a hard property: the store must answer queries correctly at every minute of it. The shape that satisfies that is dual-write, backfill, shadow reads, cut over.

          ┌── old column + old index ──► query path (today: 100%)
rows ─────┤
          └── new column + new index ──► shadow path (scored, not served)
                     ▲
        backfill in 200-row batches, throttled to provider rate limits

Phase 1 — add, do not replace. A new column keeps the old one authoritative:

ALTER TABLE tickets ADD COLUMN embedding_v2 vector(384);
CREATE INDEX tickets_embedding_v2_hnsw
  ON tickets USING hnsw (embedding_v2 vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Phase 2 — backfill on a cursor. Ordering by id and filtering on the new column makes the job restartable:

UPDATE tickets
SET embedding_v2 = embed_remote_v2(body)   -- thin wrapper around the provider call
WHERE id IN (
  SELECT id FROM tickets
  WHERE embedding_v2 IS NULL
  ORDER BY id
  LIMIT 200                                  -- one small batch; sleep between batches
);

Phase 3 — shadow reads. Run the query against both indexes and compare: how often does the new top-10 contain the old top-10, and does the disagreement move your eval score up or down? That measurement — not the vendor's benchmark — is the go/no-go, and Embedding Evaluation covers the scoring side.

Phase 4 — cut over with a return path. Swap the served column behind a flag (or move the index name onto the new column), ramp 1% → 10% → 100%, and keep the old column warm for a rollback window measured in weeks.

The version dimension matters too: if the id encodes the model (as it should — see the id scheme in Managed Vectors with Pinecone), old and new vectors can never be mixed inside one query, and a half-finished backfill degrades recall instead of returning garbage.

One index shared by a test suite and production is two incidents waiting: test vectors show up in customer results, and a suite that calls delete_all during teardown takes the tenant with it. Separation is boring and cheap, and it also protects your evaluation — a benchmark that queries an index the tests write to is measuring the tests.

The separation has a second benefit: each environment's counts mean something. In production, total_vector_count rising while ticket creation is flat is a bug; in staging it is expected noise.

Growth forecasting is arithmetic you can do on a napkin, so nobody has to be surprised by a disk alert. Start from raw storage:

rows × dimension × 4 bytes              -- float32
12,000,000 × 1536 × 4         =  73.7 GB   raw vectors
× 1.5 (heap rows, HNSW graph, bloat)  ≈ 110 GB   measured on disk
+1.5M rows/month × 1536 × 4 × 1.5      ≈  +11 GB per month
=> next quarter's projection            ≈  143 GB

The × 1.5 is not a constant — measure it once against reality:

SELECT pg_size_pretty(pg_total_relation_size('tickets')) AS on_disk,
       pg_size_pretty(pg_relation_size('tickets'))       AS heap_only;
ANALYZE tickets;

On a managed store the equivalent is describe_index_stats() — total_vector_count weekly and index_fullness where it exists. Alert on the trend: project days-to-capacity from the last 30 days of growth and page when it drops under 90, which is a quarter of warning rather than a weekend of emergency sharding. Cost Budgeting turns that curve into a number for the finance review, and when the curve says this store will no longer fit the requirements, the choice of what to move to is Choosing a Vector Store.