Designing Vector Tables for RAG with pgvector: Dimensions, Indexes, and Filtering

Why Store Vectors in PostgreSQL?

RAG (retrieval-augmented generation) finds document fragments that are semantically close to a question and passes them to an LLM along with the question. To compute "semantically close," the document fragments are converted into numeric vectors (embeddings) and stored.

pgvector is an open-source extension that lets you store vectors in PostgreSQL and search them by similarity (0.8.7 was released on October 1, 2026). Keeping vectors in PostgreSQL instead of a separate vector database has an advantage: you can handle them in the same transaction, with the same joins as your existing data, such as document permissions, tenants, and modification times. By the same token, table design determines search quality and performance.

1. A Structure That Separates Documents and Chunks

Embeddings are usually created not for a whole document but per chunk, a piece cut to a set length. Documents and chunks are split into a 1:N relationship.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT      NOT NULL,
  title       TEXT        NOT NULL,
  source_url  TEXT,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE document_chunks (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id  BIGINT NOT NULL REFERENCES documents (id) ON DELETE CASCADE,
  tenant_id    BIGINT NOT NULL,
  chunk_index  INT    NOT NULL,
  content      TEXT   NOT NULL,
  embedding    vector(1536) NOT NULL,
  model        TEXT   NOT NULL,
  embedded_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (document_id, chunk_index)
);

Key design points:

• The dimension of vector(1536) must match the output dimension of the embedding model. Vectors of a different dimension cannot go into the same column
• The model column: vectors produced by different models can't be compared. When you change models, this column tells you which rows need to be regenerated
• Put tenant_id on chunks too: searches almost always filter by tenant, so you can filter directly without a join, and you can also use it for the partial indexes and partitioning described below
• ON DELETE CASCADE: deleting a document also deletes its chunks and vectors, so deleted documents don't remain in search results
• Comparing embedded_at with the document's updated_at lets you find chunks that haven't been re-embedded since the source text changed

2. Vector Types and Dimension Limits

pgvector provides four types, and the dimensions you can store differ from the dimensions you can index. vector and halfvec can store up to 16,000 dimensions, but their index limits are lower.

If your embeddings have more than 2,000 dimensions, you can't create an index directly on the vector column. In that case, use an expression index that casts to half precision. The query must also order by the same expression as the index for the index to be used.

-- Index 3072-dimension embeddings at half precision
CREATE INDEX ON document_chunks_large
  USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

SELECT id, content
FROM document_chunks_large
ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072)
LIMIT 5;

3. Distance Operators and HNSW Indexes

pgvector's distance operators are <-> (L2), <#> (negative inner product), <=> (cosine distance), <+> (L1), and, for binary vectors, <~> (Hamming) and <%> (Jaccard). Choose the distance your embedding model recommends, and match the index's operator class (opclass) to the operator in the query. The index is used when ORDER BY sorts by the distance operator in ascending order.

There are two index types.

• HNSW: a multilayer graph. It has good search performance but is slower to build and uses more memory. It doesn't need a training step
• IVFFlat: divides vectors into lists. It is faster to build and uses less memory, but its search performance is lower than HNSW's

-- HNSW index for cosine distance (defaults: m = 16, ef_construction = 64)
CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops);

-- Number of candidates during search (default 40). Raising it: recall↑ speed↓
SET hnsw.ef_search = 100;

SELECT id, document_id, content
FROM document_chunks
WHERE tenant_id = 42
ORDER BY embedding <=> $1
LIMIT 5;

HNSW indexes build much faster when the graph fits into maintenance_work_mem. When building an index on a large table, review this setting as well.

4. Filtering: The Too-Few-Results Problem and Its Solutions

With approximate indexes, filters are applied after the index is scanned. In an example from the pgvector documentation, if 10% of all rows match the condition and hnsw.ef_search is at its default of 40, only 4 rows remain on average. That means even with LIMIT 5, you may get fewer than 5 results.

Pick a solution based on your situation.

Iterative index scans (since 0.8.0). The index is scanned further until enough results have been collected.

SET hnsw.iterative_scan = strict_order;   -- Keeps the exact distance order
-- or
SET hnsw.iterative_scan = relaxed_order;  -- Order may be slightly off, but recall is better

A regular index on the filter column. If the condition selects only a small fraction of all rows, it is better to filter first with a B-tree index.

CREATE INDEX ON document_chunks (tenant_id);

Partial indexes when only a few values are frequently used; partitioning when there are many values.

-- Vector index dedicated to a specific tenant
CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops)
  WHERE (tenant_id = 42);

When there are many distinct values, the pgvector documentation recommends partitioning by the filter column, as in PARTITION BY LIST (tenant_id).

5. Operations Checklist

• When changing embedding models: select the target rows using the model column, build the new vectors in a different column or table, and then switch over, so search is never interrupted
• When the source text changes: delete and regenerate that document's chunks, or periodically find and refresh chunks where embedded_at < updated_at
• Check dimension limits: columns that need an index must stay within the index dimension limit for their type
• Measure search quality: ef_search and iterative scan settings trade accuracy against speed. Measure recall with real queries before deciding

Designing with Erdmates

The core of a RAG schema is the 1:N document–chunk relationship, foreign keys that cascade deletes, and the tenant key used for filtering. If you draw these relationships as an ERD in Erdmates first, your team can decide which table the vector column belongs in and which columns to filter on while looking at the same picture.

Conclusion

In pgvector design, what ultimately decides performance and quality is ordinary database design: separating documents and chunks, recording the model and update times, choosing types that fit the dimension limits, and setting an index strategy (iterative scans, partial indexes, partitioning) that suits your filter conditions. With these four in place, you can manage vector search at the same level as your existing data.