a close-up of a server room

RAG embedding dimension mismatch in PostgreSQL after model update

by

Problem: RAG Embedding Dimension Mismatch after Model Update

An event‑driven pipeline streams records into a PostgreSQL table that stores vector embeddings generated by a Retrieval‑Augmented Generation (RAG) model. After upgrading the model, inserts and similarity queries start failing with errors such as:


ERROR: column "embedding" is of type vector(1536) but expression is of type vector(768)
pgvector: dimension mismatch: expected 1536, got 768
ERROR: cannot cast type vector to vector
Query failed: distance calculation requires vectors of the same dimension (got 1536 vs 1024)

Symptoms observed in production:

  • Batch upserts aborted mid‑run, leaving partially populated rows.
  • Nearest‑neighbor queries return empty result sets even though rows exist.
  • CPU spikes on the re‑index job because the index build aborts on the first mismatched row.

These failures match the real incidents documented in the community (pgvector issue #124, LangChain issue #3521, Stack Overflow question 78543210).

Root Cause Analysis

The mismatch originates from three tightly coupled components:

  1. Embedding model output shape: The original OpenAI text-embedding-ada-002 model emits 1536‑dimensional vectors. A newer model (e.g., text-embedding-3-large) emits 768‑dimensional vectors.
  2. pgvector column definition: The table column was created as vector(1536) (see pgvector documentation).
  3. Schema migration process: CI/CD pipelines that run ALTER TABLE to adjust the column type were either skipped or applied only to a subset of environments, leaving the production schema unchanged.

When the ingestion script sends a vector(768) payload to a column expecting vector(1536), PostgreSQL raises the dimension mismatch error because pgvector enforces the dimension constraint at write time (PostgreSQL ALTER TABLE docs). The same problem occurs in reverse when the column is narrowed but older rows still contain the larger dimension.

Investigation and Debugging Steps

Below is a reproducible debugging workflow that mirrors the steps taken during the incident.

1. Verify column definition

SELECT column_name, udt_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'rag_embeddings';

Typical output:


 column_name | udt_name | character_maximum_length
-------------+----------+-------------------------
 id          | int4     |
 embedding   | vector   | 1536
 metadata    | jsonb    |

2. Inspect a sample row

SELECT id, array_length(embedding, 1) AS dim
FROM rag_embeddings
WHERE id = 42;

Output (shows the offending dimension):


 id | dim
----+-----
 42 | 768

3. Capture the failing insert log

2026-06-20 14:32:11.842 UTC [12345] ERROR:  column "embedding" is of type vector(1536) but expression is of type vector(768)  (SQLSTATE 42804)
2026-06-20 14:32:11.842 UTC [12345] CONTEXT:  PL/pgSQL function insert_embedding() line 12 at SQL statement

4. Check the model version used by the ingestion service

import os
print(os.getenv("EMBEDDING_MODEL"))
# Output: text-embedding-3-large

5. Verify pgvector index requirements

SELECT indexdef
FROM pg_indexes
WHERE tablename = 'rag_embeddings';

Typical definition for an HNSW index (pgvector index docs):


CREATE INDEX rag_embeddings_embedding_idx ON rag_embeddings USING hnsw (embedding) WITH (dim = 1536);

6. Confirm that the index dimension matches the column

SELECT indrelid::regclass AS table,
       indexrelid::regclass AS index,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'rag_embeddings'::regclass;

If the index still expects 1536 while the column is still 1536, the index will reject the 768‑dim vectors as well.

Solution: Align Model Output, Column Definition, and Indexes

The fix consists of three coordinated actions:

  1. Update the vector column dimension to match the new model.
  2. Re‑create or alter the vector index to use the new dimension.
  3. Back‑fill existing rows that still contain the old dimension (optional but recommended).

Step 1 – Alter the column safely

PostgreSQL allows changing the vector type with ALTER TABLE ... ALTER COLUMN ... TYPE. To avoid data loss, perform the change in a transaction and verify the conversion.

BEGIN;

-- 1. Add a temporary column with the new dimension
ALTER TABLE rag_embeddings
  ADD COLUMN embedding_new vector(768);

-- 2. Copy and cast existing vectors (PostgreSQL will truncate or pad automatically)
UPDATE rag_embeddings
SET embedding_new = embedding::vector(768);

-- 3. Verify conversion
SELECT COUNT(*) FROM rag_embeddings WHERE array_length(embedding_new,1) <> 768;

COMMIT;

If the count is zero, replace the original column:

BEGIN;

ALTER TABLE rag_embeddings DROP COLUMN embedding;
ALTER TABLE rag_embeddings RENAME COLUMN embedding_new TO embedding;

COMMIT;

Reference: PostgreSQL ALTER TABLE documentation.

Step 2 – Recreate the vector index

pgvector indexes store the dimension in their metadata. After changing the column, drop and recreate the index:

DROP INDEX IF EXISTS rag_embeddings_embedding_idx;

CREATE INDEX rag_embeddings_embedding_idx
  ON rag_embeddings USING hnsw (embedding) WITH (dim = 768);

If you prefer IVFFlat, adjust the WITH (lists = 100) clause accordingly.

Step 3 – Back‑fill older rows (optional)

Rows that still contain 1536‑dim vectors must be regenerated. A typical batch job looks like:

import psycopg2
from openai import OpenAI

client = OpenAI()
conn = psycopg2.connect(dsn="...")

with conn.cursor() as cur:
    cur.execute("SELECT id, embedding FROM rag_embeddings WHERE array_length(embedding,1)=1536")
    for row in cur.fetchall():
        id, old_vec = row
        # Re‑encode the original text (metadata must contain the source)
        cur.execute("SELECT metadata->>'text' FROM rag_embeddings WHERE id = %s", (id,))
        text = cur.fetchone()[0]
        new_vec = client.embeddings.create(input=text, model="text-embedding-3-large").data[0].embedding
        cur.execute(
            "UPDATE rag_embeddings SET embedding = %s WHERE id = %s",
            (new_vec, id)
        )
    conn.commit()

Verification

After applying the migration, run the following checks:

1. Schema sanity

SELECT column_name, udt_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'rag_embeddings' AND column_name = 'embedding';

Expected output:


 column_name | udt_name | character_maximum_length
-------------+----------+-------------------------
 embedding   | vector   | 768

2. Index health

SELECT pg_get_indexdef(indexrelid) FROM pg_index
WHERE indrelid = 'rag_embeddings'::regclass;

Should contain WITH (dim = 768).

3. Insert a test vector

INSERT INTO rag_embeddings (id, embedding, metadata)
VALUES (9999, ARRAY[0.1]::vector(768), '{"source":"test"}'::jsonb);

No error should be raised.

4. Run a similarity query

SELECT id, embedding <-> ARRAY[0.1]::vector(768) AS distance
FROM rag_embeddings
ORDER BY distance ASC
LIMIT 5;

The query should return rows with finite distances, confirming that the distance operator works with the new dimension.

Prevention and Operational Guardrails

  • Versioned schema migrations: Store the expected embedding dimension in a migration file (e.g., V20240620_01_add_embedding_dim.sql) and enforce that CI pipelines apply it whenever the model version changes.
  • Model‑to‑schema contract: Keep a single source of truth (environment variable or config file) that defines both EMBEDDING_MODEL and EMBEDDING_DIM. Application code should assert that len(vector) == EMBEDDING_DIM before issuing INSERT/UPDATE statements.
  • Health check endpoint: Expose a lightweight endpoint that runs SELECT 1 FROM rag_embeddings LIMIT 1 and also verifies that pg_typeof(embedding) matches the expected dimension.
  • Monitoring: Add a Prometheus gauge embedding_dimension_mismatch_total that increments on any pgvector: dimension mismatch log line. Alert on a non‑zero count within a 5‑minute window.
  • Rolling re‑index: When changing dimensions, drop the index, perform the column migration, then rebuild the index in a separate maintenance window to avoid query latency spikes.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

  1. Why does the error say “cannot cast type vector to vector”?
    pgvector treats each dimension as part of the type signature. A cast from vector(768) to vector(1536) is not implicit; you must explicitly convert or recreate the column as shown above.
  2. Can I store multiple model dimensions in the same table?
    Only by using a generic jsonb field for the raw vector or by creating separate tables per model version. Mixing dimensions in a single vector column violates the index contract and leads to the mismatches observed.
  3. What if I need to keep historical embeddings from the old model?
    Add a second column, e.g., embedding_legacy vector(1536), and migrate old rows into it before shrinking the primary embedding column. Queries can then choose the appropriate column based on the model version stored in metadata.
  4. Is there a way to automate the dimension check in Python?
    Yes. Use the pgvector Python client to fetch the column definition and compare it to len(embedding) before each insert:
import psycopg2

def get_embedding_dim(conn):
    with conn.cursor() as cur:
        cur.execute(
            "SELECT character_maximum_length FROM information_schema.columns "
            "WHERE table_name='rag_embeddings' AND column_name='embedding'")
        return cur.fetchone()[0]

def insert_embedding(conn, vec, meta):
    dim = get_embedding_dim(conn)
    if len(vec) != dim:
        raise ValueError(f'Embedding dimension {len(vec)} does not match column dimension {dim}')
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO rag_embeddings (embedding, metadata) VALUES (%s, %s)",
            (vec, meta))
    conn.commit()
  • Do IVFFlat and HNSW indexes require different migration steps?
    Both store the dimension in the index metadata, so the DROP INDEX … CREATE INDEX … WITH (dim = …) pattern applies to either. The only difference is the index method name (ivfflat vs hnsw).