The Database That Grew to Two Gigabytes

A database nobody looks at

Every WordPress site keeps its content in a database: pages, posts, settings, comments, and a good deal that nobody remembers putting there.

On a new site it is small. Over the years it grows, not because the site has more pages, but because WordPress and its plugins keep things and rarely throw them away.

Nobody notices until something gives. The nightly backup starts failing, the host sends a warning about disk space, or the admin screens become slow enough that editing a page is a chore.

By then the database may be many times larger than the content in it.

What fills it up

• Revisions: a full copy of a page every time it is saved.
• Spam comments waiting in the queue on old posts.
• Tables left by removed plugins, some of them very large.
• Expired temporary data that was never cleared.
• Settings from old plugins, still loaded on every page.
• Logs kept by statistics, security or redirect plugins.

The third is often the largest single item. A plugin that logged every visit for years can leave a table bigger than the rest of the site, and deleting the plugin often leaves the table behind.

Revisions add up

WordPress saves a revision each time a page or post is updated, so changes can be undone. By default there is no limit.

A page edited once a year collects a few. A page edited every morning collects hundreds a year, each a complete copy of the page, and page builders make each copy larger.

Revisions are useful for the last few changes. Nobody restores the version of a page from three years ago.

Why it slows the site

Size alone does not always slow pages. A large table that nothing reads mostly costs disk space and backup time.

What slows every page is the part read on every request. WordPress loads a set of stored settings each time it builds a page, and plugins removed long ago can leave their settings in that set.

Temporary data swells the same table. Plugins store cached results meant to expire, but WordPress deletes an expired one only when something asks for it again. Many stay, and the lookup on every page has more to sift through.

And a plugin still logging every visit writes to the database on every page view, which slows the site most when it is busiest.

Why it breaks backups

Backups suffer first. A backup plugin on shared hosting has limited time and memory to export the database, and a two-gigabyte export can run out of both part-way through.

Some fail loudly. Others produce a file that looks complete and is not, which nobody discovers until they try to restore it.

Restoring is slower too. A site that should come back in minutes after a problem can take hours when most of what is being restored is old log entries.

A worked example

A fishmonger with one shop near a marina updates a fresh today page on its WordPress site most mornings, listing what came off the boats.

This summer the backup plugin's emails changed from completed to failed, and the host warned that the account was close to its disk limit. The database had reached two gigabytes.

Most of it was one table, left by a visitor statistics plugin removed two years earlier, which had recorded every page view the site had ever had.

The fresh today page had well over a thousand revisions, one for nearly every morning since the site launched. Tens of thousands of spam comments sat unapproved on an old recipe blog with comments still open.

After a full backup was downloaded and checked, the statistics table was removed, revisions were trimmed to the last few for each page, the spam was emptied and comments on old posts were closed.

The database came down to a small fraction of its size. Backups complete again, and the admin screens open without the pause the staff had stopped noticing.

Back up first, and check the backup

Every clean-up step deletes something, and some deletions cannot be undone without a backup.

Take a full export of the database before starting, download it, and check that the file is complete rather than cut off part-way.

Better still, rehearse the clean-up on a staging copy and confirm that nothing breaks.

It is the step people skip, and the one that matters when a table turns out to be in use.

Cleaning safely

Clear one kind of clutter, look at the site, then move to the next.

Revisions, spam and expired temporary data can be cleared by a reputable clean-up plugin, run once and then removed, or by whoever looks after the site.

Leftover tables need more care. Their names usually hint at the plugin that made them, but not always, and removing one that an active plugin uses will break it.

Match each table to a plugin, active or removed, before deleting anything.

Old stored settings need the same care, since some belong to the theme or to plugins still running.

Keeping it small

One line added to the configuration file, wp-config.php, caps revisions at a sensible number, such as five per page.

Close comments on old posts, or protect the comment form against spam, so the queue does not refill.

When removing a plugin, look for its own option to delete its data, and check afterwards that its tables went with it.

Set logging plugins to keep a few weeks of records, not years. And look at the database size every few months; many hosting control panels show it.

The counter-case

A small site with a few pages, edited occasionally and with comments switched off, may never need this. Its database stays small on its own.

Nor is size the only measure. One badly written plugin can make a small database slow, and a clean-up will not find that.

And a database clean-up is not a speed fix by itself; caching, hosting and page weight often matter more.

But any site that has run for years, been edited often or had plugins come and go should be checked, backed up and cleaned.

The clean-up in order

1. Check the database size in your hosting panel.
2. Take a full backup and download it.
3. Confirm the backup is complete.
4. Match every table to a plugin before deleting.
5. Clear revisions, spam and expired data.
6. Limit revisions in the configuration file.
7. Check the size again in three months.

Step three is the one that turns a risky job into a routine one. A backup that has never been checked is only a hope, and a clean-up is exactly when you find out.

Caching, which often matters more to speed than database size, has pitfalls of its own, covered in caching plugins fighting each other.

Frequently asked questions

West Coast Media Solutions Inc. provides web design, web development, hosting, digital marketing, and business consulting to organisations across Canada, drawing on more than twenty-five years in the field.