Schedule WordPress Database Cleanup with WP-Cron to Remove Transients and Revisions

Home » MySQL » Schedule WordPress Database Cleanup with WP-Cron to Remove Transients and Revisions
November 10, 2022 MySQL, Optimization
Schedule WordPress Database Cleanup with WP-Cron to Remove Transients and Revisions

WordPress’s wp_options table accumulates stale data from deactivated plugins, expired transients stored without a persistent cache, post revisions, and auto-draft posts — all of which increase the size of every full-table scan that WordPress performs on this table during bootstrap. Scheduling periodic cleanup tasks with WP-Cron keeps the database lean without requiring manual maintenance, as long as the cron system is running reliably. WP-Cron fires when a visitor triggers a page load after the scheduled time — on low-traffic sites this means jobs can be delayed by hours, making a real system cron entry essential for time-sensitive maintenance. The wp_schedule_event() function registers a recurring hook, and wp_next_scheduled() prevents duplicate registrations if the plugin is activated multiple times or if init fires more than once per request. WordPress core runs wp_scheduled_delete() hourly to remove trashed posts, but it does not clean expired transients stored in the options table by default — you must schedule that explicitly. The DELETE query for expired transients joins the _transient_timeout_ and _transient_ prefixed rows by key suffix to remove both the value and its timeout in a single operation. Post revisions are controlled by the WP_POST_REVISIONS constant — setting it to 5 or 3 limits new revisions but does not remove existing excess revisions, which the cleanup job handles retroactively. Orphaned postmeta rows accumulate when posts are deleted without their metadata being cleaned up — a LEFT JOIN query identifies postmeta rows with no matching post and removes them safely. The database bloat cleanup post provides the exact SQL queries that the scheduled job below runs automatically each week. The slow query log post shows how to verify the cleanup’s impact — run the slow query log before and after to see bootstrap query times decrease as the options table shrinks. Always test the cleanup queries on a staging copy first and verify that no active plugin relies on option rows that match the cleanup criteria.

Problem: WordPress databases grow with expired transients, excess post revisions, auto-drafts, and orphaned postmeta rows that slow down options table queries and increase backup size without any automatic cleanup scheduled by default.

Solution: Register a weekly WP-Cron event that removes expired transients, excess post revisions, auto-drafts older than seven days, and orphaned postmeta rows, and back it with a real system cron entry to guarantee reliable execution.

// Schedule the cleanup on plugin/theme activation
add_action('init', function() {
    if (!wp_next_scheduled('my_weekly_db_cleanup')) {
        wp_schedule_event(time(), 'weekly', 'my_weekly_db_cleanup');
    }
});

add_action('my_weekly_db_cleanup', function() {
    global $wpdb;

    // 1. Remove expired transients from wp_options
    $wpdb->query(
        "DELETE o, t FROM {$wpdb->options} o
         INNER JOIN {$wpdb->options} t
             ON t.option_name = REPLACE(o.option_name, '_transient_timeout_', '_transient_')
         WHERE o.option_name LIKE '_transient_timeout_%'
           AND o.option_value < UNIX_TIMESTAMP()"
    );

    // 2. Keep only the 5 most recent revisions per post
    $wpdb->query(
        "DELETE FROM {$wpdb->posts}
         WHERE post_type = 'revision'
           AND ID NOT IN (
               SELECT id FROM (
                   SELECT ID FROM {$wpdb->posts} r
                   WHERE r.post_type = 'revision'
                   ORDER BY r.post_modified DESC
                   LIMIT 5
               ) keep
           )"
    );

    // 3. Delete auto-drafts older than 7 days
    $wpdb->query(
        "DELETE FROM {$wpdb->posts}
         WHERE post_status = 'auto-draft'
           AND post_modified < DATE_SUB(NOW(), INTERVAL 7 DAY)"
    );

    // 4. Remove orphaned postmeta
    $wpdb->query(
        "DELETE pm FROM {$wpdb->postmeta} pm
         LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
         WHERE p.ID IS NULL"
    );
});

// System cron entry (run in terminal: crontab -e)
// */15 * * * * curl -s https://example.com/wp-cron.php?doing_wp_cron &> /dev/null

NOTE: Add define('DISABLE_WP_CRON', true); to wp-config.php once you have set up the real system cron entry — this prevents the pseudo-cron from firing on every page load and avoids duplicate job execution.

Related posts