Source: http://www.poma-ai.com/docs/pipelines/unstructured-to-pgvector

# The Missing Link Between Unstructured.io and Optimal Retrieval in pgvector

<ByAuthor />

**The short answer:** Unstructured.io gives you a typed, ordered element list; Postgres with pgvector gives you ANN search inside the database you already run. Wired together naively — one row per element, or flatten-and-split — the pipeline underuses both, because nothing in between turns the flat element list into a hierarchy the relational model can express. The missing link is POMA: `PrimeCut().ingest()` consumes the raw `elements_to_json` output (auto-detected), emits hierarchy-preserving [chunksets](/learn/chunking/chunksets), and those land in Postgres as joined tables — ANN over chunksets, lineage reconstructed by `JOIN` in the same statement, the whole document ingested in one transaction.

## What each end of the pipeline actually provides

**Unstructured.io** (the open-source `unstructured` library and the hosted API) returns a flat, ordered list of typed elements — `Title`, `NarrativeText`, `ListItem`, `Table`, `Image` — each with its `text`, a stable `element_id`, and `metadata` such as `page_number`, `text_as_html` for tables, and optionally `image_base64`. What it doesn't provide: hierarchy (the list is flat — nothing records which `Title` nests under which) or retrieval units; the built-in `by_title` and `basic` strategies emit flat fragments. Details: [The Optimal Chunker for Unstructured.io](/optimal-chunker-unstructured).

**pgvector** adds a `vector` column type, HNSW and IVFFlat indexes, and cosine/L2/inner-product operators to Postgres. Everything else is Postgres: `JOIN`s, transactions, `tsvector` full-text search, B-tree indexes on filter columns — and no new infrastructure if you already run it. What it doesn't provide: any opinion about what a row should contain. It computes nearest neighbors of whatever you embedded, fragments included. Details: [The Optimal Chunks for the Best Retrieval in pgvector](/optimal-chunks-pgvector).

## The naive wiring, and where it breaks

The trap here is pair-specific: Unstructured's output *looks* relational. Every element carries a stable `element_id` — a ready-made primary key — so the obvious schema is one table, one row per element, one embedding per row. It breaks three ways:

- **`Title` rows become three-word vectors.** A heading embedded on its own is a near-useless neighbor for any query, yet it competes in every ANN scan; the elements that gate structure become noise in the index.
- **No parent keys exist.** The element list is flat: nothing records which `Title` nests under which, so no `JOIN` — the one thing Postgres does best — can reconstruct the section a `NarrativeText` row belongs to. The relational model is there; the relation isn't in the data.
- **Tables flatten.** Embedding each element's `text` discards `metadata.text_as_html`, so revenue tables collapse into word soup and numeric answers lose their row/column meaning.

The flatten-and-split alternative fares no better: `page_number` and element types vanish at the join, and splitter overlap fills the HNSW index with near-duplicate rows that crowd top-k.

POMA's chunksets fix this at the source: every chunkset is a self-explanatory root-to-leaf unit, overlap-free, whose lineage is expressible as an actual relation. On a notoriously hard reference legal document, chunksets plus cheatsheet assembly delivered the answer in 337 tokens of retrieved context versus 1,542 for a recursive character splitter, with zero information loss ([methodology](/document-ingestion-chunking-rag)).

## The pipeline, end to end

```bash
pip install 'unstructured[pdf]' poma 'psycopg[binary]' pgvector sentence-transformers
```

The schema mirrors POMA's output — chunks, chunksets, and the lineage relation between them:

```sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id          bigserial PRIMARY KEY,
    file_id     text NOT NULL,
    chunk_index int  NOT NULL,
    depth       int  NOT NULL,   -- hierarchy level: 0 = title, deeper = finer
    page        int,             -- from Unstructured's metadata.page_number
    content     text NOT NULL,   -- tables arrive as text_as_html, never cut mid-row
    UNIQUE (file_id, chunk_index)
);

CREATE TABLE chunksets (
    id        bigserial PRIMARY KEY,
    file_id   text NOT NULL,
    to_embed  text NOT NULL,
    embedding vector(384) NOT NULL,
    tsv       tsvector GENERATED ALWAYS AS (to_tsvector('english', to_embed)) STORED
);

CREATE TABLE chunkset_members (
    chunkset_id bigint NOT NULL REFERENCES chunksets(id) ON DELETE CASCADE,
    chunk_id    bigint NOT NULL REFERENCES chunks(id)    ON DELETE CASCADE,
    position    int    NOT NULL, -- 0 = document root … last = the leaf
    PRIMARY KEY (chunkset_id, chunk_id)
);

CREATE INDEX ON chunksets USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunksets USING gin (tsv);
CREATE INDEX ON chunks (file_id, chunk_index);
```

```python
import os
import psycopg
from pgvector.psycopg import register_vector
from poma import PrimeCut
from sentence_transformers import SentenceTransformer
from unstructured.partition.pdf import partition_pdf
from unstructured.staging.base import elements_to_json

# 1. Unstructured — your existing call, unchanged.
elements = partition_pdf(
    filename="contract.pdf",
    strategy="hi_res",
    infer_table_structure=True,           # populates metadata.text_as_html
    extract_image_block_types=["Image"],
    extract_image_block_to_payload=True,  # inline base64 → POMA describes figures
)
elements_to_json(elements, filename="contract.unstructured.json")

# 2. The missing link — raw element list in, chunks + chunksets out.
poma = PrimeCut()  # reads POMA_API_KEY
result = poma.ingest("contract.unstructured.json")  # Unstructured shape auto-detected

# 3. Embed chunksets; land the whole document in ONE transaction.
model = SentenceTransformer("sentence-transformers/all-MiniLM-L6-v2")

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    register_vector(conn)
    with conn.transaction():
        chunk_ids = {}
        for chunk in result.chunks:
            row = conn.execute(
                "INSERT INTO chunks (file_id, chunk_index, depth, page, content) "
                "VALUES (%s, %s, %s, %s, %s) RETURNING id",
                (chunk.file_id, chunk.chunk_index, chunk.depth, chunk.page, chunk.content),
            ).fetchone()
            chunk_ids[chunk.chunk_index] = row[0]

        for cs in result.chunksets:
            row = conn.execute(
                "INSERT INTO chunksets (file_id, to_embed, embedding) "
                "VALUES (%s, %s, %s) RETURNING id",
                (cs.file_id, cs.to_embed, model.encode(cs.to_embed)),
            ).fetchone()
            for position, chunk_index in enumerate(cs.chunks):
                conn.execute(
                    "INSERT INTO chunkset_members (chunkset_id, chunk_id, position) "
                    "VALUES (%s, %s, %s)",
                    (row[0], chunk_ids[chunk_index], position),
                )
```

Retrieval is where the relational design pays off — ANN search and full lineage reconstruction in one statement:

```sql
WITH hits AS (
    SELECT id, embedding <=> %(query_vec)s AS distance
    FROM chunksets
    WHERE file_id = %(file_id)s          -- ordinary indexed predicate, filtered ANN
    ORDER BY embedding <=> %(query_vec)s
    LIMIT 5
)
SELECT h.id AS chunkset_id, h.distance, m.position, c.depth, c.page, c.content
FROM hits h
JOIN chunkset_members m ON m.chunkset_id = h.id
JOIN chunks c           ON c.id = m.chunk_id
ORDER BY h.distance, m.position;
```

Each hit returns ordered rows from root to leaf — every ancestor heading with its page and depth. Deduplicate the shared ancestors across hits and merge into one prompt-ready cheatsheet. For hybrid retrieval, rank the same chunksets with `tsv @@ websearch_to_tsquery('english', %(query_text)s)` and `ts_rank`, then fuse the two orderings.

POMA validates the payload shape up front (fingerprint: elements carrying `type` + `element_id`; a corrupted or mislabeled upload 422s immediately), groups elements by `page_number`, splices each `Table`'s `text_as_html`, describes inline `image_base64` figures (disk-bound `image_path` refs are neutralized and counted in `content_metadata`), drops `Header`/`Footer`/`PageNumber` furniture, and rebuilds the cross-page heading tree before chunking. To force or suppress detection, set `external_ocr_source` to `"unstructured"` or `"none"`.

## Metadata mapping: POMA fields → Postgres/pgvector primitives

| POMA chunk field | Postgres primitive | What it enables |
| --- | --- | --- |
| `to_embed` | `vector(384)` column, HNSW index | paraphrase ANN retrieval |
| `to_embed` text | generated `tsvector` column, GIN index | exact-term hybrid retrieval |
| `file_id` | `text` column, B-tree indexed | filtered ANN with an ordinary `WHERE` |
| `page` (from Unstructured `metadata.page_number`) | `int` column on `chunks` | page-cited answers, page-range predicates |
| `depth` | `int` column on `chunks` | filter/re-rank by hierarchy level |
| `chunk_index` | `int` column, part of the unique key | stable ordering at assembly time |
| chunkset lineage | `chunkset_members` join table | root-to-leaf `JOIN` in the ANN statement |

## Frequently asked questions

### How do I get Unstructured.io elements into pgvector for RAG?

Save the element list with `elements_to_json`, run `PrimeCut().ingest()` on the raw JSON (auto-detected, hierarchy rebuilt), then insert chunks and chunksets into joined Postgres tables in one transaction. Retrieval is one SQL statement: ANN over chunksets, `JOIN` back to chunks.

### What Postgres schema fits Unstructured.io output for RAG?

Three tables: `chunks` (`file_id`, `chunk_index`, `depth`, `page`, `content` — tables as HTML from `text_as_html`), `chunksets` (`to_embed`, `vector` embedding, generated `tsvector`), and `chunkset_members` for root-to-leaf lineage. HNSW, GIN, and B-tree indexes cover ANN, hybrid, and filters.

### Why not store one pgvector row per Unstructured element?

`element_id` makes a seductive primary key and a terrible retrieval unit: three-word `Title` vectors, flattened tables, mid-section paragraphs — and with no parent keys in the flat list, no `JOIN` can reconstruct the section a row belongs to.

### Can I combine pgvector ANN search with Postgres full-text search for Unstructured content?

Yes — a generated `tsvector` column with a GIN index gives you the sparse side for free. Part numbers, defined terms, and values from `text_as_html` tables are exact tokens dense embeddings blur; fuse the ANN and `websearch_to_tsquery` rankings.

### How do I keep an Unstructured document ingest atomic in Postgres?

One `conn.transaction()` block around chunks, chunksets, and lineage rows: the document lands whole or not at all. No crashed ingest ever leaves chunksets pointing at missing chunks, and re-ingest is `DELETE` plus re-insert in the same transaction.

## Related recipes

Same parser, different store: [Unstructured.io → Qdrant](/pipelines/unstructured-to-qdrant) · [Unstructured.io → Milvus](/pipelines/unstructured-to-milvus) · [Unstructured.io → Chroma](/pipelines/unstructured-to-chroma)

Same store, different parser: [Mistral OCR → pgvector](/pipelines/mistral-ocr-to-pgvector) · [Azure Document Intelligence → pgvector](/pipelines/azure-document-intelligence-to-pgvector) · [Docling → pgvector](/pipelines/docling-to-pgvector)

Foundations: [The Optimal Chunker for Unstructured.io](/optimal-chunker-unstructured) · [The Optimal Chunks for pgvector](/optimal-chunks-pgvector) · [All pipeline recipes](/pipelines/) · [RAG chunking guide](/guides/rag-chunking/)