Technical guide on reducing WordPress database bloat. Learn to limit post revisions and optimize the wp_options autoload size to improve VPS performance and query speeds.

Managing Post Revisions and Optimizing the wp_options Autoload Size

As a WordPress site matures on your Hovixa VPS, the database often accumulates "bloat" that degrades performance. Two primary culprits are Post Revisions, which can multiply the size of your wp_posts table, and a massive wp_options autoload size, which forces MySQL to load megabytes of unnecessary data on every single page request.

1. Controlling Post Revisions

By default, WordPress saves a new row in the database every time you save a draft. For a large post, this can result in dozens of redundant entries. You can limit this globally or disable it entirely within your wp-config.php.

# Limit revisions to 5
define( 'WP_POST_REVISIONS', 5 );

# Or disable them entirely
define( 'WP_POST_REVISIONS', false );
    

Cleaning Existing Revisions via WP-CLI

To delete existing revisions and reclaim NVMe space, use the following command:

wp post delete $(wp post list --post_type='revision' --format=ids) --force
    

2. Optimizing wp_options Autoload Size

The wp_options table contains a column named autoload. When set to yes, that option is loaded into memory on every page load. If this total size exceeds 1MB, it can significantly increase Time to First Byte (TTFB).

Step 1: Check Your Current Autoload Size

Run this SQL query via SSH to calculate the total bytes being autoloaded:

SELECT SUM(LENGTH(option_value)) AS autoload_size_bytes FROM wp_options WHERE autoload = 'yes';
    

Note: Aim for a size under 500KB. If it's over 1-2MB, optimization is mandatory.

Step 2: Identify the Heaviest Options

Find which plugins are cluttering the autoload:

SELECT option_name, length(option_value) AS option_length FROM wp_options WHERE autoload = 'yes' ORDER BY option_length DESC LIMIT 20;
    

3. Cleaning Up the Options Table

Once you identify "trash" options (often from uninstalled plugins), you can set their autoload status to no. This keeps the data but stops it from slowing down the site initialization.

4. Technical Implementation Details

• Transients: WordPress uses the options table to store temporary cached data (transients). If an object cache like Redis is active on your Hovixa VPS, transients are stored in RAM instead, automatically keeping the wp_options table lean.
• Index Efficiency: Large options tables can lead to slow queries if the autoload column isn't indexed. While standard WP includes this index, high-volume sites may benefit from an index on both autoload and option_name.
• Database Optimization: After deleting thousands of rows, run OPTIMIZE TABLE wp_options, wp_posts, wp_postmeta; to defragment the tables and reduce the physical file size on the disk.

Sysadmin Advice: Use the **WP-Optimize** plugin or WP-CLI's **wp db optimize** command weekly. This ensures that the overhead (unused space) in your database files is reclaimed, maintaining the high-speed performance of the Hovixa NVMe infrastructure.