PostgreSQL Tuning for Odoo: Concrete Values by Server Size

Our earlier post on the Odoo workers formula covered the basics of shared_buffers and db_maxconn sizing. This post goes further: concrete, ready-to-use postgresql.conf values by server RAM, not general guidance to "tune based on your workload."

The values, by server size

Setting	8GB RAM	16GB RAM	32GB RAM
shared_buffers	2GB	4GB	8GB
effective_cache_size	6GB	12GB	24GB
work_mem	32MB	64MB	128MB
maintenance_work_mem	512MB	1GB	2GB
max_connections	100	150	250
wal_buffers	16MB	16MB	32MB
checkpoint_completion_target	0.9	0.9	0.9
random_page_cost (SSD)	1.1	1.1	1.1
default_statistics_target	100	100	100

These are starting points sized for a server dedicated to Odoo and PostgreSQL together, not shared with unrelated heavy workloads. The 25% of RAM rule for shared_buffers and roughly 75% for effective_cache_size is the underlying logic, these are that logic already computed for the three common server sizes.

If Odoo's application workers run on the same box as PostgreSQL, which is the common single-server setup for small and mid-sized deployments, remember these values assume PostgreSQL isn't the only thing competing for RAM. A server running 6 Odoo workers at roughly 200 to 300MB each, plus Nginx, plus the OS itself, needs that overhead accounted for before shared_buffers is sized off total RAM. On an 8GB server running Odoo and PostgreSQL together, the table's 2GB shared_buffers already assumes roughly 2 to 3GB is reserved for Odoo's own workers and the OS, if your worker count is higher than typical for that RAM tier, scale shared_buffers down slightly rather than using the table value unmodified.

work_mem needs a second calculation, not just the table value

work_mem is allocated per sort/hash operation, and a single complex query can use multiple work_mem allocations simultaneously, multiplied by concurrent connections running similar queries. The table values above are conservative defaults; if you have many concurrent users running complex reports simultaneously, calculate a ceiling:

# Rough ceiling check
available_ram_for_work_mem = total_ram - shared_buffers - os_reserve
max_concurrent_complex_queries = your_worker_count
safe_work_mem = available_ram_for_work_mem / max_concurrent_complex_queries

Setting work_mem too high server-wide is a real risk, not a theoretical one, PostgreSQL can exhaust available memory under concurrent load if every connection's queries grab the configured maximum simultaneously. When in doubt, err lower and set higher work_mem only per-session for known-heavy reporting queries via SET work_mem = '256MB'; at the start of that specific query.

Applying the config

sudo nano /etc/postgresql/16/main/postgresql.conf
# set the values from the table above

sudo systemctl restart postgresql

A full restart, not just a reload, is required for shared_buffers, max_connections, and wal_buffers changes to take effect, these are not settings PostgreSQL can apply without restarting the server process. Settings like work_mem, maintenance_work_mem, checkpoint_completion_target and the planner cost settings do take effect on a plain reload (sudo systemctl reload postgresql), which matters if you're iterating on those specifically and want to avoid a full connection-dropping restart each time. When in doubt, PostgreSQL's own documentation marks each setting's required "context" (postmaster, sighup, or user), check that rather than assuming.

Change one setting category at a time on a production server rather than applying the whole table in a single restart with no baseline, if something regresses afterward (rare, but possible on unusual workloads), you want to know which specific change caused it, not be left guessing across nine simultaneous changes.

How to verify the tuning actually helped

# Confirm shared_buffers actually applied
sudo -u postgres psql -c "SHOW shared_buffers;"

# Check cache hit ratio -- should be consistently above 99% for a
# well-tuned, warmed-up production database
sudo -u postgres psql -c "
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  round(sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) as ratio
FROM pg_statio_user_tables;"

A cache hit ratio meaningfully below 99% on a server that's been running under normal load for a while (not right after a restart, when caches are cold) suggests shared_buffers is still undersized relative to your actual working data set.

Checkpoint tuning: the setting people skip

Beyond the resource settings in the table, checkpoint behaviour is worth tuning separately, and it's easy to miss because the symptoms (periodic write-heavy stalls) look like a completely different problem at first. PostgreSQL writes changes to the write-ahead log continuously, but only periodically flushes ("checkpoints") the actual data files. If checkpoints happen too frequently, or too much work piles up between them, you get visible I/O spikes under write-heavy Odoo workloads, bulk imports, month-end batch jobs, mass write operations from automated actions:

max_wal_size = 4GB          # 2GB for 8GB RAM servers, 8GB for 32GB
min_wal_size = 1GB
checkpoint_timeout = 15min

Check whether checkpoint tuning is actually your bottleneck before changing these blind:

sudo -u postgres psql -c "SELECT * FROM pg_stat_bgwriter;"

A high checkpoints_req relative to checkpoints_timed means checkpoints are being forced by write volume rather than happening on the calm, scheduled timer, that's the specific signal that max_wal_size is too low for your write load.

Autovacuum: the setting that causes slow bloat, not sudden failures

Odoo's ORM does a lot of updates and deletes on records (state changes on sale orders, stock moves, mail messages), which means table bloat from dead tuples is a real, ongoing concern, not an edge case. PostgreSQL's autovacuum handles this automatically with reasonable defaults, but the default thresholds are tuned for general workloads, not Odoo's specific write patterns on its busiest tables:

autovacuum_vacuum_scale_factor = 0.05    # default is 0.2, too infrequent for busy Odoo tables
autovacuum_analyze_scale_factor = 0.02   # default is 0.1

These lower thresholds mean autovacuum runs more often, on a smaller percentage of dead rows, which keeps bloat from accumulating on high-churn tables like mail_message, bus_bus, and stock_move. Check whether bloat is already a problem on an existing instance before assuming it isn't:

sudo -u postgres psql your_db -c "
SELECT relname, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 1) as dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;"

A table showing a dead-tuple percentage consistently above 20% on a table that's queried frequently is a real performance drag, index scans and sequential scans both get slower when a meaningful fraction of the pages they touch are dead rows waiting to be reclaimed.

Connection pooling: when raising max_connections isn't the right answer

The table above raises max_connections alongside RAM, but there's a ceiling to how far that alone should go. Each PostgreSQL connection carries real memory overhead independent of work_mem, and a server with a very high worker count times a high db_maxconn per worker can approach that ceiling faster than expected on a smaller server. If you're running many Odoo workers, or several separate Odoo instances against the same PostgreSQL server, a connection pooler like PgBouncer sitting between Odoo and PostgreSQL is often a better answer than continuing to raise max_connections indefinitely, it multiplexes many client connections onto a smaller number of actual PostgreSQL backend connections.

# A conservative starting PgBouncer config for Odoo, in transaction pooling mode
[databases]
your_db = host=127.0.0.1 port=5432 dbname=your_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
pool_mode = transaction
max_client_conn = 200
default_pool_size = 25

pool_mode = transaction is the correct mode for Odoo specifically, session pooling would defeat the purpose since each Odoo worker would still hold a dedicated backend connection for its lifetime, and statement pooling breaks features Odoo relies on like prepared statements and session-level settings. Point db_port in odoo.conf at PgBouncer's port (6432 above) instead of PostgreSQL's own port once this is in place, and confirm the change with a connection count check on the PostgreSQL side, you should see meaningfully fewer actual backend connections than before, even under the same Odoo traffic.

This is the ceiling for tuning alone

Correct postgresql.conf values solve the majority of "Odoo feels slow" cases we see. But tuning the primary server has a ceiling, if your reporting and dashboard load is large enough that no amount of primary tuning keeps transactional operations fast during business hours, that's the point where a read replica becomes the right next step rather than continuing to push single-server tuning further. See our read replica scaling post for when that trade-off actually makes sense, and read it before spending budget on a replica, in a meaningful share of cases the primary just wasn't tuned yet, and doing so closes the gap without adding infrastructure.

Sources: PostgreSQL resource configuration documentation, PostgreSQL routine vacuuming documentation.