Odoo Performance Tuning: PostgreSQL and Server Optimization

A slow Odoo instance costs money in lost productivity and frustrated users. The good news: most Odoo performance issues are solvable without hardware upgrades. The bad news: diagnosing the root cause requires understanding the entire stack — Python, PostgreSQL, Nginx, Redis, and the network layer.

This guide covers the full performance optimization lifecycle for Odoo 19 Enterprise: identifying bottlenecks, tuning PostgreSQL, optimizing Odoo server settings, configuring Nginx caching, and right-sizing your infrastructure for your user count and transaction volume.

Key Takeaways

• PostgreSQL tuning delivers the largest performance gains (50-300% in typical installations)
• Shared buffers should be set to 25% of available RAM as a starting point
• Odoo's ORM generates N+1 queries that can be caught with pg_stat_statements
• Indexes on frequently filtered fields (company_id, state, date) are mandatory
• Nginx proxy caching serves static assets without hitting the Odoo server
• Worker configuration directly impacts concurrent user capacity
• Redis session caching reduces database load for authentication
• Vacuum and analyze schedules must be tuned for high-write Odoo workloads

---

Diagnosing Performance Bottlenecks

Before tuning anything, identify where time is actually being spent. Blind optimization wastes effort.

Enable Odoo query logging:

# odoo.conf
[options]
log_level = info
logfile = /var/log/odoo/odoo.log

# For SQL query logging (development/staging only):
log_handler = odoo.sql_db:DEBUG

Enable PostgreSQL slow query logging:

# /etc/postgresql/15/main/postgresql.conf
log_min_duration_statement = 1000  # Log queries taking > 1 second
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_lock_waits = on

Install pg_stat_statements (most valuable PostgreSQL extension):

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the top 20 slowest queries
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
    left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Reset statistics after tuning to measure improvement
SELECT pg_stat_statements_reset();

Identify N+1 queries in Odoo:

N+1 queries occur when Odoo loads a list of records and then makes one query per record to fetch related data. Look for patterns like this in pg_stat_statements:

-- This query appearing 500 times in a single page load = N+1 problem
SELECT * FROM res_partner WHERE id = $1

The fix is to use Odoo's prefetch_ids mechanism or add select to your ORM query:

# Bad: triggers N+1 for partner on each order
for order in orders:
    print(order.partner_id.name)  # One query per order

# Good: prefetch partner data in one query
orders = self.env['sale.order'].search([...])
orders.mapped('partner_id')  # Forces prefetch
for order in orders:
    print(order.partner_id.name)  # No additional queries

---

PostgreSQL Configuration Tuning

PostgreSQL ships with conservative defaults designed to run on any hardware. For an Odoo production server, these defaults must be tuned.

Memory settings (for a 32 GB RAM server):

# /etc/postgresql/15/main/postgresql.conf

# Shared buffers: 25% of RAM
shared_buffers = 8GB

# Work memory: per-operation memory for sorts/joins
# Start conservative, increase if you see disk sorts
work_mem = 64MB

# Maintenance work memory: for VACUUM, CREATE INDEX
maintenance_work_mem = 2GB

# Effective cache size: tells planner how much OS cache is available
# Set to 75% of total RAM
effective_cache_size = 24GB

# WAL settings for better write performance
wal_buffers = 256MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB

Connection settings:

# Maximum connections (Odoo workers × 2 + headroom)
max_connections = 200

# For connection pooling with PgBouncer
# If using PgBouncer, reduce to 50-100

Query planner settings:

# Enable parallel query execution
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 16

# SSD storage: random_page_cost should equal seq_page_cost
random_page_cost = 1.1  # Default is 4.0 (for spinning disk)
seq_page_cost = 1.0

# Increase statistics target for better query plans on Odoo's large tables
default_statistics_target = 200

Autovacuum tuning for high-write workloads:

Odoo's inventory, accounting, and messaging modules generate heavy INSERT/UPDATE traffic. Default autovacuum settings are insufficient:

autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.01   # Vacuum when 1% of rows are dead
autovacuum_analyze_scale_factor = 0.005  # Analyze when 0.5% of rows change
autovacuum_vacuum_cost_delay = 2ms       # Reduce I/O throttling

---

Critical Database Indexes

Missing indexes are the second-most common cause of Odoo performance issues after poor configuration. Odoo creates indexes for primary keys and some foreign keys, but many commonly filtered fields lack indexes.

Check missing indexes using the pg_missing_fk_indexes view:

-- Find foreign keys without indexes
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    pg_relation_size(tc.table_name::regclass) AS table_size
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
    SELECT 1 FROM pg_indexes pi
    WHERE pi.tablename = tc.table_name
    AND pi.indexdef LIKE '%' || kcu.column_name || '%'
)
ORDER BY table_size DESC;

Essential indexes for Odoo 19:

-- Sale orders (most queried table)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sale_order_state
    ON sale_order(state);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sale_order_company_date
    ON sale_order(company_id, date_order DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sale_order_partner
    ON sale_order(partner_id) WHERE state != 'cancel';

-- Account moves (invoicing)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_account_move_state_type
    ON account_move(state, move_type);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_account_move_company_date
    ON account_move(company_id, invoice_date DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_account_move_partner
    ON account_move(partner_id) WHERE state = 'posted';

-- Account move lines (most queried for reconciliation)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_account_move_line_account_reconcile
    ON account_move_line(account_id, reconciled, date);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_account_move_line_move_date
    ON account_move_line(move_id, date);

-- Stock moves (inventory)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_stock_move_state_product
    ON stock_move(state, product_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_stock_quant_product_location
    ON stock_quant(product_id, location_id);

-- Mail messages (can grow very large)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_mail_message_res_model_id
    ON mail_message(res_model, res_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_mail_message_date
    ON mail_message(date DESC);

-- IR rule performance (access control)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ir_rule_model_groups
    ON ir_rule(model_id);

---

Odoo Worker Configuration

The number of Odoo workers determines how many concurrent requests the server can handle.

Formula for worker count:

Workers = (CPU_cores × 2) + 1
Memory per worker: 256MB - 512MB depending on workload

Example for 8 CPU cores, 32GB RAM:
Workers = (8 × 2) + 1 = 17
Memory check: 17 × 512MB = 8.5GB (well within 32GB)

odoo.conf worker settings:

[options]
# Worker processes
workers = 17

# Limits to prevent runaway workers
limit_memory_hard = 2684354560  # 2.5GB hard limit (kills worker)
limit_memory_soft = 2147483648  # 2GB soft limit (triggers gc)
limit_time_cpu = 600            # CPU seconds per request
limit_time_real = 1200          # Wall clock seconds per request
limit_request = 8192            # Requests before worker restart

# Long polling (for live notifications)
longpolling_port = 8072

Understanding worker types:

Odoo uses two types of workers:

1. HTTP workers (workers config): Handle all web requests
2. Cron workers (1 reserved): Run scheduled actions in the background

The cron worker is always running but doesn't count toward your HTTP capacity. Ensure at least 1 cron worker is available even at peak load.

---

Nginx Configuration for Performance

Nginx sits in front of Odoo and handles TLS termination, static file serving, and optionally caching.

High-performance Nginx configuration:

upstream odoo {
    server 127.0.0.1:8069 weight=1 fail_timeout=0;
}

upstream odoochat {
    server 127.0.0.1:8072 weight=1 fail_timeout=0;
}

# Cache zone for static assets
proxy_cache_path /var/cache/nginx/odoo
    levels=1:2
    keys_zone=odoo_cache:100m
    max_size=1g
    inactive=60m
    use_temp_path=off;

server {
    listen 443 ssl http2;
    server_name your-odoo.com;

    # Gzip compression
    gzip on;
    gzip_types text/plain text/css application/json application/javascript
               text/xml application/xml application/xml+rss text/javascript;
    gzip_min_length 1000;
    gzip_comp_level 6;

    # Static file caching
    location /web/static/ {
        proxy_cache odoo_cache;
        proxy_cache_valid 200 7d;
        proxy_cache_use_stale error timeout updating
            http_500 http_502 http_503 http_504;
        add_header X-Cache-Status $upstream_cache_status;
        expires 7d;
        proxy_pass http://odoo;
    }

    # Long polling for live chat/notifications
    location /web/longpolling {
        proxy_pass http://odoochat;
        proxy_read_timeout 3600s;
        proxy_connect_timeout 300s;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
    }

    # Main application
    location / {
        proxy_pass http://odoo;
        proxy_set_header X-Forwarded-Host $host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_read_timeout 720s;
        proxy_connect_timeout 300s;
        proxy_buffering on;
        proxy_buffer_size 128k;
        proxy_buffers 4 256k;
        proxy_busy_buffers_size 256k;

        # Security headers
        add_header X-Frame-Options DENY;
        add_header X-Content-Type-Options nosniff;
        add_header Referrer-Policy strict-origin-when-cross-origin;
    }
}

---

Redis for Session and Cache

Redis significantly reduces database load for session management and Odoo's ORM cache.

Install and configure Redis:

# Install Redis
sudo apt install redis-server

# Configure Redis for Odoo (max 4GB memory, LRU eviction)
sudo nano /etc/redis/redis.conf

# redis.conf
maxmemory 4gb
maxmemory-policy allkeys-lru
save ""  # Disable persistence for pure cache
tcp-keepalive 300

Configure Odoo to use Redis:

# odoo.conf
[options]
# Redis for session storage
session_redis_host = 127.0.0.1
session_redis_port = 6379
session_redis_prefix = odoo_session_

# Redis for IR rules and ORM cache
cache_redis_host = 127.0.0.1
cache_redis_port = 6379

---

Monitoring and Ongoing Performance Management

Set up pgBadger for PostgreSQL log analysis:

# Install pgBadger
sudo apt install pgbadger

# Generate report from PostgreSQL logs
pgbadger /var/log/postgresql/postgresql-15-main.log \
    -o /var/www/html/pgbadger/index.html \
    --format html \
    --top 20

Key metrics to monitor:

---

Frequently Asked Questions

What is the minimum server specification for 50 concurrent Odoo users?

For 50 concurrent users with moderate transaction volume: 8 vCPUs, 32 GB RAM, 500 GB SSD (NVMe preferred). Odoo's PostgreSQL database is the primary I/O bottleneck, so fast storage matters more than raw CPU speed. Configure 13 workers (8×2-3 for headroom), 8 GB shared_buffers, and ensure your database is on the SSD volume.

How do I diagnose whether my Odoo slowness is Python or PostgreSQL?

Use Odoo's built-in profiler (Settings → Technical → Performance → Profiler in developer mode) to record a slow operation. The flamegraph will show whether time is spent in Python code or waiting for SQL results. If SQL queries dominate, focus on PostgreSQL tuning and indexes. If Python dominates, look for missing @api.depends caching or custom code inefficiencies.

Should I use PgBouncer for connection pooling?

Yes, for deployments with 30+ Odoo workers or heavy API traffic. PgBouncer in transaction-mode pooling allows many Odoo workers to share a smaller pool of actual PostgreSQL connections, reducing the per-connection overhead. Configure max_connections in PostgreSQL to 50-100 when using PgBouncer, then set PgBouncer's pool_size to match your Odoo worker count.

How often should I run VACUUM ANALYZE on an Odoo database?

Autovacuum handles this automatically if configured correctly. After the tuning above (aggressive scale factors, more workers), autovacuum should run continuously on active tables. Run SELECT schemaname, tablename, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20; to verify tables are being vacuumed frequently.

What is the impact of too many Odoo workers?

Each Odoo worker consumes 256–512 MB RAM minimum. Too many workers lead to memory exhaustion, causing workers to crash (limit_memory_hard), which results in HTTP 500 errors for users. Additionally, too many PostgreSQL connections (workers × max_db_connections) can overwhelm the database. Start with the formula (CPU×2+1), monitor memory under load, and adjust down if needed.

---

Next Steps

Odoo performance tuning is an iterative process. A single tuning session delivers significant gains, but sustained performance requires ongoing monitoring, periodic index analysis, and configuration adjustments as your data volume grows.

ECOSIRE provides Odoo performance audits for Enterprise deployments, identifying the highest-impact optimizations for your specific workload, transaction patterns, and infrastructure. Our engineers have tuned Odoo installations from 10-user SMBs to 500-user enterprise deployments.

Request an Odoo Performance Audit from ECOSIRE →

Share your current server specs, user count, and the symptoms you're experiencing, and our team will identify the root causes and deliver a prioritized optimization plan.