This post follows the post Heroku & WordPress Website Deployment which details how to create and publish a website using WordPress and Heroku. This post details how to clean-up and optimize your WordPress database using plugins and WordPress features to minimize your Heroku database requirements.

Using WordPress Optimization Features

As you add more content to your website, change things, and activate plugins, your database entries are modified and the database increases in size. This could make your website slower as it takes longer for the server to retrieve information from the database. Also, the ClearDB add-on for Heroku has a free tier which is capped at 5MB. If your database passes 5MB, you will no longer be able to write to the database and will have to pay for the next higher tiered plan. Therefore, regularly cleaning and optimizing your database is a good idea to keep the database size at a minimum. Check out the database description page on WordPress.org for more information about WordPress core tables.

One way is to used a database management tool to interface with your database. phpMyAdmin is the most common way to manage a WordPress database. If you are not using cPanel as your hosting control panel, your hosting plan may be using a different MySQL management tool to phpMyAdmin. This is fairly technical and detailed so if you would like to explore this option, see this page for more information.

WordPress has a native tool that allows you to repair and optimize your database. More information about this tool is available in the Automatic Database Optimizing section. To activate this tool, you must first add the following line to your website wp-config.php file.

define( 'WP_ALLOW_REPAIR', true );

After saving the file and updating your Heroku application, you can access the optimization tool at http://www.yourwebsite.com/wp-admin/maint/repair.php.

I recommend selecting “Repair and Optimize Database” so that WordPress will attempt to repair each database table and optimize those not already optimized. This tool may report errors in which case you can run the tool again. It is important to note that this tool is available to anyone when it is enabled so it is necessary to remove the code from your wp-config.php file after you have used the optimization tool.

One cause of increased database sizes is the WordPress revision system. This feature stores an unlimited number of copies of every draft and update of blog posts and pages. While it allows you to revert back to older copies of drafts, it is often not necessary to maintain ever single revision.

To reduce the number of revisions that are saved, simply add the following code to your wp-config.php file.

define( 'WP_POST_REVISIONS', 2 );

Similarly, the wp-config.php file can be updated to limit the frequency of autosaves.By default, this feature saves a copy of your draft every 60 seconds. While this feature only saves a single copy,

The WordPress autosave feature saves one autosave of your article every 60 seconds so it won’t take up too much room in your database. However, this interval can be changed by adding the following code to your wp-config.php file.

define( 'AUTOSAVE_INTERVAL', 180 ); // Seconds

The length of times deleted items are stored in your database until the trash is emptied can also be modified. Items are permanently deleted after 30 days by default. All items such as posts or pages are sent to the trash folder when they are deleted. This prevents you from deleting something by accident and allows users to restore items from the trash. The wp-config.php file can be modified to change the number of days until the items in the trash are permanently deleted. The number of days before trash is emptied can be changed by adding the following code to your wp-config.php file.

define( 'EMPTY_TRASH_DAYS', 2 ); // 2 days

Using WordPress Optimization Plugins

For a more robust tool that addresses some of these issues and more, I use the plugin Optimize Database after Deleting Revisions. This plugin comes with a significant amount of features, some of which include the ability to

• Delete redundant revisions of posts and pages
• Delete trashed posts, pages and comments
• Delete spammed comments
• Optimize the database tables (optionally you can exclude certain tables, or even specific posts/pages, from optimization)
• Schedule optimization to automatically run at a specific time

The vast majority of all WordPress plugins store data and settings in the database which is not typically removed when the plugin is uninstalled. If you decide to stop using a plugin for the indefinite future, you should remove the plugin as well as all the data associated with it. Similarly, WordPress themes store settings in the database which remain when themes are changed. While you can attempt to remove these unused tables manually with a database management tool, the WPDBSpringClean plugin will identify unused tables from uninstalled plugins and give you the option to delete them.

See the following posts for more information about securing and customizing your website:

• WordPress & Heroku – Custom Website Development & Deployment
• WordPress & Heroku – Securing Your WordPress Database Connections
• WordPress & Heroku – Updating Website Content, Themes & Plugins
• WordPress & Heroku – Custom Domain Name Configuration
• WordPress & Heroku – Website Integration With CloudFlare