The Complete Guide to WordPress Database Cleanup
Every WordPress developer with even a small amount of experience has witnessed database bloat. Over time, your site’s database is going to be too large for its own good. When this happens it is time to consider the best approaches to proper WordPress database cleanup.
WordPress is a powerful content management system built on the foundations of open-source development. There are tens of thousands of plugins, and themes, at your disposal. Unfortunately, there isn’t really anyone keeping good tabs on how those plugins and themes are made. As your site grows, through the addition of content, plugins, and changes in the theme so does your WordPress database grow as well. Improperly developed plugins and themes, combined with poor maintenance practices, can leave your site running slow and fat.
Join us as we go through the reasons proper database maintenance is important. We’ll cover the best benefits of a good WordPress database cleanup plan, the best ways to handle WordPress database bloat, and how to prevent it from reoccurring in the future. In the end, you’ll be able to better provide WordPress services to your existing and future clients.
Why Proper WordPress Database Maintenance Is Important
You probably didn’t sign up for database ops when you started your WordPress agency. That’s understandable. Many WordPress developers don’t think twice about things like database size or speed. And, for projects of a certain size, it can never be an issue. Unfortunately, for most websites, there comes a time when the size of the database has gotten out of control. Knowing how to handle this, and why, is an important part of any WordPress maintenance program that you can operate for your clients.
Faster Website Speeds & Loading Times
Without proper caching, a WordPress site can run slow as it draws information out of the database. This happens on every page load and interaction. A large database, especially on a low-end server, will trend to slower response times as it grows. You can compensate for this by throwing more into your hosting: more memory and faster processing. Of course, that means more costs for you and your clients which isn’t always an ideal outcome. Staying on top of your website’s speed, through options like caching and database maintenance, is more cost-effective.
Smaller and More Practical Backups
Large WordPress databases lead to large WordPress backups. Every good backup solution for WordPress (more on this later) includes a copy of the site’s database. As that database grows, filling up with useless data, the size of your backup increases. This can be ok if you are storing backups locally on the server. Of course, most backup plugins include support for uploading backups to third-party storage providers. Those uploads can become large and fail if the backup size becomes too large for your server to handle.
How to Handle WordPress Database Cleanup
There are two common ways to handle the database cleanup process. You can either work with the database manually or automatically with WordPress plugins. We’ll cover each of those on their own but, first, let’s address the most important first step. You need to make some valid database backups before doing anything.
Stop and Make a Database Backup
Let’s stop for a minute and make a backup of your database. At the end of the day, no matter how you deal with a WordPress database cleanup, you will be touching important information. If something goes awry you are most certainly going to want a proper backup ready to go. Check out our rundown of the best WordPress backup plugins you can install and use for free right now.
Manual Database Cleanup and Operations
Many people reading this post probably think that they can handle most of these problems with some custom SQL queries. And, of course, those people would be correct. You can figure out how to eliminate a lot of bloat in your WordPress database on your own. There are post revisions to remove, tables from long uninstalled plugins to delete, and much more.
We recommend, as tempting as it can be to get your hands dirty, avoiding manual WordPress database cleanup. Even the most competent, experienced WordPress developers make mistakes. The chance of a mistake happening with an incorrectly composed SQL query is much higher than going with an automated option like a plugin. In addition, so many objects in WordPress are interrelated across tables that it can be difficult to thoroughly clean things yourself. It’s better to rely on a plugin, built to account for all scenarios. So, with that said, let’s take a look at some popular WordPress database cleaning plugins.
WordPress Database Cleanup Plugins
The WordPress.org plugin repository is full of solid plugin options for WordPress database maintenance. We’re going to cover the most popular plugins next. Many of these have overlapping features but we suggest you give several of them a try. You might find one with an interface more to your liking or one that seems to be more actively in development. You can’t go totally wrong no matter which plugin you pick.
We recommend WP-Sweep as a good solution for cleaning up your WordPress database. This plugin is simple to use and offers maintenance support for a laundry list of issues. WP-Sweep cleans up revisions and auto drafts from your posts. It also handles comments by removing any that have been trashed, unapproved, or marked as spam. Orphan data is a huge issue in WordPress databases and WP-Sweep deals with them all automatically. You’ll no longer have to worry about orphaned metadata for posts, comments, users, or taxonomies. WP-Sweep even removes duplicate content in your database.
One of the reasons we recommend WP-Sweep is due to the way the plugin is coded. It relies solely on WordPress’ built-in database delete functions to do its work. There are no actual SQL queries written or run by the code directly. This helps ensure the plugin removes things in the “WordPress way” and is an extra bit of security that’s nice to have.
In addition, WP-Sweep has a command-line interface (CLI) component as well. You can run any of the features of the plugin, on your own, from a terminal window. This is a particularly nice feature for the terminal warriors out there that find doing anything inside of the WordPress admin fussy.
Of all the plugins here, WP-Optimize is probably the most feature-packed. It handles site caching, compressing images, and database cleanup. This might be the choice for you if you want a plugin to handle multiple maintenance issues. We’re going to skip going over those extra features and just focus on the database parts.
In terms of database cleanup, WP-Optimize has a lot of useful features. It takes care of all the coming cleaning tasks you would expect, of course. It removes unnecessary data for posts, comments, pingbacks, trackbacks, and transients. The extra features make it stand out though. You can use the plugin to compact your database tables. There is support for scheduled optimizations so you can run your database maintenance automatically. The cleanup process has options to store data for a specific amount of time as a backup. Finally, you can get some database stats and reports to show how well WP-Optimize is working on your site.
We like WP-Optimize and recommend it if you are looking for a one-stop-shop solution for all of your WordPress maintenance needs. It might not be perfect for you if there are other plugins you prefer for caching and compression. Regardless, we suggest you give WP-Optimize a shot and see how it fits your workflow.
Advanced Database Cleaner
Advanced Database Cleaner is a full-featured WordPress plugin for database maintenance. It has a similar set of options to its competitors with a few neat extras. In terms of the basics, all of the support for posts, comments, etc. are there. This plugin removes revisions and auto drafts as well as trashed posts. You can have it delete pending, spam, and trashed comments. It supports the removal of pingbacks and trackbacks. Tools to delete orphan metadata for posts, comments, users, and terms are available. Finally, you can remove transients with Advanced Database Cleaner.
This plugin lets you review orphaned data before deleting it which is a nice touch. It also offers to store your cleaned data for a set period of time in case you make a mistake. You can set scheduled database cleaning tasks and specifically choose which things to take care of. In addition to WordPress tasks, Advanced Database Cleaner can run common MySQL processes like optimizing tables, repairing corruption, and removing empty table rows.
One thing that sets Advanced Database Cleaner from other options is its Multisite support. You can use this plugin, from the main site, to clean and run maintenance on every site of the network. WordPress Multisite is a difficult issue for many plugins so it’s nice when one supports it out of the box.
Optimize Database after Deleting Revisions
Finally, to wrap up our coverage of database clean plugins, we’ll discuss Optimize Database after Deleting Revisions. ODDR, for short, handles all of the obvious database maintenance tasks. It deals with post revisions, comments, pingbacks, trackbacks, and transients. You can set schedules to run these tasks automatically behind the scenes. Optimize Database after Deleting Revisions offers pre and post-maintenance reports. You can preview what is going to be removed and then review what was deleted. All of this is pretty standard stuff for a WordPress database cleanup solution.
ODDR is different in one interesting way though. It offers granular control over how your cleanup is run. You can tell it to only remove revisions, for example, by a date range or a total count. This means your cleanup process can remove excessively older content but keep more recent data to be safe. You can even set a custom field on your posts and pages to manually exclude them from the entire process. This isn’t a hands-off technique but should be trivial to handle for most WordPress developers.
We recommend ODDR if you need very tight controls over what data you clean up. Being able to exclude certain content from the process is a nice touch and may be necessary depending on your site or client’s needs.
Preventing WordPress Database Bloat & Related Website Problems
The plugins above can handle most of the database bloat issues you might experience with WordPress. Of course, relying on a third-party piece of software isn’t always best practice. You can do some things on your own to help prevent a WordPress database from filling up needlessly. Plus, you can set up caching solutions to make speed less of a concern. Let’s go over these ideas next.
Keep Your Plugins and Themes Updated
Out-of-control plugins, and occasionally themes, are a frequent source of database bloat. It’s important to understand what the plugins and the theme running your site are doing behind the scenes. This is especially true if you ever delete a plugin or change a theme. Many times, with plugins especially, removing or deleting a theme doesn’t remove any of the data the plugin generated in the database. High-quality plugins know to offer this as an option or handle it automatically, but most do not. Being cognizant of what plugins are adding to your database is a good way to stay on top of bloat and overload.
Schedule Regular WordPress Database Checks
You should be familiar enough with WordPress and its database structure to identify outstanding problems. It’s good practice to semi-regularly do a manual review of your MySQL tables. You don’t need to write any queries or actually manipulate the data in any way. Doing a look over the tables’ raw data is possible with practically every MySQL browser software on the market today.
Invest in a WordPress Caching Solution
The final way to cut down on database bloat issues is to put your website on a caching system. There is an absolutely incredible number of WordPress caching plugins. They all offer very similar features with varying degrees of setup and cost involved. You can find the right fit for your site’s size, and your budget, with a little bit of research. Consider a content delivery network (CDN) as well. You can find opinions on the best CDN for WordPress at our company’s website if you want to learn more.
Make WordPress Database Cleanup an Important Maintenance Task
WordPress is a beautiful content management system that can sometimes get gummed up by its own foundation. It’s important to make WordPress database cleanup and maintenance a priority. For the sake of your client’s websites, your servers, and your own sanity. The approach you take doesn’t matter as long as the task is getting done.
As your client base grows, and the stable of websites you are responsible for increases, finding the base maintenance routine is important. We recommend finding what works best for you and then repeating that process for all of the WordPress websites under your supervision. Over time you will have enough experience to become an expert in WordPress database maintenance. This can lead to more clients, more services to offer, and an improvement to your WordPress agency or consultancy.