Zenithonsoft

PostgreSQL pgvector in Odoo 20: How Semantic Vector Search & RAG Power Discuss and Documents

When enterprise resource planning software meets artificial intelligence, the biggest architectural obstacle is context.

How do you give an AI agent in Odoo Discuss (ai_agentic) the ability to answer questions about a company’s 200-page ISO quality manual, internal clinical SOPs, or vendor purchase contracts without stuffing millions of raw text tokens into an external LLM prompt and blowing up latency and token costs?

In legacy ERP architectures, developers relied on clunky keyword searches (ILIKE '%query%') or external vector databases (Pinecone, Weaviate, Qdrant) that required custom synchronization pipelines, external API keys, and complex ETL jobs.

In Odoo 20, Odoo S.A. solved this problem at the database foundation by making PostgreSQL pgvector a first-class citizen of the Odoo ORM.

Here is a comprehensive technical breakdown of how pgvector is integrated into Odoo 20, how Retrieval-Augmented Generation (RAG) works across Documents and Knowledge, and how systems architects should configure production database servers.

1. What is pgvector and Why Did Odoo Adopt It?

PostgreSQL pgvector is an open-source extension that allows PostgreSQL to store, index, and query dense high-dimensional vector embeddings directly alongside traditional relational tables.

       Raw Text (Knowledge SOP / PDF / Chatter)
                        │
                        ▼
       [ Text Chunking & Embedding Generator ]
                        │
                        ▼
       PostgreSQL 16 Table (ir_attachment_vector)
  ┌────────────────────────────────────────────────────────┐
  │ id │ document_id │ chunk_index │ embedding (vector)    │
  ├────┼─────────────┼─────────────┼───────────────────────┤
  │ 1  │ 42          │ 0           │ [0.012, -0.045, ...]  │
  │ 2  │ 42          │ 1           │ [-0.031, 0.089, ...]  │
  └────────────────────────────────────────────────────────┘
                        │
                        ▼
       HNSW / IVFFlat Index (<=> Cosine Distance)
                        │
                        ▼
       Sub-Millisecond Semantic Match Returned to Agent

The Architectural Advantage for Odoo:

  1. Single Source of Truth: Embeddings live directly inside the same PostgreSQL database as your invoices, partners, and products. There is zero risk of external vector database desynchronization.
  2. ACID Transactional Integrity: If an internal SOP in Odoo Knowledge or an HR handbook is deleted or updated, the vector embeddings are rolled back or updated within the exact same database transaction.
  3. ORM Access Rights Enforcement: Because embeddings are tied to standard Odoo records (ir.attachment or knowledge.article), search results strictly inherit Odoo’s access rights. A warehouse worker cannot retrieve vectors for confidential executive board minutes.

2. How Odoo 20 RAG Works Across Documents & Knowledge

When an enterprise installs ai_agentic_documents or ai_agentic_knowledge in Odoo 20, the system initiates an automated ingestion and retrieval pipeline:

Step A: Recursive Chunking

When a PDF, spreadsheet, or Knowledge article is uploaded, Odoo splits the text into semantic chunks (typically 500 to 1,000 tokens) with a 10% sliding overlap to preserve conversational context across boundaries.

Step B: Embedding Generation

Odoo passes each chunk through an embedding model (generating a 1,536-dimensional or 768-dimensional float vector). The vector array is persisted into a dedicated vector column in PostgreSQL:

-- Odoo 20 vector schema representation
CREATE TABLE ir_attachment_embedding (
    id SERIAL PRIMARY KEY,
    attachment_id INTEGER REFERENCES ir_attachment(id) ON DELETE CASCADE,
    chunk_seq INTEGER NOT NULL,
    chunk_text TEXT NOT NULL,
    embedding vector(1536) NOT NULL
);

Step C: Semantic Similarity Retrieval

When a user asks in Odoo Discuss:
“@OdooBot what is our return policy for damaged clinic equipment?”

Instead of scanning all documents, Odoo converts the user’s question into a query vector and executes a cosine distance search in PostgreSQL using the <=> operator:

-- Instant semantic retrieval in Odoo 20
SELECT chunk_text, 1 - (embedding <=> $query_vector) AS similarity_score
FROM ir_attachment_embedding
WHERE attachment_id IN (SELECT id FROM ir_attachment WHERE ...)
ORDER BY embedding <=> $query_vector
LIMIT 3;

The top 3 most relevant paragraphs are returned to the agent runtime in less than 15 milliseconds. The LLM receives only those 3 paragraphs as context, answers the employee accurately, and cites the exact document title and page number.

3. Indexing Strategies: HNSW vs. IVFFlat in Odoo 20

In production databases containing thousands of company documents, performing brute-force vector scans (exact sequential scan) will degrade PostgreSQL query performance.

Odoo 20 leverages HNSW (Hierarchical Navigable Small World) indexing:

Dimension IVFFlat (Inverted File Flat) HNSW (Hierarchical Navigable Small World)
Search Speed Fast, but degrades on high dimensions. Ultra-Fast (Logarithmic Search).
Recall Accuracy 90–95% (Approximated clusters). 98–99% (High Fidelity Semantic Match).
Build Time & RAM Lower RAM; fast index creation. Higher initial RAM; optimal for read-heavy ERP data.
Odoo 20 Default Optional for low-memory VPS. Recommended for Production Environments.

4. Production Server Sizing & Database Configuration

Running pgvector on high-concurrency production Odoo servers requires tuning standard PostgreSQL parameters:

A. Memory Allocation (shared_buffers & maintenance_work_mem)

HNSW vector indexes perform best when cached entirely in RAM.

  • Set shared_buffers to 25% of total system RAM.
  • Increase maintenance_work_mem to 1GB–2GB during index creation to prevent disk thrashing when building large document embeddings.

B. Installing the Extension on Ubuntu/Debian

If deploying Odoo 20 on-premise or on a private cloud instance (AWS EC2, Hetzner, DigitalOcean), ensure the extension is compiled for PostgreSQL 16+:

# Install pgvector for PostgreSQL 16
sudo apt-get install postgresql-16-pgvector

# Enable inside your Odoo database
sudo -u postgres psql -d your_production_db -c "CREATE EXTENSION IF NOT EXISTS vector;"

On Odoo.sh, Odoo S.A. provisions container environments with pgvector pre-installed and managed automatically across all Odoo 20 dedicated builds.

🛠️ Preparing Your Odoo Database for Version 20?
Custom modules overriding ir.attachment or executing heavy unindexed SQL queries can severely bottleneck PostgreSQL during vector embedding generation. Zenithonsoft offers automated static analysis and query profiling in 24 hours under our 18-point NDA:
👉 Request a Free Odoo Code Review & Upgrade Diagnostic →

5. Architectural Comparison: Legacy Search vs. Odoo 20 pgvector

Search Capability Legacy Odoo (v14–v19) Odoo 20 with pgvector
Search Paradigm Exact keyword matching (name ILIKE '%term%') Semantic Meaning Matching (matches synonyms, intent, and concept)
Multilingual Querying Fails unless exact translations are keyed in. Native: Querying in French retrieves matching English SOP sections.
Unstructured PDF Search Limited to OCR text dumping; high noise. Chunk-Ranked: Returns only the specific paragraph answering the question.
Token Overhead on LLM Huge (Must send full document to LLM). Minimal: Sends only top 3 relevant chunks (90% lower token cost).
Infrastructure Complexity High if using external search SaaS (Algolia/Pinecone). Zero External Middleware: 100% contained inside PostgreSQL.

Frequently Asked Questions (FAQ)

What is pgvector in Odoo 20?

pgvector is a PostgreSQL extension that stores mathematical vector embeddings directly inside Odoo’s relational database. It enables native semantic similarity search, powering intelligent knowledge retrieval for Discuss AI agents and document search.

Does Odoo 20 require pgvector to run?

Core ERP operations (CRM, Accounting, Sales, Inventory) do not strictly require pgvector. However, advanced AI features—including native Discuss RAG bots, automated Knowledge searching, and semantic document matching—require PostgreSQL 16+ with the vector extension enabled.

How does pgvector improve AI response times?

Instead of sending an entire 50-page employee handbook to an external AI model (which takes seconds and costs thousands of tokens), pgvector searches PostgreSQL in sub-milliseconds, retrieves only the 2 or 3 most relevant sentences, and sends only that snippet to the LLM.

Can pgvector run on self-hosted or on-premise Odoo installations?

Yes. As long as your database server runs PostgreSQL 15 or 16 and has the official postgresql-pgvector package installed, Odoo 20 can leverage native vector search without third-party cloud dependencies.

Summary

The inclusion of pgvector in Odoo 20 represents a masterclass in pragmatic enterprise architecture.

Instead of adding another layer of fragile microservices or third-party vector SaaS providers, Odoo leveraged the strength of its core database engine—giving businesses instant, secure, and permission-compliant AI search right where their data already lives.

Modernize Your Odoo Architecture with Zenithonsoft

Planning an upgrade to Odoo 20 or need to optimize your PostgreSQL database for high-concurrency vector workloads?