WordPress Database Getting Too Large - Optimization Guide

Your WordPress database keeps growing. What used to be 50 MB is now 200 MB. 500 MB. You're not adding that much content, so where is it all coming from?

Your backups take longer. Restores are slow. Queries feel sluggish. And your hosting provider keeps sending warnings about disk space usage.

A bloated database doesn't just waste space. It slows down queries, increases backup sizes, makes migrations painful, and can push you toward MySQL memory limits that cause errors.

The good news: database cleanup is straightforward. You know exactly what to remove and what to keep. And you can automate it so it never bloats again.

What's Actually Taking Up Space? (The Culprits)

Your database doesn't grow because of posts and pages. It grows because of this stuff:

1. Post Revisions (Usually #1 Culprit)

WordPress saves a new revision every time you update a post. Not just when you publish ā€” every time you hit "Save Draft" or "Update."

On a busy blog or news site, a single post might have 100+ revisions. With 500 posts, that's 50,000 revision rows in your database.

Impact: Revisions are queried on every admin page load, slowing down the dashboard.

2. Expired Transients (Cache Leftovers)

Plugins use transients (temporary data) that expire after a time. But WordPress doesn't automatically delete expired transients. They pile up.

A site with 20+ plugins can accumulate thousands of expired transient rows in wp_options table.

Impact: wp_options table becomes huge, slowing down queries that load autoloaded options on every page.

3. Spam Comments

Comment spam sits in trash until manually deleted. Akismet catches spam but doesn't remove it automatically.

Sites with open comments can have 10,000+ spam comments in the trash.

Impact: Comments table grows. Admin dashboard becomes slow.

4. Trashed Posts & Pages

When you delete a post, it goes to trash (not permanently deleted). Old trashed posts stay in the database forever.

Impact: Wasted space. Each trashed post has associated metadata.

5. Orphaned Metadata

When you delete a post, comment, or user, its metadata might not be deleted. Similarly, when you delete a plugin, its data stays behind.

Impact: wp_postmeta and wp_usermeta tables become bloated with entries for posts/users that no longer exist.

6. Abandoned Plugin Tables

When you deactivate and delete a plugin, it often leaves its tables behind.

A site that's installed and uninstalled 50+ plugins could have dozens of orphaned tables consuming space.

Impact: Backup size grows, database becomes cluttered.

7. Session Data

WordPress stores user sessions in the database. Old sessions stay until manually cleaned.

Impact: Accumulates on high-traffic sites with many logged-in users.

Diagnosis: How Large Is Your Database?

Check Size in WordPress Admin

  1. Go to WordPress > Tools > Site Health
  2. Click on "Info" tab
  3. Scroll to "Database"
  4. Look for "Database Size" field

Check via phpMyAdmin (More Detailed)

  1. Go to your hosting control panel (cPanel/Plesk)
  2. Open phpMyAdmin
  3. Select your WordPress database
  4. At the bottom, you'll see "Total Size" of the database
  5. Click on individual tables to see size of each table

Benchmarks:

  • Small site (0-10 posts): 1-5 MB (normal)
  • Medium site (100-500 posts): 10-50 MB (normal)
  • Large site (1000+ posts): 50-200 MB (normal)
  • Over 200 MB: āš ļø Likely bloated. Cleanup recommended.
  • Over 500 MB: āŒ Significant bloat. Cleanup urgent.

Cleanup Method 1: WP-Optimize (Easiest, Recommended)

One-click cleanup with minimal risk.

(cite index="76-1">WP-Optimize is a popular all-in-one tool that cleans your database, compresses images, and caches pages. It can remove post revisions, spam comments, trashed posts, and transient options in one click, and you can schedule cleanups to run automatically.

Step 1: Install & Activate

  1. Go to Plugins > Add New
  2. Search for "WP-Optimize"
  3. Install and Activate

Step 2: Run Cleanup

  1. Go to WP-Optimize > Dashboard
  2. You'll see a list of cleanup options:
    • ā˜ Remove post revisions
    • ā˜ Remove spam comments
    • ā˜ Remove trashed posts
    • ā˜ Remove trashed comments
    • ā˜ Remove expired transients
    • ā˜ Remove orphaned post metadata
    • ā˜ Remove orphaned comment metadata
    • ā˜ Remove orphaned user metadata
  3. Check all boxes
  4. Click "Run Optimizations"
  5. Wait for completion (usually 1-5 minutes)

Step 3: Schedule Automatic Cleanup

  1. Go to WP-Optimize > Settings
  2. Click "Automatic Cleanup"
  3. Configure schedule (e.g., "Weekly on Sunday at 2 AM")
  4. Select what to clean automatically
  5. Save

Expected result: Database 30-70% smaller. Admin dashboard faster.

Cleanup Method 2: Advanced Database Cleaner (Most Thorough)

For sites with plugin table bloat or complex metadata issues.

(cite index="76-1">Advanced Database Cleaner focuses specifically on deep cleaning and goes deeper on orphaned tables, custom cron jobs, and metadata analysis.

Step 1: Install & Activate

  1. Go to Plugins > Add New
  2. Search for "Advanced Database Cleaner"
  3. Install and Activate

Step 2: Analyze Your Database

  1. Go to Advanced Database Cleaner > Dashboard
  2. Click "Scan Database"
  3. Wait for scan to complete (shows detailed analysis)
  4. You'll see exactly how much space each type of bloat is taking

Step 3: Clean Individual Issues

  1. Review each category (revisions, transients, orphaned data, etc.)
  2. For each, click "Clean" or select items to delete
  3. Confirm deletion

Step 4: Table Audit (Advanced)

  1. Go to Advanced Database Cleaner > Tables
  2. Look for orphaned plugin tables (prefixed with old plugin names like "wp_old_plugin_settings")
  3. Select and delete them

Expected result: Database cleaned of all bloat, including abandoned plugin tables.

Cleanup Method 3: Manual Cleanup (Advanced, For Developers)

Via wp-config.php changes and WP-CLI commands.

Step 1: Limit Future Revisions (In wp-config.php)

  1. Connect via FTP
  2. Open wp-config.php
  3. Add this line BEFORE "That's all, stop editing!":
    • define( 'WP_POST_REVISIONS', 5 );
  4. This keeps only the 5 most recent revisions per post
  5. Or set to 0 to disable revisions entirely (but keep 5-10 is better)
  6. Save and upload

Step 2: Delete Existing Revisions (WP-CLI)

  1. Connect via SSH to your server
  2. Run these commands:
# See how many revisions exist
wp post list --post_type=revision --format=count

# Delete all revisions
wp post delete $(wp post list --post_type=revision --field=ID) --force

# For large sites, delete in batches (500 at a time)
wp post list --post_type=revision --field=ID --posts_per_page=500 | xargs wp post delete --force

Step 3: Delete Expired Transients (WP-CLI)

# Delete only expired transients (safe)
wp transient delete --expired

# See total transient count
wp transient list --format=count

# Delete ALL transients (they regenerate as needed, but causes brief slowdown)
wp transient delete --all

Step 4: Optimize Database Tables

Add to wp-config.php:

define( 'WP_ALLOW_REPAIR', true );

Then visit: yoursite.com/wp-admin/maint/repair.php

Click "Repair and Optimize Database" ā€” removes unused space from tables.

āš ļø CRITICAL: Remove that define from wp-config.php after running repair (for security).

Step 5: Add Database Indexes (For Performance)

This is advanced but provides huge speed gains. Add compound index to wp_postmeta:

ALTER TABLE wp_postmeta ADD INDEX post_id_meta_key (post_id, meta_key(191));

(cite index="77-1">Indexes tell MySQL where to look instead of scanning millions of rows. Adding a compound index to wp_postmeta directly speeds up queries and is the fastest way to optimize WordPress database for large sites.

Expected result: Database cleaned and optimized for performance.

Cleanup Method 4: Via phpMyAdmin (Manual SQL)

For sites where plugins/WP-CLI aren't available.

Delete All Revisions

  1. Open phpMyAdmin
  2. Select your WordPress database
  3. Go to "SQL" tab
  4. Paste this query:
DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);
  1. Click Go

Delete Spam Comments

DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

Delete Orphaned Transients

DELETE FROM wp_options WHERE option_name LIKE '%_transient_%';

Optimize Tables

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_comments, wp_options;

āš ļø WARNING: Back up your database before running ANY SQL queries. One wrong command can break everything.

Prevention: Keep Database Lean Going Forward

1. Limit Post Revisions

In wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

This keeps only 5 revisions per post (good balance between safety and space).

2. Auto-Empty Trash

In wp-config.php:

define( 'EMPTY_TRASH_DAYS', 7 );

Permanently deletes trashed items after 7 days (instead of keeping them forever).

3. Disable Spam Retention

In wp-config.php:

define( 'EMPTY_TRASH_DAYS', 0 );

Permanently deletes spam immediately (Akismet already has it archived).

4. Clean Up Old Comments

In wp-config.php:

define( 'COMMENT_AUTO_DELETE', true );

Or manually delete comments older than 1 year via Settings > Discussion.

5. Set Up Automated Cleanup

(cite index="80-1">Set up automated schedules: weekly transient and spam cleanup, monthly revision removal and table optimization, and quarterly audits of orphaned plugin tables.

Using WP-Optimize:

  1. Go to WP-Optimize > Settings > Automatic Cleanup
  2. Enable automatic weekly cleanup
  3. Select what to clean
  4. Save

6. Uninstall Plugins Properly

When deleting a plugin:

  1. Check if the plugin has an "Uninstall" option in settings
  2. Run that first (removes plugin data)
  3. Then deactivate and delete the plugin from WordPress

This removes the plugin's database tables (most plugins don't do this automatically).

7. Monitor Database Size Monthly

  1. Go to WordPress > Tools > Site Health > Info
  2. Note database size
  3. If it grows 50%+ in a month, investigate why
  4. Run cleanup before it becomes a problem

Performance Impact of Optimization

What you can expect after cleanup:

  • Admin dashboard loads 20-40% faster (revisions removed)
  • Page queries 10-30% faster (fewer rows to scan)
  • Backup file size 30-70% smaller
  • Backup/restore time significantly faster
  • TTFB (Time To First Byte) 5-10% improvement

On large sites with 500+ MB databases: Impact can be 50-100% faster queries, especially on admin pages.

On small sites with 10 MB databases: Barely noticeable, but still recommended for maintenance.

Cleanup Frequency Recommendations

Site Type Frequency Why
Blog (10-50 posts) Quarterly (every 3 months) Low activity, minimal bloat
Medium site (100-500 posts) Monthly Moderate activity, some bloat accumulates
Active blog/news (500+ posts) Weekly or automatic High revision/comment traffic
WooCommerce store (100+ products) Weekly Orders create metadata, transients pile up
High-traffic site (1000+ pages) Automated weekly (required) Massive metadata, session data, orphaned data

Backup Before Cleanup (Critical)

Always back up before any database cleanup, especially manual methods.

Quick Backup via UpdraftPlus

  1. Install UpdraftPlus (Plugins > Add New)
  2. Go to UpdraftPlus > Backup Now
  3. Wait for completion
  4. Now you can safely clean the database

Schedule Regular Backups

  1. Go to UpdraftPlus > Settings
  2. Configure automatic daily or weekly backups
  3. Store backups in cloud (Google Drive, Dropbox, AWS)

Troubleshooting: Something Went Wrong

If site breaks after cleanup:

  1. Restore from backup immediately (UpdraftPlus > Backups > Restore)
  2. Contact the cleanup tool's support
  3. Don't attempt repeated cleanups on a broken site

If queries are now slower:

  1. Run "Optimize Tables" via WP-Optimize or phpMyAdmin
  2. Rebuild indexes (if using manual method)
  3. Clear cache plugins

The Real Talk

WordPress databases bloat naturally. Post revisions, expired transients, spam comments, and orphaned metadata all pile up. 90% of sites over 2 years old could save 30-50% of database size with a single cleanup.

Use WP-Optimize or Advanced Database Cleaner. Set it to run automatically weekly. Limit revisions to 5-10 in wp-config.php. That's it. Your database stays lean, backups stay small, and queries stay fast.

A 50 MB database queried 1,000 times per day runs circles around a 500 MB database queried the same number of times. Size matters. Clean it up.