pgvector: Embedding Column on the Table or a Separate Embeddings Table?

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Put the embedding where the row it describes is written least. A document that is chunked needs a chunks table anyway; an embedding that will be regenerated by a second model belongs in its own table keyed by chunk and model, with an untyped vector column. A vector column on a hot parent row costs a 6 KB HNSW entry on every non-HOT edit of any other column: 5,000 title edits took a 31 MB index to 67 MB.

An embedding belongs where the row it describes is written least, and for most RAG schemas that is not the parent table. A document that gets chunked needs a chunks table whatever you decide, and an embedding you expect to regenerate with a newer model belongs in its own table keyed by chunk and model. The one case for a vector column on the parent row is one vector per entity, on a row that nothing else updates.

The reason is what an HNSW index costs on a row that changes for other reasons. We measured it on PostgreSQL 17.11 with pgvector 0.8.7: a documents table with a vector(1536) column and an HNSW index, 5,000 rows loaded in one INSERT, then one UPDATE that appended a few words to every title and never touched the vector. PostgreSQL made zero HOT updates, the heap went from 2.8 MB to 5.7 MB, and the HNSW index went from 31 MB to 67 MB. VACUUM left it at 67 MB. The control was a chunk_embeddings table holding 20,000 vectors of the same size under an 81 MB HNSW index: the same title edit on its parent rows cannot touch it, and did not.

Every non-HOT update of a row with an HNSW index adds a new, full-size entry to the index, and after a bulk load at the default fillfactor nothing is HOT.

The zero is not the vector's doing. A copy of the table with no vector column at all, loaded and updated the same way, also made 0 HOT updates out of 5,000: INSERT ... SELECT packs each page to the default fillfactor of 100, so there is no room on the page for a new row version, and a HOT update needs exactly that. The title column had no index either, so the only indexes in play were the primary key and the HNSW index. What the vector decides is the price of each non-HOT update. A vector(1536) value is 6,148 bytes (pg_column_size on the measured table), pgvector declares the type with external storage, so PostgreSQL keeps it out of line and uncompressed in the TOAST table (2.8 MB of heap against 39 MB of TOAST for the 5,000 rows), and the HNSW entry for the new row version carries the whole vector again. That is why one pass of title edits roughly doubled the index.

Lowering the fillfactor helps but does not fix it. A copy of the table created WITH (fillfactor = 90) made 834 of the 5,000 updates HOT: better, and still 4,166 new index entries for an edit that did not change a single vector.

After VACUUM the index stays at 67 MB: PostgreSQL never shrinks an index file, and pgvector reuses the freed slots for later inserts, so a second pass of title edits after the vacuum left it at 67 MB and a third took it to 72 MB. Until vacuum runs, pgvector's README warns that dead elements can cut the number of rows a search returns, and it says vacuuming an HNSW index "can take a while", recommending REINDEX INDEX CONCURRENTLY first. A parent table that is edited every day carries an index that is always part dead and never smaller than its worst day.

Yes when the text is chunked or when you will ever re-embed; a column on the row only when there is exactly one vector per entity and nothing else on that row is updated.

Three layouts cover the real cases:

The middle column is the usual answer. A RAG pipeline splits a document into chunks sized for the model's context, so the chunk row already exists with its position and text, and the vector sits beside the text it encodes. The chunk row changes only when the text changes, which is when the vector must change anyway.

The right column earns its second join when models change. ALTER TABLE documents ALTER COLUMN embedding TYPE vector(768) fails the moment a row holds a 1,536-value vector: PostgreSQL answers ERROR: expected 768 dimensions, not 1536. A new column per model is the same ALTER TABLE on the table that holds the text, then a backfill UPDATE of every row, which by the measurement above adds a new entry to the existing HNSW index for every row it touches, then two indexes on one row and a cleanup migration later. A chunk_embeddings table keyed by (chunk_id, model) takes the new model as new rows and nothing in the tables that hold the text changes. To hold two sizes at once the column is declared as an untyped vector, which pgvector's README recommends for mixed dimensions, and each model gets its own partial index over a cast:

CREATE TABLE chunk_embeddings (
    chunk_id  bigint NOT NULL REFERENCES chunks (id) ON DELETE CASCADE,
    model     text   NOT NULL,
    embedding vector NOT NULL,
    PRIMARY KEY (chunk_id, model)
);
CREATE INDEX chunk_embeddings_small ON chunk_embeddings
    USING hnsw ((embedding::vector(1536)) vector_cosine_ops)
    WHERE (model = 'text-embedding-3-small');
CREATE INDEX chunk_embeddings_nomic ON chunk_embeddings
    USING hnsw ((embedding::vector(768)) vector_cosine_ops)
    WHERE (model = 'nomic-embed-text');

Measured on the same server: 200 chunks stored under both models, 1,536 and 768 values side by side in one column, both indexes built. Queries use the same cast and the same WHERE model = ... so the planner picks the right partial index, and the old model's rows are deleted when the switch is done. A model over 2,000 dimensions needs a halfvec cast instead, which is pgvector's index limit for vector.

The left column is for entity embeddings: one vector per product, per user profile, per support ticket, that changes only when the entity's own text changes, on a row nothing else updates. If the table also carries a view_count or a last_seen_at, it is no longer that case, and the index growth above is what you get.

Keep the column you filter on in the same table as the HNSW index, because pgvector's remedies for a filtered search, a partial index per value or an exact B-tree search, only work when the column is on the indexed table.

pgvector's README is direct about what happens otherwise: with an approximate index, "filtering is applied after the index is scanned", and with the default hnsw.ef_search of 40 a condition that matches 10% of rows leaves about 4 candidates. A tenant filter that lives two joins away on documents is in that position with no index to help it. The README's own remedies are a partial index per filter value when there are few of them, a plain B-tree on the filter column for exact search when the condition is selective, and from pgvector 0.8 iterative index scans (SET hnsw.iterative_scan = strict_order) that keep scanning until enough rows pass, which work with a join too.

For the chunk_embeddings layout that means copying tenant_id onto the embeddings row. A CHECK cannot see another table, so the honest way to keep the copy true is a composite foreign key: UNIQUE (id, tenant_id) on chunks, then FOREIGN KEY (chunk_id, tenant_id) REFERENCES chunks (id, tenant_id) ON UPDATE CASCADE on the embeddings table. For the chunks layout it usually means document_id is already there and tenant_id joins in from documents; put the filter on chunks too, the same way, if the tenant filter is on every query.

Schemity draws a pgvector column with its dimension and an HNSW index with its operator class and options exactly as PostgreSQL stores them, and flags an index PostgreSQL will refuse before the migration runs.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Connect it to a database with pgvector installed, or import the pg_dump of one, and the layout above is drawn with what pgvector actually stores: embedding VECTOR on chunk_embeddings with no dimension, VECTOR(1536) where one was declared, the composite primary key on (chunk_id, model), and each HNSW index with its cast expression, its WHERE condition, its operator class and its WITH (m=16, ef_construction=64) in the table's Indexes list, read from reloptions rather than typed in.

That matters when you design the next one on a schema you did not write. A vector column with no dimension looks fine on a canvas and builds no plain index: pgvector refuses CREATE INDEX ... USING hnsw on it with column does not have dimensions, and refuses it again above 2,000 dimensions for vector and 4,000 for halfvec. Schema lint reports both as "HNSW index on a vector column PostgreSQL will refuse" before the migration runs.

The change preview is what shows the model-switch mistake: changing vector(1536) to vector(768) on a column that holds rows is drawn as an altered column, with the planned ALTER COLUMN ... TYPE statement for you to read before it runs, so the chunk_embeddings rows arrive as a design decision instead of a failed deploy.

Adding a plain vector index is one dialog: pick HNSW, the operator class pgvector needs (there is no default one, CREATE INDEX ... USING hnsw (embedding) fails with data type vector has no default operator class for access method "hnsw"), and the options, and the written SQL is CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=64). An agent working over MCP creates the same index with create_index, operator class included, and is declined if it leaves the class out.

The field editor labels the length of a VECTOR column as its dimensions, which is the number the whole decision above turns on.

The numbers above came from a 5,000-row table on one laptop. Scale them by your row count and your edit rate, and the choice usually makes itself: the same way an integer key that outgrows its type forces a rewrite you would rather have planned, an embedding on the wrong row forces an index rebuild you would rather never need.