Prevent WordPress Database Bloat: Revisions, Transients, Autoload

This article is a localized edition of the Japanese original. Read the Japanese original

A WordPress database grows over time with revisions, transients (temporary caches), plugin options, spam comments and more. Even after a cleanup, it grows back unless you stop the causes. This article explains preventive settings for each cause. WordPress already runs some of these jobs automatically, so the settings here avoid duplicating them. The content assumes WordPress 6.x as of October 2026.

Back up your database before you start. The SQL examples assume the table prefix wp_. If $table_prefix in wp-config.php is different, adjust accordingly.

What WordPress already deletes automatically

First, check what is removed without you doing anything.

WP-Cron is triggered by visits to the site, so on low-traffic sites, or on sites that stopped WP-Cron with DISABLE_WP_CRON without setting up a server cron, these jobs may not be running. You can check whether they are scheduled with wp cron event list.

Set a revision limit

Add this to wp-config.php above /* That's all, stop editing! */.

<?php
// wp-config.php: keep only the latest 5 revisions per post
define( 'WP_POST_REVISIONS', 5 );

Setting it to false stops revisions entirely, but then you cannot recover from an accidental overwrite, so keeping a few is recommended.

To vary the limit by post type, use the wp_revisions_to_keep filter. This filter takes precedence over WP_POST_REVISIONS.

<?php
// Change the number of revisions to keep per post type (PHP 8.0 or later)
add_filter( 'wp_revisions_to_keep', function ( $num, $post ) {
	return match ( $post->post_type ) {
		'post'    => 5,
		'page'    => 10,
		'product' => 2,
		default   => $num,
	};
}, 10, 2 );

Even with a limit, existing old revisions remain until that post is saved again. For cleaning them up in bulk, see How to delete WordPress revisions from the database. Deleting revisions directly from wp_posts with SQL leaves related metadata behind, so deleting them with WordPress functions or WP-CLI is safer.

About the autosave interval

Raising AUTOSAVE_INTERVAL (60 seconds by default) does almost nothing against database bloat. An autosave overwrites a single revision per post and user, so it does not keep adding rows. A longer interval only means you lose more if the browser crashes, so there is no need to change it.

Keep transients from piling up

As described above, WordPress deletes expired transients once a day. You normally do not need a custom cron job for this. To delete them right away, use WP-CLI.

# Delete only expired transients
wp transient delete --expired

wp transient delete --all deletes every transient. That includes transients without an expiration (saved with no expiry time), so if a plugin stores data there that it cannot regenerate, things can break. Use --expired in normal cases.

You may see SQL examples that delete "_transient_ rows without a matching timeout row". Those are valid transients saved with no expiration. Such a query deletes transients that have not expired, so do not use it.

If large numbers of expired transients appear every day, a specific plugin is usually creating them. Check the name patterns with the following SQL and deal with it through the plugin's settings or an update.

-- Count transients by the leading part of their names
SELECT SUBSTRING_INDEX(option_name, '_', 4) AS prefix, COUNT(*) AS cnt
FROM wp_options
WHERE option_name LIKE '\_transient\_%'
GROUP BY prefix
ORDER BY cnt DESC
LIMIT 20;

If you use a persistent object cache such as Redis or Memcached, transients are stored in the object cache instead of the database.

Audit autoloaded options

Rows in the wp_options table with autoload enabled are all loaded at the start of every page view. When they grow large, they affect not only database size but also page speed. Since WordPress 6.6, the autoload column uses on, off, auto, auto-on and auto-off in addition to yes and no. The rows that get loaded are those with yes, on, auto-on or auto.

-- Number and total size of autoloaded options
SELECT COUNT(*) AS cnt,
       ROUND(SUM(LENGTH(option_value)) / 1024, 1) AS size_kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

-- The 20 largest
SELECT option_name, autoload,
       ROUND(LENGTH(option_value) / 1024, 1) AS size_kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 20;

Under "Tools" → "Site Health" in the admin, WordPress also shows a warning when the autoloaded total exceeds about 800 KB by default.

Use the names of the large rows to work out which plugin they belong to. If a row belongs to a plugin you have already removed, consider deleting it after taking a backup. For rows of plugins still in use, do not delete them or change their autoload on your own; follow the plugin's settings or its author's guidance.

When your own code saves options, turn autoload off for data that is not needed on every page view. Since WordPress 6.7, passing 'yes' or 'no' for autoload is deprecated, so use a boolean.

<?php
// Save data that is not needed on every request with autoload turned off
add_option( 'my_plugin_log', $data, '', false );
update_option( 'my_plugin_log', $data, false );

Delete spam comments and orphaned metadata regularly

Spam comments are not deleted by default. If you do not use Akismet, add a job that deletes them regularly. The trash is emptied by default, so it is not covered here.

The following code is meant to be saved as wp-content/mu-plugins/db-cleanup.php so that it keeps running when you change themes.

<?php
/*
 * Plugin Name: DB cleanup (spam and orphaned meta)
 */

// Once a day, delete spam comments and orphaned metadata
add_action( 'my_daily_db_cleanup', function () {
	global $wpdb;

	// Spam comments older than 30 days
	$wpdb->query(
		"DELETE FROM {$wpdb->comments}
		 WHERE comment_approved = 'spam'
		   AND comment_date_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)"
	);

	// commentmeta whose parent comment no longer exists
	$wpdb->query(
		"DELETE cm FROM {$wpdb->commentmeta} cm
		 LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID
		 WHERE c.comment_ID IS NULL"
	);

	// postmeta whose parent post no longer exists
	$wpdb->query(
		"DELETE pm FROM {$wpdb->postmeta} pm
		 LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
		 WHERE p.ID IS NULL"
	);
} );

// Schedule it if not already scheduled (daily, starting around 4 a.m. tomorrow)
add_action( 'init', function () {
	if ( ! wp_next_scheduled( 'my_daily_db_cleanup' ) ) {
		wp_schedule_event( strtotime( 'tomorrow 04:00:00' ), 'daily', 'my_daily_db_cleanup' );
	}
} );

strtotime() interprets the time in PHP's time zone (normally UTC in WordPress), so this will not be 4 a.m. Japan time. If the exact time matters, run it from the server cron described below.

To delete spam once right now, you can also use WP-CLI.

wp comment delete $(wp comment list --status=spam --format=ids) --force

With tens of thousands of spam comments the argument list can become too long and the command can fail, so use the scheduled job above or "Empty Spam" under "Comments" → "Spam" in the admin.

Optimize tables regularly

After many rows are deleted, tables keep unused space. You can optimize them with WP-CLI's wp db optimize. WP-Cron has no standard "monthly" schedule (the standard ones are hourly, twicedaily, daily and weekly), so the server cron is the easiest way.

# Add with crontab -e: run at 3 a.m. on the 1st of every month
0 3 1 * * cd /var/www/html && /usr/local/bin/wp db optimize --quiet

Replace /var/www/html with your WordPress directory and /usr/local/bin/wp with the location of WP-CLI (check it with which wp).

To run it from WP-Cron instead, you need to add a custom interval with the cron_schedules filter.

<?php
// Add an "every 30 days" interval to WP-Cron
add_filter( 'cron_schedules', function ( $schedules ) {
	$schedules['every_30_days'] = array(
		'interval' => 30 * DAY_IN_SECONDS,
		'display'  => 'Every 30 days',
	);
	return $schedules;
} );

For InnoDB tables, optimizing means rebuilding the table, which takes time and adds load on large tables. Run it during low-traffic hours. On a rental server without WP-CLI, you can also run it table by table from phpMyAdmin's SQL screen, as in OPTIMIZE TABLE wp_posts;.

Summary

• WordPress deletes expired transients and the trash once a day. Check that WP-Cron is running with wp cron event list
• Cap revisions with WP_POST_REVISIONS or wp_revisions_to_keep. Changing AUTOSAVE_INTERVAL does not prevent bloat
• Do not delete "transients without a timeout" with SQL. That removes valid transients that have no expiration
• Autoload applies to yes, on, auto-on and auto. Save your own options with false
• Delete spam comments and orphaned metadata with a daily job in a mu-plugin
• Optimize with wp db optimize from the server cron once a month. WP-Cron has no standard monthly schedule

Related articles:

• How to limit the number of revisions in WordPress
• How to delete transient options to optimize the WordPress database
• How to run WordPress cron jobs from the server

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)