Your ERPNext Database Hit 40GB: What to Delete, Archive, and Partition

Your ERPNext database has crossed 40GB, restores now take four hours, and someone has proposed migrating to a bigger server. Before you do, run one query. On most of the "huge" ERPNext databases I have opened, the actual business data - invoices, ledger entries, stock movements - is under 20 percent of the total size. The rest is log tables, version history, and dead email rows that nobody has ever looked at.

You do not have a big data problem. You have a housekeeping problem wearing a big data costume. Here is the order of operations.

Step one: measure, do not assume

SELECT table_name,
       table_rows,
       ROUND(data_length /1024/1024) AS data_mb,
       ROUND(index_length/1024/1024) AS index_mb,
       ROUND(data_free  /1024/1024) AS free_mb,
       ROUND((data_length + index_length)/1024/1024) AS total_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 25;

A typical result on a five-year-old ERPNext site:

+---------------------------+------------+---------+----------+---------+----------+
| table_name                | table_rows | data_mb | index_mb | free_mb | total_mb |
+---------------------------+------------+---------+----------+---------+----------+
| tabVersion                |   18244190 |   14822 |     1104 |     220 |    15926 |
| __global_search           |    9912004 |    6140 |     3980 |      64 |    10120 |
| tabError Log              |    2044188 |    4410 |       88 |      12 |     4498 |
| tabEmail Queue            |     844021 |    2980 |       74 |      36 |     3054 |
| tabGL Entry               |    4128744 |    2210 |     1880 |     140 |     4090 |
| tabStock Ledger Entry     |    3011922 |    1640 |      980 |      88 |     2620 |
| tabScheduled Job Log      |    6120334 |    1180 |      212 |      20 |     1392 |
| tabActivity Log           |    1802991 |     740 |      166 |      10 |      906 |
| tabSales Invoice Item     |     980412 |     512 |      302 |      18 |      814 |
| tabDeleted Document       |      41208 |     602 |        4 |       2 |      606 |
+---------------------------+------------+---------+----------+---------+----------+

Read that carefully. tabGL Entry and tabStock Ledger Entry, the tables the business actually depends on, are 6.7GB out of 40. tabVersion alone is nearly 16GB. And table_rows from information_schema is an InnoDB estimate, sometimes off by 30 percent, so confirm with SELECT COUNT(*) before you act on any single number.

Step two: kill the logs, and stop them coming back

Frappe ships a Log Settings DocType that sets a retention window per log DocType. Open it in the Desk and set sensible values: Error Log 30 days, Activity Log 90, Email Queue 30, Scheduled Job Log 7, Route History 30. Then force the cleanup rather than waiting for the nightly scheduler:

bench --site erp.example.co.ke execute \
  frappe.core.doctype.log_settings.log_settings.run_log_clean_up

For a table with millions of rows, the row-by-row delete path is too slow and generates enormous binlog and undo. Frappe has a faster primitive: clear_log_table creates a new table, copies only the recent rows into it, and swaps the tables with a rename. It is close to instant regardless of how big the original was.

bench --site erp.example.co.ke execute \
  frappe.core.doctype.log_settings.log_settings.clear_log_table \
  --kwargs "{'doctype': 'Error Log', 'days': 30}"

bench --site erp.example.co.ke execute \
  frappe.core.doctype.log_settings.log_settings.clear_log_table \
  --kwargs "{'doctype': 'Scheduled Job Log', 'days': 7}"

Take a backup first. This function drops the original table after the swap. There is no undo.

tabVersion is the real monster

Every time a document is saved, Frappe writes a JSON diff into tabVersion. On a site where a scheduled job touches Item or Bin records constantly, this table grows without bound and nobody ever reads it. Trim it:

SELECT ref_doctype, COUNT(*) AS n
FROM `tabVersion`
GROUP BY ref_doctype
ORDER BY n DESC
LIMIT 15;

Then stop the source. In Customize Form, uncheck Track Changes on the high-churn DocTypes the query above exposes - typically Item, Bin, Stock Reconciliation and anything a custom integration updates on a loop. Keep it on for the DocTypes where audit history is a compliance requirement: Sales Invoice, Purchase Invoice, Journal Entry, Customer, Supplier.

The other quiet offenders

• __global_search - the search index. It can be dropped and rebuilt: truncate it, then run bench --site ... rebuild-global-search. Rebuilding a 10GB index takes hours, so schedule it for a weekend.
• tabEmail Queue and tabEmail Queue Recipient - full copies of every email body ever sent, including PDF-embedding HTML.
• tabDeleted Document - stores the complete JSON of every deleted document forever.
• tabPrepared Report - cached report output, plus rows in tabFile and real files on disk.
• tabView Log, tabAccess Log, tabRoute History, tabNotification Log - pure telemetry.

Step three: archive real business data (carefully)

Once the logs are gone, if you are still large, you are dealing with genuine transactions. This needs more caution: tabGL Entry rows are legally significant and linked to submitted documents.

Never run a single giant DELETE

A DELETE FROM tabGL Entry WHERE posting_date < '2021-04-01' against 2 million rows holds one transaction open for many minutes, inflates the undo log, blocks other writers, and can fill your disk with binlog. Chunk it:

-- run in a loop from a shell script, not interactively
DELETE FROM `tabActivity Log`
WHERE creation < DATE_SUB(NOW(), INTERVAL 180 DAY)
ORDER BY creation
LIMIT 5000;

while :; do
  n=$(mariadb -N -B _5f1a2b3c4d5e6f70 -e \
    "DELETE FROM \`tabActivity Log\` WHERE creation < DATE_SUB(NOW(), INTERVAL 180 DAY) LIMIT 5000; SELECT ROW_COUNT();")
  echo "deleted $n"
  [ "$n" -eq 0 ] && break
  sleep 0.5
done

The sleep matters. It gives replication and the InnoDB purge thread room to catch up.

Or use pt-archiver, which was built for exactly this

pt-archiver \
  --source h=localhost,D=_5f1a2b3c4d5e6f70,t=tabVersion,i=PRIMARY \
  --dest   h=archive-db,D=erp_archive,t=tabVersion \
  --where "creation < DATE_SUB(NOW(), INTERVAL 365 DAY)" \
  --limit 1000 \
  --commit-each \
  --sleep 0.5 \
  --charset utf8mb4 \
  --progress 100000 \
  --statistics

Swap --dest for --purge if you want deletion without copying. pt-archiver walks the table by its index, keeps transactions tiny, and can be stopped and resumed. It is still doing row-by-row DELETEs underneath, so it is slow on very large tables - but it is safe, and safe beats fast on production accounting data.

Step four: partitioning, and the trap nobody warns you about

RANGE partitioning on posting_date is the textbook answer for time-series ERP tables: dropping a partition removes a year of data instantly, with no DELETE at all. It also enables partition pruning so a query for FY2026 never touches FY2019 pages.

Here is the trap. In MariaDB, every unique key - including the primary key - must contain all columns used in the partitioning expression. Every Frappe table's primary key is name varchar(140). So you cannot partition tabGL Entry by posting_date without first changing the primary key to a composite:

ALTER TABLE `tabGL Entry`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (`name`, `posting_date`),
  PARTITION BY RANGE (YEAR(`posting_date`)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
  );

Be clear-eyed about what you are signing up for. Frappe and ERPNext have no built-in awareness of partitioned tables; this is a manual, unsupported customisation that you must re-apply after any restore and re-verify after every major upgrade. The composite primary key also changes the clustered index layout, which makes every secondary index wider. Test it on a clone, measure, and only keep it if the numbers justify the ongoing maintenance. Aim for well under 50 partitions - MariaDB supports thousands, but high counts slow down table opening.

Dropping old data afterwards becomes trivial:

ALTER TABLE `tabGL Entry` DROP PARTITION p2021;

Step five: actually reclaim the disk

Here is the punchline that catches everyone: after deleting 15GB of rows, df -h shows no change. InnoDB marks the pages free inside the tablespace file but does not shrink the file. That is what the data_free column in the first query was telling you.

Rebuild the table to hand the space back to the filesystem:

ALTER TABLE `tabVersion` FORCE, ALGORITHM=INPLACE, LOCK=NONE;
-- OPTIMIZE TABLE `tabVersion`;  -- equivalent for InnoDB, but noisier

Two prerequisites. innodb_file_per_table must be ON, which it is by default on any modern MariaDB, otherwise the space goes back to the shared ibdata1 and stays there forever. And you need free disk space at least equal to the current table size, because InnoDB builds a complete new copy before swapping. Rebuilding a 16GB table on a server with 10GB free will fail partway and leave you worse off.

Step six: stop it happening again

• Set Log Settings retention on day one of every new site, not on the day the disk fills.
• Uncheck Track Changes on DocTypes that no auditor will ever ask about.
• Track growth so you see the curve before it becomes a cliff. A weekly cron that appends the top-25 table sizes to a small table gives you a trend line and a culprit.
• Size the buffer pool for the working set, not the database. A 40GB database where the hot last-90-days data is 6GB runs beautifully on a server with innodb_buffer_pool_size = 8G. The same 40GB with a 2GB buffer pool thrashes constantly. Check your hit rate: SHOW ENGINE INNODB STATUS\G and look for a buffer pool hit rate below 995 per 1000.

Do the free work first. Housekeeping and Log Settings usually reclaim more than half the database in an afternoon, with no schema risk and no downtime. Only when the remaining bytes are genuinely business records should you reach for archiving or partitioning - and by then you will have real numbers to justify the complexity, instead of a hunch and a bigger invoice from your hosting provider.