Metadata Mapping & Schema Design for pgvector Pipelines

Metadata mapping and schema design form the structural backbone of any production vector search system, and getting them wrong is rarely a loud failure — it is a slow one. When metadata columns are modeled without regard to query cardinality, the PostgreSQL planner quietly abandons your indexes, falls back to sequential scans over millions of rows, and p99 latency drifts from 30ms to several seconds with no error ever raised. Within a well-run embedding ingestion pipeline, the schema is the contract that decides which filters can be pushed into an index scan, how idempotent your upserts are, and whether re-ingestion corrupts referential integrity. This page covers the architectural decisions, exact parameter configurations, and diagnostic routines required to keep vector embeddings and their structured metadata in lockstep in PostgreSQL/pgvector.

Architectural Divergence & Trade-offs #

The foundational decision in pgvector schema design is where each metadata field lives: inlined as a native typed column, folded into a JSONB document, or split into a referenced side table. Production deployments converge on three patterns, each with distinct consequences for query planning and ingestion velocity.

1. JSONB monolith — a single metadata JSONB column holds every auxiliary field. It maximizes schema flexibility (no DDL to add a key) but sacrifices index selectivity on nested keys and inflates TOAST traffic, because every filtered read must deserialize the whole document.
2. Normalized relational — dedicated columns (tenant_id UUID, doc_type VARCHAR(32), created_at TIMESTAMPTZ) with constraints. This gives the planner exact statistics and the tightest B-tree scans, but every new filter is a migration and the schema contract is rigid.
3. Hybrid co-location — high-cardinality, frequently-filtered fields as native types; volatile or sparse attributes in JSONB. This balances planner efficiency against ingestion agility and is the default recommendation for multi-tenant search platforms.

For most workloads the hybrid pattern wins. Declare explicit types for anything that appears in a WHERE clause or a partial index predicate, and reserve JSONB for the sparse remainder of optional attributes:

CREATE TABLE document_vectors (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    embedding   vector(768),
    tenant_id   UUID NOT NULL,
    doc_type    VARCHAR(32) NOT NULL,
    status      VARCHAR(16) NOT NULL DEFAULT 'active',
    metadata    JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_status CHECK (status IN ('active', 'archived', 'pending'))
);

Metadata granularity is not a standalone choice — it is coupled to how documents are split. Align it with your batch chunking strategy for embeddings so that each chunk inherits deterministic parent identifiers and a version tag. When the chunk-to-metadata ratio drifts, reconstructing full document context forces the planner into expensive bitmap heap scans that re-fetch the same parent rows repeatedly. The vector column type is equally load-bearing: choosing vector versus halfvec changes both storage and index compatibility, a trade-off quantified under pgvector storage overhead analysis and decided in vector data type selection.

Parameter Space & Diagnostic Workflow #

pgvector’s HNSW and IVFFlat indexes do not evaluate metadata predicates themselves. Filtering happens through PostgreSQL’s native B-tree, GIN, or BRIN indexes, either before or after the vector scan. The knobs below govern whether that combination stays index-driven or collapses into a sequential scan.

The planner prices index paths from pg_statistic and pg_class. When a query pairs vector similarity with a metadata filter, it typically runs a bitmap index scan on the B-tree or GIN index, then a bitmap heap scan that feeds surviving IDs into the vector ordering. To verify the workflow behaves:

-- Refresh planner statistics after any bulk load
ANALYZE document_vectors;

-- Confirm an index is actually chosen (not a seq scan)
SET enable_seqscan = off;  -- profiling only; never in production sessions
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM document_vectors
WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
  AND status = 'active'
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;

If the plan shows a Seq Scan with a high Rows Removed by Filter, the composite index does not match the predicate order — reorder columns so the highest-selectivity term leads. Refer to the pgvector indexing documentation for the recall-versus-parameter matrices across dimensions.

Step-by-Step Implementation #

1. Build composite indexes that mirror real query shapes #

Index the exact (column, column) order your WHERE clauses filter on, and add partial indexes for the dominant status so the hot working set stays small.

-- B-tree for exact/high-selectivity equality filters
CREATE INDEX idx_doc_tenant_type
    ON document_vectors (tenant_id, doc_type);

-- GIN for JSONB containment (@>); jsonb_path_ops is smaller and faster
CREATE INDEX idx_doc_metadata_gin
    ON document_vectors USING gin (metadata jsonb_path_ops);

-- HNSW for cosine similarity
CREATE INDEX idx_doc_hnsw
    ON document_vectors USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Partial HNSW for the hot 'active' path (30–40% smaller index)
CREATE INDEX idx_doc_active_hnsw
    ON document_vectors USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64)
    WHERE status = 'active';

The m and ef_construction values here are conservative starting points; calibrate them against your recall target using the optimizing m and ef_construction parameters procedure, and confirm HNSW is even the right structure for your write pattern with the HNSW vs IVFFlat algorithm selection framework.

2. Map metadata to vectors idempotently at the write boundary #

Re-ingestion is normal — documents mutate, models get re-run — so every write must be idempotent on a stable natural key. Drive metadata enrichment and database writes concurrently with the async processing with Python AsyncIO model so a slow embedding call never blocks the connection pool.

import asyncpg

UPSERT = """
INSERT INTO document_vectors (id, embedding, tenant_id, doc_type, status, metadata)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (id) DO UPDATE
SET embedding = EXCLUDED.embedding,
    metadata  = EXCLUDED.metadata,
    status    = EXCLUDED.status
"""

async def upsert_batch(pool: asyncpg.Pool, rows: list[tuple]) -> None:
    # rows carry pre-validated metadata and unit-normalized vectors
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.executemany(UPSERT, rows)

Two invariants make this safe. First, validate metadata before it reaches PostgreSQL — CHECK (metadata IS JSON) guarantees well-formedness but not structure, so enforce shape with pydantic or jsonschema at the pipeline edge. Second, guarantee vector length matches the column declaration exactly; a mismatch raises ERROR: invalid input syntax for type vector. Normalizing embeddings to unit length lets cosine similarity reduce to a dot product, which is why it belongs in type casting and vector normalization rather than in ad-hoc write code.

3. Evolve the schema without a table rewrite #

Adding a filter column, promoting a JSONB key, or migrating dimensions must happen without downtime. Add nullable/defaulted columns (a metadata-only change in PostgreSQL 15+), backfill in batches, then index after population.

-- Metadata-only add (no full-table rewrite)
ALTER TABLE document_vectors ADD COLUMN content_version INT NOT NULL DEFAULT 1;

-- Promote a legacy JSONB key into the typed column, in bounded batches
UPDATE document_vectors
SET content_version = (metadata->>'version')::int
WHERE metadata ? 'version'
  AND content_version = 1;

-- Index only after the backfill settles
CREATE INDEX CONCURRENTLY idx_doc_version ON document_vectors (content_version);

Never run ALTER TABLE ... ALTER COLUMN ... TYPE on a live vector table — it takes an ACCESS EXCLUSIVE lock and rewrites every row. Instead add a parallel column, backfill asynchronously, and cut over via a view or application routing. When the field semantics themselves change (a string becoming an array of tags), that is drift, and the full contract-and-quarantine procedure lives in handling metadata drift during vector ingestion. Keep deeply nested arrays out of the hot row: TOAST pushes anything over ~2KB out of line, so flatten hierarchies and store large payloads in a separate content_blobs table referenced by foreign key.

Validation & Recall Testing #

Schema changes are only correct if they preserve both index utilization and retrieval recall. Two checks catch the common regressions.

First, confirm the metadata filter is pruning the candidate set before the vector scan rather than after:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, embedding <=> '[...]'::vector AS distance
FROM document_vectors
WHERE tenant_id = $1 AND doc_type = 'contract'
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;

A healthy plan shows a Bitmap Index Scan on idx_doc_tenant_type feeding an Index Scan using idx_doc_hnsw, with Rows Removed by Filter near zero. If the filter runs after the HNSW scan, the graph traversal wastes work on rows the tenant will never see — raise hnsw.ef_search or introduce a partial index scoped to the filter.

Second, quantify recall against exact search, because aggressive pre-filtering silently drops relevant neighbors:

async def recall_at_k(pool, query_vec, tenant_id, k=10) -> float:
    approx_sql = """
        SELECT id FROM document_vectors
        WHERE tenant_id = $1
        ORDER BY embedding <=> $2 LIMIT $3
    """
    exact_sql = approx_sql  # run with SET LOCAL enable_indexscan = off
    async with pool.acquire() as conn:
        approx = {r["id"] async for r in conn.cursor(approx_sql, tenant_id, query_vec, k)}
        await conn.execute("SET LOCAL enable_indexscan = off")
        exact = {r["id"] async for r in conn.cursor(exact_sql, tenant_id, query_vec, k)}
    return len(approx & exact) / max(len(exact), 1)

Track recall_at_k per tenant in CI. A drop after a schema migration usually means the new predicate defeated a partial index and the query fell back to brute force on a truncated set.

Failure Modes & Gotchas #

• Silent sequential-scan fallback. After a bulk load without ANALYZE, stale statistics make the planner mis-estimate selectivity and abandon composite indexes. Symptom: Seq Scan with Rows Removed by Filter above 80%. Fix: ANALYZE in the same transaction as the load, and raise default_statistics_target on skewed columns.
• JSONB key-existence queries with the wrong opclass. jsonb_path_ops supports containment (@>) but not the existence operators (?, ?|, ?&). A WHERE metadata ? 'flag' query against a jsonb_path_ops index silently ignores it. Fix: use default jsonb_ops for existence, or rewrite as containment.
• WAL pressure during backfills. A single unbounded UPDATE promoting a JSONB key rewrites every row into WAL, stalling replication and triggering checkpoint storms. Fix: batch with LIMIT ... RETURNING loops or ctid ranges, and watch pg_stat_bgwriter.
• TOAST amplification on nested metadata. Arrays and deep objects over ~2KB move out of line, so every filtered read pays an extra fetch. Fix: audit with pg_column_size(metadata) and relocate large blobs to a side table.
• Cross-chunk metadata fragmentation. When chunks of one document carry inconsistent parent IDs or versions, context reconstruction fans out into repeated heap fetches. Fix: attach doc_id, chunk_index, and content_version deterministically at chunk time.
• Multi-tenant leakage through missing predicates. Forgetting tenant_id in a filter returns another tenant’s neighbors with no error. Enforce isolation at the database layer via securing pgvector tables with row-level security rather than trusting application code.

Monitoring & Alerting Hooks #

Schema decisions compound at scale, so instrument the table and its indexes continuously rather than diagnosing after an SLA breach.

-- Index-to-table size ratio: HNSW typically 1.5–2.5x raw vectors; alert above 3x
SELECT
  pg_size_pretty(pg_relation_size('idx_doc_hnsw'))   AS index_size,
  pg_size_pretty(pg_relation_size('document_vectors')) AS table_size;

-- Scan-type balance: rising seq_scan on a large table signals a lost index
SELECT relname, seq_scan, idx_scan,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'document_vectors';

-- Per-index usage: idx_scan stuck at 0 means the index is dead weight
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'document_vectors'
ORDER BY idx_scan;

Export these as gauges to a Prometheus-compatible collector: pgvector_index_table_ratio, document_vectors_seq_scan_total, and document_vectors_dead_tup_ratio. Alert when the seq-scan counter grows on a table over ~1M rows, or when the dead-tuple ratio exceeds the autovacuum threshold, which points to churn outrunning vacuum. For write-path audit trails on the metadata contract, enable pgaudit scoped to DDL and UPDATE on document_vectors so schema mutations are attributable. Rebuild bloated graphs with REINDEX INDEX CONCURRENTLY idx_doc_hnsw inside a maintenance window, and partition historical rows with pg_partman to keep the active index working set bounded.

FAQ #

Related #

• Batch chunking strategies for embeddings — align chunk boundaries with metadata granularity.
• Async processing with Python AsyncIO — non-blocking metadata enrichment and upserts.
• Type casting and vector normalization — match vector dimensions and unit-length before insertion.
• Handling metadata drift during vector ingestion — versioned contracts and quarantine gates for evolving payloads.
• Up: Embedding Ingestion Pipeline Engineering — the pipeline this schema work sits inside.