Database Size & Cleanup

Managing Database Size

A bloated database slows down queries and increases backup times. This guide helps you identify what is consuming space and safely clean it up.

Check Overall Database Size

sudo wp db size --tables --path=/home/DOMAIN/public_html --allow-root

Or directly via SQL:

sudo mysql -e "SELECT table_name, ROUND(data_length/1024/1024, 2) AS data_MB, ROUND(index_length/1024/1024, 2) AS index_MB FROM information_schema.tables WHERE table_schema='YOUR_DB' ORDER BY data_length DESC;"

Common Space Hogs

Table	Common Size Issue	Fix
wp_options	Transients, plugin data, large autoload	Clean transients, remove orphaned plugin data
wp_postmeta	Orphaned meta from deleted posts	Delete orphaned meta rows
wp_posts	Thousands of revisions, auto-drafts	Delete revisions, limit future revisions
wp_actionscheduler_*	WooCommerce scheduled actions history	Clean old completed actions
wp_comments/wp_commentmeta	Spam comments	Delete spam: wp comment delete $(wp comment list --status=spam --format=ids)

Clean Orphaned Post Meta

sudo wp db query "DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;" --path=/home/DOMAIN/public_html --allow-root

Clean Auto-Drafts

sudo wp post delete $(sudo wp post list --post_status=auto-draft --format=ids --path=/home/DOMAIN/public_html --allow-root) --force --path=/home/DOMAIN/public_html --allow-root

Clean Spam and Trashed Comments

# Delete spam
sudo wp comment delete $(sudo wp comment list --status=spam --format=ids --path=/home/DOMAIN/public_html --allow-root) --force --path=/home/DOMAIN/public_html --allow-root

# Delete trashed
sudo wp comment delete $(sudo wp comment list --status=trash --format=ids --path=/home/DOMAIN/public_html --allow-root) --force --path=/home/DOMAIN/public_html --allow-root

Reclaim Disk Space

After deleting data, optimize tables to reclaim disk space:

sudo wp db optimize --path=/home/DOMAIN/public_html --allow-root

Prevent Future Bloat

Add to wp-config.php:

define( 'WP_POST_REVISIONS', 5 );        // Max 5 revisions per post
define( 'EMPTY_TRASH_DAYS', 7 );           // Auto-delete trash after 7 days
define( 'AUTOSAVE_INTERVAL', 300 );        // Autosave every 5 min (default 60s)