Post Revisions in WordPress: Why They Bloat the Database and How to Limit Them Safely

Every "Save Draft" click and every autosave creates a copy of the post in wp_posts with post_status='inherit' and post_type='revision'. WordPress keeps every revision forever by default. A post edited 80 times leaves 80 extra rows, each with its own wp_postmeta entries. A site with 500 posts can carry 30,000+ unnecessary revision rows.

Why this matters

First impact: database size. wp_posts and wp_postmeta grow 5-10x. Daily backups take longer (and cost more if your provider charges by size). Staging exports become unwieldy. Even queries that filter on post_status='publish' still walk revision rows internally before discarding them.

Second impact: slower queries. The composite index on wp_posts (post_type, post_status, post_date) loses efficiency as row counts balloon. Bulk admin actions - searching posts, filtering by category, paginating long lists - drag.

There is no reason to keep unlimited history. Five to ten recent revisions are plenty - they cover rollback, comparison, and recovery. A 2019 revision of a 2024 post is rarely useful.

How to detect

Quick diagnostic:

SELECT COUNT(*) AS revisions,
       (SELECT COUNT(*) FROM wp_posts WHERE post_type='post') AS posts
FROM wp_posts
WHERE post_type='revision';

A revision-to-post ratio above 10:1 is a problem. A healthy site sits at 3-5:1.

How to fix

Step 1: cap future revisions. Add to wp-config.php before /* That's all */:

define('WP_POST_REVISIONS', 5);    // keep only the 5 most recent
define('AUTOSAVE_INTERVAL', 300);   // autosave every 5 minutes instead of 60s

This does not delete existing revisions - it only caps growth from now on. Each new revision causes WordPress to discard the oldest if there are already five.

Step 2: back up the database before cleaning existing revisions. mysqldump or UpdraftPlus.

Step 3: clean existing revisions. Safest path is WP-CLI:

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

Without WP-CLI, raw SQL (carefully):

-- delete revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

-- clean orphan postmeta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- clean orphan term_relationships
DELETE tr FROM wp_term_relationships tr
LEFT JOIN wp_posts p ON tr.object_id = p.ID
WHERE p.ID IS NULL;

Step 4: optimize the tables:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_term_relationships;

InnoDB does not shrink the physical file on its own. Without OPTIMIZE the file stays the same size despite the deletes.

Common mistakes

Mistake one: setting WP_POST_REVISIONS = false. That disables history entirely. If you mis-edit a post, there is nothing to roll back to. Five is far better than zero. Mistake two: deleting active posts by accident. Revisions carry post_status = 'inherit'; trashed posts carry post_status = 'trash'. Do not mix them. Mistake three: skipping OPTIMIZE TABLE - backup file size stays inflated. Mistake four: leaning on "Optimize Database after Deleting Revisions" or similar plugins without a backup - plugins can fail mid-run.

Verifying the fix

Re-run the count query - revisions should be 5 per active post at most. Database size via SELECT SUM(data_length+index_length)/1024/1024 FROM information_schema.tables WHERE table_schema=DATABASE(); should drop 30-60%. UpdraftPlus backup files shrink accordingly.

Confirm new revisions still get created. Edit a post, refresh, and check the "Revisions" panel - 1-5 entries should appear.