The reflex when adding semantic search is to reach for a dedicated vector database. For most applications that adds a second datastore to operate, back up and keep consistent, in exchange for capabilities Postgres already has. Start in the database you already run.
Model chunks as their own table
The unit you search is a chunk, not a document, and chunks need their own identity so you can re-chunk without losing document metadata.
CREATE TABLE document (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL REFERENCES tenant(id),
source_uri text NOT NULL,
title text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (tenant_id, source_uri)
);
CREATE TABLE document_chunk (
id bigserial PRIMARY KEY,
document_id bigint NOT NULL REFERENCES document(id) ON DELETE CASCADE,
tenant_id bigint NOT NULL REFERENCES tenant(id), -- denormalised on purpose
ordinal int NOT NULL,
content text NOT NULL,
token_count int NOT NULL,
embedding vector(1536),
model text NOT NULL, -- which model produced this vector
UNIQUE (document_id, ordinal)
);`tenant_id` is duplicated onto the chunk deliberately. Filtering by tenant must happen in the same query as the search; joining to `document` to discover the tenant means the planner may search across tenants first, which is both slower and a data-leak shape.
Filter before you search
-- Tenant filter and search in one statement
SELECT c.id, c.content, c.embedding <=> $1 AS distance
FROM document_chunk c
WHERE c.tenant_id = $2
AND c.model = $3
ORDER BY c.embedding <=> $1
LIMIT 10;
-- Make the filter cheap
CREATE INDEX ON document_chunk (tenant_id, model);Approximate indexes and filters interact badly
HNSW and IVFFlat are approximate: they search a subset of the graph. Apply a highly selective filter and the index may return far fewer than `LIMIT` rows, because most candidates it examined were filtered out. For small per-tenant corpora, an exact scan is often both faster and correct — measure before adding the index.
Choosing an index
- No index — exact and correct. Fine into the low tens of thousands of rows per tenant, which is more applications than people assume.
- HNSW — high recall, fast queries, slower builds and more memory. The usual production default.
- IVFFlat — cheaper to build, needs a representative sample to train, and requires tuning `lists` and `probes` to hit acceptable recall.
Plan for re-embedding on day one
Embedding models change. Vectors from one model cannot be compared with another's, so an upgrade means re-embedding the whole corpus. The `model` column above makes this survivable: write new vectors alongside the old ones, verify retrieval quality against your evals, then delete the old rows.
-- Migrate without downtime: both models coexist, queries pin one
ALTER TABLE document_chunk DROP CONSTRAINT document_chunk_document_id_ordinal_key;
CREATE UNIQUE INDEX ON document_chunk (document_id, ordinal, model);Keep the source of truth outside the index
Chunks are derived data. Store enough — `source_uri`, `ordinal`, `updated_at` — to rebuild the entire index from the originals, and treat that rebuild as a routine operation rather than an emergency. Chunking strategy will change more often than you expect.
Frequently Asked Questions
When should I move to a dedicated vector database?
When you have measured a limit: index build times that block deploys, memory pressure from HNSW, or a need for features like distributed sharding. Not before.
Should the embedding column be nullable?
Yes, at least during ingestion. Rows exist before they are embedded, and a nullable column lets you insert content and backfill vectors asynchronously.
How do I handle deletes?
ON DELETE CASCADE from document to chunk keeps the index consistent automatically. The trap is soft deletes — a soft-deleted document whose chunks stay searchable is a straightforward data-leak bug.
References
- pgvector — open-source vector similarity search for Postgres — pgvector
- PostgreSQL Documentation — Indexes — PostgreSQL
About Jishu Labs
Jishu Labs is a software development company founded in 2016. We build custom software, AI/ML systems, and full-stack web and mobile applications for clients, and we make eight AI tools for software teams.