A Documents Table That Survives Re-Indexing

You will re-chunk and re-embed your corpus several times: a better splitter, a cheaper model, a bug in the PDF extractor. A schema that treats those as exceptional turns each one into a migration with downtime. A schema keyed on content hashes turns them into a job you can run twice with no ill effect, and stop halfway with no cleanup.

What re-indexing does to a naive schema

The schema everybody writes first is one table: chunks, with the text and the vector. It works until the first reprocessing run, and then all of the following are true at once.

• You cannot tell which chunks came from which version of the extractor, so you cannot roll back a bad one without reprocessing everything.
• Re-running a partially failed job either duplicates the chunks it already wrote or requires you to delete and start over — and deleting first means search is degraded for the duration.
• A document that has not changed is re-embedded anyway, because nothing records that its content is identical. On a large corpus that is the dominant cost of the whole exercise.
• Two embedding models cannot coexist, so switching models means an outage rather than a shadow index and a cutover.

Each of those is fixed by the same thing: identify content by its hash, and separate the four entities that the single table was conflating.

The schema

-- 1. The document as an identity. Stable across every revision of
--    its content: this id is what the rest of your application uses.
CREATE TABLE documents (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL,
  source_uri   text NOT NULL,          -- s3://…, https://…, file path
  title        text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  deleted_at   timestamptz,
  UNIQUE (tenant_id, source_uri)
);

-- 2. A concrete revision of that document's bytes. Immutable.
CREATE TABLE document_versions (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id     uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  content_sha256  bytea NOT NULL,      -- of the extracted text, not the file
  file_sha256     bytea NOT NULL,      -- of the original bytes
  extractor       text NOT NULL,       -- 'pdfium-1.4', 'html2text-2026.3'
  byte_length     int  NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),
  -- The same text extracted by the same extractor is the same version.
  UNIQUE (document_id, content_sha256, extractor)
);

-- 3. Chunks of a version, under a named chunking strategy. Immutable.
CREATE TABLE chunks (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  version_id    bigint NOT NULL REFERENCES document_versions(id) ON DELETE CASCADE,
  tenant_id     uuid NOT NULL,          -- denormalised: see below
  chunker       text NOT NULL,          -- 'recursive-800-120'
  ord           int  NOT NULL,
  content       text NOT NULL,
  content_sha256 bytea NOT NULL,
  token_count   int  NOT NULL,
  UNIQUE (version_id, chunker, ord)
);

-- 4. An embedding of a chunk under one model. Many per chunk.
CREATE TABLE chunk_embeddings (
  chunk_id    bigint NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  model       text   NOT NULL,          -- 'text-embedding-3-small'
  dim         int    NOT NULL,
  embedding   vector(1536) NOT NULL,
  tenant_id   uuid   NOT NULL,          -- denormalised: see below
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (chunk_id, model)
);

CREATE INDEX chunk_embeddings_hnsw
  ON chunk_embeddings USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

CREATE INDEX chunks_version_idx ON chunks (version_id);
CREATE INDEX versions_doc_idx   ON document_versions (document_id, created_at DESC);

tenant_id is denormalised onto chunks and chunk_embeddings deliberately. Normalisation would say it belongs only on documents, and normalisation is wrong here for two reasons: a retrieval query must filter by tenant without joining three tables, and a row-level security policy has to be expressible as a predicate on the table it protects. A policy that requires a join is a policy that is slow and easy to get wrong.

Note that the vector lives in its own table rather than as a column on chunks. That is what allows two models to coexist, and it also keeps the wide vector out of the table you scan when you are fetching text — a vector(1536) is toasted out of line, and separating it means listing chunks does not touch it at all.

Three hashes, three jobs

The third one is where the money is. Re-chunking a corpus after a small edit produces mostly identical chunks; matching them by hash lets you copy the existing vector instead of paying for an embedding call. On a corpus where documents are edited rather than replaced, this turns a full re-embed into a marginal one.

-- Carry embeddings across to a new version wherever the chunk text
-- is byte-identical to a chunk of the previous version.
INSERT INTO chunk_embeddings (chunk_id, model, dim, embedding, tenant_id)
SELECT new_c.id, old_e.model, old_e.dim, old_e.embedding, new_c.tenant_id
FROM chunks new_c
JOIN chunks old_c
  ON old_c.content_sha256 = new_c.content_sha256
 AND old_c.chunker        = new_c.chunker
 AND old_c.version_id     = $old_version
JOIN chunk_embeddings old_e ON old_e.chunk_id = old_c.id
WHERE new_c.version_id = $new_version
ON CONFLICT (chunk_id, model) DO NOTHING;

The idempotent write path

Idempotent means: running the job twice produces the same state as running it once, and a job that dies halfway can simply be restarted. Every unique constraint in the schema above exists to make one step of this possible.

1. Upsert the document identity. Keyed on tenant and source URI, so re-ingesting the same source updates rather than duplicates.

   INSERT INTO documents (tenant_id, source_uri, title)
   VALUES ($1, $2, $3)
   ON CONFLICT (tenant_id, source_uri)
     DO UPDATE SET title = EXCLUDED.title
   RETURNING id;

2. Insert the version, or find the existing one. ON CONFLICT … DO NOTHING returns no rows when the version already exists, so the statement is written to return the id either way. If this returns an existing id, the document is unchanged and the whole rest of the pipeline can be skipped.

   WITH ins AS (
     INSERT INTO document_versions
       (document_id, content_sha256, file_sha256, extractor, byte_length)
     VALUES ($1, $2, $3, $4, $5)
     ON CONFLICT (document_id, content_sha256, extractor) DO NOTHING
     RETURNING id
   )
   SELECT id FROM ins
   UNION ALL
   SELECT id FROM document_versions
   WHERE document_id = $1 AND content_sha256 = $2 AND extractor = $4
   LIMIT 1;

3. Insert chunks. Unique on (version_id, chunker, ord), so a restart re-inserts the same rows and conflicts harmlessly.

   INSERT INTO chunks
     (version_id, tenant_id, chunker, ord, content, content_sha256, token_count)
   VALUES ($1, $2, $3, $4, $5, $6, $7)
   ON CONFLICT (version_id, chunker, ord) DO NOTHING;

4. Carry embeddings across with the statement in the previous section, then embed only what is left — which is a query, not a guess:

   SELECT c.id, c.content
   FROM chunks c
   LEFT JOIN chunk_embeddings e
     ON e.chunk_id = c.id AND e.model = $model
   WHERE c.version_id = $new_version
     AND e.chunk_id IS NULL
   ORDER BY c.id
   LIMIT 256;

5. Flip the pointer. Retrieval reads the newest complete version. Add a completed_at column to document_versions, set it in the same transaction as the last batch of embeddings, and have the read path select the newest version with a non-null completed_at. A half-finished re-index is then invisible rather than partially visible.

Every step is a statement that can be run any number of times. That is the whole design goal, and it is why the job needs no cleanup logic and no distributed lock — two workers running it concurrently produce the same result as one, more slowly.

Naming the extractor and the chunker

Three columns in that schema hold free-text identifiers — extractor, chunker, model — and they carry more weight than they look. Each one is part of a unique constraint, which means the value decides whether a reprocessing run is treated as the same work or as new work.

The rule: the string must change whenever the output could change. If you upgrade the PDF library and the extracted text differs by a single character, the version in extractor must change too, or you get a unique-constraint conflict against the old version and your improved extraction is silently discarded as a duplicate. Conversely, if the string changes on every deploy — because somebody put a git hash in it — then every deploy reprocesses your entire corpus.

extractor: 'pdfium-1.4'          -- library plus its version
           'html2text-2026.3'
           'ocr-tesseract-5.3-eng'  -- including the language pack, which
                                    -- changes the output

chunker:   'recursive-800-120'      -- strategy, size, overlap
           'semantic-p95-1200'
           'markdown-heading'

model:     'text-embedding-3-small' -- provider's identifier, verbatim

Encode the parameters, not just the name. recursive tells you nothing a year later; recursive-800-120 tells you the target size and the overlap, which is exactly what you need to know when comparing two chunking runs and exactly what nobody writes down otherwise. The strategies these names refer to are covered in text chunking strategies; the point here is that whichever you pick becomes part of your primary key.

Add a check constraint if you want the discipline enforced rather than remembered — a pattern match requiring a version suffix costs nothing and catches the deploy where somebody wrote pdfium without one. The failure it prevents is subtle and slow to diagnose: a corpus where half the chunks came from one extractor version and half from another, with no way to tell which is which, and therefore no way to reprocess only the affected half.

Switching embedding models without downtime

Because chunk_embeddings is keyed on (chunk_id, model), a second model is additional rows rather than a migration. The procedure:

1. Backfill the new model in batches, writing to the same table with a different model value. Search continues on the old model throughout, at full quality.
2. Build a partial HNSW index for the new model only, so the two do not share a graph: CREATE INDEX CONCURRENTLY … WHERE model = 'text-embedding-3-large'.
3. Evaluate both against the same query set before cutting over. This is the step that gets skipped, and choosing an embedding model is where the criteria live.
4. Change the model constant in the read path. That is the cutover: one deploy, instantly reversible.
5. Delete the old rows a week later, once you are confident. DELETE FROM chunk_embeddings WHERE model = $old, in batches, then read deletion that reaches the vector index before assuming the space came back.

The queries this shape makes cheap

-- Retrieval: filter to the current version, order by distance.
SELECT c.id, c.content, e.embedding <=> $qvec AS distance
FROM chunk_embeddings e
JOIN chunks c ON c.id = e.chunk_id
JOIN document_versions v ON v.id = c.version_id
JOIN documents d ON d.id = v.document_id
WHERE e.model = $model
  AND e.tenant_id = $tenant
  AND d.deleted_at IS NULL
  AND v.completed_at IS NOT NULL
ORDER BY e.embedding <=> $qvec
LIMIT 10;

-- How much of a re-index remains, as a number you can put on a dashboard.
SELECT count(*) FILTER (WHERE e.chunk_id IS NULL) AS pending,
       count(*)                                   AS total
FROM chunks c
LEFT JOIN chunk_embeddings e ON e.chunk_id = c.id AND e.model = $model
WHERE c.version_id = $new_version;

-- Storage by model, which is the number that decides when to drop the old one.
SELECT model, count(*), pg_size_pretty(sum(pg_column_size(embedding))::bigint)
FROM chunk_embeddings GROUP BY model;

That retrieval query has four joins, which will worry someone. It should not: every join is on an indexed primary key and the planner resolves them after the LIMIT has already cut the candidate set to ten rows. The cost is the vector scan, and it was going to be the vector scan regardless. What the joins buy is that a deleted document disappears from search the moment its deleted_at is set, without touching the index at all — which is the cheapest correct answer to soft deletion available.

One thing this schema is not, and it is worth being clear about: it is not the minimum. Four tables where one would do is a real cost in queries to write, joins to reason about and migrations to run, and for a prototype with a static corpus it is over-engineering. The moment it pays for itself is the first reprocessing run, which arrives sooner than anybody plans for — usually when the chunk size turns out to be wrong. If you are certain you will never re-chunk, use one table; if you are not, the four tables cost you an afternoon now and save you a weekend later.