pgvector Inside Odoo

Postgres is already running underneath every Odoo instance. Adding one extension turns it into the vector database for a retrieval-augmented chat module — no second datastore, no sync job between two systems. Here is how the storage works, where Odoo's ORM gets in the way, and what the module ended up looking like.

The task was ordinary enough: let people ask questions in plain language and get answers grounded in data that already lives in Odoo — product descriptions, partner notes, an FAQ document someone maintains in a Word file. A language model alone cannot do this. It has never seen your database. The standard fix is retrieval-augmented generation: find the handful of passages that actually bear on the question, paste them into the prompt, and let the model answer from that.

Every part of that is cheap except one. Finding the right passages means comparing meaning, not keywords, and comparing meaning means turning text into vectors and searching by distance. That search needs somewhere to live.

Part one

Postgres as a vector database

An embedding model maps a piece of text to a fixed-length list of floats — 1536 of them, in the configuration used here. Texts that mean similar things land near each other. “Refunds are issued within 14 days” and “how long until I get my money back” have almost no words in common and sit close together in that space, which is the entire point.

Retrieval, then, is a nearest-neighbour problem: given the question's vector, return the stored vectors closest to it. pgvector is a Postgres extension that adds a vector column type, distance operators, and approximate-nearest-neighbour indexes. One statement installs it:

CREATE EXTENSION IF NOT EXISTS vector;

After that, a vector column is just a column, and a similarity search is just a query:

CREATE TABLE ai_vector_chunk (
    id        serial PRIMARY KEY,
    content   text,
    metadata  jsonb,
    embedding vector(1536)
);

-- cosine distance, ascending: nearest rows first
SELECT id, content, 1 - (embedding <=> '[0.013, -0.271, ...]') AS score
  FROM ai_vector_chunk
 ORDER BY embedding <=> '[0.013, -0.271, ...]'
 LIMIT 5;

Three operators matter. <=> is cosine distance, <-> is L2, <#> is negative inner product. Cosine is the usual choice for text, and because it ranges 0–2, similarity is 1 - distance. Without an index, that ORDER BY is an exact scan of the whole table. With one, it is approximate and fast:

CREATE INDEX ON ai_vector_chunk USING hnsw (embedding vector_cosine_ops);

The index type has to match the operator — an hnsw index built with vector_cosine_ops accelerates <=> and nothing else. Note also that pgvector's indexes cap out at 2000 dimensions. That single constraint drove a design decision later: 1536 fits and can be indexed, 3072 cannot.

What you gain and what you give up

The honest summary: for an ERP-sized corpus — a product catalogue, a few thousand documents, a support archive — Postgres is not a compromise, it is the correct default. The cost of running a second database exceeds its benefit until you are well past the point where you would notice.

Part two

The two paths through the system

Every RAG system is two pipelines that meet inside one table. Text goes in on a schedule; questions come in live. They share nothing except the vector column and, critically, the embedding model — a query embedded by a different model than the documents produces distances that are pure noise.

That last dashed line is the part worth insisting on. Storing which chunks produced an answer is what makes the system debuggable. When an answer is wrong, the first question is always did retrieval find the right passages? — and if the chunks are attached to the message, that takes a glance rather than a re-run.

Part three

Where the ORM stops

Odoo's ORM maps Python field objects to Postgres columns. There is no fields.Vector, and no way to teach it one without patching the framework. So the vector column has to be created and accessed outside the ORM — which sounds worse than it is, because Odoo gives every model an init() hook that runs after its table exists.

The init() hook does three things: probe for the extension, add the column, build the index. All three have to survive failure, because on a managed database the very first step is likely to be refused.

def init(self):
    """Create the pgvector column/index, or record that we run on the numpy fallback."""
    params = self.env['ir.config_parameter'].sudo()
    dimension = int(params.get_param(PARAM_DIMENSION, DEFAULT_DIMENSION))
    cr = self.env.cr

    available = False
    cr.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'")
    if cr.fetchone():
        available = True
    else:
        try:
            with cr.savepoint():
                cr.execute("CREATE EXTENSION IF NOT EXISTS vector")
            available = True
        except Exception as exc:
            _logger.warning("pgvector unavailable (%s). Falling back to numpy.", exc)

    if available:
        with cr.savepoint():
            cr.execute("ALTER TABLE ai_vector_chunk "
                       "ADD COLUMN IF NOT EXISTS embedding vector(%s)" % dimension)

    params.set_param(PARAM_BACKEND, "pgvector" if available else "numpy")

The cr.savepoint() is not decoration. A failed statement in Postgres aborts the whole transaction — without a savepoint, a database that refuses CREATE EXTENSION does not fall back gracefully, it fails the entire module installation.

Two backends, one API

Since the extension may be unavailable, the module ships a fallback that stores vectors as JSON text and does the cosine arithmetic in numpy. Both backends expose the same search_similar() signature, so nothing above them knows which is live. They are not equivalent, though, and the difference is worth drawing.

Part four

The module

Six models, roughly 880 lines. Deliberately small: the interesting parts are the boundaries, not the volume of code.

Keeping the index fresh

The obvious way to notice a changed record is to override create and write. The module does not, because it indexes arbitrary models chosen at runtime, and monkey-patching whatever a user picks in a dropdown is not a thing to inflict on someone else's database. Instead it uses what Odoo already maintains on every row:

The trade-off is explicit: freshness is bounded by the cron interval, 30 minutes by default. In exchange, nothing in the module touches the runtime behaviour of the models it indexes. Both crons ship inactive — installing a module should never start spending someone's API budget on its own.

Retrieval, both ways

@api.model
def search_similar(self, query_vector, limit=5, source_ids=None,
                   res_model=None, min_score=None):
    where, args = ["c.state = 'done'"], []
    if source_ids:
        where.append("c.source_id IN %s")
        args.append(tuple(source_ids))
    where_sql = " AND ".join(where)

    if self._backend() == "pgvector":
        literal = to_vector_literal(query_vector)
        self.env.cr.execute("""
            SELECT c.id, c.content, c.metadata,
                   1 - (c.embedding <=> %%s::vector) AS score
              FROM ai_vector_chunk c
             WHERE %s AND c.embedding IS NOT NULL
          ORDER BY c.embedding <=> %%s::vector
             LIMIT %%s
        """ % where_sql, [literal] + args + [literal, limit])
        rows = self.env.cr.dictfetchall()
    else:
        rows = self._search_similar_numpy(query_vector, where_sql, args, limit)

    if min_score is None:
        return rows
    return [row for row in rows if row["score"] >= min_score]

Two details in there are load-bearing. The vector is passed as a parameter and cast with %s::vector, never string-formatted into the SQL. And the ordering expression is repeated verbatim in SELECT and ORDER BY — Postgres will use the index for the sort while still computing the displayed score.

One dimension to rule them

The column type fixes the dimension, so the choice is made once and changing it means rewriting the column. 1536 was picked because it is the one number that lets both providers coexist: Gemini's gemini-embedding-001 accepts output_dimensionality=1536, OpenAI's text-embedding-3-small accepts dimensions=1536, and both sit under pgvector's 2000-dimension index ceiling.

Part five

What it does not do

Stated plainly, because a retrieval layer that overstates itself is a liability:

• Retrieval bypasses record rules. A chunk's text is returned by raw SQL, so a user who can query a source can see text from records they could not open directly. Scope chat sources per group until this is fixed properly, by intersecting hits with an ORM-side permission check.
• No hybrid scoring. Pure vector similarity misses exact-token matches — part numbers, invoice references, surnames — which is precisely what pg_trgm is good at. Combining both with reciprocal rank fusion is the natural next step, and Postgres can do it in one query.
• Freshness lags by the cron interval. Acceptable for catalogues and documentation; wrong for anything that must be searchable the second it is saved.
• Chunking is naive. Paragraph-aware word windows, nothing more. Structure-aware splitting on headings would retrieve better, particularly for long documents.

None of these needed solving to get a working system, and pretending otherwise would have meant shipping later with more to be wrong. The version that exists indexes real records, answers real questions from them, and shows its work.

Built on Odoo 17.0 with pgvector and google-genai. Verified by installing into a disposable local database and driving it from odoo-bin shell — the numpy fallback path end-to-end, the pgvector path by inspection, since the test host had no extension to install.