Performance6 min readPublished: 8 Mar 2025Updated: 9 Nov 2026

How to Safely Trim a Large WordPress Database

Find what is filling a WordPress or WooCommerce database, delete the parts that are safe to remove, and leave orders, customers and logins untouched.

G7Cloud Engineering
Platform team
Share:
Abstract illustration representing how to safely trim a large wordpress database.
  • 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.

Find out where the size is
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'"
The autoloaded figure matters more than total size. Those settings are read on every request, so 5 MB there costs more than 5 GB of old order history.
TableUsually holdsSafe to trim?
wp_optionsSettings, transients, plugin cachesExpired transients only
wp_postsContent, revisions, ordersRevisions and trash only
wp_postmetaMetadata, much of it orphanedOrphaned rows only
wp_actionscheduler_actionsWooCommerce background jobsCompleted and failed, older than 30 days
wp_woocommerce_order_itemsOrder line itemsNever
wp_users, wp_usermetaAccountsNever, 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.

The backup, before anything else
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'
A few commands that separate a cleanup from an incident.

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.

Transients
# 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_%'"
Use the WP-CLI command, not raw SQL. It removes the matching timeout rows too, which hand-written DELETEs usually miss.
Revisions, trash and spam
# 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);
The define is the important half. Deleting revisions once frees space; capping them keeps it free.
Orphaned metadata
# 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"
Orphaned rows belong to posts or comments that no longer exist. Run each query as a SELECT COUNT(*) with the same joins first, so you know how many rows will go.
Pro Tip

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.

Action Scheduler
# 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"
Work in batches on a large table. One DELETE across millions of rows can lock the table long enough to take the shop down.

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.

Other WooCommerce growth, safe to trim
# 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=1
Sessions and logs regenerate. The lookup table is built from your product data, so you rebuild it rather than restore it.

Should 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.

Reclaiming the space
# 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.

  1. 1Cap post revisions with define('WP_POST_REVISIONS', 5); in wp-config.php. The default is unlimited, which causes most wp_posts growth.
  2. 2Empty the trash sooner with define('EMPTY_TRASH_DAYS', 7);. The default is 30.
  3. 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.
  4. 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.
  5. 5Check the autoloaded options figure monthly. It most directly affects how fast every page loads.
A monthly check worth keeping
#!/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"
Run it monthly and keep the output. A trend tells you far more than one reading.

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.

Put this into practice on G7Cloud

Every site runs in its own dedicated container behind ScaleShield, with daily backups that are restore-tested every night. Start on the free plan, no card needed.

About G7Cloud Engineering

Platform team

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 →