Storing Documents and Chunks: Table Design

The schema most people write first has one table with a text column and a vector column. It works until the first time you change the chunk size, at which point every stored citation, every feedback record and every evaluation result points at a chunk id that no longer means what it meant.

Get the grain right

There are four grains here and they are genuinely different things:

• Document — the thing a user recognises. One row per source URI, stable forever.
• Document version — the content at a point in time, identified by the hash of its normalised text. New version when the text changes; nothing else changes.
• Chunk — a span of one document version under one chunking configuration. Two chunking configurations over the same version coexist.
• Embedding — one vector for one chunk under one model. Two models over the same chunk coexist.

Collapsing any adjacent pair is where the trouble comes from. Collapse document and version and you cannot answer “what did this say in March?”. Collapse chunk and embedding and you cannot run two embedding models side by side, which is the only safe way to migrate between them.

The schema

CREATE TABLE documents (
  id            bigserial PRIMARY KEY,
  source_uri    text NOT NULL UNIQUE,
  tenant_id     text NOT NULL,
  access_labels text[] NOT NULL DEFAULT '{}',
  first_seen_at timestamptz NOT NULL DEFAULT now(),
  deleted_at    timestamptz                       -- tombstone, never DELETE
);

CREATE TABLE document_versions (
  id            bigserial PRIMARY KEY,
  document_id   bigint NOT NULL REFERENCES documents(id),
  text_sha256   bytea  NOT NULL,                  -- of the NORMALISED text
  raw_sha256    bytea  NOT NULL,                  -- of the original bytes
  extractor     text   NOT NULL,                  -- 'pymupdf'
  extractor_ver text   NOT NULL,                  -- '1.24.9'
  text          text   NOT NULL,
  effective_at  timestamptz,                      -- from the content
  fetched_at    timestamptz NOT NULL,
  superseded_at timestamptz,
  UNIQUE (document_id, text_sha256, extractor, extractor_ver)
);

CREATE TABLE chunks (
  id            bytea PRIMARY KEY,                -- content-derived, see below
  version_id    bigint NOT NULL REFERENCES document_versions(id),
  chunk_config  text   NOT NULL,                  -- 'v7:size=800:overlap=100'
  ordinal       int    NOT NULL,                  -- position, for context
  char_start    int    NOT NULL,                  -- offsets into versions.text
  char_end      int    NOT NULL,
  section_path  text,                             -- 'Pricing > Enterprise'
  token_count   int    NOT NULL,
  UNIQUE (version_id, chunk_config, ordinal)
);

CREATE TABLE embeddings (
  chunk_id      bytea NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  model         text  NOT NULL,                   -- provider/model:version
  dim           int   NOT NULL,
  vec           vector(1536) NOT NULL,            -- pgvector
  created_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (chunk_id, model)
);

CREATE INDEX ON embeddings USING hnsw (vec vector_cosine_ops)
  WHERE model = 'text-embedding-3-small';         -- one partial index per model

Three things in there are load-bearing. The chunk text is not stored — it is substring(text, char_start, char_end) from the version, so there is exactly one copy of every character and a citation can highlight a span in the full document. The composite primary key on embeddings means two models coexist by construction. And the partial HNSW index per model keeps each model’s index free of the other’s vectors, which matters because a mixed index is meaningless — cosine distance between vectors from different models is not a similarity.

If storing chunk text as a real column is more convenient — and with a very large corpus and cold storage for versions, it often is — keep the offsets anyway. They are eight bytes and they are what makes a citation point at a place rather than at a blob.

Chunk ids from content, not position

The single decision that makes re-chunking survivable:

import hashlib

def chunk_id(version_sha: bytes, config: str, start: int, end: int) -> bytes:
    h = hashlib.blake2b(digest_size=16)
    h.update(version_sha)
    h.update(config.encode())
    h.update(f"{start}:{end}".encode())
    return h.digest()

The consequence is worth stating plainly: identical content chunked identically produces the identical id, on any machine, in any run, forever. Re-run the pipeline over an unchanged corpus and every insert is a no-op. Change the config and you get a completely disjoint set of ids — old and new coexist rather than overwriting each other. And a citation stored last year still resolves, because the row it names is still there.

A bigserial chunk id has none of these properties. It is assigned by insertion order, so the same content gets a different id on every run, and any stored reference is a reference to whatever happened to be inserted in that position.

The offsets in the key are what make it a coordinate rather than a label. Because the version hash is in there too, a document whose text changed by a single character gets an entirely new set of chunk ids — correctly, because every span after the edit has shifted.

There is a cost and it is worth stating. Content-derived ids are opaque: you cannot tell by looking whether one chunk precedes another, which is exactly why ordinal is a separate column. Keep it, and keep the unique constraint on (version_id, chunk_config, ordinal), because widening the context by fetching a chunk’s neighbours is a common operation and it needs an ordering that the primary key deliberately does not carry.

Migration one: a new chunk size

You want 500-token chunks instead of 800. With this schema it is additive and the live index is never inconsistent:

-- 1. Write the new configuration alongside the old. Nothing reads it.
INSERT INTO chunks (id, version_id, chunk_config, ordinal, char_start,
                    char_end, section_path, token_count)
SELECT ... FROM document_versions WHERE superseded_at IS NULL;

-- 2. Embed only the new chunks.
--    Cost = sum(token_count) WHERE chunk_config = 'v8:size=500:overlap=60'

-- 3. Compare, on your evaluation set, retrieving with each config.
--    Both are queryable at the same time; this is the whole point.

-- 4. Flip the reader. One config string in one place.

-- 5. Delete the loser when you are confident.
DELETE FROM chunks WHERE chunk_config = 'v7:size=800:overlap=100';
--   embeddings go with it via ON DELETE CASCADE

Steps 3 and 4 are what the schema is for. An A/B between chunking strategies is a query-side change, not a rebuild, so the chunking question becomes something you can settle with evidence rather than argue about.

Migration two: a new embedding model

Same shape, one dimension over. Insert rows into embeddings with the new model value; the old vectors are untouched and still serving. Two wrinkles worth planning for.

First, dimensions differ between models, and vector(1536) is a fixed width. Either add a second column typed for the new dimension, or — cleaner — a second table per dimension with a view over both. Some models support Matryoshka truncation, where a prefix of the vector is itself a usable embedding, which lets one column serve several sizes; that property is a real one and is worth knowing about before you design around a fixed width.

Second, the vector index must be built for the new model before you switch, and building an HNSW index over millions of rows is not instant. Build it as a partial index on the new model value while the old one serves traffic; that is exactly what the partial index in the DDL is for.

Include a version string in the model value — provider/model@2026-05, not model. Providers do update embedding models in place, and vectors from before and after such an update are not comparable. If the identifier does not record which version produced a vector, you cannot tell whether your index is homogeneous, and you cannot fix it selectively.

A note on what this schema deliberately does not do. It does not try to be the vector store. If your vector search lives in a dedicated engine rather than in Postgres, keep these tables as the system of record and treat the engine as a derived index that can be rebuilt from them at any time. That way the answer to “the vector store lost a shard” is a re-index rather than a re-embed, which is the difference between an hour and the whole budget again.