Michael Adams, Quantum Bug Creator at Automattic once said:
“Revisions are stored in the posts table. I don’t yet know what kind of impact that will have on post queries. We’re going to turn the switch to “on” on WordPress.com sometime
soonish and we’ll have a better idea about what kind of damage, if
any, it does.”
And sometime later they did turn it on, and WordPress.com now stores the last 25 revisions for each post on a blog. So, yeah, you can deal with revisions.
The problem with post revisions is that your database size can grow really big (depending on the number of revisions created) and it in turn adds to MySQL’s processing burden.
Nevertheless, you can deal with it. Enable page and database-level caching — W3 Total Cache does this, and if your website is served from a single server, try WP Super Cache. And then there are various web application accelerators like Varnish cache. Websites with tens of thousands of posts have to do sharding, slave and master replication of database and stuff anyway.
Even then, revisions on (very?) old posts is a waste of resources, IMO. As for me, I delete revisions of published posts from time to time (every 2-3 days).
Deleting Revisions:
Backup, backup, backup first. Always!
If you want to use a plugin to delete revisions and optimize database, and keep things simple, try Better Delete Revisions. You might also want to take a look at Revision Control.
Or you can do it manually using SQL as shown below.
(1A) Delete revisions of all posts and pages on your WordPress blog (source):
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type="revision";
(1B) If you use the Links feature in WordPress (Blogroll, etc), DON’T use the aforementioned SQL query. Use this instead (read why?):
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type="revision"
AND d.taxonomy != 'link_category';
(2) Delete auto-drafts:
DELETE FROM wp_posts WHERE post_status="auto-draft";
(3) Optimize your database tables:
OPTIMIZE TABLE `wp_commentmeta`, `wp_comments`, `wp_links`, `wp_options`, `wp_postmeta`, `wp_posts`, `wp_terms`, `wp_term_relationships`, `wp_term_taxonomy`, `wp_usermeta`, `wp_users`
NOTE: In the SQL queries listed above, don’t forget to replace wp_ with your database table prefix.