How to Safely Trim a Large WordPress Database

Measure before you delete. Four tables account for almost all growth on nearly every site.
Expired transients, post revisions, orphaned metadata and old Action Scheduler rows are safe to remove. Order data is not.
Never delete from wp_posts where post_type is shop_order, or from wp_woocommerce_order_items. Those are your records.
Take a backup and test the restore before running a single DELETE. There is no undo.
Cap revisions and set a transient policy afterwards, or the same growth simply returns.

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. Measure first, because the shape of the problem decides which fix is worth doing.

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: it is read on every request, so 5 MB there costs more than 5 GB of dormant order history.

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 the ones that make an optimisation plugin dangerous when it offers a one-click clean.

Back up and test the restore first

Every command below deletes rows permanently. Take a dump, restore it somewhere else, and confirm the site loads from the copy. A backup you have not restored is an assumption. 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'

Four commands. They are the difference between a cleanup and an incident.

Which data is safe to delete?

Start with transients. They are cached values with an expiry date, and WordPress regenerates anything it still needs. Expired ones are pure waste, and on a neglected site they are frequently the single largest contributor to 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 rather than raw SQL: it removes the paired timeout rows correctly, 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 buys space; capping them keeps it.

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"

Run each as a SELECT COUNT(*) first with the same joins, so you know how many rows you are about to remove.

Pro Tip

Convert any DELETE to a SELECT before you run it. SELECT COUNT(*) FROM wp_postmeta pm LEFT JOIN wp_posts p ON p.ID = pm.post_id WHERE p.ID IS NULL tells you the number without touching anything. If the count surprises you, stop and find out why.

How do I clean up WooCommerce safely?

WooCommerce adds its own tables and its own growth. The Action Scheduler tables in particular can reach millions of rows on a busy shop and are the usual reason a WooCommerce admin becomes slow. 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"

Do it in batches on a large table. A single 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 companions. These are your financial records. You will need them for refunds, disputes, accounting and HMRC, and they are not recoverable 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 lookup tables afterwards
wp wc update

Sessions and logs regenerate. The lookup table is derived data, which is why it can be rebuilt rather than restored.

Should I optimise tables afterwards?

Deleting rows leaves gaps in the data files, so the disk usage does not fall until you rebuild the table. On InnoDB, OPTIMIZE TABLE rebuilds it and reclaims the space. It also locks the table while it runs, so it needs 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 reclaimable space. If it is small, optimising buys you nothing and is not worth the lock.

OPTIMIZE TABLE locks the table

On a large wp_postmeta this can take several minutes, during which the site cannot read or write it. That means the site is effectively down for that period. Run it out of hours, one table at a time, and only where data_free is genuinely large.

How do I stop it growing back?

A one-off cleanup buys a few months. These five changes make it stay clean, and all of them are set once.

1Cap post revisions with define('WP_POST_REVISIONS', 5); in wp-config.php. The default is unlimited, which is where most wp_posts growth comes from.
2Empty trash sooner with define('EMPTY_TRASH_DAYS', 7);. The default is 30.
3Make sure the scheduler is running. Transient and Action Scheduler cleanup are themselves scheduled tasks, so a stalled queue means cleanup never happens. See WordPress cron and missed tasks.
4Audit 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.
5Watch the autoloaded options figure monthly. It is the number that 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 a single reading.

A smaller database is faster to query, faster to back up and faster to restore, which matters most on the day you need it. If queries are still slow after trimming, the next step is caching the results rather than shrinking the data further: see WordPress object caching with Redis. On G7Cloud each site has its own database with nightly restore-tested backups, so a cleanup you regret is recoverable. 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?

Yes, expired ones are safe: they are cached values with an expiry date, and WordPress regenerates anything it still needs. Use wp transient delete --expired rather than raw SQL, because the command removes the paired timeout rows that hand-written DELETEs usually leave behind.

Can I delete old WooCommerce orders to save space?

Not casually. Orders are financial records you may need for refunds, chargebacks and accounting, and UK tax rules expect them to be retained for years. If you do 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 read on every single page load, including AJAX and REST requests. A 5 MB autoloaded options table means 5 MB read and unserialised on every hit, which is far more costly than gigabytes of dormant order history that is rarely touched. Keep it under 1 MB.

Should I use a database optimisation plugin?

With care. They are convenient for transients and revisions, but one-click cleanup buttons often offer to remove things that are not safe on a shop, and they run inside a PHP timeout so a large operation can stop halfway. Take a backup first regardless of which route you use.

Does OPTIMIZE TABLE speed up WordPress?

It reclaims disk space left behind by deleted rows and can slightly improve scan performance. It is not a general speed fix, and it locks the table while it runs. Only bother where data_free is genuinely 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 →