- Measure before you delete. Four tables cause almost all growth.
- Expired transients, revisions, orphaned metadata and old Action Scheduler rows are safe to remove. Order data is not.
- Back up and test the restore before any DELETE. There is no undo.
- Cap revisions and keep the scheduler running, or the growth comes back.
What is making my WordPress database so large?
Almost always the same four things: expired transients in wp_options, post revisions in wp_posts, orphaned metadata in wp_postmeta and, on WooCommerce sites, the Action Scheduler tables. Back up first, measure which one is your problem, then delete only those rows. Orders, customers and logins stay untouched.
cd /var/www/example.com
# Every table by size, largest first
wp db query "SELECT table_name,
ROUND(data_length/1024/1024,1) AS data_mb,
ROUND(index_length/1024/1024,1) AS index_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC LIMIT 20"
# Total size
wp db size --human-readable
# Autoloaded options: read on EVERY page load. Keep under 1 MB.
wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024/1024,2) AS autoload_mb
FROM wp_options WHERE autoload='yes'"| Table | Usually holds | Safe to trim? |
|---|---|---|
| wp_options | Settings, transients, plugin caches | Expired transients only |
| wp_posts | Content, revisions, orders | Revisions and trash only |
| wp_postmeta | Metadata, much of it orphaned | Orphaned rows only |
| wp_actionscheduler_actions | WooCommerce background jobs | Completed and failed, older than 30 days |
| wp_woocommerce_order_items | Order line items | Never |
| wp_users, wp_usermeta | Accounts | Never, without a deliberate policy |
The last two rows are why a one-click optimisation plugin can be dangerous on a shop.
Back up and test the restore first
Every command below deletes rows for good. Take a dump, restore it somewhere else and check the copy loads. Until you have restored a backup, you are only assuming it works. See our backup strategy guide.
wp db export /var/backups/pre-cleanup-$(date +%F).sql
gzip /var/backups/pre-cleanup-$(date +%F).sql
# Prove it restores, into a scratch database
mysql -e 'CREATE DATABASE restore_test'
gunzip -c /var/backups/pre-cleanup-$(date +%F).sql.gz | mysql restore_test
mysql restore_test -e 'SELECT COUNT(*) FROM wp_posts'
mysql -e 'DROP DATABASE restore_test'Which data is safe to delete?
Start with transients: cached values with an expiry date, which WordPress rebuilds if it still needs them. Expired ones are pure waste. On a neglected site they are often the biggest cause of a slow options table.
# The safe, supported way
wp transient delete --expired
# Site transients on multisite
wp transient delete --expired --network
# How much space is involved
wp db query "SELECT COUNT(*) AS rows_count,
ROUND(SUM(LENGTH(option_value))/1024/1024,2) AS mb
FROM wp_options WHERE option_name LIKE '%_transient_%'"# How many revisions exist?
wp post list --post_type=revision --format=count
# Delete them all (they are copies; published content is untouched)
wp post delete $(wp post list --post_type=revision --format=ids) --force
# Empty the trash
wp post delete $(wp post list --post_status=trash --format=ids) --force
# Spam and trashed comments
wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force
# Stop revisions accumulating again, in wp-config.php:
# define('WP_POST_REVISIONS', 5);# Post meta whose post no longer exists
wp db query "DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL"
# Term relationships pointing at deleted posts
wp db query "DELETE tr FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON p.ID = tr.object_id
WHERE p.ID IS NULL"
# Comment meta for comments that are gone
wp db query "DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL"SELECT COUNT(*) FROM wp_postmeta pm LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL gives you the number without touching anything. If the count surprises you, stop and find out why.
How do I clean up WooCommerce safely?
The Action Scheduler tables (WooCommerce's background job queue) can reach millions of rows on a busy shop, and are the usual reason its admin slows down. They are safe to trim. Order data is not.
# How large has it become?
wp db query "SELECT status, COUNT(*) FROM wp_actionscheduler_actions GROUP BY status"
# Supported cleanup
wp action-scheduler clean --batch-size=1000
# Remove completed actions older than 30 days
wp db query "DELETE FROM wp_actionscheduler_actions
WHERE status IN ('complete','failed')
AND scheduled_date_gmt < DATE_SUB(NOW(), INTERVAL 30 DAY)"
# Then the orphaned log rows they leave behind
wp db query "DELETE l FROM wp_actionscheduler_logs l
LEFT JOIN wp_actionscheduler_actions a ON a.action_id = l.action_id
WHERE a.action_id IS NULL"Never touch these
wp_posts rows where post_type is shop_order or shop_order_refund, wp_woocommerce_order_items, wp_woocommerce_order_itemmeta and, on newer installs, wp_wc_orders and its related tables. These are your financial records. You need them for refunds, disputes, accounts and HMRC, and you cannot get them back from anywhere else.
# Expired sessions: rebuilt automatically, safe to clear
wp db query "DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP()"
# Old WooCommerce logs
wp db query "DELETE FROM wp_woocommerce_log
WHERE timestamp < DATE_SUB(NOW(), INTERVAL 30 DAY)"
# Orphaned lookup rows for products that no longer exist
wp db query "DELETE lt FROM wp_wc_product_meta_lookup lt
LEFT JOIN wp_posts p ON p.ID = lt.product_id
WHERE p.ID IS NULL"
# Rebuild the product lookup tables afterwards
wp wc tool run regenerate_product_lookup_tables --user=1Should I optimise tables afterwards?
Deleting rows leaves gaps in the data files, so disk use does not fall until the table is rebuilt. On InnoDB, OPTIMIZE TABLE rebuilds it and reclaims the space. It is heavy work, so do it in a quiet window.
# One table at a time, largest first, during a quiet period
wp db query "OPTIMIZE TABLE wp_postmeta"
wp db query "OPTIMIZE TABLE wp_options"
wp db query "OPTIMIZE TABLE wp_actionscheduler_actions"
# WP-CLI can do the lot, but check the timing first
wp db optimize
# How much unused space is sitting in each table?
wp db query "SELECT table_name, ROUND(data_free/1024/1024,1) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE() AND data_free > 0
ORDER BY data_free DESC"data_free is the space you can reclaim. If it is small, optimising gains nothing and is not worth the load.OPTIMIZE TABLE can block the site
It copies the whole table, which on a large wp_postmeta takes minutes and needs free disk about the table's size. Recent MySQL and MariaDB keep the table usable for most of that time; older versions, and tables with full-text indexes, lock it throughout. Run it out of hours, one table at a time.
How do I stop it growing back?
A one-off cleanup buys a few months. These five changes keep it clean, and you set each of them once.
- 1Cap post revisions with
define('WP_POST_REVISIONS', 5);in wp-config.php. The default is unlimited, which causes most wp_posts growth. - 2Empty the trash sooner with
define('EMPTY_TRASH_DAYS', 7);. The default is 30. - 3Make sure the scheduler is running. Transient and Action Scheduler cleanup are scheduled tasks too, so a stalled queue means cleanup never happens. See WordPress cron and missed tasks.
- 4Check plugins that log to the database. Anything writing a row per page view will outgrow everything else. Point it at a file, or remove it.
- 5Check the autoloaded options figure monthly. It most directly affects how fast every page loads.
#!/bin/sh
cd /var/www/example.com
echo "--- total ---"; wp db size --human-readable
echo "--- autoload ---"; wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024/1024,2) AS autoload_mb FROM wp_options WHERE autoload='yes'"
echo "--- revisions ---"; wp post list --post_type=revision --format=count
echo "--- scheduler ---"; wp db query "SELECT status, COUNT(*) FROM wp_actionscheduler_actions GROUP BY status"A smaller database is faster to query, back up and restore. If queries are still slow after trimming, cache the results instead: see WordPress object caching with Redis. On G7Cloud each site has its own database, with daily backups you restore from the dashboard, so a cleanup you regret can be undone. See how our backups work.
Frequently asked questions
What makes a WordPress database grow so large?
Usually expired transients in wp_options, unlimited post revisions in wp_posts, orphaned rows in wp_postmeta and, on WooCommerce sites, the Action Scheduler tables. Plugins that log to the database rather than to a file are the other common cause.
Is it safe to delete WordPress transients?
Expired ones, yes: WordPress rebuilds anything it still needs. Use wp transient delete --expired rather than raw SQL, because it also removes the matching timeout rows that hand-written DELETEs often leave behind.
Can I delete old WooCommerce orders to save space?
Not casually. Orders are financial records you may need for refunds, chargebacks and accounts, and UK tax rules expect you to keep them for years. If you have a retention policy, export first and delete through WooCommerce's own tools, never with direct SQL against wp_posts.
What are autoloaded options and why do they matter more than table size?
They are settings read on every page load, including AJAX and REST requests. 5 MB of them is read and unpacked on every hit, which costs far more than gigabytes of rarely touched order history. Keep it under 1 MB.
Should I use a database optimisation plugin?
With care. They are handy for transients and revisions, but one-click buttons often offer to remove things that are not safe on a shop, and a large job can stop halfway when PHP's time limit hits. Back up first either way.
Does OPTIMIZE TABLE speed up WordPress?
It reclaims disk space left by deleted rows and can slightly speed up full scans. It is not a general speed fix, and it puts heavy load on the database while it runs. Only bother where data_free is large, and always out of hours.
About G7Cloud Engineering
Articles written by the engineers who build and run G7Cloud: UK managed hosting and the AI Website Builder. We write about what we operate every day: containers, backups, databases, and the small-business websites that run on them.
More about G7Cloud →