I regularly copy my live WordPress sites back to my local server. Not only is it an extra backup, but it allows me to develop locally with up to date content.
Lately I’ve been getting annoyed at how bloated the WordPress databases seem to get.
A lot of it is coming from the REVISIONs feature in WordPress.
Step 1: Limit how many revisions WP keeps
By default WordPress will store an infinite amount of revisions (every save plus one autosave). However you can limit how many it stores (see official docs).
Add this code into your wp-config.php file:
define( ‘WP_POST_REVISIONS’, 3 );
This will store 3 revisions. If you want to stop revisions altogether, then set the number to zero.
define( ‘WP_POST_REVISIONS’, 0 );
I’ve only very occasionally used the revision feature, so three is good enough for me.
Step 2: Clean Up Old Revisions
The best plugin I have found is Optimize Database after Deleting Revisions. It has a lot of downloads, is up to date, and is simple to operate (while still allowing some customization).
Out of the box it will do some basic optimization. I changed the settings and asked it to retain only 3 revisions. Make sure you take a backup first!
Voila! One third of my database gone. I then went and set some other settings
Delete trashed items
Delete spammed items
Delete unused tags
Delete expired transients
This shaved off another chunk of data. A smaller database means quicker backups, migrations, and better performance.
NOTE: If you have a larger / older WordPress site, try switch on only a few settings at a time, then optimizing. This can be heavy on the database and in a couple of timeouts I got a 504 Gateway Time-out error. This is because the script was taking a long time to run.