The Data Layer and Performance

ERP databases are large, wide, heavily indexed, and queried by people who don’t know SQL. Performance work here is a permanent job.

What ERP data actually looks like

Characteristic Reality
Table count 5,000 – 100,000+ (SAP ECC ships ~90,000)
Columns per table 50–500 is normal
Largest tables Journal lines, material documents, change logs, deliveries — billions of rows
Growth Append-heavy; almost nothing is deleted
Access pattern Point lookups by document key + huge aggregations at close
Retention 7–10 years minimum, often forever
Referential integrity Enormous, often enforced in the application rather than the DB

The indexing problem

An ERP table is queried on dozens of different key combinations, because different roles ask different questions.

journal_line, queried by:
  document number             (drill-down from a report)
  account + period            (trial balance)
  cost centre + period        (cost centre report)
  customer + open items       (AR ageing)
  posting date range          (period reports)
  reference document          (traceability)
  created_by + date           (audit)

You cannot index all of them without destroying write performance and doubling storage.

Strategy:

  1. The primary access path gets a covering index. Usually (tenant, company, fiscal_year, document_number).
  2. Secondary indexes for the top 3–5 real query patterns, measured from production, not guessed.
  3. Everything else goes to a read replica or a reporting store.
  4. Partition large tables by fiscal year or posting date. Most queries touch one or two periods; partition pruning is the single biggest win available on ledger tables.
  5. Archive cold data out of the hot tables.

Partitioning 

CREATE TABLE journal_line (
  ...
  posting_date DATE NOT NULL
) PARTITION BY RANGE (posting_date);

CREATE TABLE journal_line_2026 PARTITION OF journal_line
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Benefits: partition pruning on date-filtered queries, per-partition index maintenance, and — the underrated one — dropping a partition is instant where deleting the equivalent rows would take hours and bloat the table.

Choose the key carefully: partition by the column that appears in nearly every WHERE clause. For ledgers that’s posting_date or fiscal_year. For multi-tenant systems it may be tenant_id, or both (composite/sub-partitioning).

Aggregates: derived vs materialised

The eternal ERP trade-off.

Approach Consistency Read speed Write cost
Compute on read Always correct Slow at scale Zero
Materialised totals, updated in the same transaction Correct if the code is correct Fast Contention on hot rows
Materialised, updated asynchronously Eventually correct Fast Low
Columnar / in-memory aggregation Always correct Fast Zero

Classic ERPs chose option 2 and carried dozens of totals tables. S/4HANA deleted most of them because option 4 became viable.

If you’re building on PostgreSQL/MySQL:

journal_line              ← truth, append only
account_balance_period    ← materialised: (company, ledger, account, dims, period)
                            updated in the same transaction as the posting
balance_reconciliation    ← nightly job: rebuild from journal_line, compare, alert

The nightly reconciliation is non-negotiable. Materialised aggregates drift. Without a job that proves they still match, you find out from an auditor.

Hot row contention

Every posting to the same account/period updates the same balance row. Under load this serialises.

Mitigations:

  • Sharded counters — N rows per key, summed on read, each transaction updating a random shard
  • Asynchronous aggregation with a short lag
  • Insert deltas instead of updating a balance — balance_delta(key, amount) rows, periodically compacted. Turns an UPDATE hotspot into an INSERT, which scales far better

Query patterns to avoid

The N+1 

for each order:                      -- 1 query
    fetch order lines                -- N queries
    for each line:
        fetch material               -- N×M queries

The classic. In ERP it’s worse than usual because loops over document lines are everywhere. Batch-fetch and join in memory, or use a single joined query.

SELECT *

Wide ERP tables mean SELECT * transfers 300 columns to read 4. Always project explicitly.

Client-side filtering 

-- Wrong
rows = SELECT * FROM journal_line WHERE company = 1000
filtered = rows.filter(r => r.posting_date >= start)   -- 40M rows transferred

-- Right
SELECT ... FROM journal_line
WHERE company = 1000 AND posting_date BETWEEN start AND end

Obvious, and it still happens constantly in report code.

Unbounded queries

Every user-facing query needs a limit and a date range. “Show all customers” on a table with 2 million rows is a denial of service against your own system. Default the date range; force a filter.

Functions on indexed columns 

WHERE YEAR(posting_date) = 2026            -- index unusable
WHERE posting_date >= '2026-01-01'
  AND posting_date <  '2027-01-01'         -- index used

Implicit type conversion

Comparing a VARCHAR document number to an integer literal disables the index silently. ERP keys are frequently zero-padded strings ('0004500001'), which makes this a common trap.

Batch job performance

The close window is your peak. Techniques:

  1. Parallelise by natural key ranges — company code, plant, asset number range. Embarrassingly parallel in most cases.
  2. Commit in batches — every 1,000–10,000 records, not once at the end (a 6-hour transaction blocks vacuum/cleanup and makes recovery brutal) and not per record (commit overhead dominates).
  3. Bulk operations — COPY, array binds, batch inserts. Row-by-row is 10–100× slower.
  4. Restartability — checkpoint progress so a failure at 80% resumes rather than restarts.
  5. Read the data once. Many close jobs re-read the same base data; stage it.
  6. Avoid index churn — for very large loads, drop and rebuild non-essential indexes.
  7. Test at production volume. “Fast in DEV with 500 rows” is how go-live weekends get ruined.

Archiving

Data you must keep but rarely query:

Live (0–2 years)      → hot tables, fully indexed
Near-line (2–5 years) → separate tablespace or columnar store, queryable, slower
Archived (5+ years)   → archive files or object storage, retrievable on request
Purged                → only after the legal retention period

Archiving is a real ERP function with a defined sequence: check the object can be archived (no open items, order closed, period closed), write to the archive, verify, then delete from the live table. Never delete before verifying the archive is readable.

Legal retention is typically 7–10 years for financial records, longer for some (personnel records, product liability, pharma batch records can be 30+ years). Purge before the retention period and you’ve committed an offence; keep personal data forever and you’ve breached GDPR. Both directions have legal consequences.

Monitoring

What to watch:

Signal Why
Slow query log The obvious one
Top queries by total time Optimise what runs 10,000×/day, not the one slow ad-hoc
Lock waits and deadlocks Contention on hot rows
Table and index bloat Append-heavy tables with updates
Table growth rate Predicts when you’ll hit a wall
Batch job durations, tracked over time A job creeping from 20 min to 3 hours is an early warning
Connection pool saturation The batch window competing with users
Replication lag Reports reading stale data
Missing/unused indexes Both are problems

Track batch job durations as a time series. The trend tells you months in advance that the close window will break.

Database choice

DB Fit
PostgreSQL Excellent default. Partitioning, JSONB for flexible fields, strong consistency, extensions. What most new ERPs are built on
SQL Server Dynamics ecosystem, strong tooling, columnstore indexes are genuinely good for reporting
Oracle The legacy incumbent; partitioning and RAC are mature
SAP HANA In-memory columnar; removes the OLTP/OLAP split. Expensive
MySQL/MariaDB Workable (ERPNext uses it); weaker partitioning and analytics story
NoSQL for the ledger No. You need multi-row ACID transactions
NoSQL for satellites Fine — documents, logs, search, sessions, caches

Practical rules

  1. Design the schema first. In ERP the schema is the architecture.
  2. NUMERIC/DECIMAL for money. Never float.
  3. Append-only for financial and inventory movements.
  4. Partition your biggest tables by date from day one. Adding partitioning to a 2-billion-row table later is a migration project.
  5. Materialise aggregates, and reconcile them nightly.
  6. Index deliberately, from measured production patterns.
  7. Every user query has a bound.
  8. Batch jobs: parallel, chunked commits, restartable, measured.
  9. Plan archiving before you need it.

Next