Optimizing PostgreSQL for ERP Systems (Adempiere ERP Case Study)

on

Running an ERP like Adempiere on PostgreSQL is very different from running a simple website database. ERP workloads are transaction-heavy, with constant inserts, updates, and reporting queries running in parallel. If PostgreSQL is not tuned, you’ll quickly run into issues like:

  • Slow reports that take hours to finish.

  • Too many idle connections blocking new users.

  • Table and index bloat that silently kills performance.

In this article, we’ll look at the most important PostgreSQL settings for ERP workloads, based on real-world Adempiere experience.

1. Memory Configuration for ERP Workloads

By default, PostgreSQL is conservative. On a 64 GB dedicated database server, the defaults use only a fraction of available memory. ERP software needs more aggressive tuning to handle complex joins and batch posting jobs.

Key memory parameters to adjust:

  • shared_buffers
    ~25–30% of RAM → ~16 GB on a 64 GB server.
    This is PostgreSQL’s main data cache.

  • work_mem
    Memory for each sort/join operation. ERP reports often have large joins.
    Set to 64MB–128MB, but remember: it’s per operation, per connection.

  • maintenance_work_mem
    Used for maintenance tasks (VACUUM, CREATE INDEX).
    Set to 2GB or higher to speed up vacuuming and reindexing.

  • effective_cache_size
    How much OS cache is available for query planner estimates.
    ~75% of total RAM (≈ 48 GB).

  • wal_buffers & checkpoint tuning
    Increase WAL buffers (e.g. 256MB) and tune checkpoints so large ERP transactions don’t stall.

2. The Busy Connection Problem

One of the most common issues in Adempiere PostgreSQL is idle transactions.

When you run:

SELECT * FROM pg_stat_activity;

you may see many lines like:

idle in transaction

This means an application opened a transaction but never committed or rolled it back. In Adempiere, this can happen if code does not close connections properly.

Why it’s dangerous:

  • It holds locks → blocking other users.

  • It consumes memory.

  • It can keep autovacuum from cleaning up tables.

  • In worst cases, it can cause “too many connections” errors.

Solution

Set timeouts in postgresql.conf:

statement_timeout = '30min'                  # no query runs forever
idle_in_transaction_session_timeout = '5min' # kill bad idle sessions

👉 Apply these on servers, not on developer machines.

Developers should regularly check:

SELECT * FROM pg_stat_activity;

and trace any idle in transaction sessions back to their code.

3. Autovacuum – The Unsung Hero

ERP workloads generate tons of updates and deletes. In PostgreSQL, these leave behind “dead tuples” which bloat tables and indexes.

Without cleanup, queries on large ERP tables (fact_acct, c_orderline, m_transaction) will slow to a crawl.

Autovacuum is the background process that prevents this. But the defaults are tuned for small databases, not busy ERP systems.

Better Autovacuum Settings for ERP

autovacuum = on
autovacuum_naptime = 10s
autovacuum_max_workers = 8
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms

autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_insert_scale_factor = 0.05
autovacuum_vacuum_insert_threshold = 1000

Benefits:

  • Runs more frequently (not waiting for 20% of the table to change).

  • Handles insert-heavy tables (important for ERP accounting).

  • Keeps planner statistics fresh, so reports run with the right execution plans.

  • Prevents index bloat before it becomes unmanageable.

4. Final Checklist – PostgreSQL on a 64 GB Dedicated ERP Server

Here’s a consolidated list of recommended changes:

🔹 Memory & Performance

shared_buffers = 16GB
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB
wal_buffers = 256MB
max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

🔹 Connection Safety

statement_timeout = '30min'
idle_in_transaction_session_timeout = '5min'

🔹 Autovacuum

autovacuum = on
autovacuum_naptime = 10s
autovacuum_max_workers = 8
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_insert_scale_factor = 0.05
autovacuum_vacuum_insert_threshold = 1000

Conclusion

Adempiere ERP (and similar ERP systems) push PostgreSQL harder than most applications. By tuning memory settings, controlling idle connections, and configuring autovacuum properly, you can transform PostgreSQL from a slow, bloated system into a fast and reliable ERP backbone.

A little tuning goes a long way — the difference between reports taking 2 hours vs 20 minutes often comes down to PostgreSQL configuration.