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

> 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.

Source: https://schemity.com/blog/pgvector-embedding-column-vs-separate-table/

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:

```sql
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.

## Where does the tenant filter go?

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.

## Designing the three tables in Schemity

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.

![The three tables read from a live pgvector database in Schemity: documents with embedding VECTOR(1536) marked nullable and one index, chunks with its composite unique on document_id and position, and chunk_embeddings keyed by chunk_id and model with a bare VECTOR column and two indexes](https://schemity.com/images/blog/pgvector-embedding-column-vs-separate-table/canvas-three-tables.webp)

![The Indexes list of chunk_embeddings in Schemity's entity editor: two hnsw indexes, each with its cast expression ((embedding)::vector(768)) or ((embedding)::vector(1536)), its vector_cosine_ops operator class and its WHERE model = ... condition, read from the live database](https://schemity.com/images/blog/pgvector-embedding-column-vs-separate-table/indexes-list-cast-partial.webp)

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](https://schemity.com/doc/schema-lint/) reports both as "HNSW index on a vector column PostgreSQL will refuse" before the migration runs.

![Schemity's lint drawer with one finding under Fails at runtime: chunk_embeddings.embedding, index chunk_embeddings_embedding_idx, HNSW index on a vector column PostgreSQL will refuse, the column has no dimension; the embedding row is marked red on the chunk_embeddings table](https://schemity.com/images/blog/pgvector-embedding-column-vs-separate-table/lint-vector-no-dimension.webp) The [change preview](https://schemity.com/doc/impact-analysis/) 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.

![Schemity's index dialog for documents_embedding_hnsw: Fields embedding, Type HNSW, Opclass vector_cosine_ops, Options m=16, ef_construction=64, Unique unticked](https://schemity.com/images/blog/pgvector-embedding-column-vs-separate-table/index-dialog-hnsw.webp) The [field editor](https://schemity.com/doc/tables-and-fields/) 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](https://schemity.com/blog/postgres-change-int-primary-key-to-bigint/) forces a rewrite you would rather have planned, an embedding on the wrong row forces an index rebuild you would rather never need.

## Frequently asked questions

### Does updating a non-vector column add entries to the HNSW index in pgvector?

Yes, for every update that is not HOT, and after a bulk load at the default `fillfactor` of 100 none of them are. Measured on PostgreSQL 17 with pgvector 0.8.7: 5,000 title edits on a table with an unindexed title, 0 HOT updates, HNSW index 31 MB to 67 MB. A table created `WITH (fillfactor = 90)` made 834 of the 5,000 HOT. The vector does not decide whether an update is HOT; it decides what each non-HOT update costs.

### Can I change the dimension of a pgvector column with ALTER COLUMN TYPE?

Only while the column is empty or every stored vector already has the new size. With rows present, `ALTER TABLE documents ALTER COLUMN embedding TYPE vector(768)` fails with `ERROR: expected 768 dimensions, not 1536`. To hold two models at once, declare the column as an untyped `vector` and build one partial HNSW index per model over a cast, `(embedding::vector(768))`.

### Where should the tenant filter go for a pgvector search?

On the same table as the HNSW index. pgvector applies a `WHERE` clause after the approximate index scan, so with the default `hnsw.ef_search` of 40 a filter matching 10% of rows leaves about 4 candidates, and its remedies, a partial index per value or an exact B-tree search, need the column on the indexed table. A separate embeddings table should carry the filter column too, or enable iterative index scans in pgvector 0.8.