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…
The debate over whether to store embeddings in a separate table or as a column on the parent row in a database involves trade-offs in performance and flexibility. When an embedding is regenerated by a newer model, it is best to store it in its own table keyed by chunk and model. This allows for efficient updates without impacting other rows in the parent table.
For documents that are chunked, it is recommended to create a chunks table that holds the embedding alongside the chunk's position and text. This way, the embedding only changes when the text changes, which is when the embedding needs to change anyway.
If a single vector is associated with one entity and nothing else on that row is updated, then a vector column on the parent row can be used. This reduces the number of indexes involved and avoids unnecessary overhead from maintaining a separate table.
In general, it is advisable to store embeddings in a separate table when chunking or when re-embedding is expected. However, if there is exactly one vector per entity and no other updates occur on that row, storing the embedding as a column on the parent row is the optimal choice.
Three main layouts cover the various cases:
1. A vector column on the parent row along with a chunks table for chunked documents.
2. A separate table specifically for chunk_embeddings, with a vector column keyed by chunk_id and model.
3. Individual tables for each model, with untyped vector columns and partial indexes for mixed dimensions.
The choice between these layouts depends on the specific requirements of the RAG pipeline and the frequency of model changes.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
