WordPress revisions – the killer of wp_posts table in DB

Have you ever woken up and discovered an email from the hosting company whose server your website ‘lives’ on, stating something along the lines of: ‘Database size limit has been reached‘?

Afterwards, upon entering phpMyAdmin, you’re surprised to find that your database is the size of a massive application with numerous records, when in reality, you have an informative portfolio website with just a few pages?

If your answers to these questions are ‘yes’, see what I have to share with you below ?.

What is causing the database to grow so much?

The answer to this question is not straightforward and valid for every WordPress website. The size of the tables in the database (a total of 12 tables) depends on the plugins used, the theme(s), page builders, automatic drafts, and revisions. I’ll explain these aspects to you in the following paragraphs (and the video accompanying this article), as often neglecting their settings is the reason for a table like wp_posts to have a size of, for example, 105MB.

WordPress Revisions – what are the options and how to manage them

Losing your latest edits and additions to content while editing or publishing a new article, for example, can be quite frustrating and time-consuming. Fortunately, WordPress prevents this with its built-in functionality for saving revisions and automatic drafts.

But even with good intentions, revisions can lead to literal congestion in one of the tables in the database, which in turn can cause slow loading of the website, pressure from the hosting company (especially if you’re using shared hosting), and the inability to upload new content.

ℹ️ I won’t go into details about how to work with the revisions themselves and how you can restore a version of a given publication or page – you can figure all of this out on your own in the administration of your website.

Revisions are a default feature in WordPress that come with predefined settings. WordPress stores them in the posts table of its database as children of a specific post. The more revisions you create, the heavier the database will become.

Therefore, managing post and page revisions through their deletion, limitation, or deactivation is not a ‘light’ task. I will explain each of these, starting with deleting old revisions in WordPress.

➡️ Deleting Revisions

For advanced users – one of the clean ways to delete old revisions is by accessing phpMyAdmin and running an SQL query.

⚠️ Caution: It’s important to be cautious with this method. There are many code snippets from forums and social media that aren’t entirely ‘trustworthy’. Some of them could easily delete something important from your site, or worse, they could bring the whole site down.

For safely deleting old revisions from the SQL database using a query, access phpMyAdmin, select the specific WordPress database from the panel on the left, then choose SQL from the tabs above:

Next, you can enter a command to execute the SQL query in the WordPress database:

DELETE FROM wp_posts WHERE post_type="revision"; // change wp_ with your DB prefix

Here, pay attention to what the prefix of your tables is (in the example, it’s indicated as wp_, but it could be anything, like ozh_, znt_, etc.). Click the Go button, and the query will execute ?

If you’re an experienced WordPress developer and use the CLI (command-line interface), you can enter the following command in the terminal (making sure to adjust the database prefix again):

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

Keep in mind that deleting old revisions won’t stop WordPress from generating new ones. This is a way to ‘clean up’ the database a bit, and if you don’t want to end up in a similar situation again, follow the steps below.

➡️ Limiting Revisions

The next step in maintaining an optimally clean database while still benefiting from revisions is to limit them.

In the WordPress repository, there are plenty of plugins that do just that, but the goal of {dev} blondie; is to inform and demonstrate methods for solving issues through clean code ?

In this context, to limit revisions, open your website’s files via FTP (e.g., FileZilla, Cyberduck, or whatever FTP client you use) and navigate to the wp-config.php file. Add this code snippet right at the beginning:

define('WP_POST_REVISIONS', 3); // define how many revisions you'd like to keep

In this specific case, we will instruct WordPress to store the last 3 revisions, although this number can vary.

➡️ Disabling Revisions

There are cases where a website (or its administrator) doesn’t need revisions. In this scenario, after all the existing revisions have been deleted, you can go to wp-config.php and add the following line of code:

define('WP_POST_REVISIONS', false);

Instead of a conclusion

A website impresses not only with design and interactive elements, but also with its speed of operation and responsiveness. In this regard, an optimized database is the first step towards achieving this goal.

{dev} blondie; recommends that you visit your phpMyAdmin right now and check the size of your database. If it seems suspiciously high, check wp_posts for accumulated revisions. From there, you know what to do ?

⚠️ And even though you know what you’re doing, having a backup of both the files and the database wouldn’t hurt ?

Stay blond 😉