PostgreSQL 17 for Odoo: pgvector, Performance, and What Changed

What's new in PostgreSQL 17 (the parts Odoo cares about)

PostgreSQL 17 dropped in September 2024 with a long changelog. Most of it is incremental. A handful of changes matter a lot if you run Odoo, because Odoo hits the database harder than most ORMs and stores more rows per user than most apps. Here is the short list of things that move the needle.

Better planner stats, especially for partial indexes

The planner now keeps better statistics for partial indexes. Odoo uses partial indexes everywhere:mail_message, stock_move, account_move_line all have indexes that filter on state or active = true. On PG14, the planner sometimes misjudged selectivity and picked sequential scans when an index scan was faster. PG17 fixes most of those cases. We have seen 30 to 50 percent faster reporting queries on the same data.

Parallel index builds

CREATE INDEX can now use multiple workers on more index types, including BRIN. If you have ever waited 40 minutes to build an index on a 20 GB account_move_line table during an upgrade window, this is a gift. On a 16-core box with max_parallel_maintenance_workers = 4, index builds drop to roughly a third of the wall time.

VACUUM is finally not embarrassing

PG17 rewrote how VACUUM tracks dead tuples. The old structure used up to 1 GB of memory and required multiple index passes when a table had a lot of dead rows. The new TID store uses a fraction of the memory and almost always does one pass. For Odoo this matters because tables like mail_tracking_value and ir_attachment generate massive amounts of dead tuples. Autovacuum used to fall behind; on PG17 it keeps up.

WAL improvements

WAL files are smaller and replication is faster. If you run a hot standby for backup or read replicas, you will see less network traffic and lower replication lag. We measured roughly 15 percent smaller WAL volume on a typical Odoo workload (lots of small transactions hitting mail_message and bus_bus).

JSON_TABLE

PG17 ships the SQL standard JSON_TABLE function. If your Odoo modules store JSON metadata (custom fields, integration payloads, webhook bodies) you can now query them like relational tables without writing jsonb_array_elements gymnastics. It reads cleaner and the planner handles it better.

pgvector: vector search for Odoo

pgvector is a PostgreSQL extension that adds a vector column type and operators for similarity search. You store an N-dimensional float array on a row, build an index, and query for the nearest neighbors. It is the database half of every RAG and semantic-search system you have read about in the last two years.

We ship pgvector pre-installed on every OEC.sh PostgreSQL deployment. PG14, PG15, PG16, and PG17 all get it, so you do not need to wait on PG17 to start using it.

Why Odoo needs it

Three real use cases keep showing up in customer projects:

• Semantic FAQ search. A customer types "my invoice didn't arrive" and the helpdesk module finds the article titled "Email deliverability for invoices" even though they share zero keywords. pg_trgm cannot do this. Vector search can.
• Find similar customers. Embed each res.partner using a profile of industry, size, products purchased, and geography. Then sales reps can pull a list of partners similar to a customer who just renewed at twice their previous spend.
• RAG over Odoo data. Build an internal chatbot that can answer "what was our average delivery time for orders over $5k last quarter?" by retrieving relevant rows from stock_picking and sale_order and feeding them to an LLM.

The schema

Here is what a partner-embeddings setup looks like. The 1536 dimension matches OpenAI's text-embedding-3-small model. Adjust to whatever embedding model you use.

-- Run once per database
CREATE EXTENSION IF NOT EXISTS vector;

-- Add an embedding column to res_partner
ALTER TABLE res_partner ADD COLUMN embedding vector(1536);

-- Build an IVFFlat index for fast approximate nearest-neighbor search
-- The lists value should be roughly sqrt(rowcount). For 100k partners, use 316.
CREATE INDEX idx_res_partner_embedding
  ON res_partner
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 316);

-- Find the 10 most similar partners to a given embedding
SELECT id, name, email, 1 - (embedding <=> $1) AS similarity
FROM res_partner
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1
LIMIT 10;

HNSW vs IVFFlat

pgvector supports two index types. IVFFlat is faster to build and uses less memory. HNSW is faster at query time and gives better recall, but takes longer to build and uses more RAM. For most Odoo workloads (100k to 1M vectors, low-to-medium QPS) IVFFlat is fine. If you are doing real-time semantic search at high traffic, switch to HNSW. The tradeoff is real, not academic.

If you are wiring up an AI agent to actually call Odoo records, the right entry point is MCP, Odoo's Model Context Protocol guide. pgvector handles retrieval; MCP handles the agent calling Odoo back to write data.

pg_trgm: fuzzy text search for Odoo

pg_trgm has been in Postgres for a decade and Odoo developers reach for it constantly, but it is not always installed by default on managed Postgres providers. We install it on every OEC.sh deployment.

It splits strings into trigrams (three-character chunks) and uses similarity scoring to match them. In plain English: it finds "Apple" when the user types "aple" or "appl". Built-in Odoo partner search uses ILIKE '%term%' which is fast on small tables and miserable once you cross 100k partners.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIST is best for similarity ranking, GIN is faster for "contains"
CREATE INDEX idx_partner_name_trgm
  ON res_partner
  USING gist (name gist_trgm_ops);

-- The % operator returns rows where similarity exceeds the threshold
-- (default 0.3, tune with SET pg_trgm.similarity_threshold)
SELECT id, name, similarity(name, 'aple') AS score
FROM res_partner
WHERE name % 'aple'
ORDER BY score DESC
LIMIT 20;
-- Returns "Apple Inc.", "Pineapple Co.", etc.

In practice, point Odoo's partner autocomplete at a custom search method that uses % instead of ILIKE. The lookup goes from 800 ms to under 30 ms on a 500k-partner database. Your salespeople notice immediately.

unaccent: international text

If you run Odoo in any non-English-speaking country, unaccent is mandatory. It strips accents so searches work regardless of how the user types. "Rétro", "retro", and "RETRO" all match the same row. Without unaccent, "Müller" and "Muller" are two different partners as far as the database is concerned.

CREATE EXTENSION IF NOT EXISTS unaccent;

-- Combine unaccent + pg_trgm for fuzzy, accent-insensitive search
CREATE INDEX idx_partner_name_unaccent_trgm
  ON res_partner
  USING gist (unaccent(name) gist_trgm_ops);

-- Find "Müller" by typing "muler"
SELECT id, name
FROM res_partner
WHERE unaccent(name) % unaccent('muler')
ORDER BY similarity(unaccent(name), unaccent('muler')) DESC
LIMIT 10;

unaccent is installed on every OEC.sh PostgreSQL deployment by default, alongside pgvector and pg_trgm. You do not have to file a support ticket to get any of them.

Version-aware deployment on OEC.sh

When you create a new environment, the platform looks at your Odoo version and picks a sensible Postgres default. You can override it with the version dropdown if you know what you are doing. The rules:

Why no PG17 for Odoo 14, 15, or 16? Odoo's own test matrix never covered those combinations. PG17 has subtle ORM-affecting changes, like stricter NULL handling in some functions and changed behavior in window functions, that older Odoo versions can trip on. We have seen real regressions in customer projects, so we will not let you shoot yourself in the foot. Use what Odoo S.A. tested against.

For Odoo 17 onward, we let you pick anything from PG14 through PG17. Pick PG17 if you want the new planner stats and parallel index builds. Pick PG15 or PG16 if you have a specific reason to.

PgBouncer connection pooling

Odoo's worker model is the reason you need a connection pooler. Each worker holds at least one database connection open for the duration of the process. With 10 workers per environment and 5 environments on a single server, you have 50 connections sitting idle most of the time, eating PG memory (every backend is roughly 10 MB of overhead before any real work).

Postgres handles connections one-process-per-connection. There is no async pool inside Postgres. PgBouncer fixes this by sitting in front of PG, accepting connections from Odoo workers, and pooling them down to a smaller number of real PG backends. Idle Odoo workers do not waste a real connection.

We run PgBouncer in transaction-pooling mode by default. That is the safe choice for Odoo because Odoo does not depend on session state across transactions. Statement pooling breaks anything that uses transactions (which is most of Odoo). Session pooling defeats the purpose. Transaction is the sweet spot.

One gotcha: PgBouncer transaction pooling does not support SET outside a transaction or prepared statements that span transactions. If you have custom modules that rely on session GUCs, set them inside a transaction or via SET LOCAL.

Tuning PostgreSQL for an Odoo workload

Default Postgres ships with conservative settings that assume you are running on a Raspberry Pi. Odoo on a real server (we use Hetzner AX boxes with 62 GB RAM as our reference) needs the memory knobs turned up. Here are the settings we apply to OEC.sh local-Postgres deployments on a 62 GB Hetzner AX server, and the reasoning behind each one.

# postgresql.conf for an Odoo workload on 62 GB RAM

# Memory
shared_buffers = 16GB              # ~25% of RAM
effective_cache_size = 40GB        # ~65% of RAM (hint to planner, not allocation)
work_mem = 32MB                    # per-sort, per-hash. Multiply by max parallel queries
maintenance_work_mem = 1GB         # for VACUUM, CREATE INDEX, REINDEX
huge_pages = try                   # use Linux huge pages if available

# Checkpoints and WAL
max_wal_size = 8GB                 # default 1GB causes constant checkpoints under load
min_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 64MB
wal_compression = on               # cheap CPU, less I/O

# Parallelism
max_worker_processes = 16
max_parallel_workers = 12
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4

# Connections (real PG, behind PgBouncer)
max_connections = 200              # PgBouncer pools, you do not need 1000

# Stats and autovacuum
track_io_timing = on
default_statistics_target = 200    # default 100 is too low for Odoo's complex queries
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025

The two settings that fix the most pain are work_mem and max_wal_size. Default work_mem is 4MB. Odoo's reporting queries (sales reports, partner ledgers, BoM cost rollups) sort huge result sets and spill to disk constantly with the default. Bumping it to 32MB keeps most of those in memory. Default max_wal_size is 1GB and on a busy Odoo (heavy mail_message writes) you will hit it every couple of minutes, triggering full checkpoints and tanking write throughput.

We covered this in more depth in the previous post on PostgreSQL tuning for Odoo. Read that if you want the why behind every setting, including how to size things for smaller boxes.

Should you upgrade?

For new environments on Odoo 17, 18, or 19: yes. Pick PG17 in the dropdown when you create the environment. There is no cost difference, the performance is better, and you get the new features for free.

For existing PG14 or PG15 production databases: maybe. The upgrade is real work and you need to test it. Two paths:

pg_dump and restore (slow, simple)

Dump the old database, restore into a fresh PG17 cluster. Downtime equals dump time plus restore time plus index rebuild time. On a 50 GB Odoo database with default parallelism, expect 30 to 90 minutes. Acceptable for off-hours. Simple, well-understood, hard to mess up.

pg_upgrade (fast, careful)

pg_upgrade can do a 50 GB database in under five minutes using the --link mode (hard-links the data files instead of copying). Catch: if anything goes wrong, you cannot easily roll back. You also need to run ANALYZE across the whole database afterwards because pg_upgrade does not carry over planner statistics. Plan an extra hour for ANALYZE on a 50 GB database.

We support both methods. The right way to test either: clone your production environment to a staging copy on OEC.sh, run the upgrade against the clone, run your test suite, then schedule the prod cut. Staging clones are part of every paid plan. Use them. Surprises during a Postgres major-version upgrade in prod are the kind of stories you do not want to live through.

TL;DR

• PostgreSQL 17 has real wins for Odoo: better planner, parallel index builds, faster VACUUM, smaller WAL.
• pgvector is pre-installed on every OEC.sh PostgreSQL deployment. So is pg_trgm and unaccent.
• Pick your PG version from a dropdown when you create an environment. Odoo 17, 18, 19 can run on PG17. Older Odoo stays on PG14 or PG15 for safety.
• PgBouncer in transaction-pooling mode is the default. It saves real RAM on multi-environment servers.
• Tune shared_buffers, work_mem, and max_wal_size. Defaults assume a Raspberry Pi.
• Test major-version upgrades on a staging clone first. Always.