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. Why a vector column makes every update of the row expensive 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. Should pgvector embeddings be in a separate table? 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: Vector column on the parent row chunks table with a vector column Separate chunk_embeddings (chunk_id, model) One document, many vectors Impossible: one row holds one vector Natural: one row per chunk Natural Second embedding model A second column, backfilled by an UPDATE of every row A second column per chunk, same backfill New rows under a new model value Edits to the document's other columns New HNSW entry per non-HOT edit (measured above) Only when the chunk row itself changes Never A model with a different dimension ALTER COLUMN TYPE refused once rows exist Same An untyped vector column and one cast partial index per model Filter by tenant or document On the same table, where pgvector's remedies work Needs document_id on the chunk, or a join Needs the filter column copied onto this table, or a join Extra joins on read None One, chunk to document Two, embedding to chunk to document 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 chu